Parameters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:
I currently have a Command Button on a Form that is used to Preview a report
of a table that consist of about 260 rows in Access. There are about 15
field names in this table. In the Event Procedure for the Preview Report
command button is there a way to add a feature that will ask for a specifc
result on the Field Name: Topic ID (e.g. A01), so when the report is
previewed only the rows specifc to that parameter will appear and not the
entire 260 rows?

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String

stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub
 
I think you only need to add a criteria to your query/record source or use a
where clause in DoCmd.OpenReport.
Add a text box (txtTopicID) on your form for users to enter or select a
topic. The modify your code:

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere as String
If Not IsNull(Me.txtTopicID) Then
strWhere = "[Topic ID] = " & Me.txtTopicID
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub
 
Hi Duane: Thanks for this. I re-wrote the statement. The text box is named
Text82 and the Field Name is SDS Topic. When I run it it I get a Comple
Error on End Sub. It reads "Block If without End If"

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.Text82) Then
strWhere = "[SDS Topic]= " & Me.Text82
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

Duane Hookom said:
I think you only need to add a criteria to your query/record source or use a
where clause in DoCmd.OpenReport.
Add a text box (txtTopicID) on your form for users to enter or select a
topic. The modify your code:

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere as String
If Not IsNull(Me.txtTopicID) Then
strWhere = "[Topic ID] = " & Me.txtTopicID
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

--
Duane Hookom
MS Access MVP


CP said:
Hi:
I currently have a Command Button on a Form that is used to Preview a
report
of a table that consist of about 260 rows in Access. There are about 15
field names in this table. In the Event Procedure for the Preview Report
command button is there a way to add a feature that will ask for a specifc
result on the Field Name: Topic ID (e.g. A01), so when the report is
previewed only the rows specifc to that parameter will appear and not the
entire 260 rows?

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String

stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub
Click to expand...
Click to expand...
 
Ooops my bad, but you should really learn how to fix my errors:
Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.Text82) Then
strWhere = "[SDS Topic]= " & Me.Text82
End If ' I missed this line
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

BTW: this assumes SDS Topic is Numeric. Also, do yourself a favor in the
future and give your command buttons, text boxes and other objects
descriptive names. A report named "LOV_tbl" suggests the object is a table,
not a report and Text82 doesn't suggest anything.


--
Duane Hookom
MS Access MVP
--

CP said:
Hi Duane: Thanks for this. I re-wrote the statement. The text box is
named
Text82 and the Field Name is SDS Topic. When I run it it I get a Comple
Error on End Sub. It reads "Block If without End If"

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.Text82) Then
strWhere = "[SDS Topic]= " & Me.Text82
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

Duane Hookom said:
I think you only need to add a criteria to your query/record source or
use a
where clause in DoCmd.OpenReport.
Add a text box (txtTopicID) on your form for users to enter or select a
topic. The modify your code:

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere as String
If Not IsNull(Me.txtTopicID) Then
strWhere = "[Topic ID] = " & Me.txtTopicID
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

--
Duane Hookom
MS Access MVP


CP said:
Hi:
I currently have a Command Button on a Form that is used to Preview a
report
of a table that consist of about 260 rows in Access. There are about
15
field names in this table. In the Event Procedure for the Preview
Report
command button is there a way to add a feature that will ask for a
specifc
result on the Field Name: Topic ID (e.g. A01), so when the report is
previewed only the rows specifc to that parameter will appear and not
the
entire 260 rows?

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String

stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks Duane! I need to remeber to be specifc on the naming. I get lazy...
Actually it's a data type of text for the "SDS Topic" field name, is that
what you meant?
Could I make it a wildcard search, so when the parameter box appears I would
not have to enter the specifc criteria of "A01 Employment Status" , and just
enter "A01"?

Duane Hookom said:
Ooops my bad, but you should really learn how to fix my errors:
Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.Text82) Then
strWhere = "[SDS Topic]= " & Me.Text82
End If ' I missed this line
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

BTW: this assumes SDS Topic is Numeric. Also, do yourself a favor in the
future and give your command buttons, text boxes and other objects
descriptive names. A report named "LOV_tbl" suggests the object is a table,
not a report and Text82 doesn't suggest anything.


--
Duane Hookom
MS Access MVP
--

CP said:
Hi Duane: Thanks for this. I re-wrote the statement. The text box is
named
Text82 and the Field Name is SDS Topic. When I run it it I get a Comple
Error on End Sub. It reads "Block If without End If"

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.Text82) Then
strWhere = "[SDS Topic]= " & Me.Text82
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

Duane Hookom said:
I think you only need to add a criteria to your query/record source or
use a
where clause in DoCmd.OpenReport.
Add a text box (txtTopicID) on your form for users to enter or select a
topic. The modify your code:

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere as String
If Not IsNull(Me.txtTopicID) Then
strWhere = "[Topic ID] = " & Me.txtTopicID
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

--
Duane Hookom
MS Access MVP


Hi:
I currently have a Command Button on a Form that is used to Preview a
report
of a table that consist of about 260 rows in Access. There are about
15
field names in this table. In the Event Procedure for the Preview
Report
command button is there a way to add a feature that will ask for a
specifc
result on the Field Name: Topic ID (e.g. A01), so when the report is
previewed only the rows specifc to that parameter will appear and not
the
entire 260 rows?

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String

stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Let's remove the "lazy" and re-write the code to accept the entry of the
first few characters of the topic:

