Vba – How to Filter On Load with Microsoft Access Navigation Forms

formsms-accessnavigationsubformsvba

I have a Navigation Form with 3 tabs. Homes, Customer, Decor.

On the Navigation Form, I have an unbound field LOTNUMSELECT.

On the Subform within the each tab, it has a field called LOT_NUMBER.

Since the master / child relationship doesn't seem to be possible with Navigation forms, I'm looking for the subform to filter on load.

User inputs a lot # in LOTNUMSELECT, then clicks on a tab. When the tab becomes current, the specific lot # (record) is showing.

I've been doing this from a "control panel" form opening another form with the following code.

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Decor"

If IsNull(LotNumberSelect.Value) = True Then

MsgBox "Please enter a lot number first."

Else
stLinkCriteria = "[Lot_Number]=" & "'" & Me![LotNumberSelect] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

I just don't know how to edit this to work with the Navigation form and subforms.

Best Answer

A navigation control has two parts. Navigation menu and a navigationsubform. Each navigation button (aka tab) will load a target form into the navigationsubform control. Only one form can be loaded into the navigationsubform at a time. Since the navigationsubform opens your target form, you could use the standard "form_open" method to set your filter or set your recordset accordingly. All you have to do, click on the forms open event and within that event you can access the parent controls simply by 

 dim lotno as long
Lotno = nz(me.Parent!controlname.value,0)
If lotno <> 0 the 
' do your filter or recordset operation
End if

You can also use the form_load event. Since you are going to perform the filter after load, it is better to set it on the form_open. This will increase your performance.

Related Topic