Report based on form value

  • Thread starter Thread starter gsanderson99
  • Start date Start date
G

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
 
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.
 
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.
Click to expand...

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
 
Geoff -

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

--
Daryl S



[email protected] said:
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.
Click to expand...
strCriteria = "[ID#] = " & Me.ID
Click to expand...
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.
Click to expand...
I'm probably missing something simple, can anyone help?
Click to expand...
Geoff
.- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Dim strReport As String
Dim strCriteria As String

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

DoCmd.OpenReport strReport, acViewPreview, , strCriteria


Geoff
 
you could do this:


if IsNull(Dlookup("[ID#]", "tableName", "[ID#] = '" & me.ID & "'"))
then
Msgbox "Not found"
else
Docmd.OpenReport........
end if
 
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


[email protected] said:
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.
Click to expand...

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
.
Click to expand...
 
you could do this:

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

This worked perfectly, thank you!

Geoff
 
Back
Top