Open Report from button with variable

[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

 

[aaron.kempf]

longmire??

-Aaron

 

[Sylvain Lafontaine]

Remove a comma before stLinkCriteria.

 

[Sylvain Lafontaine]

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

Remove a comma before stLinkCriteria.

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

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

 

[Guest]

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?

 

[Robert Morley]

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

 

[Guest]

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

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

 

[Guest]

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?

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

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

 

[Robert Morley]

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

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?

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

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

 

[Douglas J. Steele]

strWhere = "CaseNumber = " & Me!CaseNumber

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

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?

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

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

 

[Robert Morley]

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

 

[Douglas J. Steele]

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)

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

 

[Robert Morley]

True, but his original code should have worked fine for a numeric field.


Rob

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

 

[Guest]

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.

 

[Guest]

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

 

[Guest]

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

 

[Robert Morley]

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

 

[Guest]

You guys are fantastic. Yes, it's working fine. Extremely helpful.