Open Report from button with variable

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

New to Access Programming and would like to open (preview) a report from
clicking a form button. I would like the form to pass a value (its UID) to
the underlying report query (Stored Procedure) to narrow the results. Not
working, any help? Thanks

Button's Event Procedure:

Private Sub Print_Roster_Click()
On Error GoTo Err_Print_Roster_Click

Dim stDocName As String

stDocName = "CourseRoster"
stLinkCriteria = "[ScheduleDetailsID]=" & Me![ScheduleDetailsID]
DoCmd.OpenReport stDocName, acPreview, , , stLinkCriteria

Exit_Print_Roster_Click:
Exit Sub

Err_Print_Roster_Click:
MsgBox Err.Description
Resume Exit_Print_Roster_Click

End Sub
 
Also, using a WhereCondition with a stored procedure as the source of the
report is not the best idea. Instead, you should add the parameter to your
stored procedure and add it to the InputParameters property as described
many times in this newsgroup.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Sylvain Lafontaine said:
Remove a comma before stLinkCriteria.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: http://cerbermail.com/?QugbLEWINF


Steven said:
New to Access Programming and would like to open (preview) a report from
clicking a form button. I would like the form to pass a value (its UID)
to
the underlying report query (Stored Procedure) to narrow the results. Not
working, any help? Thanks

Button's Event Procedure:

Private Sub Print_Roster_Click()
On Error GoTo Err_Print_Roster_Click

Dim stDocName As String

stDocName = "CourseRoster"
stLinkCriteria = "[ScheduleDetailsID]=" & Me![ScheduleDetailsID]
DoCmd.OpenReport stDocName, acPreview, , , stLinkCriteria

Exit_Print_Roster_Click:
Exit Sub

Err_Print_Roster_Click:
MsgBox Err.Description
Resume Exit_Print_Roster_Click

End Sub
Click to expand...
Click to expand...
 
Private Sub PrintLetterButton_+Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "NotificationToPhysicians"
strWhere = "[CaseNumber]=" & Me!CaseNumber
DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub

When running the above code, I receive the error:
Run-time error '3075'
Extra ) IN QUERY EXPRESSION '([CaseNumber]=)'.

Any suggestions?
 
Is CaseNumber numeric, or a string? If it's a string, you'll have to revise
your code to:

strWhere = "[CaseNumber]='" & Me!CaseNumber & "'"



Rob

Zanstemic said:
Private Sub PrintLetterButton_+Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "NotificationToPhysicians"
strWhere = "[CaseNumber]=" & Me!CaseNumber
DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub

When running the above code, I receive the error:
Run-time error '3075'
Extra ) IN QUERY EXPRESSION '([CaseNumber]=)'.

Any suggestions?


Steven said:
New to Access Programming and would like to open (preview) a report from
clicking a form button. I would like the form to pass a value (its UID)
to
the underlying report query (Stored Procedure) to narrow the results. Not
working, any help? Thanks

Button's Event Procedure:

Private Sub Print_Roster_Click()
On Error GoTo Err_Print_Roster_Click

Dim stDocName As String

stDocName = "CourseRoster"
stLinkCriteria = "[ScheduleDetailsID]=" & Me![ScheduleDetailsID]
DoCmd.OpenReport stDocName, acPreview, , , stLinkCriteria

Exit_Print_Roster_Click:
Exit Sub

Err_Print_Roster_Click:
MsgBox Err.Description
Resume Exit_Print_Roster_Click

End Sub
Click to expand...
Click to expand...
 
CaseNumber is numberic, however, I'll have to double check and ensure that it
is not defined as text.

Robert Morley said:
Is CaseNumber numeric, or a string? If it's a string, you'll have to revise
your code to:

strWhere = "[CaseNumber]='" & Me!CaseNumber & "'"



Rob

Zanstemic said:
Private Sub PrintLetterButton_+Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "NotificationToPhysicians"
strWhere = "[CaseNumber]=" & Me!CaseNumber
DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub

When running the above code, I receive the error:
Run-time error '3075'
Extra ) IN QUERY EXPRESSION '([CaseNumber]=)'.

Any suggestions?


Steven said:
New to Access Programming and would like to open (preview) a report from
clicking a form button. I would like the form to pass a value (its UID)
to
the underlying report query (Stored Procedure) to narrow the results. Not
working, any help? Thanks

Button's Event Procedure:

Private Sub Print_Roster_Click()
On Error GoTo Err_Print_Roster_Click

Dim stDocName As String

stDocName = "CourseRoster"
stLinkCriteria = "[ScheduleDetailsID]=" & Me![ScheduleDetailsID]
DoCmd.OpenReport stDocName, acPreview, , , stLinkCriteria

