Create User Selectable Filter for a Report

I have a key field in a table that is used to link to other tables in a one to many relationship. Would like to be able to create a report that somehow allows the user to select which record in the key field of the table to base the report on. Is there a way to create a form that would open just prior to the report opening that would permit the user to select the specific record? I don't want to have to create a separate query and report for each of the records of the key field. Any help greatly appreciated. Pete

Create the form, with a combo box for selecting the value, and a command
button to open the report.

This example assumes you have a combo named "cboField1", that has the value
to match against "Field1" in the report.

Private Sub cmdPreview_Click()
Dim strWhere As String

If Not IsNull(Me.cboField1) Then
strWhere = "[Field1] = " & Me.cboField1
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End Sub


Note: If Field1 is a Text field, you need to add extra quotes:
strWhere = "[Field1] = """ & Me.cboField1 & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pete Sperling" wrote in message
...
I have a key field in a table that is used to link to other tables in a
one to many relationship. Would like to be able to create a report that
somehow allows the user to select which record in the key field of the table
to base the report on. Is there a way to create a form that would open just
prior to the report opening that would permit the user to select the
specific record? I don't want to have to create a separate query and report
for each of the records of the key field. Any help greatly appreciated.
Pete

Allen - Works great - Thanks for your assistance - Pete

"Allen Browne" wrote:

Create the form, with a combo box for selecting the value, and a command
button to open the report.

This example assumes you have a combo named "cboField1", that has the value
to match against "Field1" in the report.

Private Sub cmdPreview_Click()
Dim strWhere As String

If Not IsNull(Me.cboField1) Then
strWhere = "[Field1] = " & Me.cboField1
End If

DoCmd.OpenReport "MyReport", acViewPreview, , strWhere
End Sub


Note: If Field1 is a Text field, you need to add extra quotes:
strWhere = "[Field1] = """ & Me.cboField1 & """"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pete Sperling" wrote in message
...
I have a key field in a table that is used to link to other tables in a
one to many relationship. Would like to be able to create a report that
somehow allows the user to select which record in the key field of the table
to base the report on. Is there a way to create a form that would open just
prior to the report opening that would permit the user to select the
specific record? I don't want to have to create a separate query and report
for each of the records of the key field. Any help greatly appreciated.
Pete