Subform query only retrieving records for one parent form dependent item

[jase7678]

Hello,

I'm confused with my latest issue in Access 2013 because at one point in time it's worked exactly as intended. I'm putting together a pretty large database application so finish one piece once it's working to work on something else; it worked when I finished developing it but now that I've returned it no longer works as designed.

I have a form that contains 2 subforms:

1. tblItemTypesItemColorJunction subform1
2. tblItemDescription subform

#1 is populated with a query via the Property Sheet | Form | Data | Record Source. The Recordset Type of each subform is Dynaset and I'm sure all other properties are the defaults.

Capture0.jpg
Capture.jpg

This form populates just fine with the entire contents of tblItemTypesItemColorJunction.

The idea is that when you select a record in subform #1 then subform #2 is populated with those records that belong to this. In my database every item in subform #1 has at least one record that should appear in #2. However, only the 1st item in #1 populates #2 and I don't understand why.

Initially I had a query in Property Sheet | Form | Data | Record Source that used the ItemTypesItemColorJunctionID from #1 to pull the correct record but replaced this with VBA code in the OnCurrent event of #1 as follows in an attempt to debug:

Private Sub Form_Current()
    'Confirm we're getting the correct ID from subform #1
    MsgBox ItemTypesItemColorJunctionID
    Dim GetQuerySQL As String
    Dim RST As dao.Recordset
    Dim iItemDescriptionID As Integer
 
    'query to get records based on item selected in subform #1
    GetQuerySQL = "SELECT tblItemDescription.ItemDescriptionID, tblItemDescription.tblItems_ID, tblItemDescription.tblItemTypesItemColorJunction_ID, tblItemDescription.QtyInPackage, tblItemDescription.tblUnitOfQuantity_ID, tblItemDescription.Description, tblItemDescription.Price, tblItemDescription.Months, tblItemDescription.tblItemVendor_ID, tblItemDescription.WebPage, tblItemDescription.ShippingFee, tblItemDescription.SalesTax " & _
    "From tblItemDescription " & _
    "WHERE (((tblItemDescription.tblItemTypesItemColorJunction_ID)=" & ItemTypesItemColorJunctionID & "));"
 
    'this is just here for debugging to ensure that the record selected in subform #1 returns records that should appear in subform #2 - and it does in every instance!
    Set RST = CurrentDb.OpenRecordset(GetQuerySQL)
 
    With RST
        'loop through all tblEventTasks records with current EventTaskID and return TaskQuantity
        Do While Not .EOF
            'get TaskQuantity
            iItemDescriptionID = !ItemDescriptionID
 
            .MoveNext
        Loop
    End With
 
    'set record source property of subform #2 and requery and form should contain same records as would be found in above Do While loop - but they're not???
    Me.Parent![tblItemDescription subform].Form.RecordSource = GetQuerySQL
    Me.Parent![tblItemDescription subform].Requery

In the above code I set the query and then have a Do While loop which I can step through to confirm that, based on the record selected in subform #1, records are found that should be displayed in subform #2 - and in every case they are. After the Do While loop I set the RecordSource of subform #2 to the query and force a requery - when return to the form subform #2 should contain as many records as found in the Do While loop code but, unless it's the first subform #1 record that was selected, 0 records are displayed.

Any advice greatly appreciated.

Thank you.

[jforbes]

You may want to check the LinkMasterFields and LinkChildFields Properties of the SubForm to make sure they are blank.

[jase7678]

jforbes,

I cleared those 2 cells and everything worked - thank you!

Some related questions if I may:

I removed the OnCurrent event from subform #1 so the VBA code to populate subform #2 was omitted and reinstated the query in the Record Source field of subform #2 but subform #2 returned to not being populated, even for the first item in subform #1. I reinstated the OnCurrent event to subform #1 with all code commented out except that to requery subform #2 and still no content in subform #2. Do you know why this might be the case? The query was exactly the same as in the VBA code with the exception of the WHERE clause so I guess this could be the issue:

