Print Report Based on Form Data

  • Thread starter Thread starter Matthew Loraditch
  • Start date Start date
M

Matthew Loraditch

Hello
I have a report based on a query. When i open the report it prompts me as to
which record i wish to print. I have a form that enters data that appears on
this report. I would like to have a button where a user can press print and
based on the current record open in the form it passes the record id number
to the report and the user is not prompted for which record to print, and
then automatically the report is printed based on the current open record in
the form.

Does anyone have any ideas how to do this?
Thanks
Matthew Loraditch
 
Matthew Loraditch said:
I have a report based on a query. When i open the report it prompts me as to
which record i wish to print. I have a form that enters data that appears on
this report. I would like to have a button where a user can press print and
based on the current record open in the form it passes the record id number
to the report and the user is not prompted for which record to print, and
then automatically the report is printed based on the current open record in
the form.
Click to expand...


Use the command button wizard to create the form's button to
print the report.

After that's done, modify the wizard generated code to use
the strCriteria something like:

' Save record if changes are being made
If Me.Dirty Then Me.Dirty = False
' Set criteria to current record
strCriteria = "<keyfield> = " Me.keyfield
DoCmd.OpenReport strDocName, , , strCriteria
 
Marshall.
here is the code the way i have it:

Private Sub Print_Po_Test_Click()
On Error GoTo Err_Print_Po_Test_Click
' Save record if changes are being made
If Me.Dirty Then Me.Dirty = False
' Set criteria to current record
strCriteria = "<keyfield> = " Me.keyfield "

Dim stDocName As String

stDocName = "PO by PO #"
DoCmd.OpenReport stDocName, acNormal, strCriteria


Exit_Print_Po_Test_Click:
Exit Sub

Err_Print_Po_Test_Click:
MsgBox Err.Description
Resume Exit_Print_Po_Test_Click

End Sub

two questions A) do i put my field name in key field, the field that i'm
using is called PO Number and b) MS VB says unexpected end of statement for
the StrCriteria line, i don't know if it matters but this is access 2k3
thanks so much
 
Matthew Loraditch said:
' Set criteria to current record
strCriteria = "<keyfield> = " Me.keyfield "

Dim stDocName As String

stDocName = "PO by PO #"
DoCmd.OpenReport stDocName, acNormal, strCriteria [snip]
two questions A) do i put my field name in key field, the field that i'm
using is called PO Number and b) MS VB says unexpected end of statement for
the StrCriteria line, i don't know if it matters but this is access 2k3
Click to expand...


Three problems here.

1. Yes, you should replace keyfield with your field name.

2. You added an extra quote on the end of that line while I
was inconsistent in my notation and left out an &. Your
line of code should be:

strCriteria = "[PO Number] = " & Me.[PO Number]

3. You left out a comma in the DoCmd line:

DoCmd.OpenReport stDocName, acNormal, , strCriteria
 
Marshall it all seems to be passing the data ok with one exception PO number
can be specified twice in my query, I tried to change
this:
strCriteria = "[PO Number] = " & Me.[PO Number]
to this:
strCriteria = "[PO Table.PO Number] = " & Me.[PO Number]
obviously my guess was wrong, but basically the PO Number used in the query
is the one in PO Tables
how would i fix this part?
Thanks much!

--
Matthew Loraditch
Marshall Barton said:
Matthew Loraditch said:
' Set criteria to current record
strCriteria = "<keyfield> = " Me.keyfield "

Dim stDocName As String

stDocName = "PO by PO #"
DoCmd.OpenReport stDocName, acNormal, strCriteria [snip]
two questions A) do i put my field name in key field, the field that i'm
using is called PO Number and b) MS VB says unexpected end of statement
for
the StrCriteria line, i don't know if it matters but this is access 2k3
Click to expand...


Three problems here.

1. Yes, you should replace keyfield with your field name.

2. You added an extra quote on the end of that line while I
was inconsistent in my notation and left out an &. Your
line of code should be:

strCriteria = "[PO Number] = " & Me.[PO Number]

3. You left out a comma in the DoCmd line:

DoCmd.OpenReport stDocName, acNormal, , strCriteria
Click to expand...
 
Matthew Loraditch said:
Marshall it all seems to be passing the data ok with one exception PO number
can be specified twice in my query, I tried to change
this:
strCriteria = "[PO Number] = " & Me.[PO Number]
to this:
strCriteria = "[PO Table.PO Number] = " & Me.[PO Number]
obviously my guess was wrong, but basically the PO Number used in the query
is the one in PO Tables
Click to expand...


You should use an expicit field list in the query to avoid
ambiguous field names. Since its a "linking" field, you do
not need two copies of the same value.

If you must leave the extra field in the query, then fix the
square brackets. Try this:
strCriteria = "[PO Table].[PO Number] = " & Me.[PO Number]
 
Back
Top