By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,854 Members | 869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,854 IT Pros & Developers. It's quick & easy.

Changing a form's recordsource within a subform

Megalog
Expert 100+
P: 378
Hey guys.. my turn to ask a question:

I'm having a weird issue with a form I've reworked. This form has a combo box, which when used is changing the recordsource of a subform. This subform has no Master/Child relationship set to the main form. Specifically the problem seems to be that everytime the recordsource of the subform is changed, the subform object itself tries to set a default Master/Child value (both fields fill with the "ID" value). There is no real relationship between the forms, so it screws up the subform and the results are not correct.

I've found a workaround (lines 11 & 12), by clearing out the Master/Child values everytime I send a new recordsource, but it feels like a cheap work around and I'm wondering if there's something simple I've overlooked, or if this is a normal occurance.

Here's the code I'm using. This is called whenever the combo box on the main form has a value change, or when someone clicks the 'Reset' button next to it, which also set's the combo box's value to "All".

Main Form name: "Post-Submission QA"
combo box name: "cboDirection"
subform/subreport container name: "subPost_QA_Set_Mgmt"
subform name: "Post_QA_Set_Mgmt"

Expand|Select|Wrap|Line Numbers
  1. Private Sub subfrmRequery()
  2.     If Me.cboDirection.Value = "All" Then
  3.         Forms("Post-Submission QA").Controls("subPost_QA_Set_Mgmt").Form.RecordSource = _
  4.         "SELECT * FROM [Post_QA_Set_Mgmt] ORDER BY [Post_QA_Set_Mgmt].[ID]; "
  5.     Else
  6.         Forms("Post-Submission QA").Controls("subPost_QA_Set_Mgmt").Form.RecordSource = _
  7.         "SELECT [Post_QA_Set_Mgmt].*, [Post_QA_Set_Mgmt].[Direction] as Filter FROM [Post_QA_Set_Mgmt] " & _
  8.         "WHERE ((([Post_QA_Set_Mgmt].[Direction])='" & Me.cboDirection.Value & "')) ORDER BY [Post_QA_Set_Mgmt].[ID]; "
  9.     End If
  10.         Me.subPost_QA_Set_Mgmt.LinkChildFields = ""
  11.         Me.subPost_QA_Set_Mgmt.LinkMasterFields = ""
  12.         Forms("Post-Submission QA").Controls("subPost_QA_Set_Mgmt").Form.Requery
  13. End Sub
Again the above code works, but I'm wondering why it's behaving like this.. I'm also concerned that it may not work on a runtime client (which I have yet to test today).
Jun 20 '08 #1
Share this Question
Share on Google+
8 Replies


Expert Mod 2.5K+
P: 2,545
Hi Megalog. I've never known Access to set a default value for parent/child field links between a form and a subform, so I wonder if there is another explanation entirely for this behaviour?

You could check for certain whether or not there are existing links in place or created for your form by adding the following debug code at the top of your requery code (before the IF), to show you as you run the routine what the defaults are on entry:

Expand|Select|Wrap|Line Numbers
  1. Dim frmSubform as Form
  2. Set frmSubform = Forms("Post-Submission QA").Controls("subPost_QA_Set_Mgmt").Form
  3. With frmSubform
  4.     msgbox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
  5. End With
If, as I would expect, there are no defaults, there is some other problem involved. If there are defaults, and you have not set them in code somewhere else, it will be interesting to find the source...

-Stewart
Jun 23 '08 #2

ADezii
Expert 5K+
P: 8,623
A wild guess would be that you have a 1 to MANY Relationship between the Tables comprising the Record Sources for the Main and Sub-Forms and that the Linkage is coerced or automatically created when you created the Sub-Form. I told you it was wild (LOL).
Jun 23 '08 #3

Megalog
Expert 100+
P: 378
Hrm well I had some time to revisit this, and so far nothing has made any sense. It just plain defaults the Master/Child relationship everytime the form's recordsource is modified.

