Report button needs to pull specific records only

[nurjml]

I created a button that pulls up a specific record in a form:

Private Sub OpenExistingAuthor_Click()
On Error GoTo Err_OpenExistingAuthor_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ManscriptTitleForm"
stLinkCriteria = "[LeadAuthor]=" & "'" & Me![Combo5] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_OpenExistingAuthor_Click:
Exit Sub

Err_OpenExistingAuthor_Click:
MsgBox Err.Description
Resume Exit_OpenExistingAuthor_Click

End Sub
​

And a button that pulls ALL records into the report:

Private Sub AllLeadAuthorsReport_Click()
On Error GoTo Err_AllLeadAuthorsReport_Click

Dim stDocName As String

stDocName = "ManscriptReport"
DoCmd.OpenReport stDocName, acViewReport

Exit_AllLeadAuthorsReport_Click:
Exit Sub

Err_AllLeadAuthorsReport_Click:
MsgBox Err.Description
Resume Exit_AllLeadAuthorsReport_Click

End Sub

​

Now what I need is a button that pulls a specific record for the report. I tried adapting the stLinkCriteria in the form code to try to get it to work in the report code but no such luck. Any suggestions????

 

[pbaldy]

What is your failing code? OpenReport has the same argument available as OpenForm, and it uses the same syntax. Did you have it in the wrong position perhaps?

 

[nurjml]

This is the code for the button that is not working (It just acts like the button that returns ALL records instead of the one I specify in Combo5)

Private Sub LeadAuthorReport_Click()
On Error GoTo Err_LeadAuthorReport_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ManscriptReport"
stLinkCriteria = "[LeadAuthor]=" & "'" & Me![Combo5] & "'"
DoCmd.OpenReport stDocName, acViewReport

Exit_LeadAuthorReport_Click:
Exit Sub

Err_LeadAuthorReport_Click:
MsgBox Err.Description
Resume Exit_LeadAuthorReport_Click

End Sub

 

[pbaldy]

You didn't actually use the variable (stLinkCriteria) in the OpenReport line.

 

[nurjml]

Okay, I added the stLinkCriteria to the command line... but it is pulling the data from the child file even if it is not pulling from the parent file. Do I need to create some sort of error event for when there is no lead author by the user-input name? I am thinking I need an if-then statement or something but have not been able to get one to work. (I have made HUGE progress on this database and this pesky button is one of the last steps.)

Private Sub Command38_Click()
On Error GoTo Err_Command38_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "ManscriptQuery"
stLinkCriteria = "[LeadAuthor]=" & "'" & Me![Combo5] & "'"
DoCmd.OpenReport stDocName, acViewReport, , stLinkCriteria

Exit_Command38_Click:
Exit Sub

Err_Command38_Click:
MsgBox Err.Description
Resume Exit_Command38_Click

End Sub

 

[pbaldy]

So the report would be empty? Typically that would be handled with

Cancel = True

In the no data event of th report.

 

[nurjml]

I am still working on the button issue but I did figure out part of the problem... I had done something wrong with the queries that run the forms/reports. I think I got that working so the button issue should be resolved soon.

Oh, and I found the workbook (2 are still missing) from a query building class I took about 10 years ago. That should help me at least give more informative information when I am asking questions.

Thanks to whoever created this forum, it is so nice to heave help for those of us who "play" in database development!

 

[pbaldy]

Post back if you get stuck.