Report based on specific record(s)

[Chris Howarth]

Apologies if this is a dumb question, I'm on my first database and I'm having problems with setting up my report. I've created my forms etc and I've placed a command button on one of the forms to show a formatted report.

Is there a way to only show an 'individual' report for the currently selected record on the form? I've tried using the ApplyFilter macro command but can't work out how to perform a filter by selection on my unique ID field before I show the report to achieve what I want.

Any help gratefully recieved.

 

[Rob.Mills]

Use

DoCmd.OpenReport "ReportName", , ,"[UniqueID]=" & "'" & Forms!FormName!UniqueID & "'"

 

[Chris Howarth]

Great, thanks very much. I found I had to refresh the current record to catch any update in the report and ended up with the following code. Is this the usual way of doing it, or can you foresee any problems?

Private Sub cmdPrintPreview_Click()
On Error GoTo ErrTrap
    Dim frmCurrentForm As Form
    Set frmCurrentForm = Screen.ActiveForm
    DoCmd.RunCommand acCmdRefresh
    DoCmd.OpenReport "tblMancons1", acPreview, "", "[Issue Number]=[Forms]![" _
    & frmCurrentForm.Name & "]![Issue Number]"

Code_Exit:
    Exit Sub

ErrTrap:
    MsgBox Error$
    Resume Code_Exit
End Sub

 

[Rob.Mills]

I've never used acCmdRefresh before so I wouldn't be able to tell you about that. But you could probably simplify your Where clause to:

"[Issue Number]=frmCurrentForm![Issue Number]"

I've not tested this so I don't know for sure it would work but if it does will make it easier to read.

 

[Chris Howarth]

Thanks, Rob, I'll give that a try on Monday.

Strange, though, I've done a lot of programming with Excel VBA and yet, when it comes to Access, everything I've learnt seems to go out the window and I'm fumbling about again. Still, fun when you get something to work .