open a form/subform in a 1 to many at a specific record in subform

[tim johnson]

I have a form and subform in a 1 to many relationship. The main form is
called frmMain and the subfrom is called frmSub. frmMain is linged to tblMain
and has 2 fields, CustomerID as primary key and the second field is
CustomerName.

frmSub is linked to tblSub and has CustomerID , SubID as the primary key and
a few more fields not relevant to my question.
All primary keys are AutoNumber with long data type.

The customer with customerID = 2 had 12 records in the sub form.

I have an unbound form and when I click a cmdButton I want to open frmMain
at customerID 2 in the main form and also find the record where the SubID = 5.

This is what I have.

DoCmd.OpenForm "frmMain", , , "CustomerID = " & 2 & _
" AND Forms!frmMain!frmSub!SubID = " & 5

The opens but displays nothing.


In a nutshell how do I open a form/subform in a 1 to many relationship at a
specific record in the many side?

 

[John Vinson]

On Fri, 7 Jan 2005 06:03:01 -0800, "tim johnson" <tim

This is what I have.

DoCmd.OpenForm "frmMain", , , "CustomerID = " & 2 & _
" AND Forms!frmMain!frmSub!SubID = " & 5

You're misunderstanding how Subforms work. The OpenForm opens the main
form - but the subform is semi-independent. You can't pass a parameter
to the subform in the mainform's open event.

The opens but displays nothing.


In a nutshell how do I open a form/subform in a 1 to many relationship at a
specific record in the many side?

You'll need to use VBA code in the Subform's Open event to navigate to
the selected record. I'm curious though - how did you decide that 5
was the right ID? Will it always remain 5, or is this a parameter
you're passing from somewhere else? Might it not be better to change
the Recordsource of the subform to just display the desired record?

John W. Vinson[MVP]

 

[tim johnson]

Thanks for your reply. In the real db I will pass a parameter from an
unbound form rather than those specific record. It was just easier to
explain my problem by selecting those specific record.

Could you expand more on how to do your suggestion.

Thanks