Command button code problem

[Tony Williams]

I have a form with a command button that opens a report in preview. The form
has a combo box from which you select the record that should be shown on the
report but when I click on the command button I get all the records not just
the one I have clicked What's wrong with my code?
Here it is
Private Sub cmdprintcr_Click()
On Error GoTo Err_cmdprintcr_Click
Dim strAVCISCode As String
'set strAVCISCode equal to the selected value before closing the form,
otherwise it will give us an error
strAVCISCode = "AVCISCode"
DoCmd.OpenReport "rptCrimereport", acViewPreview,
"strAVCIScode=[AVCISCodea]"


Exit_cmdprintcr_Click:
Exit Sub

Err_cmdprintcr_Click:
MsgBox Err.Description
Resume Exit_cmdprintcr_Click
End Sub

AVCISCode is the field holding the number of the record I want and
AVCISCodea is the name of the control combobox on my form.

Thanks in anticipation
Tony

 

[Tony Williams]

I've changed the Docmd line to this and it still doesn't work?
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Dim stDocName As String


stDocName = "rptCrimereport"
DoCmd.OpenReport stDocName, acPreview, , "[AVCISCode]=Forms![Print Crime
Report]![AVCISCodea]"

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

Anyone any ideas?
Thanks
Tony

 

[Gina Whipp]

Try:

Forms![Print Crime Report]![AVCISCodea] <--- Is there supposed to be an 'a'
at the end of AVCISCode?

Or

If text...
DoCmd.OpenReport stDocName, acPreview, , "[AVCISCode]= " & Forms![Print
Crime Report]![AVCISCodea]

Or

If numeric...
DoCmd.OpenReport "rptCancelContractNotice", acPreview, , "[scContractID]='"
& Forms![Print Crime Report]![AVCISCodea] & "'"

You might also want a If, Then Else statement if Forms![Print Crime
Report]![AVCISCodea] is null.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

I've changed the Docmd line to this and it still doesn't work?
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Dim stDocName As String


stDocName = "rptCrimereport"
DoCmd.OpenReport stDocName, acPreview, , "[AVCISCode]=Forms![Print
Crime Report]![AVCISCodea]"

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

Anyone any ideas?
Thanks
Tony

I have a form with a command button that opens a report in preview. The
form has a combo box from which you select the record that should be shown
on the report but when I click on the command button I get all the records
not just the one I have clicked What's wrong with my code?
Here it is
Private Sub cmdprintcr_Click()
On Error GoTo Err_cmdprintcr_Click
Dim strAVCISCode As String
'set strAVCISCode equal to the selected value before closing the form,
otherwise it will give us an error
strAVCISCode = "AVCISCode"
DoCmd.OpenReport "rptCrimereport", acViewPreview,
"strAVCIScode=[AVCISCodea]"


Exit_cmdprintcr_Click:
Exit Sub

Err_cmdprintcr_Click:
MsgBox Err.Description
Resume Exit_cmdprintcr_Click
End Sub

AVCISCode is the field holding the number of the record I want and
AVCISCodea is the name of the control combobox on my form.

Thanks in anticipation
Tony

 

[Tony Williams]

Thanks Gina I'll try that.It's now 19.17 in the UK and I've had enough for
today so I'll try tomorrow.
Thanks again
Tony

Try:

Forms![Print Crime Report]![AVCISCodea] <--- Is there supposed to be an
'a' at the end of AVCISCode?

Or

If text...
DoCmd.OpenReport stDocName, acPreview, , "[AVCISCode]= " & Forms![Print
Crime Report]![AVCISCodea]

Or

If numeric...
DoCmd.OpenReport "rptCancelContractNotice", acPreview, ,
"[scContractID]='" & Forms![Print Crime Report]![AVCISCodea] & "'"

You might also want a If, Then Else statement if Forms![Print Crime
Report]![AVCISCodea] is null.


--
Gina Whipp

"I feel I have been denied critical, need to know, information!" - Tremors
II

I've changed the Docmd line to this and it still doesn't work?
Private Sub Command27_Click()
On Error GoTo Err_Command27_Click

Dim stDocName As String


stDocName = "rptCrimereport"
DoCmd.OpenReport stDocName, acPreview, , "[AVCISCode]=Forms![Print
Crime Report]![AVCISCodea]"

Exit_Command27_Click:
Exit Sub

Err_Command27_Click:
MsgBox Err.Description
Resume Exit_Command27_Click

End Sub

Anyone any ideas?
Thanks
Tony

I have a form with a command button that opens a report in preview. The
form has a combo box from which you select the record that should be
shown on the report but when I click on the command button I get all the
records not just the one I have clicked What's wrong with my code?
Here it is
Private Sub cmdprintcr_Click()
On Error GoTo Err_cmdprintcr_Click
Dim strAVCISCode As String
'set strAVCISCode equal to the selected value before closing the
form, otherwise it will give us an error
strAVCISCode = "AVCISCode"
DoCmd.OpenReport "rptCrimereport", acViewPreview,
"strAVCIScode=[AVCISCodea]"


Exit_cmdprintcr_Click:
Exit Sub

Err_cmdprintcr_Click:
MsgBox Err.Description
Resume Exit_cmdprintcr_Click
End Sub

AVCISCode is the field holding the number of the record I want and
AVCISCodea is the name of the control combobox on my form.

Thanks in anticipation
Tony

 

[fredg]

I have a form with a command button that opens a report in preview. The form
has a combo box from which you select the record that should be shown on the
report but when I click on the command button I get all the records not just
the one I have clicked What's wrong with my code?
Here it is
Private Sub cmdprintcr_Click()
On Error GoTo Err_cmdprintcr_Click
Dim strAVCISCode As String
'set strAVCISCode equal to the selected value before closing the form,
otherwise it will give us an error
strAVCISCode = "AVCISCode"
DoCmd.OpenReport "rptCrimereport", acViewPreview,
"strAVCIScode=[AVCISCodea]"


Exit_cmdprintcr_Click:
Exit Sub

Err_cmdprintcr_Click:
MsgBox Err.Description
Resume Exit_cmdprintcr_Click
End Sub

AVCISCode is the field holding the number of the record I want and
AVCISCodea is the name of the control combobox on my form.

Thanks in anticipation
Tony

You need to add a comma to place your argument in the Where argument
position (not in the Filter position).
Also the value must be concatenated into the Where string.

If the datatype of [AVISCode] is a Number datatype:
strAVCISCode = "[AVCISCode] = " & Me![AVCISCodea]
DoCmd.OpenReport "rptCrimereport", acViewPreview, , strAVISCode

If the datatype of [AVISCode] is a Text datatype:
strAVCISCode = "[AVCISCode] = '" & Me![AVCISCodea] & "'"
DoCmd.OpenReport "rptCrimereport", acViewPreview, , strAVISCode

Fred