Exit_Print_Roster_Click:
Exit Sub

Err_Print_Roster_Click:
MsgBox Err.Description
Resume Exit_Print_Roster_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
The update resolved the error, however, I have a new error with the report.

The successful code:

Private Sub Print_Form_Click()
On Error GoTo Err_Print_Form_Click

Dim stDocName As String
Dim strWhere As String

stDocName = "NotificationForm"
strWhere = "CaseNumber" = " & Me!CaseNumber" & ""

DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Print_Form_Click:
Exit Sub

Err_Print_Form_Click:
MsgBox Err.Description
Resume Exit_Print_Form_Click

The new error is the report does not seem to have the information from the
query and #error is present in all the tag locations.

The query is working fine when the report is launched independently.

Any suggestions?



Zanstemic said:
CaseNumber is numberic, however, I'll have to double check and ensure that it
is not defined as text.

Robert Morley said:
Is CaseNumber numeric, or a string? If it's a string, you'll have to revise
your code to:

strWhere = "[CaseNumber]='" & Me!CaseNumber & "'"



Rob

Zanstemic said:
Private Sub PrintLetterButton_+Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "NotificationToPhysicians"
strWhere = "[CaseNumber]=" & Me!CaseNumber
DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub

When running the above code, I receive the error:
Run-time error '3075'
Extra ) IN QUERY EXPRESSION '([CaseNumber]=)'.

Any suggestions?


:

New to Access Programming and would like to open (preview) a report from
clicking a form button. I would like the form to pass a value (its UID)
to
the underlying report query (Stored Procedure) to narrow the results. Not
working, any help? Thanks

Button's Event Procedure:

Private Sub Print_Roster_Click()
On Error GoTo Err_Print_Roster_Click

Dim stDocName As String

stDocName = "CourseRoster"
stLinkCriteria = "[ScheduleDetailsID]=" & Me![ScheduleDetailsID]
DoCmd.OpenReport stDocName, acPreview, , , stLinkCriteria

Exit_Print_Roster_Click:
Exit Sub

Err_Print_Roster_Click:
MsgBox Err.Description
Resume Exit_Print_Roster_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
What are you trying to do with strWhere? It's distinctly not right, but I'm
not sure what you're trying to do.


Rob

Zanstemic said:
The update resolved the error, however, I have a new error with the
report.

The successful code:

Private Sub Print_Form_Click()
On Error GoTo Err_Print_Form_Click

Dim stDocName As String
Dim strWhere As String

stDocName = "NotificationForm"
strWhere = "CaseNumber" = " & Me!CaseNumber" & ""

DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Print_Form_Click:
Exit Sub

Err_Print_Form_Click:
MsgBox Err.Description
Resume Exit_Print_Form_Click

The new error is the report does not seem to have the information from the
query and #error is present in all the tag locations.

The query is working fine when the report is launched independently.

Any suggestions?



Zanstemic said:
CaseNumber is numberic, however, I'll have to double check and ensure
that it
is not defined as text.

Robert Morley said:
Is CaseNumber numeric, or a string? If it's a string, you'll have to
revise
your code to:

strWhere = "[CaseNumber]='" & Me!CaseNumber & "'"



Rob

Private Sub PrintLetterButton_+Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "NotificationToPhysicians"
strWhere = "[CaseNumber]=" & Me!CaseNumber
DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub

When running the above code, I receive the error:
Run-time error '3075'
Extra ) IN QUERY EXPRESSION '([CaseNumber]=)'.

Any suggestions?


:

New to Access Programming and would like to open (preview) a report
from
clicking a form button. I would like the form to pass a value (its
UID)
to
the underlying report query (Stored Procedure) to narrow the
results. Not
working, any help? Thanks

Button's Event Procedure:

Private Sub Print_Roster_Click()
On Error GoTo Err_Print_Roster_Click

Dim stDocName As String

stDocName = "CourseRoster"
stLinkCriteria = "[ScheduleDetailsID]=" & Me![ScheduleDetailsID]
DoCmd.OpenReport stDocName, acPreview, , , stLinkCriteria

Exit_Print_Roster_Click:
Exit Sub

Err_Print_Roster_Click:
MsgBox Err.Description
Resume Exit_Print_Roster_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
strWhere = "CaseNumber = " & Me!CaseNumber

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Zanstemic said:
The update resolved the error, however, I have a new error with the
report.

The successful code:

Private Sub Print_Form_Click()
On Error GoTo Err_Print_Form_Click

Dim stDocName As String
Dim strWhere As String