Private Sub cmdLOVRpt_Click()
On Error GoTo Err_cmdLOVRpt_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.txtTopic) Then
strWhere = "[SDS Topic] Like """ & Me.txtTopic & "*"" "
End If ' I missed this line
stDocName = "rptLOV"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdLOVRpt_Click:
Exit Sub

Err_cmdLOVRpt_Click:
MsgBox Err.Description
Resume Exit_cmdLOVRpt_Click

End Sub


--
Duane Hookom
MS Access MVP
--

CP said:
Thanks Duane! I need to remeber to be specifc on the naming. I get
lazy...
Actually it's a data type of text for the "SDS Topic" field name, is that
what you meant?
Could I make it a wildcard search, so when the parameter box appears I
would
not have to enter the specifc criteria of "A01 Employment Status" , and
just
enter "A01"?

Duane Hookom said:
Ooops my bad, but you should really learn how to fix my errors:
Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.Text82) Then
strWhere = "[SDS Topic]= " & Me.Text82
End If ' I missed this line
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

BTW: this assumes SDS Topic is Numeric. Also, do yourself a favor in the
future and give your command buttons, text boxes and other objects
descriptive names. A report named "LOV_tbl" suggests the object is a
table,
not a report and Text82 doesn't suggest anything.


--
Duane Hookom
MS Access MVP
--

CP said:
Hi Duane: Thanks for this. I re-wrote the statement. The text box is
named
Text82 and the Field Name is SDS Topic. When I run it it I get a
Comple
Error on End Sub. It reads "Block If without End If"

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.Text82) Then
strWhere = "[SDS Topic]= " & Me.Text82
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

:

I think you only need to add a criteria to your query/record source or
use a
where clause in DoCmd.OpenReport.
Add a text box (txtTopicID) on your form for users to enter or select
a
topic. The modify your code:

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere as String
If Not IsNull(Me.txtTopicID) Then
strWhere = "[Topic ID] = " & Me.txtTopicID
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

--
Duane Hookom
MS Access MVP


Hi:
I currently have a Command Button on a Form that is used to Preview
a
report
of a table that consist of about 260 rows in Access. There are
about
15
field names in this table. In the Event Procedure for the Preview
Report
command button is there a way to add a feature that will ask for a
specifc
result on the Field Name: Topic ID (e.g. A01), so when the report is
previewed only the rows specifc to that parameter will appear and
not
the
entire 260 rows?

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String

stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Thanks again. It's working fine.

Duane Hookom said:
Let's remove the "lazy" and re-write the code to accept the entry of the
first few characters of the topic:

Private Sub cmdLOVRpt_Click()
On Error GoTo Err_cmdLOVRpt_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.txtTopic) Then
strWhere = "[SDS Topic] Like """ & Me.txtTopic & "*"" "
End If ' I missed this line
stDocName = "rptLOV"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_cmdLOVRpt_Click:
Exit Sub

Err_cmdLOVRpt_Click:
MsgBox Err.Description
Resume Exit_cmdLOVRpt_Click

End Sub


--
Duane Hookom
MS Access MVP
--

CP said:
Thanks Duane! I need to remeber to be specifc on the naming. I get
lazy...
Actually it's a data type of text for the "SDS Topic" field name, is that
what you meant?
Could I make it a wildcard search, so when the parameter box appears I
would
not have to enter the specifc criteria of "A01 Employment Status" , and
just
enter "A01"?

Duane Hookom said:
Ooops my bad, but you should really learn how to fix my errors:
Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.Text82) Then
strWhere = "[SDS Topic]= " & Me.Text82
End If ' I missed this line
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

BTW: this assumes SDS Topic is Numeric. Also, do yourself a favor in the
future and give your command buttons, text boxes and other objects
descriptive names. A report named "LOV_tbl" suggests the object is a
table,
not a report and Text82 doesn't suggest anything.


--
Duane Hookom
MS Access MVP
--

Hi Duane: Thanks for this. I re-wrote the statement. The text box is
named
Text82 and the Field Name is SDS Topic. When I run it it I get a
Comple
Error on End Sub. It reads "Block If without End If"

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere As String
If Not IsNull(Me.Text82) Then
strWhere = "[SDS Topic]= " & Me.Text82
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

:

I think you only need to add a criteria to your query/record source or
use a
where clause in DoCmd.OpenReport.
Add a text box (txtTopicID) on your form for users to enter or select
a
topic. The modify your code:

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String
Dim strWhere as String
If Not IsNull(Me.txtTopicID) Then
strWhere = "[Topic ID] = " & Me.txtTopicID
stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub

--
Duane Hookom
MS Access MVP


Hi:
I currently have a Command Button on a Form that is used to Preview
a
report
of a table that consist of about 260 rows in Access. There are
about
15
field names in this table. In the Event Procedure for the Preview
Report
command button is there a way to add a feature that will ask for a
specifc
result on the Field Name: Topic ID (e.g. A01), so when the report is
previewed only the rows specifc to that parameter will appear and
not
the
entire 260 rows?

Private Sub Command130_Click()
On Error GoTo Err_Command130_Click

Dim stDocName As String

stDocName = "LOV_tbl"
DoCmd.OpenReport stDocName, acPreview

Exit_Command130_Click:
Exit Sub

Err_Command130_Click:
MsgBox Err.Description
Resume Exit_Command130_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
Back
Top