• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 415
  • Last Modified:

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.
0
MikeDTE
Asked:
MikeDTE
  • 4
  • 4
  • 3
1 Solution
 
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
 
mbizupCommented:
Also check the Filter property of your popup form to see if there might be anything else narrowing down the results.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
"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 MVP, Access and Data Platform)Commented:
"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

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now