Populating Report Fields with Counts from Queries? (1 Viewer)

[nasa09]

I have ten queries that I run at the beginning of each month that identify different transactions run by my team during the previous month. I'm trying to put together a report that lists each of these transaction types and the number that were run. It feels like this should be a super simple process, but my lack of experience is making it much harder than it probably needs to be. I've looked at examples of other problems online and nothing has really helped me so far.

Here's an example of the data I'm working with.

The report is titled rpt_totals, and on that report is a text field titled txtPmtMort. I would like that field to display the total number of records identified by a query titled qry_payment_ml whenever the report is opened. What would be the best way to accomplish this, keeping in mind that I have nine other fields that I want to populate with the totals of nine other queries?

I'd appreciate any help that you folks can offer in helping me figure out the best way to approach this.

 

[pbaldy]

A textbox with:

=DCount("*", "qry_payment_ml")

 

[nasa09]

Thanks Paul. After fooling around a bit that was the solution I came to, but now I'm getting an error that states "You can't assign a value to this object."

Here's the code that I came up with. The error is happening on this line:

txtPmtInst.Value = payIL

Option Explicit


Private Sub Report_Open(Cancel As Integer)

Dim payML, payIL As Long
Dim adjustML, adjustIL As Long
Dim waiverML, waiverIL As Long
Dim odloc, tmatic As Long

payML = DCount("*", "qry_payment_ml")
payIL = DCount("*", "qry_payment_il")
adjustML = DCount("*", "qry_adjustment_ml") + DCount("*", "qry_reversal_ml")
adjustIL = DCount("*", "qry_adjustment_il") + DCount("*", "qry_reversal_il")
waiverML = DCount("*", "qry_lc_waiver_ml")
waiverIL = DCount("*", "qry_lc_waiver_il")
odloc = DCount("*", "qry_odloc")
tmatic = DCount("*", "qry_tmatic")



txtPmtInst.Value = payIL
txtPmtMort.Value = payML
txtPmtTotal.Value = txtPmtInst.Value + txtPmtTotal.Value

txtAdjInst.Value = adjustIL
txtAdjMort.Value = adjustML
txtAdjTotal.Value = txtAdjInst.Value + txtAdjMort.Value

txtWaiversInst.Value = waiverIL
txtWaiversMort.Value = waiverML
txtWaiversTotal.Value = txtWaiversInst.Value + txtWaiversMort.Value

txtOdlocsTotal.Value = odloc

txtTmaticTotal.Value = tmatic

End Sub

 

[pbaldy]

I would just use the DCount() in the control source. If you want to do it in code, use the format event of the section containing the textboxes. By the way, this:

Dim waiverML, waiverIL As Long

Declares the first variable as Variant, not Long. You'd want:

Dim waiverML As Long, waiverIL As Long

 

[nasa09]

Wow...I actually had no idea about that. I guess that's something I carried over from Java. Thanks for pointing that out.

I made the change you recommended (which in retrospect was a no-brainer...d'oh), but I'm still getting that error. Here's my current code:

Option Explicit


Private Sub Report_Open(Cancel As Integer)


txtPmtInst.Value = DCount("*", "qry_payment_il")
txtPmtMort.Value = DCount("*", "qry_payment_ml")
txtPmtTotal.Value = txtPmtInst.Value + txtPmtTotal.Value

txtAdjInst.Value = DCount("*", "qry_adjustment_il") + DCount("*", "qry_reversal_il")
txtAdjMort.Value = DCount("*", "qry_adjustment_ml") + DCount("*", "qry_reversal_ml")
txtAdjTotal.Value = txtAdjInst.Value + txtAdjMort.Value

txtWaiversInst.Value = DCount("*", "qry_lc_waiver_il")
txtWaiversMort.Value = DCount("*", "qry_lc_waiver_ml")
txtWaiversTotal.Value = txtWaiversInst.Value + txtWaiversMort.Value

txtOdlocsTotal.Value = DCount("*", "qry_tmatic")

txtTmaticTotal.Value = DCount("*", "qry_odloc")

End Sub

 

[pbaldy]

You missed:

If you want to do it in code, use the format event of the section containing the textboxes.

 

[nasa09]

Paul, so sorry for the late thanks and response. I've been out of the office.

I'm actually not very familiar with the format event. I think I've made the change you suggested, but now the report is opening but the fields aren't populating at all. Any idea what I've done wrong?

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

txtPmtInst.Value = DCount("*", "qry_payment_il")
txtPmtMort.Value = DCount("*", "qry_payment_ml")
txtPmtTotal.Value = txtPmtInst.Value + txtPmtTotal.Value

txtAdjInst.Value = DCount("*", "qry_adjustment_il") + DCount("*", "qry_reversal_il")
txtAdjMort.Value = DCount("*", "qry_adjustment_ml") + DCount("*", "qry_reversal_ml")
txtAdjTotal.Value = txtAdjInst.Value + txtAdjMort.Value

txtWaiversInst.Value = DCount("*", "qry_lc_waiver_il")
txtWaiversMort.Value = DCount("*", "qry_lc_waiver_ml")
txtWaiversTotal.Value = txtWaiversInst.Value + txtWaiversMort.Value

txtOdlocsTotal.Value = DCount("*", "qry_tmatic")

txtTmaticTotal.Value = DCount("*", "qry_odloc")

End Sub

 

[nasa09]

Actually, I think I've figured it out! I put my code into the Load event and it worked perfectly.

Thanks for your help, Paul!

 

[pbaldy]

The format event should have worked, but glad you got it working. The open event is too soon, as you've found out. The load event can work, but most commonly used is probably the format event. Were those textboxes in the detail section?

 

[nasa09]

Yep, they were in the Details section. When I opened the report, the fields were blank.

 

[pbaldy]

Curious. Well, if you were stuck I'd suggest posting the db so we could figure it out, but since you have it working it's probably not worth the effort.