VBA for report detail data record source

[mark kubicki]

I have a report where several of the fields, because of multiple contingency
fields and "in-fill" text", content is best (?) determined by
putting them into code (I had tried SQL, but that was beyond cumbersome)
the content would be based on the value of other fields in the same or other
tables

the code I have entered for the report detail field txtDescription might be
something like this

----------------------------------------------------------------------------------
Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Dim dbs As Database
Dim rst As DAO.Recordset
Dim rst1 As DAO.Recordset
Dim rst2 As DAO.Recordset
Dim strDescription As String

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("FixtureTypesNoProject", dbOpenSnapshot)
Set rst1 = dbs.OpenRecordset("projectninfo", dbOpenSnapshot)
Set rst2 = dbs.OpenRecordset("tblFixtureSchedulePrintoptions",
dbOpenSnapshot)

strDescription = ""
strDescription = strDescription & (rst!basedescription + " ")
If rst2!ShortDescription <> "false" Then
If rst!PrintAddDescription Then
strDescription = strDescription & (rst!AdditionalDes + " ")
End If
End If
If rst2!LocationPrint <> "false" Then
strDescription = strDescription & ("Location: " +
rst!Location + " ")
End If
.... 'lots more "if" criteria and inserted text...

Me.txtDescription = strDescription
----------------------------------------------------------------------------------
what it results in is the information for record 1, not being shown at all
in record 1 of the report, but showing in record 2, and every subsequent
record in the report


thanks in advance,
mark

 

[George Nicholson]

I don't know why nothing shows for record 1, but I can make a guess as to
why the same thing appears in all subsequent records.

Print_Detail fires for each record. What in your code would cause a
different result from one record to the next? There's nothing in the code
provided to indicate that results would ever change, hence, the same
information is displayed in each record. There is no discernable
connection/relationship between the current record and the recordsets you
have opened that would ever cause results to differ (and if that's the case,
why not just your recordsets up as modular-level variables & open them once
when the report opens, not open a new identical recordset for every record
as you are now.).

If any of your lines like "Me.txtDescription = strDescription" would change
the report layout (i.e., change a 1 line textbox to 2 lines) you shouldn't
use Detail_Print, you should use Detail_Format. If it were me, I'd use the
Format event anyway.

HTH,

 

[mark kubicki]

thanks for your advice,

your comments about moving recordset information to modular-level variable
are completely appropriate for my rst1 and rst2 record sets; and I will
implement that change

about you comment on creating a "connection/relationship between the current
record and the recordsets I have opened"; could you suggest what the code
might be like?

as always, thanks in advance,
mark
--------------------------------------------------------------------------

 

[George Nicholson]

Keep in mind that I have no idea what data is in your report and what data
is in the recordsets you've opened. If what you are doing is something that
really does have to be done within the Detail section then I assume the text
you are grabbing differs in some way record-to-record not
grouping-by-grouping, or else there's no reason to do it within Detail (you
can set the value of a textbox from anywhere in code, you don't need to wait
for that section's event to fire unless the value is likely to change when
the event fires.)

So, assuming there is some unique ID for the current record...

(within a Detail event)
rst.FindFirst "RecordID = " & me.RecordID

....would synchronize rst with the report's current record. (If rst and the
report have the same sort order on RecordID then you could use FindNext)

If you haven't already considered and disgarded these approaches: consider
adding the "missing" fields to the query the report is based on and just
grab the necessary text from the report's recordset. Or create a subreport
to place the additional data into Detail.

HTH,

 

[mark kubicki]

.... found out that nothing showed for the 1st record, because the code was
behind the OnPrint event; moved to OnFormat, all was better
-m.

-------------------------------------------------------------------------