Report not returning specific information

[christophercubitt]

I have multiple clients, multiple companies, and I give referrals to clients for companies regrding their services. I created a "referral" form that has a button within it that opens the report "referral report" that I hope will give the 1 clients information along with the 1 company's informations. I have the following macro attached to that button but its not working. When I press the button it asks for the Client ID and Contact ID, despite it appearing on the "referral" form. It then returns a report listing all referrals.


[Client ID]=[Forms]![Referrals]![Client ID] And [Contact ID]=[Forms]![Referrals]![Contact ID]

Please help me!!

 

[dhookom]

You created a "referral" form but use "Referrals" as the form name. Are you positive you have the form and control names all correct?

Duane
Hook'D on Access
MS Access MVP

 

[christophercubitt]

Yeah I'm pretty certain. The Form that users use is called "Referrals". My objective is to pull the contact information related with the contact id and the client information related with the client id number and have it placed in the "referral report". The client id and the contact id are entered on the "referrals" form.

 

[dhookom]

Is the form open the entire time the report is run?

Can you provide the complete SQL of the query?

What are the data types of the two fields?

What are the actual field names?

What is the report name?

What is the current code you use to open the report?

Duane
Hook'D on Access
MS Access MVP

 

[christophercubitt]

1. The referral form is open because the button that executes the macro is open.

2. The complete macro's where condition is:

[ClientID]=[Forms]![Referrals]![ClientID] And [ContactID]=[Forms]![Referrals]![ContactID]

3. ClientID, and Contact ID are Long Integers

4. The field names, I believe, match.

5. The Reports name is "Referral Form"

 

[dhookom]

I don't use macros but question the macro's where condition and wonder if it is the filter applied to the report.

What do you see if you add a text box to the report and set its control source to:
=[Filter]

Duane
Hook'D on Access
MS Access MVP

 

[christophercubitt]

I see the name of the filter that I've applied in the where condition for the macro. I'm open to using code if it will achieve my objective.

 

[dhookom]

With macros, there are columns for:
[tt][COLOR=black #DDDDDD]| Condition | Action | Arguments | Comment |[/color][/tt]
The Action Arguments for OpenReport have a Where Condition. You continually use "condition for the macro" which is not the correct condition to filter a report. You would need to use the Where Condition in the Action Arguments.


Duane
Hook'D on Access
MS Access MVP

 

[christophercubitt]

I'm very new to Access so I apologize that my lingo is off. The macro is in the Where Condition.

 

[dhookom]

I would use code associated with a command button like:

Dim strReport as String
Dim strWhere as String
strReport = "Referral Form"
strWhere = "[ClientID]=" & Me.[ClientID] & _
  " And [ContactID]= " & Me.[ContactID]
DoCmd.OpenReport strReport, acViewPreview, , strWhere

Duane
Hook'D on Access
MS Access MVP