Access: opening form with filter

I have a form that is called from a parent form.

The parent form shows booking for an event and the child form shows the payments made for the currently selected booking.

The child form fJDWEventPay Record Source is table tJDWPayments

The key field is jdwp_BookingRef.  This is an unique reference that matches a booking to the payments.

The pertinent code follows:


Dim stDocName As String
Dim stLinkCriteria As String

stLinkCriteria = "tJDWPayments.jdwp_BookingRef =" & lngBookingID

lngNoPayments = DCount("jdwp_BookingRef", tJDWPayments","jdwp_BookingRef=" & lngBookingID)

stDocName = "fJDWEventPay"

DoCmd.OpenForm stDocName, , , stLinkCriteria, , acDialog


In debugging the code I observe that:

lngNoPayments = 3 – this is correct and verified by opening the table and setting the filter to ‘jdwp_BookingRef=11211’

stLinkCriteria = “tJDWPayments.jdwp_BookingRef=11211”

Yet when fJDWEventPay opens it shows only one record.  The record does match the filter in that the booking ref is 11211.
MikeDTEAsked:
Who is Participating?
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You don't need the stuff in the Load event.

Try this instead:

DoCmd.OpenForm stDocName, , , , , acDialog, lngBookingID  ' pass Open Args

Then, in the Load event of the popup form, do this:

Private Sub Form_Load()
    Me.Filter = "[BookingID] = " & Me.OpenArgs
    Me.FilterOn = True
End Sub

mx
0
 
mbizupCommented:
You are effectively setting the control source to one record like this.

If you want all the records, but filtered to a given subset, do this:

DoCmd.OpenForm stDocName,,,,,, acDialog, stlinkcriteria

and in the open event of your popup form:

Me.Filter = stlinkcriteria
Me.FilterOn = True



0
 
mbizupCommented:
Hmm... I may be misreading your question.

Are you sure only one record is present in your form (ie: the bottom of the popup form says something like "record 1 of 1 records"?

Is it possible that you are just seeing the first record of three due to the default view being single form view vs datasheet or continuous forms?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
mbizupCommented:
Also check the Filter property of your popup form to see if there might be anything else narrowing down the results.
0
 
MikeDTEAuthor Commented:
Hi there

I'm about to try your suggestion in your first post.

There are definately 3 records in th3 table matching teh filter but only on shows in the form - I've re-enabled the record  navigation buttons and it shows 1 of 1 (filter).

The form filter property is blank and there is nothing else in the properties that would narrow the results.

Back in 10
0
 
MikeDTEAuthor Commented:
OK - tried your suggested code - this results in a compile error "Wrong number of arguements or invalid assignment"

Also in Form_Load of the child form

 Me.Filter = stLinkCriteria
 Me.FilterOn = True

This does nothing because stLinkCriteria is locally scoped in the parent form and not available in the child form - so the filter is set to nothing.
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Do you happen to have the Form Data Entry property set to Yes?

mx
0
 
MikeDTEAuthor Commented:
On the child form - Yes

On the parent form - No

Don't recall setting Data Entry to Yes
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"On the child form - Yes"
I was referring to the Popup form.  That would show Record 1 of 1 ... BUT ... you would not see your single filtered record either.

mx
0
 
MikeDTEAuthor Commented:
All working with the last code modifications

Thank you - one last question should the Data Entry property be set to Yes on the Child (Pop-up) form?
0
 
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"should the Data Entry property be set to Yes on the Child (Pop-up) form?"
No.  That was just a long shot guess at that the problem. As I noted, that would result in no records showing.

What you originally have *should* work.  I tested that scenario here ... no problem. But, I don't have your system.  So, what I posted was just an alternate way to do it ... and I guess we got lucky :-)

mx
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.