Microsoft Access - Navigation form is causing my query to not work

2
votes

I got a form that works amazingly by itself, but the second I attach it to my navigation form. I start to get prompted for user input since this line

[Forms]![frm_addReceiveReportInformation]![cbo_PurchaseOrderID]

no longer works due to the current form becoming subform in the navigation form which was explained in ACCESS 2010 Navigation Form Query Property

I can't seem to figure a way out of using the !form since I absolutely need to retrieve the ID from a combo box to update another combo box.

I tried multiple ways of using the !forms but I can't seem to wrap my head around how to retrieve my information that I am seeking.

I got the 2 way navigation menu(vertical + horizontal tabs). Anyone got advice or has encounter this problem in the pass, who can direct me down the right path.

enter image description here

2
sqlformsms-accessms-access-2010
Where is the code that references that combobox? If it's in 'frm_addReceiveReportInformation' why not just reference as 'Me.cbo_PurchaseOrderID'? - Wayne G. Dunn
@WayneG.Dunn its directly in the query for the other combo box. - Joshua Dalley
I need clarification on where everything is located. You had a form named 'frm_addReceiveReportInformation' that worked just great. Now you have made it a subform and it doesn't work. Are both comboboxes on the subform, or is one on the parent form? And which combobox doesn't work? - Wayne G. Dunn
@WayneG.Dunn the form isn't a subform, but when you attach it to a navigation form, it technically becomes a subform. sorry for the badly worded description on my part. Both combo box are on the same form. I added a picture to the main post. - Joshua Dalley
The Part Code CBO rely off the ID from PO No. CBO. - Joshua Dalley

2 Answers

2
votes

To access a field inside a form, which is attached to a navigation tab, you should use the following structure:

[Forms]![YourNavigationTab]![NavigationSubform].[Form]![YourField]

Note: tested in MS Access 2013

0
votes

In order for queries that contain form references to work, the form must be fully loaded. I think the problem you are experiencing is that the query in the Row Source of the Part Code combo is being evaluated before the form is loaded and therefore you are being asked to input the parameter value.

You can work around this by leaving the Row Source property of the Part Code combo empty until it gets focus for the first time, something like:

Private Sub cboPartCode_GotFocus()
  If Len(cboPartCode.RowSource) = 0 Then
    cboPartCode.[RowSource] = "Your Query"
    cboPartCode.Requery
  End If

End Sub