If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Maintaining one-to-many relationships between main forms and subforms
I want to open my subforms from my main forms by using command buttons. My
command buttons work well and successfully open the appropriate subforms, however, the parent-child or one-to-many relationship is not maintained as it is if I work off of a nested view where both parent and child are visible at the same time. For example I want to use an autonumber as my primary key and then use a number with the same field name as my foreign key in the subform. Thus, when I click the command button to open the subform, I would like the primary key's autonumber to populate automatically in the subform's foreign key field. How do I accomplish this? please help...I am stuck. |
#2
|
|||
|
|||
Maintaining one-to-many relationships between main forms and subforms
SubForms are displayed on MainForms with the use of a SubFormControl that has
LinkChild/MasterFields properties that does this work for you behind the scenes. In order to accomplish the same effect with a separate form, you will need to pass the ForeignKey to the other form and then use maybe the Dirty Event to populate the FK field. tm_6187 wrote: I want to open my subforms from my main forms by using command buttons. My command buttons work well and successfully open the appropriate subforms, however, the parent-child or one-to-many relationship is not maintained as it is if I work off of a nested view where both parent and child are visible at the same time. For example I want to use an autonumber as my primary key and then use a number with the same field name as my foreign key in the subform. Thus, when I click the command button to open the subform, I would like the primary key's autonumber to populate automatically in the subform's foreign key field. How do I accomplish this? please help...I am stuck. -- RuralGuy (RG for short) aka Allan Bunch MS Access MVP - acXP WinXP Pro Please post back to this forum so all may benefit. Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200805/1 |
#3
|
|||
|
|||
Maintaining one-to-many relationships between main forms andsubforms
Another way:
1) Mainform 1 - the one you currently have with the buttons A) as part of the code for the button put if me.dirty = true then me.dirty = false This will save the record. 2) Create a second "main Form" that has the same table/query as the record source. The only field necessary is that recordID field 3) Place the subform that you already have designed on this second mainform and have it use up all the space (make MainForm2 just large enough to hold the subform) 4) establish the parent/child relationship with this mainform2 ID information. This will initiate all of the automatic logic of parent child forms. 5) Go back to the calling buttons and change the form calls to include (using your field names) the criteria of "[ID] = " & me.ID Ron. |
#4
|
|||
|
|||
Maintaining one-to-many relationships between main forms andsubforms
Ron,
Thanks for your help. Steps 1-4 were applied. Works great so far except step 5. I do not know what you mean by "form calls". I don't know where to enter that. Right now, parent-child relationship is maintained while working on the subform, but when I added a second or new record in the main and then went to the subform to enter records it did not populate the ID for the second record of the new form. EX... My mainform primary key is CaseID, which is the foreign key on my subform. The primary key is autonumber and foreign key is number. Thus, when I entered the first record on the main - the CaseID field assigned "1" and when I clicked on the control button for the subform (steps 1-4 were applied) CaseID on subform populated "1". When I entered a new record on the mainform CaseID field assigned "2" but when I clicked the control button for the subform CaseID remained "1" and did not change to "2". Probably because I was not smart enough to accomplish your 5th step? Ron2006 wrote: Another way: 1) Mainform 1 - the one you currently have with the buttons A) as part of the code for the button put if me.dirty = true then me.dirty = false This will save the record. 2) Create a second "main Form" that has the same table/query as the record source. The only field necessary is that recordID field 3) Place the subform that you already have designed on this second mainform and have it use up all the space (make MainForm2 just large enough to hold the subform) 4) establish the parent/child relationship with this mainform2 ID information. This will initiate all of the automatic logic of parent child forms. 5) Go back to the calling buttons and change the form calls to include (using your field names) the criteria of "[ID] = " & me.ID Ron. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...forms/200805/1 |
#5
|
|||
|
|||
Maintaining one-to-many relationships between main forms andsubforms
5) Go back to the calling buttons and change the form calls to
include (using your field names) the criteria of "[ID] = " & me.ID In the ONClick event of the button to call the other form you will find something like this Private Sub Command21_Click() On Error GoTo Err_Command21_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "Form2" DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_Command21_Click: Exit Sub Err_Command21_Click: MsgBox err.Description Resume Exit_Command21_Click End Sub ==================== What you want to do is to add the criteria.. so it will become something like this. Private Sub Command21_Click() On Error GoTo Err_Command21_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "Form2" stLinkCriteria = "[ID]=" & Me.ID DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_Command21_Click: Exit Sub Err_Command21_Click: MsgBox err.Description Resume Exit_Command21_Click End Sub ============================ Without that extra little bit of criteria, you were simply looking at ALL of the records in the subform. With the criteria you will only get the records that belong to the main form. You almost had it. But you will NEVER forget it once you see it working....... Ron |
Thread Tools | |
Display Modes | |
|
|