ms Access: Switching between subform recordsource when changing records-CodePudding

I have a main form and a subform on it. I want the recordsource of the subform to change depending on the value of an ID field of the main form. This works when I do it in the Form_Load event of the parent Form. However, I also want it to update when the user switches to a new record on the main form (single form) while it is open.

The code I have atm is the followng:

Private Sub Form_Load()
'***************************************************************************
'Purpose: Switch subform recordsource based on recipe ID
'Inputs: None
'Outputs: None
'***************************************************************************

    Dim strRecipeID As String
    Dim strSub1QueryName As String
    
    strRecipeID = Forms![frmMainForm]![RecipeID] 'get RecipeID
    strSub1QueryName = DLookup("[QueryName]", "tblRecipeQueries", [RecipeID] = strRecipeID) 'Get name of query
    Forms!frmMainForm!frmSub1Sub.Form.RecordSource = MakeSqlString(strSub1QueryName) ' Set Subform1 recordsource
    
End Sub

MakeSQLString() is a function that gets the SQL SELECT statement as a string from a given access query. I tried the change event, the Click event and the Current event and requerying the subform after that but none have any impact on contents of the subform (the recordsource does not change) but stays the same one that was selected when the form loaded. I'm not sure which other events could work for this or from where I can execute the change in recordsource most effectively, maybe the subform instead? Maybe it has something to do with the subform loading before the parent form? I'm not sure, might be a rooky mistake.

I appreciate any help. BTW, the fields on the subform stay the same, so changing the recordsource just updates the calculations that are made in the query to get the values for those fields. The query names are stored in a table in my db. Again, It works in the Load event of the main form I just can#t get it to update on the fly while the main form is open and the user navigates to a new RecipeID on said main form.

CodePudding user response：

In your DLookup you are not enclosing the where portion in quotes. You have:

DLookup("[QueryName]", "tblRecipeQueries", [RecipeID] = m_selectedId)

But it should be:

DLookup("[QueryName]", "tblRecipeQueries", "RecipeID =" & m_selectedId)

That should take care of your issue.