Report based on form value

[gsanderson99]

I have a report based on a table. The table contains an ID# which is
unique to an individual, but the ID# may appear more than once in the
table. I also have a form that displays one ID# and has a command
button to print the report. I use the DoCmd.OpenReport command in the
OnClick function of the command button, but am having problems with
the criteria part.

strCriteria = "[ID#] = " & Me.ID

where ID# is the field in the table and ID is the field on the form,
returns a dialog box asking for the ID#, though the current ID# is
displayed in the dialog box.

I'm probably missing something simple, can anyone help?

Geoff

 

[ghetto_banjo]

are you certain that the report's record source is your table (or a
query that selects [ID#])? when Access prompts for a value like
that, it usually means that the field name is not found in the
report's record source.

 

[gsanderson99]

are you certain that the report's record source is your table (or a
query that selects [ID#])?   when Access prompts for a value like
that, it usually means that the field name is not found in the
report's record source.

I confirmed that the report's record source is a table and the ID# is
the correct field name. However, I found the problem. The ID field in
the form is a string, so I changed the strCriteria to:

strCriteria = "[ID#] = " & " ' " & Me.ID & " ' "

This worked, except when the ID# does not exist in the table. I added
a NoData function to the report, but after displaying the "No Record
Found" message box I created, I get an error message that leads me
back to the DoCmd, which is:

DoCmd.OpenReport, strReport, acViewPreview, , strCriteria

The NoData function is:

Private Sub Report_NoData(Cancel As Integer)

Cancel = MsgBox("No Record Found", vbInformation, Me.Caption)

End Sub

Any ideas?

Geoff

 

[Daryl S]

Geoff -

Post your DoCmd.OpenReport statement so we can help you with it.

 

[gsanderson99]

Geoff -

Post your DoCmd.OpenReport statement so we can help you with it.

--
Daryl S

I have a report based on a table. The table contains an ID# which is
unique to an individual, but the ID# may appear more than once in the
table. I also have a form that displays one ID# and has a command
button to print the report. I use the DoCmd.OpenReport command in the
OnClick function of the command button, but am having problems with
the criteria part.

strCriteria = "[ID#] = " & Me.ID

where ID# is the field in the table and ID is the field on the form,
returns a dialog box asking for the ID#, though the current ID# is
displayed in the dialog box.

I'm probably missing something simple, can anyone help?

Geoff
.- Hide quoted text -

- Show quoted text -

Dim strReport As String
Dim strCriteria As String

strReport = "rptMember_Complexity_Tool"
strCriteria = "[ID#] = " & "'" & Me.txtID & "'"

DoCmd.OpenReport strReport, acViewPreview, , strCriteria


Geoff

 

[ghetto_banjo]

you could do this:


if IsNull(Dlookup("[ID#]", "tableName", "[ID#] = '" & me.ID & "'"))
then
Msgbox "Not found"
else
Docmd.OpenReport........
end if

 

[Duane Hookom]

I think the error message (which you didn't provide) has to do with canceling
the opening of the report. Your error handling must be in the same procedure
that opens the report:

Select Case Err
Case 2501
'ignore
Case Else
' real error handling goes here
End Select

--
Duane Hookom
Microsoft Access MVP

are you certain that the report's record source is your table (or a
query that selects [ID#])? when Access prompts for a value like
that, it usually means that the field name is not found in the
report's record source.

I confirmed that the report's record source is a table and the ID# is
the correct field name. However, I found the problem. The ID field in
the form is a string, so I changed the strCriteria to:

strCriteria = "[ID#] = " & " ' " & Me.ID & " ' "

This worked, except when the ID# does not exist in the table. I added
a NoData function to the report, but after displaying the "No Record
Found" message box I created, I get an error message that leads me
back to the DoCmd, which is:

DoCmd.OpenReport, strReport, acViewPreview, , strCriteria

The NoData function is:

Private Sub Report_NoData(Cancel As Integer)

Cancel = MsgBox("No Record Found", vbInformation, Me.Caption)

End Sub

Any ideas?

Geoff
.

 

[gsanderson99]

you could do this:

if IsNull(Dlookup("[ID#]", "tableName", "[ID#] = '" & me.ID & "'"))
then
   Msgbox "Not found"
else
   Docmd.OpenReport........
end if

This worked perfectly, thank you!

Geoff