SELECT tblItemDescription.ItemDescriptionID, tblItemDescription.tblItems_ID, tblItemDescription.tblItemTypesItemColorJunction_ID, tblItemDescription.QtyInPackage, tblItemDescription.tblUnitOfQuantity_ID, tblItemDescription.Description, tblItemDescription.Price, tblItemDescription.Months, tblItemDescription.tblItemVendor_ID, tblItemDescription.WebPage, tblItemDescription.ShippingFee, tblItemDescription.SalesTax FROM tblItemDescription WHERE (((tblItemDescription.tblItemTypesItemColorJunction_ID)=[Forms]![frmMaintainItems]![tblItemTypesItemColorJunction subform1].[Form]![ItemTypesItemColorJunctionID].[Text])); 

Without a debugger how is one supposed to debug queries?

With the VBA method fully reinstated and functioning well, the first time I open the form I get the following error:
Capture.JPG

From looking online it appears to be caused by the subform #1 OnCurrent event being fired before subform #2 has been built (if you click End and return to the parent form everything works just fine for any subform #1 record selected). I've not been able to find a solution to this. Do you have any advice - maybe how to delay the OnCurrent event or put a check in the OnCurrent code to confirm that subform #2 has been built?

Finally, with my VBA in place, if I add a record using subform #2 it isn't added to the database (which I seem to recall did occur when the SQL in the Record Source field was functioning correctly). If I'm unable to get the Record Source method working again, would the recommendation be to add an event to subform #2 with an APPEND query?

Thank you to you or anybody else for their continued assistance.

Jason.

[jforbes]

There are three ways to populate a SubForm with records that I can think of:

1. Define the Subform with a Static RecordSource then use a the Master/Child Linking to link the SubForm on a Foreign Key to the MainForm to allow Access to maintain the SubForms Filter.
2. Define the SubForm with a Static RecordSource, then in the MainForm, set the SubForms Filter Property to limit the records of the SubForm.
3. Define the SubForm with whatever RecordSource is handy, then the MainForm replaces the RecordSource of the SubForm with the full Select Statement.

The First option is the Default and it was probably what you started with. It works well if you can use it. It can also take care of populating the Foreign key for you in the SubForm when adding records.

The Second and Third options are great if the filtering is more complex than the basic Master/Child link on a FK. Of these options, I usually use the Second option of Filtering the Records as it seems quicker. To me it feels like it is less disruptive to Access as it doesn't have to rebind the fields on the SubForm, just get new data.

The Second option can be done on the MainForms OnCurrent or OnLoad events without throwing an error. Since I don't use the Third Option, I'm really not sure how or why the error is occurring.

When Inserting Records with the Second or Third options, I'm pretty sure you can still get the UI to allow you to add records through the SubForm. But, you may need to provide a Default (from the MainForm) for the FK as Access can't populate this for you in the scenario. An easy way to do this is to set the Default for the SubForm FK to a Function call that looks up the MainForm's FK. It sounds confusing, but it really isn't.

I think the last thing to address it about the Debugging of SQL. If you were to put a breakpoint on line 14 of your code, you could then type ?GetQuerySQL into the immediate window and Access will spit out the contents of your SQL String. You could then copy that SQL string onto your clipboard, then paste it into a new Query Editor (SQL View of this) and attempt to run it. ...That is SQL Debugging in Access.

[jase7678]

Hi jforbes,

Thank you for all the additional detail and sorry for delay in coming back to you. Following your explanation of the 3 different scenarios I was able to determine the root cause of my issue and resolve.

Unfortunately I don't recall the exact Form Properties I had prior to going down the VBA route in an attempt to get the form to work properly so don't know where I initially went wrong, but the key to the fix was to create a text box on the Parent form to contain the Index from subform #1 and enter this into the Link Master Fields input of subform #2 (and eliminate all events & VBA code).

With this done the two subforms interact beautifully for both read & write.

Thanks again for your help.

Jason.