Querying A SubForm With A MANY To MANY Relationship With MainForm
Jul 5, 2005
Hi,
I am assuming Access cannot handle Many to Many relationships very well.
I have an Account form ("MainForm") with an embedded SubForm that displays only the Account Address with suite number, street number, street name, street type. Account to Address is a Many to Many relationship. My Account form's source is tblAccount, and the SubForm is built off of a query of 3 tables -- Querying Account, LinkTable, and Address table. The link between the SubForm and the "MainForm" is done via AccountID.
My problem is, when I query for an Address (say entering a street number of 25), I want to get back all the addresses with street number of 25 AND the corresponding Accounts that have street number 25 only
What it does is return the addresses with Street Number 25 and ALL the accounts regardless of what their addresses are!
Is there a way to do this?? Can I design a form without using a SubForm in this case, ie, display the account and its many addresses? Or does Access Forms not support Many to Many Relationships? I'm thinking the link table is causing the problems here.
Please advise -- this "simple" project is becoming quite the headache! :eek:
"edit" -- I also notice this problem with a 1 to Many relationship (the "many" portion being the subform)
Thanks for any help,
Mike
View Replies
ADVERTISEMENT
May 1, 2005
I have a main form with a control that can take one of three values. And I would like to add a subform that work as follows.
Selecting each value should change what the subform displays like this.
Control Subform
A Select * from TableA where Key = "A"
B Select * from TableB where Key = "B"
C Disable subform somehow because it doesn't apply
Could someone please provide some advice on how I could do this.
Thanks
Fearless
View 1 Replies
View Related
Feb 8, 2006
Hi,
I had a text box on a main form linked to a text box on a sub form using the following control source =Forms!Form![frm_Doctors_details subform].[Form].[Title]. This was working, however I had to play around with the subform alot and now the link isn't working. All i get in the field is #name?. Nothing has changed on the subform textboxs, just that I added a primary key to assist in the linking action bewteen forms. I can't for the life of me get it back to what it was . any ideas??
Thanks in advance
View 4 Replies
View Related
Dec 13, 2006
Hi,
I want to open a subform programatically with a 'linkcriteria' filter.
However, What i am experiencing is just the subform opening without the main form.
How can I open the subform on the appropriate record and the mainform as well, as one??
Thanks
View 3 Replies
View Related
Mar 9, 2005
Hi,
i'm working in access 97 and am having difficulties pulling a subtotal or referencing any fields on my subform on my main form. I've searched the forum and help and tried to apply the info provided, but i still can't seem to get it to work.
as far as i can tell this should work:
=[MySubForm].[Form]![TxtTotalStaffNumbers]
but i'm still getting the dreaded #Name? error.
anyone got any clues as to what i'm doing wrong?
what about the case where the subtotal i'm referencing is a calculation such as =Sum([Quantity]*[FacilityArea]). Should i still be able to reference it on my main form by pointing to the name of the field?
Thanks very much!
View 3 Replies
View Related
Oct 8, 2006
Hi,
I have a query (SelectedData_Query) , on which a form (SelectedDataQuery_SubForm) in datasheetview is based, which is placed on a form (NAWInvoerForm) where customer adress data is put in. When someone clicks new case button on the NAWInvoer_Form, another form (CaseDateTimeInfo_Form) is opened, data is entered, but when the "CaseDateTimeInfo_Form" form is closed, the data in the subform in datasheetview on the NAWInvoerForm is not yet refreshed. So I tried the following:
Private Sub Form_Close()
'------------------------------------------------------------
' RefreshNAWInvoerForm
'
'------------------------------------------------------------
On Error GoTo RefreshNAWInvoerForm_Err
DoCmd.OpenForm "KlantNAW_InvoerForm", acNormal, "", "", , acNormal
DoCmd.RunCommand acCmdRefresh
RefreshNAWInvoerForm_Exit:
Exit Sub
RefreshNAWInvoerForm_Err:
MsgBox Error$
Resume RefreshNAWInvoerForm_Exit
End Sub
Which works just fine. After the form is closed, the name, adress and cases overview form is reopened and refreshed, thus the case that has just been created is shown nicely in the subform which is based on a query.
But it is starting to become bothersome, sometimes when I close the form in which I create a new case, I don't want to have the other form (NAWInvoerForm) reopening again.
So how can i make it so that when I close the form only the data in the "NAWInvoer_Form" is refreshed, without having to reopen the form again. I mean, in the background, the form is still open anyway. It just shouldn't keep popping up everytime.
It can't be hard, but I can't see it. I'm new at this. I have found some similar topics on this forum, but none which helps me out enough.
It should be something like:
On "thisandthatevent" do a "thisandthatcommand" on "thisandthatsubform" in "thisandthatform"
If I know how the syntax of that is done, it would help me out with some other problems I see coming in the near future. I've looked at, and tried some things based on this link: http://www.mvps.org/access/forms/frm0031.htm
But without success.
Can anyone help?
View 4 Replies
View Related
Jun 25, 2005
May be this is easy for your guys. I want to freeze or lock the mainform data fields while allowing users to input, edit, delete data on a subform. I tried to change mainform properties so it can not edit, add or delete data on mainform. By doing so, I cannot do anything with subform, it seem read only as well.
Thank you for help
Peter
View 1 Replies
View Related
Jul 28, 2014
My Database is really simple, it contains 1 table (called Querys) and a form called Dashboard.
In my main form (Dashboard) I have inserted a sub form which shows all the records in my table (called Querys) when I use my main form and add a record this sub table can then be refreshed and it shows all my records.
This part is fine,What I would like to do is be able to select a record in my sub form and it show the record data in my main form. This would really speed up the navigation process as the database gets bigger.
View 3 Replies
View Related
Dec 13, 2013
I have a form which shows training events - these can take place over one or several days, and can be run by one trainer or several, so I have my basic Events Info in one table (EventID, EventType, Location, that kind of thing) and the 'Jobs' in a separate table (EventID, JobDate, TrainerID, etc)
The problem I'm having is that I want the List of Events to be sorted in order of their start date... which is on the subform, not the main form.
How can I go about it - and still leave both the main and subforms fully editable?
View 12 Replies
View Related
Jul 25, 2013
I'm having issues with this one.
Table name is CompDates
Subform is ViewByMarket
Start date is Text13 and End Date is Text15.
I want it to filter out any matches between the 7 fields. Here is my code:
Code:
Private Sub Command19_Click()
Dim strFilter As Variant, _
strSDate As String, _
strEDate As String
'check Text13
Text13.SetFocus 'set focus to Text13 to be checked
[code].....
It will only filter for Build Date 1 and not for any of the other fields.
View 1 Replies
View Related
Jul 22, 2014
In open Mainform event
Forms!Mainform.Form!SubForm!controlname.columnhidd en = false doesn't work. Ms Access can't find "subform"
On load and open events in subform doesn't work with columnhidden. (no reaction)
View 7 Replies
View Related
Nov 25, 2014
I have a Mainform [FrmReconcileMain] and it contains a Subform [FrmReconcilesub]
What I'm trying to is, on the Main form type in a statement date in textbox [TxtStatementDate].
I have a checkbox on my subform [ReconciledYN], along with a textbox [TxtReconcileDateSub]. when I click the checkbox, it simply pulls the date from the mainform and populates the date in the subform.
I've even tried experimenting on a simple form (with no subform) to see whats going on, but still I can't get it to work, even on a simple event such as this...
If [yourcheckboxname] = -1 Then [controlnametoupdate] = date() Else [controlnametoupdate] = ""
View 13 Replies
View Related
Dec 16, 2013
I'm having a problem with the syntax of a recordset of a Datasheet inside a subform which is also inside a Main Form.
Main Form - frm_1_0_LMS
Subform - frm_1_4_0_TeamApprovals
Subform(Datasheet) - frm_1_4_1_TeamApprovalsList
Here is my code:
Code:
Dim rs As DAO.Recordset
Set rs = Forms!frm_1_0_LMS.frm_1_4_0_TeamApprovals.frm_1_4_1_TeamApprovalsList.Form.Recordset
If Not (rs.EOF And rs.BOF) Then
Forms!frm_1_4_2_ApproveDeclineUserLeave.Controls("lblFiledDateLeave").Caption = rs!Leave_Date
End If
I am getting this error: Object doesn't support this property or method
View 1 Replies
View Related
Nov 4, 2005
I need to take a value from a subform and use it in a query but I don't seem to be able to come up with the correct criteria in the query....
I have so far tried:
[Forms].[frm_Matching].[SubMatching].[txtSingleFixedPayer]
[Forms].[frm_Matching].[SubMatching].[form].[txtSingleFixedPayer]
[Forms].[sub_Matching_single].[txtSingleFixedPayer]
[Forms].[sub_Matching_single].[form].[txtSingleFixedPayer]
[Forms].[frm_matching].[sub_matching_single].[txtsinglefixedpayer]
[Forms].[frm_matching].[sub_matching_single].[Form].[txtsinglefixedpayer]
And probably a load of other versions of the above.... can anyone help out possibly?
The following pulls back the value in code but isn't accepted in the query as a valid criteria:
Forms("frm_Matching").[SubMatching].[Form].[txtSingleFixedPayer]
View 1 Replies
View Related
Mar 1, 2007
Hi, I'm having some issues querying a sub form.
I'm using a combobox (in a subform) which supplies some search criteria. I'm using the query Like [Forms]![Employee]![Course Subform].[Form].[Combo12] & "*" but its simply not working.
Can anyone see any glaringly obvious mistakes?
View 8 Replies
View Related
Nov 2, 2005
How can I get some mainform fields' data to be the first entry in a subform? From the mainform, I would like the Head of Household name and date of birth to be carried over to the first entry in the HouseholdMembers subform.
The two forms are tied to separate tables. For each household member I need to be able to enter full legal name, date of birth and some additional pieces of information such as income and source of income.
What I'd like to avoid is making the users enter the Head of Household's name and date of birth twice. Is there a way to carry that information over to the subform?
Thank you, Charlotte
View 14 Replies
View Related
Sep 25, 2006
Ohh Stress. Need Help....I have a mainform that contained two subforms in it. MainForm called "Employee", the two subform called "vac", and "Prevac". There is a field in my mainform called "Hours", that I'd like to use to Add with a field in Vac subform called "VacA" and a field in Prevac called "FHours". I'd like to display the total (sum of all three field on the same mainform. Is there anyone have any idea? Thanks in advance...
View 3 Replies
View Related
Jan 9, 2006
Would like to be able to update a text field on the main form with information from a dropdown box on a subform when the subform data equals a certain value. Would like the main form field to update as soon as the subform information is selected from the dropdown box.
Example (sorry don't have code for it)
Mainform field = Mytext
Subform dropdown field = Newtext1, or Newtext2, or Newtext3
If Newtext2 is selected on subform, I would like Mytext to immediatly show the value in this field (mainform/mytext). Do not want either Newtext1, or 3 to ever show in Mytext but want it to remain in subform field
It can either be on Lostfocus from subform or onupdate from subform, etc.
Whatever works best/easiest
Thanks for the help in advance
View 1 Replies
View Related
Jan 9, 2006
Would like to be able to update a text field on the main form with information from a dropdown box on a subform when the subform data equals a certain value. Would like the main form field to update as soon as the subform information is selected from the dropdown box.
Example (sorry don't have code for it)
Mainform field = Mytext
Subform dropdown field = Newtext1, or Newtext2, or Newtext3
If Newtext2 is selected on subform, I would like Mytext to immediatly show the value in this field (mainform/mytext). Do not want either Newtext1, or 3 to ever show in Mytext but want it to remain in subform field
It can either be on Lostfocus from subform or onupdate from subform, etc.
Whatever works best/easiest
Thanks for the help in advance
View 1 Replies
View Related
Jan 25, 2008
Hi,
I have a main form (stud_details) also I have a subform called (pay_det_sub).The subform is displayed on the main form.
I want to create a relationship between the stud_details and pay_det_sub, with the aid of a field on the stud_details called numhus (which is the primary key).
So that when a student with a specific numhus value is displayed it brings about the payers details on the subform.
What's the best way of doing it??? Is it necessary to have numhus value stored also in the subform table to create a relationship....
Thanks
View 3 Replies
View Related
May 7, 2006
Hi. can anyone help me. I've spent the last two hours getting no where.
I have a tbl_Enquiry,tbl_EnquiryType and tbl_Questions
On the main form (frm_Enquiry), depending on which enquiry type is chosen, will depend on which questions are displayed.
this is a many to many relationship so I have a junction table, called tbl_TypeQuestionJunction.
The questions are displayed in a sub from when the enquiry type is selected, and this all works fine.
However, I then need to add a field where the user can write thier answers to the questions.
for this I have a table tbl_QAJunction with the fields
EnquiryID
QuestionID and
Answer
How can I get my subform to display the correct questions when the enquiry type is selected, with an answer box next to it and then pick up the EnquiryID, QuestionID and the answer and add them to the tbl_QAJunction.
I just can't get a query to work.
I hope this makes sense
This has really bugged me and is stopping me completing my database.
Am i going abotu this all wrong? If anyone can help, I'd be grateful
I've attached a cut down version of the DB so you can see what i'm on about
Thanks
View 7 Replies
View Related
Feb 27, 2006
Hey guy im kinda new to this program so excuse me if my question is simplistic:I built a form for making Purchase orders. In the main form...a user enters a bunch of text/num info (name, address, ect)into the table. After that i have it tab to a subform..where a user can add items by line (as many as they like). All good till here: Once i go to click out of the subform...i get message "You cannot add or change a record b/c a related record is required in table 'UCT_Req'"My relationship has a 'one-to-many' link (the req number to the many line items). I have auto-numbers counting up in each table.My main problem seems to be attaching the entered line items to a specific record in the main Purchase order form...and then opening a fresh form afterwards. Any hints?Thanks folksSTeve
View 2 Replies
View Related
Mar 28, 2014
I have a Workorder Form that holds all the fields that apply to the entire project (payment, customer, etc).
Then I have a subform for Stock. This allows there to be several different stock items (and production information relevant to that stock) assigned to a Workorder.
I have successfully linked the two so that when a Workorder is opened, it shows any related Stock in the subform that has already been created.
The problem is new stock entries. I need it to only allow Stock additions to the current open related Workorder.
I currently have it where you can type in the WorkorderID (autonumber) on the Stock subform new entry and it will work. But I do not trust employees to type in the correct number every time they need to add stock to a Workorder. I can't release it this way.
I mean, the whole point is for the database to promote as little error as possible, right?
View 5 Replies
View Related
Feb 20, 2006
I am having trouble establishing a subform connection (relationship) to a main form. I am using Access 2003.
I am creating a medical record database where I already have one form / subform relationship established that works. However, I created another form where I want to indicate the Physician(s) information within the main form and his specialties within a subform. I need to use the subform because one physician can have more than one specialty.
My fields within two tables are:
Doctor (Table)
ID
Doctor First Name (Key)
Doctor Last Name (Key)
Doctor Address
Doctor City
Doctor State
Specialty (Related Field)
Doctor Specialty (Table)
ID (Key)
Doctor First Name
Doctor Last Name
Specialty (Related Field)
The problem
I can’t establish the relationship.:confused: Also, when I look at my relation diagram – does each table have to relate to another? Can I have two separate relationships indicated?
View 5 Replies
View Related
Mar 27, 2014
Is it possible to create a relationship between subform and a table?
View 1 Replies
View Related
Apr 3, 2014
I have a "Main" form called frmProjects that sports multiple tabs. One of those tabs is labelled "Milestones", into which I inserted a datasheet subform called frmSubMilestones. The table that feeds the subform is tblMilestones. I established the parent / child relationship between frmProject and frmSubMilestones, and everything is working just fine...
To summarize: frmProject as main form -> Milestones Tab on main form -> frmSubMilestones as datasheet subform on the Milestones Tab (there are other tabs that are not subforms).
I have been asked, for each milestone in the frmSubMilestones subform's datasheet, to capture the number of days spent by employees on a monthly basis. The Milestones table and the PersonDays table have a primary key and a foreign key that are similar.
To summarize: frmSubMilestones as subform -> txtAggregatePersonDays as calculated Textbox fetching data from tblPersonDays -> OnClick event -> FormLoad of frmPersonDays as datasheet PopUp form.
DoCmd.FormLoad is supplied with the usual parameters to make the PopUp form appear, filtered on the currently highlighted milestone. Everything works fine so far... well almost.
Problem 1: When I introduce new rows in the frmPersonsDays datasheet, everything seems fine at the surface, BUT the primary key of the calling form (i.e. frmSubMilestones) does not appear in the tblPersonDays table; this is normal because there is no form-based parent / child relationship between the two forms. As one might expect, I am trying, from frmPersonDays, to get the primary key value from the "parent" frmSubMilestones using the Forms collection. No dice. I surfed and surfed, tried the bang and the dot operators, drank scotch, but nothing worked. I had to add foreing keys manually in tblPersonDays to further my tests. I can't figure out how to reach any control on the calling form, which is a subform.
Problem 2: The calculated txtAggregatePersonDays on the frmSubMilestones works wonders for existing data in tblPersonDays. However, when I introduce new rows in the PopUp form, I also realized that the calculated Textbox in frmSubMilestones is not updated when I close the frmPersonDays PopUp. I have to close the PopUp form and move the cursor in the Main form (frmProjects) to refresh its associated milestones.
View 3 Replies
View Related
