print current record on main form and subform

[robertm600635]

I have a form with client info and then a subform with client screening info.
Each client can be screened multiple times. So I have multiple screening
records related to each client record. I have a report set up with client
info and with a subreport with screening info. I need to be able to print the
current record for a client. I use the following code:

Private Sub cmdPrintScreening_Click()
On Error GoTo Err_cmdPrintScreening_Click

Dim stDocName As String
Dim stWhere As String

DoCmd.Save
Me.Dirty = False
stDocName = "rptScreening"
stWhere = "infSSSN ='" & Me.infSSN & "'"

DoCmd.OpenReport "rptScreening", , , stWhere

Exit_cmdPrintScreening_Click:
Exit Sub

Err_cmdPrintScreening_Click:
MsgBox Err.Description
Resume Exit_cmdPrintScreening_Click
End Sub

This works fine except for that if a client has mulitple screening records
(in the subform) it prints all of them. I just want to print the record that
is currently being displayed in the screening subform. I have a PK for the
table that the subform is based on and have a one-to-many relationship
between the client info table and the screening table. any ideas?

 

[Cheese_whiz]

Hi robert,

I ran into this problem with a form of mine, though it didn't have a
subform. I just wanted to print the current form/record but because my form
was setup to allow users to navigte through the records, when I tried to
print it it wanted to print out every record in the database.

This is the solution I came up with. You will need to mofidy it to fit your
needs (for example, you won't be able to use the 'Me' if you are referring to
a subform from the main form). I removed the error code.


_________________________
Private Sub cmdPrint_Click()
Dim intMyID As Integer

If Not (Me.NewRecord) Then
intMyID = Me.txtID
End If

'This is a 'print record' button. It filters the form to just the
record _
showing, then prints, then removes the filter and then moves back to
the_ same record as it printed

If Me.NewRecord Then
MsgBox ("You cannot print a blank record!")
Exit Sub
End If

DoCmd.ApplyFilter , "ID = " & Me.ID
DoCmd.PrintOut
Me.FilterOn = False

DoCmd.FindRecord intMyID, , , acSearchAll, , acAll, True

End Sub
________________________

It's pretty simple, but it works. Basically you will be applying a simple
filter to your form (you will substitute the pk from the record displayed in
your subform where i have 'ID'). Since you are filtering on the PK, only one
record will then be in the recordset filling the subform. Then you can print
it, and then remove the filter. As a final touch, I added the last part that
takes the PK stored at the beginning of the sub and uses it to move the now
unfiltered form back to the record you just printed.

Again, you may have to requery the subform after applying the filter (or
removing it), I dunno. Just play with it and I think it will work for you.

Hope that helps,
CW

 

[Cheese_whiz]

All the forms talk sort of through me off base. You still may be able to use
the general ideas in my post but that code wasn't designed to print a report.
It was designed to print the form displayed on the screen.

Sorry for any confusion,
CW

 

[Marshall Barton]

I have a form with client info and then a subform with client screening info.
Each client can be screened multiple times. So I have multiple screening
records related to each client record. I have a report set up with client
info and with a subreport with screening info. I need to be able to print the
current record for a client. I use the following code:

Private Sub cmdPrintScreening_Click()
On Error GoTo Err_cmdPrintScreening_Click

Dim stDocName As String
Dim stWhere As String

DoCmd.Save
Me.Dirty = False
stDocName = "rptScreening"
stWhere = "infSSSN ='" & Me.infSSN & "'"

DoCmd.OpenReport "rptScreening", , , stWhere

Exit_cmdPrintScreening_Click:
Exit Sub

Err_cmdPrintScreening_Click:
MsgBox Err.Description
Resume Exit_cmdPrintScreening_Click
End Sub

This works fine except for that if a client has mulitple screening records
(in the subform) it prints all of them. I just want to print the record that
is currently being displayed in the screening subform. I have a PK for the
table that the subform is based on and have a one-to-many relationship
between the client info table and the screening table. any ideas?

A couple of comments.

First, get rid of the DoCmd.Save line. It save the form's
design, which is a bad thing to do in a running application.
You probably intended to save the current record and the
line Dirty = False takes care of that.

Because you are using a subreport, it gets a little
complicated. You can not use the OpenReport method to
filter the subreport. Instead you need to add criteria to
the subreport's record source query's screening PK field:
Forms!clientform.screensubform.Form.pkfield
__________