stDocName = "NotificationForm"
strWhere = "CaseNumber" = " & Me!CaseNumber" & ""

DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Print_Form_Click:
Exit Sub

Err_Print_Form_Click:
MsgBox Err.Description
Resume Exit_Print_Form_Click

The new error is the report does not seem to have the information from the
query and #error is present in all the tag locations.

The query is working fine when the report is launched independently.

Any suggestions?



Zanstemic said:
CaseNumber is numberic, however, I'll have to double check and ensure
that it
is not defined as text.

Robert Morley said:
Is CaseNumber numeric, or a string? If it's a string, you'll have to
revise
your code to:

strWhere = "[CaseNumber]='" & Me!CaseNumber & "'"



Rob

Private Sub PrintLetterButton_+Click()
Dim strDocName As String
Dim strWhere As String
strDocName = "NotificationToPhysicians"
strWhere = "[CaseNumber]=" & Me!CaseNumber
DoCmd.OpenReport strDocName, acPreview, , strWhere

End Sub

When running the above code, I receive the error:
Run-time error '3075'
Extra ) IN QUERY EXPRESSION '([CaseNumber]=)'.

Any suggestions?


:

New to Access Programming and would like to open (preview) a report
from
clicking a form button. I would like the form to pass a value (its
UID)
to
the underlying report query (Stored Procedure) to narrow the
results. Not
working, any help? Thanks

Button's Event Procedure:

Private Sub Print_Roster_Click()
On Error GoTo Err_Print_Roster_Click

Dim stDocName As String

stDocName = "CourseRoster"
stLinkCriteria = "[ScheduleDetailsID]=" & Me![ScheduleDetailsID]
DoCmd.OpenReport stDocName, acPreview, , , stLinkCriteria

Exit_Print_Roster_Click:
Exit Sub

Err_Print_Roster_Click:
MsgBox Err.Description
Resume Exit_Print_Roster_Click

End Sub
Click to expand...
Click to expand...
Click to expand...
 
I think he might've been trying to do it as a string, in which case, it
would be either of:

strWhere = "CaseNumber = """ & Me!CaseNumber & """"

or

strWhere = "CaseNumber = '" & Me!CaseNumber & "'"



Rob
 
Except that he said "CaseNumber is numberic, however, I'll have to double
check and ensure that it is not defined as text."


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Robert Morley said:
I think he might've been trying to do it as a string, in which case, it
would be either of:

strWhere = "CaseNumber = """ & Me!CaseNumber & """"

or

strWhere = "CaseNumber = '" & Me!CaseNumber & "'"



Rob
Click to expand...
 
True, but his original code should have worked fine for a numeric field.


Rob

Douglas J. Steele said:
Except that he said "CaseNumber is numberic, however, I'll have to double
check and ensure that it is not defined as text."
Click to expand...
 
The field has been both numeric and text . The field in the table is now
defined as numeric which is appropriate. The number "CaseNumber" is unique
for every record. All the input has been right on the issue has resolved the
initial error.

The #error is happening when the report is called from a button in a form.
If I use a macro "Open Report" and "Print" it prints the entire database of
records successfully. The intent is to print the single record.




The CaseNumber field is unique for each record.
 
My mistake. When I attempt to change to a numeric value, I receive the error
"Type Mismatch"

CaseNumber is a numeric field in the table.

Larry
 
I've updated the code below. It launches into the report and the #error is
resolved though now it is printing every record in the database.

The below is set as a "Event Procedure"

Private Sub Print_Form_Click()
On Error GoTo Err_Print_Form_Click

Dim stDocName As String
Dim strWhere As Long

stDocName = "NotificationToPhysicians"
strWhere = "CaseNumber" = " & Me!CaseNumber"

DoCmd.OpenReport stDocName, acPreview, , , strWhere

Exit_Print_Form_Click:
Exit Sub

Err_Print_Form_Click:
MsgBox Err.Description
Resume Exit_Print_Form_Click

End Sub
 
You've got one too many commas in your DoCmd statement, and you should go
back to the strWhere you had in your original code. The strWhere your
current code is incorrect. So this is what it should look like when you're
done:

Private Sub Print_Form_Click()
On Error GoTo Err_Print_Form_Click

Dim stDocName As String
Dim strWhere As Long

stDocName = "NotificationToPhysicians"
strWhere = "CaseNumber = " & Me!CaseNumber

DoCmd.OpenReport stDocName, acPreview, , strWhere

Exit_Print_Form_Click:
Exit Sub

Err_Print_Form_Click:
MsgBox Err.Description
Resume Exit_Print_Form_Click

End Sub
 
Back
Top