DoCmd.OpenForm problems..

[SGT68]

I have a MEMBERSHIP MASTER form which carries membership data, and this form has a subform called MEMBERSHIP SUBS which shows all the annual subscriptions the member has had over the years

this is the linking :

Parent Form
MEMBERSHIP MASTER
MemberNo

Subform
MEMBERSHIP SUBS
MemberNo, SubscriptionID

From any other form which carries a MemberNo value I use this command:
DoCmd.OpenForm "MEMBERSHIP MASTER", acNormal, "", "[MemberNo]=" & [MemberNo] , , acNormal

to open the form for any given member, and the MEMBERSHIP SUBS subform obviously opens at the first subscription for that member. Simple enough

Now....I have a new list form that lists all the Subscriptions in MemberNo,SubscriptionID order .From a button on this list form I want to be able to open MEMBERSHIP MASTER form for the MemberNo and have the MEMBERSHIP SUBS subform open at the SubscriptionID for that MemberNo.

How do I achieve this ? I did this and it didnt work:

DoCmd.OpenForm "MEMBERSHIP MASTER", acNormal, "", "[MemberNo]=" & [MemberNo] & " AND Forms![MEMBERSHIP MASTER]![MEMBERSHIP SUBS].Form![SubID] =" & [SubID] & " AND Forms![MEMBERSHIP MASTER]![MEMBERSHIP SUBS].Form![MemberNo] =" & [MemberNo], , acNormal


I just got an empty form , no error messages. Thank you

 

[MarkK]

That string you pass in when you open the form just becomes the filter, but the filter only knows about the fields in the parent form, so when you throw other stuff at it, it just returns "False" for every record, and you get no records.

For record navigation on forms what I very, very commonly do is add a routine that does this . . .

Public Sub GoToID(FormID as Long)
   With Me.RecordsetClone
      .FindFirst "FormID = " & FormID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

. . . which has the effect of navigating to the record. Of course you need to use your ID field name, not "FormID."

If your subscription subform has a routine like that, then you can add this routine to your parent form, and it will call the GoToID routine on the subform . . .

Sub GoToSubscriptionID(SubscriptionID as Long)
[COLOR="Green"]'  calls the GoToID method of the subform, from the parent form[/COLOR]
   Me.MembershipSubs.Form.GoToID SubscriptionID
End Sub

Then, you can write code like . . .

[COLOR="Green"]'open the form, filtered[/COLOR]
DoCmd.OpenForm "fMembership", , , "MemberNo = " & Me.MemberNo
[COLOR="Green"]'call the "go to subscription" routine[/COLOR]
Forms("fMembership").GoToSubscription Me.SubID

 

[SGT68]

That string you pass in when you open the form just becomes the filter, but the filter only knows about the fields in the parent form, so when you throw other stuff at it, it just returns "False" for every record, and you get no records.

For record navigation on forms what I very, very commonly do is add a routine that does this . . .

Public Sub GoToID(FormID as Long)
   With Me.RecordsetClone
      .FindFirst "FormID = " & FormID
      If Not .NoMatch Then Me.Bookmark = .Bookmark
   End With
End Sub

. . . which has the effect of navigating to the record. Of course you need to use your ID field name, not "FormID."

If your subscription subform has a routine like that, then you can add this routine to your parent form, and it will call the GoToID routine on the subform . . .

Sub GoToSubscriptionID(SubscriptionID as Long)
[COLOR="Green"]'  calls the GoToID method of the subform, from the parent form[/COLOR]
   Me.MembershipSubs.Form.GoToID SubscriptionID
End Sub

Then, you can write code like . . .

[COLOR="Green"]'open the form, filtered[/COLOR]
DoCmd.OpenForm "fMembership", , , "MemberNo = " & Me.MemberNo
[COLOR="Green"]'call the "go to subscription" routine[/COLOR]
Forms("fMembership").GoToSubscription Me.SubID

That's great stuff, thankyou for the time you took in writing that out