Report for One Record

[davegauthier]

I need to print a report based on a single record taken from a group of records. The one record I want to print in the report would be the record currently being viewed in the main form.

This is a client database. I have multiple tables with all information connected with Client ID's and relationships.

I need to be able to click a button on the form and view and/or print a report for only the record being viewed at that time. I do not want to ask the user to enter the Client ID in a dialog box, I want it to be more streamlined then that.

I have tried coding the command button as follows:
Dim stDocName As String
Dim strWhere As String

stDocName = "rptOneRecord"
stWhere = "[Client ID]=" & Me!ClientID
DoCmd.OpenReport stDocName, acPreview, , strWhere

And the report does open, but without any infomation on it whatsoever.


Any suggestions???

 

[gromit]

Hi Dave -

Your basic approach should be correct, there must be something not quite right in the details.

1. Verify that the text box on the form is really called ClientID (not just the label for it - I've done that more times than I care to admit.)

2. Try inserting a message box after the stWhere variable assignment
MsgBox (stWhere)
and see if the string looks correct to you.

3. You have a space in the [Client ID]. Check this against your table to make sure that your key looks the same. (In general, I recommend eliminating spaces in variable names, too much room for trouble.).

4. If none of the above helps, try to post a simplified version of your db and let's see if we can exorcise the demons.

hth,

- g

 

[RuralGuy]

PMFJI but I would also make sure you had:
Option Compare Database
Option Explicit
at the top of your code module since
Dim strWhere As String
stWhere = "[Client ID]=" & Me!ClientID
is a typo somewhere. There is also the outside chance the ClientID is a text field where you would need:
strWhere = "[Client ID]='" & Me!ClientID & "'"

Just a couple more ideas.

 

[davegauthier]

Thanks, it works like a charm now.

I appreciate the help from both of you.

 

[healer]

printing individual reports

This is my code for a similar kind of problem.. am not able to get indivual reports rather i get the complete set.. treied everything you have mentioned ..any other tips?
Vinay

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String
Dim strWhere As String
stDocName = "rptNewpatient"
strWhere = "[TSCID]=" & Me!TSCID
MsgBox (strWhere)
DoCmd.OpenReport stDocName, acPreview, strWhere

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

 

[RuralGuy]

The WhereCondition is the 4th argument, not the 3rd.
DoCmd.OpenReport stDocName, acPreview, , strWhere