OpenRecordSet Doesn’t Make all Records Visible? (Access 2000 SP2)

Topic

New Reply

Hi All,

Hopefully just something simple… I have a report which contains (and runs) the following code upon being opened:
==========================================================================================
Option Compare Database
Option Explicit

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

fldMYRECNUM = recDATABAG ! MYRECNUM
fldTWO = recDATABAG ! TWO
fldTHREE = recDATABAG ! THREE

**** THE PROBLEM IS HERE ****

‘ The report fields listed above are defined in the Detail section of my Report as unbounded text boxes with
‘ the names given. The report is supposed to list a single page header, followed by one or more lines of
‘ tabular data from the query. This only formats (and presents) the FIRST record of the query?! The other
‘ records are present, but are not formatted. Why? How do I get them to show up (as an ordinary tabular
‘ list (like when the Record Source box is filled in with the same query in Design View)?
‘ It doesn’t work to put this code in the PRINT event Procedure either.
‘
‘ Thanks for any help!!! I can usually paddle my own canoe, but you folks have been a lot of help when I get stuck!

End Sub

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

‘ this works fine
fldREPORTTITLE = “Title of Report”

End Sub

Private Sub Report_Close()

‘ this works fine
recDATABAG.Close

End Sub

Private Sub Report_Open(Cancel As Integer)

Set db = CurrentDb()
Set recDATABAG = db.OpenRecordset(“My Query”, dbOpenDynaset)

recDATABAG.MoveLast
recDATABAG.MoveFirst

‘ for proof all the records in the query were located (this works fine)
MsgBox (“Found ” & recDATABAG.RecordCount & ” records”)

End Sub
==========================================================================================

Reply | Quote

Replies

There are two problems here:

• Although you don’t state it explicitly, I would guess that your report is unbound, i.e. the Record source property is empty. If that is correct, the report will display/print only one detail section – there are no other records to go to.
• Although you open a recordset in code, there is no MoveNext in sight, so the subsequent records of the recordset never get fetched.
  [/list]

Reply | Quote

As Hans pointed out, your code only addresses the first record in your recordset. Recordsets can only deal with a single record at a time, the “current” record. You can’t do anything with any other record until you call a method like .MoveNext (or .MoveFirst and .MoveLast, for that matter). But no matter how much moving you do, you can still only look at one record at a time.

Reply | Quote

Thanks for the replies!

In my zeal, I didn’t mention that I had added the required While…Wend…MoveNext structure (below), before.

The code below goes with a REPORT that is unbound (ie no Record Source). It is to run when the REPORT
is opened. I am now confused by the following:
=========================================================================================
Option Compare Database
Option Explicit

Dim intCancel as integer
Dim intFCount as integer

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
fldMYRECNUM = recDATABAG!MYRECNUM
fldTWO = recDATABAG!TWO
fldTHREE = recDATABAG!THREE
MsgBox (“Record ” & recMYRECNUM)
End Sub

Private Sub PageHeaderSection_Format(Cancel As Integer, FormatCount As Integer)
fldREPORTTITLE = “Title of Report”
End Sub

Private Sub Report_Close()
recDATABAG.Close
End Sub

Private Sub Report_Open(Cancel As Integer)
Set db = CurrentDb()
Set recDATABAG = db.OpenRecordset(“Special Query”, dbOpenDynaset)

recDATABAG.MoveLast
recDATABAG.MoveFirst

MsgBox (“Found ” & recDATABAG.RecordCount & ” records”)

while not recDATABAG.EOF
Call Detail_Format(intCancel, intFCount)
recDATABAG.MoveNext
wend
End Sub
=========================================================================================
This code works and iterates through the record set, except that it gives an error message telling me I can’t assign
values to fldMYRECNUM, fldTWO and fldTHREE. Yet, I can do exactly this in a FORM. Why is a REPORT different?
The MsgBox inside the DETAIL lists each record number, proving the DETAIL is executing on each iteration. The
unbounded textboxes exist in the DETAIL section on the REPORT and appear if I type “Me.” in the code.

Thanks for any insights!

Reply | Quote

This still isn’t going to work. You can’t set the value of text boxes in the Report_Open event handler – it’s too early for that. But even if you could, it wouldn’t help – you would keep on overwriting the controls until the last record of the recordset had been reached. Calling Detail_Format doesn’t cause new detail sections to appear from out of the blue. A report displays one detail section for each record of the report’s record source, and since the report is unbound, there isn’t going to be more than one detail section.

Why do you want this report to be unbound? It seems to me that you could set the record source of the form to Special Query, and make fldMyRecNum etc. bound controls. Then Access would do all the work for you.

A report is a very different animal from a form indeed – they are designed for very different purposes. In a form, you can edit data, which is impossible in a report. In a report, you can group and summarize data in ways that are impossible (or very hard) in a form.

Reply | Quote

Thanks for your reply, Hans.

You’re right to wonder why I’d want to “go ’round the Horn” to make this report complicated. I’ve always used the bound report approach before. This mess arose as a consequence of trying to have a calculated field in the DETAIL section of the REPORT. While it’s easy to convert a code number (1, 2, 3, 4) stored in the underlying table into a corresponding dollar amount with a calculated field in the REPORT; what I couldn’t do was avoid having the dollar figures themselves hard-coded inside the calculated field (which is to say: trying to do the calculation in VBA, where I could access pre-defined global constants (which vary over time) and define (and change) them easily from a MODULE (as I do everywhere else in this particular application.)) The calculated field always sees inserted variables as PARAMETERS and prompts for them when the report is run.

I’m going to give up and just go and convert the codes in the table to the correct dollar amounts and store those, (pain in the a**, since I need to dig up the archived values for about 2,500 records) unless you can suggest something more intelligent.

Reply | Quote

You can write a user-defined function in the report module or in a standard module, and use that in the control source of a text box. This way, you can perform complicated calculations. Here is an example, just for illustration purposes:

In the module:

Const conExtra1 = 100
Const conExtra2 = 50

Function ConvertAmount(AnyAmount As Currency) As Currency
If AnyAmount < 1000 Then
ConvertAmount = AnyAmount * 1.1 + conExtra1
Else
ConvertAmount = AnyAmount + conExtra2
End If
End Function

On the report:

Say that you have a currency field DollarAmount in the record source. Create a text box with control source

=ConvertAmount([DollarAmount])

Reply | Quote

Thank-you. Much more intelligent. Works just fine! Now, on to the next challenge…

Reply | Quote