Changing a form's recordsource within a subform

[Megalog]

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: "cboDirecti on"
subform/subreport container name: "subPost_QA_Set _Mgmt"
subform name: "Post_QA_Set_Mg mt"

Private Sub subfrmRequery()
    If Me.cboDirection.Value = "All" Then
        Forms("Post-Submission QA").Controls("subPost_QA_Set_Mgmt").Form.RecordSource = _
        "SELECT * FROM [Post_QA_Set_Mgmt] ORDER BY [Post_QA_Set_Mgmt].[ID]; "
    Else
        Forms("Post-Submission QA").Controls("subPost_QA_Set_Mgmt").Form.RecordSource = _
        "SELECT [Post_QA_Set_Mgmt].*, [Post_QA_Set_Mgmt].[Direction] as Filter FROM [Post_QA_Set_Mgmt] " & _
        "WHERE ((([Post_QA_Set_Mgmt].[Direction])='" & Me.cboDirection.Value & "')) ORDER BY [Post_QA_Set_Mgmt].[ID]; "
    End If
        Me.subPost_QA_Set_Mgmt.LinkChildFields = ""
        Me.subPost_QA_Set_Mgmt.LinkMasterFields = ""
        Forms("Post-Submission QA").Controls("subPost_QA_Set_Mgmt").Form.Requery
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).

[Stewart Ross]

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:

Dim frmSubform as Form
Set frmSubform = Forms("Post-Submission QA").Controls("subPost_QA_Set_Mgmt").Form
With frmSubform
	msgbox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
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

[ADezii]

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).

[Megalog]

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.

Private Sub Command6_Click()
Dim frmSubform As Control
Dim SQL As String

SQL = "SELECT * from [TestSub];"

Set frmSubform = Forms("frmTestMain").Controls("subTestSub")

With frmSubform
    MsgBox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
End With

MsgBox "Applying new recordsource:" & vbCrLf & SQL

frmSubform.Form.RecordSource = SQL

With frmSubform
    MsgBox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
End With

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...

[ADezii]

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.

Private Sub Command6_Click()
Dim frmSubform As Control
Dim SQL As String

SQL = "SELECT * from [TestSub];"

Set frmSubform = Forms("frmTestMain").Controls("subTestSub")

With frmSubform
    MsgBox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
End With

MsgBox "Applying new recordsource:" & vbCrLf & SQL

frmSubform.Form.RecordSource = SQL

With frmSubform
    MsgBox ">> Parent Links: " & .LinkMasterFields & " >> Child Links: " & .LinkChildFields
End With

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.

[Megalog]

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.

[ADezii]

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.

[Megalog]

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 ;)

[ADezii]

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 ;)

[CODE=vb]So if you use the default 'ID' in both tables, you're going to get defaulted on the master/child properties everytime?[/CODE]
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.