VBA Code to Filter a Report from a Subform

[jesterII]

Hello friends,



I have two tables, one called Company Profile and the second called Contact Persons. The field "Company Name" exists in both, each contact person belongs to a company that is in the Company Profile table (one-to-many relationship with Contact Persons as many).

I created a Form-Subform. What I want to happen is that on my subform (Contact Persons), I have a 'generate record' button that will show me a report of the all the Contact Persons that belong to the same Company X.

I tried doing this

DoCmd.OpenReport "rcdContacts", , "[Company Name] = " & Me.Company_Name

but the report is not filtered and contains records of all contact persons, not the ones in Company X. Please advise.

[pbaldy]

I think you have it in the wrong position, and if it's really a name you need the text syntax:

BaldyWeb wherecondition

[jesterII]

Dear pbadly,
Thanks for your fast reply. Here is what I have now

DoCmd.OpenRecord "rcdContacts", , , "[Company Name] = '" & Me.[Company_Name] & "'"

but this gives me a compiler error "Method or data member not found", although the Field [Company_Name] surely exists in my subform.

Any thoughts?

[jesterII]

Sorry I meant to say

DoCmd.OpenReport "rcdContacts", , , "[Company Name] = '" & Me.[Company_Name] & "'"

yields the error Microsoft Access can't find the field '|1' referred to in your expression.

[pbaldy]

I note that one has a space and the other an underscore. Is that correct? The error implies that something is misspelled. Can you post the db here if that's not it?

[jesterII]

That does not seem to be the problem. It appears that my database exceeds the limit to be uploaded here. Do you have an idea on what to do next?

[pbaldy]

Did you compact/repair then zip? That will get most under the limit. If not, you can email to

pbaldy
gmail
com

[jesterII]

It worked! I traced it back to an issue in my one-to-many relationship, which I ended up just deleting. It had no effect on the Master/Child relationship in my form/subform, and I edited the Query Statement such that they are still linked by Company Name.

I was doubting something with the VBA code but it is indeed intact. Thank you.

[pbaldy]

Glad you got it sorted out.