Access 2007 populating a report using a macro

Question

I have a student application database with a form that contains the information for each application.

I would like to create a report from the form (I`m unsure if I should use a macro or a command button) but what it should do is upon clicking, it should create a report which only contains the information from the current application that is being displayed on the form.

I have created a report that looks like this:

To create the Macro (which when clicked opens the report in print preview mode) I used the following criteria under the where box in macro design: [Application]![Record ID]=[Forms]![Form]![Record ID]

However when I click the macro on the form it brings up the report but does not populate the fields in the report with the data from the form.

The question I am asking is how does one usually create a button on a form that once clicked will bring up a print preview that contains the information that is contained within that form (not multiple records)

Thanks,

Tom

Answer 1

It should be as simple as something like this (in VBA, not a macro):

If Me.NewRecord = False Then
    DoCmd.OpenReport "rptMyReportName", acViewPreview, , "[RecordID] = " & Me!RecordID
End If

As a side note, it is a best practice not to use spaces or symbols in your field names when you design your tables. I would change Record ID to RecordID.

Answer 2

I am not sure how to do this with macros. I never use macros - you can do everything that macros do with vba - and wuite easy:

Private Sub ReportOpenButton_Click()
On Error GoTo Err_ReportOpenButton_Click

If Not Me.NewRecord Then
    DoCmd.OpenReport "My_Reportname", acPreview, , "[ID] = " & Me![ID]
Else
  If MsgBox("Save the new Record ?", vbQuestion & vbOKCancel, "Attention") = vbOK Then
    RunCommand acCmdSaveRecord
    DoCmd.OpenReport "Bericht1", acPreview, , "[ID] = " & Me.ID
  End If
End If

Exit_ReportOpenButton_Click:
    Exit Sub
Err_ReportOpenButton_Click:
    MsgBox Err.Description
    Resume Exit_ReportOpenButton_Click:
End Sub

The important part here is: [ID] is the name of the ID field in the report, Me![ID] is the name of the id field in the form where this command is executed (me). So the last parameter "[ID] = " & Me![ID] means, that the string "[ID =" and the number in the field id of my form will be concatenated to "[ID] = 7" for example.

So the line executes a macro that reads

OpenReport my_reportname, inPreviewMode, ID = 7

Edit: Added some code to save the current record if it is new