At this point, I don't see a reason to use a subreport so I
suggest that you explore using a single report that groups
on the client. This way the filtering can all be done in
the button's code without messing with a query.

With this approach, the report's record source query will
look something like:

SELECT C.infSSSN, C.fa, C.fb, ...,
S.ScreenPK, S.clientFK, S.f1, S.f2, ...
FROM Clients As C INNER JOIN Screening As S
ON C.infSSSN = S.clientFK

If you preview the report without a filter, it will display
all clients and all screening records. However, the form
can filter the report to a single client and a single
screening record by using:

stWhere = "infSSSN ='" & Me.infSSN & _
"' AND ScreenPK = " & Me.subform.Form.ScreenPK

 

[robertm600635]

I don't know any other way of going about this except using a sub report (
I'm still pretty new at Access). When you say "add criteria to
the subreport's record source query's screening PK field" how do I do that
exactly?

 

[robertm600635]

Ok, ignore my last post, I figured that part out. I now have a report with no
subreports and fields from both form and subforms (both tables).I used this
code for the Where clause:

stWhere = "infClientID ='" & Me.infClientID & "' AND infScreeningInstance =
" & Me.sfrmScreening.Form.infScreeningInstance

[infClientID] is the PK for the ClientInfo table datatype=autonumber,
[infScreeningInstance] is the PK for the screening table datatype=number.

Now when I click the command button to print I get a dialog box that simply
says "infScreeningInstance" and if I type something there or not I get the
following msg:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

Any idea what I'm doing wrong?
Thanks

 

[Marshall Barton]

Ok, ignore my last post, I figured that part out. I now have a report with no
subreports and fields from both form and subforms (both tables).I used this
code for the Where clause:

stWhere = "infClientID ='" & Me.infClientID & "' AND infScreeningInstance =
" & Me.sfrmScreening.Form.infScreeningInstance

[infClientID] is the PK for the ClientInfo table datatype=autonumber,
[infScreeningInstance] is the PK for the screening table datatype=number.

Now when I click the command button to print I get a dialog box that simply
says "infScreeningInstance" and if I type something there or not I get the
following msg:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

The fact that you are being prompted for a value for
"infScreeningInstance"
indicates that name is not a field in one of the query's
tables. Check the spelling carefully and if you can figure
it out, post a Copy/Paste of the query's SQL view.

You said the infClientID is an AutoNumber field. If that's
the case then you should not have quotes around the value:

stWhere = "infClientID =" & Me.infClientID
& " AND infScreeningInstance = " &
Me.sfrmScreening.Form.infScreeningInstance

 

[robertm600635]

Just wanted to thank you Marshall, your advice worked like a charm and I
learned a better way to design my reports. Thanks again.

Ok, ignore my last post, I figured that part out. I now have a report with no
subreports and fields from both form and subforms (both tables).I used this
code for the Where clause:

stWhere = "infClientID ='" & Me.infClientID & "' AND infScreeningInstance =
" & Me.sfrmScreening.Form.infScreeningInstance

[infClientID] is the PK for the ClientInfo table datatype=autonumber,
[infScreeningInstance] is the PK for the screening table datatype=number.

Now when I click the command button to print I get a dialog box that simply
says "infScreeningInstance" and if I type something there or not I get the
following msg:
"This expression is typed incorrectly, or it is too complex to be evaluated.
For example, a numeric expression may contain too many complicated elements.
Try simplifying the expression by assigning parts of the expression to
variables."

The fact that you are being prompted for a value for
"infScreeningInstance"
indicates that name is not a field in one of the query's
tables. Check the spelling carefully and if you can figure
it out, post a Copy/Paste of the query's SQL view.

You said the infClientID is an AutoNumber field. If that's
the case then you should not have quotes around the value:

stWhere = "infClientID =" & Me.infClientID
& " AND infScreeningInstance = " &
Me.sfrmScreening.Form.infScreeningInstance

 

[Ofer Cohen]

To get a Sum of one field in the table you can use

=Dsum("[FieldName]","[TableName]")

To get a a sum of calculation, you can use

Dsum("[Field1Name]-[Field2Name]","TableName")

** There is no need to store a calculation in the table, you can always get
the same resault using a query

 

[Ofer Cohen]

To get a Sum of one field in the table you can use

=Dsum("[FieldName]","[TableName]")

To get a a sum of calculation, you can use

=Dsum("[Field1Name]-[Field2Name]","TableName")

** There is no need to store a calculation in the table, you can always get
the same resault using a query