I made a new db just to test out the scenario, stripped down to about as simple as it can be, and the problem still persists.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command6_Click()
  2. Dim frmSubform As Control
  3. Dim SQL As String
  4.  
  5. SQL = "SELECT * from [TestSub];"
  6.  
  7. Set frmSubform = Forms("frmTestMain").Controls("subTestSub")
  8.  
  9. With frmSubform
  10.     MsgBox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
  11. End With
  12.  
  13. MsgBox "Applying new recordsource:" & vbCrLf & SQL
  14.  
  15. frmSubform.Form.RecordSource = SQL
  16.  
  17. With frmSubform
  18.     MsgBox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
  19. End With
  20.  
  21. End Sub
I've got 2 buttons, one sends the above code, and the second just clears out the master/child data.

As you can see, a message box shows the master/Child data, which is empty.. then it applies "SELECT * from [TestSub];" to the subform. Then another message box shows the master/child data again, which has now defaulted to "ID"/"ID".

I've attached the sample db, in 2007 format, if anyone wants to figure out my fundamental flaw...
Attached Files
File Type: zip Subform Test.zip (68.8 KB, 102 views)
Jun 23 '08 #4

ADezii
Expert 5K+
P: 8,623
Hrm well I had some time to revisit this, and so far nothing has made any sense. It just plain defaults the Master/Child relationship everytime the form's recordsource is modified.

I made a new db just to test out the scenario, stripped down to about as simple as it can be, and the problem still persists.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command6_Click()
  2. Dim frmSubform As Control
  3. Dim SQL As String
  4.  
  5. SQL = "SELECT * from [TestSub];"
  6.  
  7. Set frmSubform = Forms("frmTestMain").Controls("subTestSub")
  8.  
  9. With frmSubform
  10.     MsgBox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
  11. End With
  12.  
  13. MsgBox "Applying new recordsource:" & vbCrLf & SQL
  14.  
  15. frmSubform.Form.RecordSource = SQL
  16.  
  17. With frmSubform
  18.     MsgBox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
  19. End With
  20.  
  21. End Sub
I've got 2 buttons, one sends the above code, and the second just clears out the master/child data.

As you can see, a message box shows the master/Child data, which is empty.. then it applies "SELECT * from [TestSub];" to the subform. Then another message box shows the master/child data again, which has now defaulted to "ID"/"ID".

I've attached the sample db, in 2007 format, if anyone wants to figure out my fundamental flaw...
Hello Megalog, any chance of converting the DB to 2002-2003 Format? Your dilemma has also peaked my interest, and I'd love to take a look at the DB.

P.S. - Behind the times and don't have Access 2007 as of yet.
Jun 24 '08 #5

Megalog
Expert 100+
P: 378
Hello Megalog, any chance of converting the DB to 2002-2003 Format? Your dilemma has also peaked my interest, and I'd love to take a look at the DB.
Sure, both versions are in this zip file.
Attached Files
File Type: zip Subform Test.zip (96.2 KB, 80 views)
Jun 24 '08 #6

ADezii
Expert 5K+
P: 8,623
Sure, both versions are in this zip file.
  1. Change the Name of the [ID] AutoNumber, Primary Key Field in the Table TestMain to 'anything' but ID.
  2. Change the ControlSource of the ID Text Box on frmTestMain to the newly named AutoNumber, Primary Key Field in Table TestMain.
  3. You should now be fine as indicated by the adjusted Attachment.
Jun 24 '08 #7

Megalog
Expert 100+
P: 378
So if you use the default 'ID' in both tables, you're going to get defaulted on the master/child properties everytime?

I can see how it's ok to do that once, when you create the object itself on the form.. but i think Access needs to quit assuming that afterwards ;)
Jun 24 '08 #8

ADezii
Expert 5K+
P: 8,623
So if you use the default 'ID' in both tables, you're going to get defaulted on the master/child properties everytime?

I can see how it's ok to do that once, when you create the object itself on the form.. but i think Access needs to quit assuming that afterwards ;)
Expand|Select|Wrap|Line Numbers
  1. So if you use the default 'ID' in both tables, you're going to get defaulted on the master/child properties everytime?
Yes, it would seem so, as I had an inclination toward his line of thought in Post #3. In any event, it seems as though the problem has been solved, which is all that really matters.
Jun 24 '08 #9

Post your reply

Sign in to post your reply or Sign up for a free account.