Open report from a prompt form

[Tony Williams]

I have a form, frmviewid, on which there is a combo box, cmbidcard, which is
based on a table tblindividual. The row sorce of cmbidcard is

SELECT tblindividual.ID, [txtfirstname] & (" "+[txtsecondname]) & ("
"+[txtthirdname]) & (" "+[txtsurname]) AS Fullname FROM tblindividual;

There is a command button, cmdviewreport, that the user clicks to open a
report, rptIDCardForm. If the user selects a name from the combo box I want
the report to open for that person. If it is left blank I want the report to
include all the names. There is a control on my report called txtFullname
the row source for this is the same as the row source for cmbidcard ie

=[txtfirstname] & (" "+[txtsecondname]) & (" "+[txtthirdname]) & ("
"+[txtsurname])

If the value of cmbidcard = txtFullname then the report should only print
for that name.
However when I click on the command button I get a prompt asking me for the
value of txtFullname
Here is my code behind the OnClick event of the command button

Private Sub cmdviewreport_Click()
On Error GoTo Err_cmdviewreport_Click

Dim stDocName As String

stDocName = "rptIDCardForm"
DoCmd.OpenReport stDocName, acViewPreview, "",
Eval("IIf([Forms]![frmviewid]![cmbidcard] Is
Null,"""",""[TxtFullname]=Forms![frmviewid]![cmbidcard]"")")

Exit_cmdviewreport_Click:
Exit Sub

Err_cmdviewreport_Click:
MsgBox Err.Description
Resume Exit_cmdviewreport_Click

End Sub


Can anyone help?
Thanks
Tony

 

[Marshall Barton]

I have a form, frmviewid, on which there is a combo box, cmbidcard, which is
based on a table tblindividual. The row sorce of cmbidcard is

SELECT tblindividual.ID, [txtfirstname] & (" "+[txtsecondname]) & ("
"+[txtthirdname]) & (" "+[txtsurname]) AS Fullname FROM tblindividual;

There is a command button, cmdviewreport, that the user clicks to open a
report, rptIDCardForm. If the user selects a name from the combo box I want
the report to open for that person. If it is left blank I want the report to
include all the names. There is a control on my report called txtFullname
the row source for this is the same as the row source for cmbidcard ie

=[txtfirstname] & (" "+[txtsecondname]) & (" "+[txtthirdname]) & ("
"+[txtsurname])

If the value of cmbidcard = txtFullname then the report should only print
for that name.
However when I click on the command button I get a prompt asking me for the
value of txtFullname
Here is my code behind the OnClick event of the command button

Private Sub cmdviewreport_Click()
On Error GoTo Err_cmdviewreport_Click

Dim stDocName As String

stDocName = "rptIDCardForm"
DoCmd.OpenReport stDocName, acViewPreview, "",
Eval("IIf([Forms]![frmviewid]![cmbidcard] Is
Null,"""",""[TxtFullname]=Forms![frmviewid]![cmbidcard]"")")

You can not filter a report using a report control 's value.

The standard code for what you are trying to do is more
like:

If IsNull(Me.cmbidcard) Then
DoCmd.OpenReport stDocName, acViewPreview, , _
Else
DoCmd.OpenReport stDocName, acViewPreview, , _
"ID=" & Me.cmbidcard
End If

 

[Tony Williams]

Thanks Marshall here is my code based on your reply

Private Sub cmdviewreport_Click()
On Error GoTo Err_cmdviewreport_Click
Dim stdocname As String
stdocname = "rptidcardform"
If IsNull(Me.cmbidcard) Then
DoCmd.OpenReport stdocname, acViewPreview
Else
DoCmd.OpenReport stdocname, acViewPreview, , "[txtFullname]=" &
Me.cmbidcard
End If
Exit_cmdviewreport_Click:
Exit Sub
Err_cmdviewreport_Click:
MsgBox Err.Description
Resume Exit_cmdviewreport_Click
End Sub

When I leave the combo box blank the report opens fine. However when I
choose a name from the combo box I get an error message
Syntax error (missing operator) in query expression '([txtFullname]=Martin
Leyshon)'.

Where am I going wrong, I haven't got a query?
Thanks for your help
Tony

I have a form, frmviewid, on which there is a combo box, cmbidcard, which
is
based on a table tblindividual. The row sorce of cmbidcard is

SELECT tblindividual.ID, [txtfirstname] & (" "+[txtsecondname]) & ("
"+[txtthirdname]) & (" "+[txtsurname]) AS Fullname FROM tblindividual;

There is a command button, cmdviewreport, that the user clicks to open a
report, rptIDCardForm. If the user selects a name from the combo box I
want
the report to open for that person. If it is left blank I want the report
to
include all the names. There is a control on my report called txtFullname
the row source for this is the same as the row source for cmbidcard ie

=[txtfirstname] & (" "+[txtsecondname]) & (" "+[txtthirdname]) & ("
"+[txtsurname])

If the value of cmbidcard = txtFullname then the report should only print
for that name.
However when I click on the command button I get a prompt asking me for
the
value of txtFullname
Here is my code behind the OnClick event of the command button

Private Sub cmdviewreport_Click()
On Error GoTo Err_cmdviewreport_Click

Dim stDocName As String

stDocName = "rptIDCardForm"
DoCmd.OpenReport stDocName, acViewPreview, "",
Eval("IIf([Forms]![frmviewid]![cmbidcard] Is
Null,"""",""[TxtFullname]=Forms![frmviewid]![cmbidcard]"")")

You can not filter a report using a report control 's value.

The standard code for what you are trying to do is more
like:

If IsNull(Me.cmbidcard) Then
DoCmd.OpenReport stDocName, acViewPreview, , _
Else
DoCmd.OpenReport stDocName, acViewPreview, , _
"ID=" & Me.cmbidcard
End If

 

[Marshall Barton]

You can not filter by txtFullname. The filter field
**must** be a field in the report's record source
table/query. I think you need to filter by the ID field.

Then you also need to change the combo box's BoundColumn to
1 to make the ID field the combo box's value. Then you
should be able to use the where condition I posted earlier.
--
Marsh
MVP [MS Access]

Thanks Marshall here is my code based on your reply

Private Sub cmdviewreport_Click()
On Error GoTo Err_cmdviewreport_Click
Dim stdocname As String
stdocname = "rptidcardform"
If IsNull(Me.cmbidcard) Then
DoCmd.OpenReport stdocname, acViewPreview
Else
DoCmd.OpenReport stdocname, acViewPreview, , "[txtFullname]=" &
Me.cmbidcard
End If
Exit_cmdviewreport_Click:
Exit Sub
Err_cmdviewreport_Click:
MsgBox Err.Description
Resume Exit_cmdviewreport_Click
End Sub

When I leave the combo box blank the report opens fine. However when I
choose a name from the combo box I get an error message
Syntax error (missing operator) in query expression '([txtFullname]=Martin
Leyshon)'.

You can not filter a report using a report control 's value.

The standard code for what you are trying to do is more
like:

If IsNull(Me.cmbidcard) Then
DoCmd.OpenReport stDocName, acViewPreview, , _
Else
DoCmd.OpenReport stDocName, acViewPreview, , _
"ID=" & Me.cmbidcard
End If

I have a form, frmviewid, on which there is a combo box, cmbidcard, which
is
based on a table tblindividual. The row sorce of cmbidcard is

SELECT tblindividual.ID, [txtfirstname] & (" "+[txtsecondname]) & ("
"+[txtthirdname]) & (" "+[txtsurname]) AS Fullname FROM tblindividual;

There is a command button, cmdviewreport, that the user clicks to open a
report, rptIDCardForm. If the user selects a name from the combo box I
want
the report to open for that person. If it is left blank I want the report
to
include all the names. There is a control on my report called txtFullname
the row source for this is the same as the row source for cmbidcard ie

=[txtfirstname] & (" "+[txtsecondname]) & (" "+[txtthirdname]) & ("
"+[txtsurname])

If the value of cmbidcard = txtFullname then the report should only print
for that name.
However when I click on the command button I get a prompt asking me for
the
value of txtFullname
Here is my code behind the OnClick event of the command button

Private Sub cmdviewreport_Click()
On Error GoTo Err_cmdviewreport_Click

Dim stDocName As String

stDocName = "rptIDCardForm"
DoCmd.OpenReport stDocName, acViewPreview, "",
Eval("IIf([Forms]![frmviewid]![cmbidcard] Is
Null,"""",""[TxtFullname]=Forms![frmviewid]![cmbidcard]"")")

 

[Tony Williams]

Thanks Marshall, worked a treat. I take it from what you said that you can't
filter on a calculated control, yet another thing I've learned. This is
sooooo useful as a place of learning.
Thanks again
Tony

You can not filter by txtFullname. The filter field
**must** be a field in the report's record source
table/query. I think you need to filter by the ID field.

Then you also need to change the combo box's BoundColumn to
1 to make the ID field the combo box's value. Then you
should be able to use the where condition I posted earlier.
--
Marsh
MVP [MS Access]

Thanks Marshall here is my code based on your reply

Private Sub cmdviewreport_Click()
On Error GoTo Err_cmdviewreport_Click
Dim stdocname As String
stdocname = "rptidcardform"
If IsNull(Me.cmbidcard) Then
DoCmd.OpenReport stdocname, acViewPreview
Else
DoCmd.OpenReport stdocname, acViewPreview, , "[txtFullname]=" &
Me.cmbidcard
End If
Exit_cmdviewreport_Click:
Exit Sub
Err_cmdviewreport_Click:
MsgBox Err.Description
Resume Exit_cmdviewreport_Click
End Sub

When I leave the combo box blank the report opens fine. However when I
choose a name from the combo box I get an error message
Syntax error (missing operator) in query expression '([txtFullname]=Martin
Leyshon)'.

You can not filter a report using a report control 's value.

The standard code for what you are trying to do is more
like:

If IsNull(Me.cmbidcard) Then
DoCmd.OpenReport stDocName, acViewPreview, , _
Else
DoCmd.OpenReport stDocName, acViewPreview, , _
"ID=" & Me.cmbidcard
End If
Tony Williams wrote:
I have a form, frmviewid, on which there is a combo box, cmbidcard,
which
is
based on a table tblindividual. The row sorce of cmbidcard is

SELECT tblindividual.ID, [txtfirstname] & (" "+[txtsecondname]) & ("
"+[txtthirdname]) & (" "+[txtsurname]) AS Fullname FROM tblindividual;

There is a command button, cmdviewreport, that the user clicks to open a
report, rptIDCardForm. If the user selects a name from the combo box I
want
the report to open for that person. If it is left blank I want the
report
to
include all the names. There is a control on my report called
txtFullname
the row source for this is the same as the row source for cmbidcard ie

=[txtfirstname] & (" "+[txtsecondname]) & (" "+[txtthirdname]) & ("
"+[txtsurname])

If the value of cmbidcard = txtFullname then the report should only
print
for that name.
However when I click on the command button I get a prompt asking me for
the
value of txtFullname
Here is my code behind the OnClick event of the command button

Private Sub cmdviewreport_Click()
On Error GoTo Err_cmdviewreport_Click

Dim stDocName As String

stDocName = "rptIDCardForm"
DoCmd.OpenReport stDocName, acViewPreview, "",
Eval("IIf([Forms]![frmviewid]![cmbidcard] Is
Null,"""",""[TxtFullname]=Forms![frmviewid]![cmbidcard]"")")