How can I code button to print a report w/current record only?

[Xeseus]

I am trying to get the code for my form's print button to print a report with the form's current record only. What do I need to do to get it to do this?

Here's my code:

Private Sub cmdPrint_Click()
On Error GoTo Err_cmdPrint_Click

Dim stDocName As String

stDocName = "Agency Acknowledgement"
DoCmd.OpenReport stDocName, acNormal

Exit_cmdPrint_Click:
Exit Sub

Err_cmdPrint_Click:
MsgBox Err.Description
Resume Exit_cmdPrint_Click

End Sub

 

[straybullet]

I use the following to open a report based on the selections made (or not made) in 2 separate comboboxes on a form:

'Open report with all information
If (IsNull(Me.Combo2)) And (IsNull(Me.Combo11)) Then
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.Close acForm, "BFUpRptSelFrm", acSaveNo


'Open report based on selection made in Combo2 only
ElseIf Not (IsNull(Me.Combo2)) And (IsNull(Me.Combo11)) Then
DoCmd.OpenReport stDocName, acViewPreview, , "Zone = " & Me.Combo2
DoCmd.Close acForm, "BFUpRptSelFrm", acSaveNo

'Open report based on selection made in Combo2 AND Combo11
ElseIf Not (IsNull(Me.Combo2)) And Not (IsNull(Me.Combo11)) Then
DoCmd.OpenReport stDocName, acViewPreview, , "[SECT]='" & Me.Combo11 & "' And [Zone]=" & Me.Combo2
DoCmd.Close acForm, "BFUpRptSelFrm", acSaveNo

End If

Basically, what is happening when the button is clicked is that the report is filtered based on the selection(s) made - the red text above being the "Where condition"

Let them hate - so long as they fear... Lucius Accius

 

[Xeseus]

Very nice! I'm not familiar with VBA but I would figure that because I only have one choice to make, the report to print, then I would use code something like this:

'Open report based on selection made in Combo2

‘If nothing is chosen then nothing is printed
If (IsNull(Me.Combo2)) Then
DoCmd.OpenReport stDocName, acViewPreview
DoCmd.Close acForm, "Agency Acknowledgement", acSaveNo

‘If something is chosen then that choice is printed
ElseIf Not (IsNull(Me.Combo2)) Then
DoCmd.OpenReport stDocName, acViewPreview, , "Zone = " & Me.Combo2
DoCmd.Close acForm, "Agency Acknowledgement", acSaveNo

End If

...but I am not sure what the rest of the coding looks like and I'm not sure where I define the zones. Would the zones be what I put in the Row Source? (For ex: "Agency Acknowledgement", "Report 2", "Report 3", etc...?)

 

[TheAceMan1]

How are ya Xeseus . . .

Have a look here: How to Print a Single Record from a Form in a Report

See Ya! . . . . . .

 

[straybullet]

Xeseus, the link AceMan posted has excellent information in it - the "zone" in my example was referring to a specific field in the report's underlying query.
Basically, edit the Me.Combo2 in the example you posted in your response to refer to whatever control on your form contains the record's unique identifier.

Let them hate - so long as they fear... Lucius Accius

 

[Xeseus]

Thanks Aceman, that did the job.

Straybullet, I really like the idea of using a combo box to select a report to print but I am still not clear on how the report choices are represented in the code.

 

[straybullet]

The following is a query which will list all the Reports available in your DB and can be used as a record source for a combobox (thanks to FancyPrairie):
SELECT DISTINCTROW MSysObjects.Name
FROM MSysObjects
WHERE ((MSysObjects.Type)=-32764) AND ((MSysObjects.Flags)<>8)
ORDER BY MSysObjects.Name;

This combobox could then be represented by stDocName:

Dim stDocName As String
stDocName = Forms!frmFormName.cboPickReportName.value

Let them hate - so long as they fear... Lucius Accius

 

[Xeseus]

Straybullet, I do not have as much of an understanding of VBA and Access applications programming I would like to yet. I would like to get a good book on the subject once I have a better handle on the basics. I do not want to take advantage of your generosity but if you have the time could you give me a little more specifics as to where I need to inject this code? If you are busy I very much understand. Either way, I really appreciate your help and patience (as well as everyone else's)!

 

[TheAceMan1]

[blue]Straybullet, I really like the idea of using a combo box to select a report to print but I am still [purple]not clear on how the report choices are represented in the code.[/purple][/blue]

You don't need a book for this! . . .

Select the [blue]Tables[/blue] window of the DB, then from the menubar select [blue]Tools[/blue] - [blue]Options[/blue] - [blue]View tab[/blue].

Put a check in [blue]System objects[/blue]. You'll see other tables popup in the tables window, one of which is [blue]MSysObjects[/blue]. This system table (which occurs in every db . . . just hidden . . . no editing please . . . you could render the db inoperative!) holds a line of info for every object you create (table, form, report, macro . . . ect). If you look over the table ([purple]no editing please![/purple]) you'll see each type of object has a type number . . . forms being -32764 . . . So your reports are all there! . . . can you see!

A query/SQL is simply set to return reports only!

See Ya! . . . . . .

 

[Xeseus]

Okay, I'll see what I can do. Thanks guys.