Open a report based on contact name (1 Viewer)

[mkdrep]

Sorry for the lengthy description but I hope to make this issue I am having as clear as possible.

I have a report that I have developed which is associated with a contact's name on an "AIA Firm Contact form". The only way I can currently get the report to open and only show that contact's info, is to have the underlying report query prompt the user to enter the contact's name. I have a "Report" button on the contact form that allows the user to click the button and just enter the contact name and the report shows up with the information I desire. (See attached .jpg)

Rather than having the user enter a contact's name each time, what code can I write so upon clicking the "Report" button, the report automatically associates the Contact's name on the AIA Firm Contact form and the report opens up showing ONLY that contact's information.

Below is the code I wrote to open the FORM for one contact at a time. It works perfectly as it only opens the contact on the form.
---------------------------------------------
Private Sub open_Rpt_Spec_Details_AIA_Contact_Click()
On Error GoTo Err_open_Rpt_Spec_Details_AIA_Contact_Click
Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "rpt_Spec_Details_AIA_Contact"
stLinkCriteria = [ContactName] = "&" & Me![ContactName] & " '"
DoCmd.OpenReport stDocName, acPreview
Exit_open_Rpt_Spec_Details_AIA_Contact_C:
Exit Sub
Err_open_Rpt_Spec_Details_AIA_Contact_Click:
MsgBox Err.Description
Resume Exit_open_Rpt_Spec_Details_AIA_Contact_C
End Sub
-------------------------------------------------
Below is the code I wrote to have the REPORT Open to the contact's name on the form that is open. The report shows the information properly BUT shows EVERY contact's information, rather than ONLY the immediate contact on AIA Firm Contact form.
----------------------------------------------------------
Private Sub open_frm_Job_Spec_DetailxProj_AIA_Click()
On Error GoTo Err_open_frm_Job_Spec_DetailxProj_AIA_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frm_Job_Spec_DetailxProj_AIA"

stLinkCriteria = "[ContactName]=" & "'" & Me![ContactName] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_open_frm_Job_Spec_DetailxProj_AIA_Clic:
Exit Sub

Err_open_frm_Job_Spec_DetailxProj_AIA_Click:
MsgBox Err.Description
Resume Exit_open_frm_Job_Spec_DetailxProj_AIA_Clic

End Sub
------------------------------------------------

Thank you in advance for your help......Mark

 

[pbaldy]

You created the filter string (though incorrectly) but didn't actually use it, like you did with the OpenForm.

 

[sneuberg]

Try
stLinkCriteria = "[ContactName] = '" & Me![ContactName] & "'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria, acWindowNormal

 

[jdraw]

Also, please use code tags to surround your code.

Go to Advanced, highlight the vba code, then click on the hash/number/octothorpe character.

If you are using 32 bit Access, you might also consider the free vba utility called Smart Indenter that can quickly indent your code.

 

[mkdrep]

You created the filter string (though incorrectly) but didn't actually use it, like you did with the OpenForm.

Perhaps you would be kind enough to show me how I should have created the filter string AND then how I should use it? I would greatly appreciate you help on this.......

Thank you, Mark

 

[pbaldy]

Don't need to, somebody else already provided the fish.

 

[mkdrep]

Try
stLinkCriteria = "[ContactName] = '" & Me![ContactName] & "'"
DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria, acWindowNormal

Tried your suggestion and the report opened, but it was blank, no data at all.

Before I added the code you suggested, the report opened but showed ALL contacts data.... very frustrating....:banghead:

Thank you for your suggestion though!

 

[mkdrep]

Don't need to, somebody else already provided the fish.

I tried the only suggestion I received and it didn't work....

 

[pbaldy]

Something must be different if the same criteria works for the form but not the report. Does the report have that name field in its source?

 

[mkdrep]

Something must be different if the same criteria works for the form but not the report. Does the report have that name field in its source?

It does but I am now getting the attached error msg....does this give you a better idea of what the issue is? thx for your patience...

 

[pbaldy]

What's your code? It doesn't appear you included the necessary delimiters for a text value. The criteria should look exactly like it does in your form code.

 

[mkdrep]

What's your code? It doesn't appear you included the necessary delimiters for a text value. The criteria should look exactly like it does in your form code.

The code for the form and the report definitely are NOT the same...I'll give that a try and let you know. Thank you!

 

[mkdrep]

What's your code? It doesn't appear you included the necessary delimiters for a text value. The criteria should look exactly like it does in your form code.

BINGO! THAT WAS IT!! My link criteria for the report was different than the form link criteria!

THANK YOU SOOOOOOO MUCH FOR YOUR HELP!!

 

[pbaldy]

No problem.

 

[pbaldy]

Oh, we were posting at the same time. Glad it worked!!