Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Aug 2001
    Location
    Spring City, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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
    ================================================== ========================================

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OpenRecordSet Doesn't Make all Records Visible? (Access 2000 SP2)

    There are two problems here:
    <UL><LI>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.
    <LI>Although you open a recordset in code, there is no MoveNext in sight, so the subsequent records of the recordset never get fetched.[/list]

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: OpenRecordSet Doesn't Make all Records Visible? (Access 2000 SP2)

    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. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Charlotte

  4. #4
    New Lounger
    Join Date
    Aug 2001
    Location
    Spring City, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordSet Doesn't Make all Records Visible? (Access 2000 SP2)

    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!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OpenRecordSet Doesn't Make all Records Visible? (Access 2000 SP2)

    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.

  6. #6
    New Lounger
    Join Date
    Aug 2001
    Location
    Spring City, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordSet Doesn't Make all Records Visible? (Access 2000 SP2)

    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: OpenRecordSet Doesn't Make all Records Visible? (Access 2000 SP2)

    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])

  8. #8
    New Lounger
    Join Date
    Aug 2001
    Location
    Spring City, Pennsylvania, USA
    Posts
    18
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: OpenRecordSet Doesn't Make all Records Visible? (Access 2000 SP2)

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •