How to Filter On Load with Microsoft Access Navigation Forms

Question

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.

Answer 1

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.

Answer 2

PART2

I've had some more thoughts having done some experiments.

I don't think the navigation control is the right control to use for your situation. The navigation control does not seem to have events, and properties that will let you link it to a control on the main form that has the navigation control (which I think is what you are trying to do).

I think you will find it easier to use the "tab control", which is easier to work with in this case.

I think the navigation control is really designed to be a menu that allows you access other forms and reports inside it.

Here's a link to videos about using the tab control: video 1 Video 2

Here's some links to videos about using the navigation control: video 1 video 2 video 3

Here's some notes I made abut the navigation control:

The navigation control has a "navigation subform" which does not have any master/child data links fields. However, this subform is not a normal subform.

With the navigation control, you have to set the "Navigation target name" property of the navigation button (in the Navigation menu) to be the form you want to appear in the navigationsubform when the button is clicked.

Further more, the button has a property "Navigation Where clause" which I believe be used to set to filter the rows shown WHEN the form is loaded. There do not appear to be events that will allow you to change this filter when a control is used on the main form. .