Open Form Filter Issue (Inconsistent)

[txaccess]

I have a main form that lists a company and a sub form that shows all contacts in the company. Double click on the contact & it "should" open up the contact record. However, it opens up all records. Even if I try to code this with the wizard, the find specific records does not show up on the wizard. This leads me to think that there is some setting on my form that disables filters (the allow filter event is set to yes. Other forms work fine with exactly the same code. What could be going on?

Thanks

I haven't failed, I have just found 10,000 ways that it won't work!

 

[roppetech]

Double click on the contact & it "should" open up the contact record. However, it opens up all records. "

I assume you mean to open the contact record in a new form? Have you tried:
docmd.openform "frmContact",,,"cntID = " & contactID

This will set a WHERE condition on the recordset of the contact form, as an alternative to filters.


- RoppeTech

 

[txaccess]

Thanks

The source form is a continuous form that displays all contacts related to a company. On clicking the contact in the sub form, the main contacts form should be displayed showing the detailed contact record. The trouble is, even though I have a WHERE clause to filter only the contacts with the same PK, the form displays all contacts.

In looking at this, I have found that if the destination form is unbound, then the filter does not work. If I bind the contacts form to the contacts table, the WHERE clause works fine.

On opening the contacts form in the main view, the On Open Event interrogates the user and dependent on their role will display certain records. This is a SQL statement attached to a Select Case with the recordsource selected depending on the case. I have tried using the OpenArgs to pass a value to the contacts form with an If Statement to override the Select Case in the event that the contacts form is opened from the sub form rather than directly, but this did not work either. I don't want to bind the form to solve this problem. Any other ideas?

Thank you

I haven't failed, I have just found 10,000 ways that it won't work!

 

[roppetech]

This is how i understand your form structure, please correct me if i'm wrong:

frmCompany: company single form
-frmCompany_contacts: contacts continuous subform

frmContact: contact single form


Have you tried this?
Define a global variable (in a module) called sqlContact.
In your Select Case statement, set sqlContact to your full sql string.
During the frmContact_OnOpen event, set your recordsource to sqlContact.

I've had good luck with this using complex sql strings on reports, might work for this too!



- RoppeTech

 

[txaccess]

Thanks for the idea, I will give that a try


I haven't failed, I have just found 10,000 ways that it won't work!