Command button code problem

  • Thread starter Thread starter Tony Williams
  • Start date Start date
T

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
 
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
 
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
Tony Williams said:
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
Tony Williams said:
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
Click to expand...
Click to expand...
 
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
Gina Whipp said:
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
Tony Williams said:
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
Tony Williams said:
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
Click to expand...
Click to expand...
Click to expand...
 
Tony Williams said:
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
Click to expand...

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
 
Back
Top