Report - lookup field to report on

[Paul Dennis]

I have a table [SIP] and I would like to create a report to report on all
records were a field [Origin] is = to what the user selects from a lookup.
Normally I would use a criteria in a query, however this leaves the user with
free format entry, what I need is to be able to allow them to select from a
list. I can't seem to be able to do this in a query or on the report unless
I'm missing anything?

All I can think of is to have a form for the selection but if so how do I
then run a macro to run the report and pass the value selected to it. Or is
there a better way?

 

[Duane Hookom]

Create a combo box on a form (frmRptCriteria!cboOrigin) and use the command
button wizard to create a button that opens your report. Right-click the
button in design view and change the code to something like:

Private Sub cmdRptCustLabels_Click()
On Error GoTo Err_cmdRptCustLabels_Click

Dim stDocName As String
Dim strWhere As String 'added

stDocName = "rptSIP"
If Not IsNull(Me.cboOrigin) Then
strWhere = "[Origin]='" & Me.cbOrigin & "'"
End If
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdRptCustLabels_Click:
Exit Sub

Err_cmdRptCustLabels_Click:
MsgBox Err.Description
Resume Exit_cmdRptCustLabels_Click

End Sub

If Origin is numeric then change a line to
strWhere = "[Origin]=" & Me.cbOrigin

 

[Paul Dennis]

thanks for this I will give it a go. Is there something similar if I want to
extract to excel just the records were the origin is xyz.

currently I have a macro, that uses Transferspreadsheet, Transfer Type =
Export, Table Name = BAU Open.

How would I get the [Origin] value into the query?

Create a combo box on a form (frmRptCriteria!cboOrigin) and use the command
button wizard to create a button that opens your report. Right-click the
button in design view and change the code to something like:

Private Sub cmdRptCustLabels_Click()
On Error GoTo Err_cmdRptCustLabels_Click

Dim stDocName As String
Dim strWhere As String 'added

stDocName = "rptSIP"
If Not IsNull(Me.cboOrigin) Then
strWhere = "[Origin]='" & Me.cbOrigin & "'"
End If
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdRptCustLabels_Click:
Exit Sub

Err_cmdRptCustLabels_Click:
MsgBox Err.Description
Resume Exit_cmdRptCustLabels_Click

End Sub

If Origin is numeric then change a line to
strWhere = "[Origin]=" & Me.cbOrigin


--
Duane Hookom
MS Access MVP

I have a table [SIP] and I would like to create a report to report on all
records were a field [Origin] is = to what the user selects from a lookup.
Normally I would use a criteria in a query, however this leaves the user
with
free format entry, what I need is to be able to allow them to select from
a
list. I can't seem to be able to do this in a query or on the report
unless
I'm missing anything?

All I can think of is to have a form for the selection but if so how do I
then run a macro to run the report and pass the value selected to it. Or
is
there a better way?

 

[Duane Hookom]

I generally create an MS Query from Excel to pull Access records. This allows
you to sort and filter.

--
Duane Hookom
Microsoft Access MVP

thanks for this I will give it a go. Is there something similar if I want to
extract to excel just the records were the origin is xyz.

currently I have a macro, that uses Transferspreadsheet, Transfer Type =
Export, Table Name = BAU Open.

How would I get the [Origin] value into the query?

Create a combo box on a form (frmRptCriteria!cboOrigin) and use the command
button wizard to create a button that opens your report. Right-click the
button in design view and change the code to something like:

Private Sub cmdRptCustLabels_Click()
On Error GoTo Err_cmdRptCustLabels_Click

Dim stDocName As String
Dim strWhere As String 'added

stDocName = "rptSIP"
If Not IsNull(Me.cboOrigin) Then
strWhere = "[Origin]='" & Me.cbOrigin & "'"
End If
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdRptCustLabels_Click:
Exit Sub

Err_cmdRptCustLabels_Click:
MsgBox Err.Description
Resume Exit_cmdRptCustLabels_Click

End Sub

If Origin is numeric then change a line to
strWhere = "[Origin]=" & Me.cbOrigin


--
Duane Hookom
MS Access MVP

I have a table [SIP] and I would like to create a report to report on all
records were a field [Origin] is = to what the user selects from a lookup.
Normally I would use a criteria in a query, however this leaves the user
with
free format entry, what I need is to be able to allow them to select from
a
list. I can't seem to be able to do this in a query or on the report
unless
I'm missing anything?

All I can think of is to have a form for the selection but if so how do I
then run a macro to run the report and pass the value selected to it. Or
is
there a better way?