Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report recordset (2002)

    I'm trying to get a function from the MS KB to work in the mdb, but I'm doing something wrong. I pasted it below. I call this function "=Getlinenumber([Report],"ID",[ID]) in the control source from a text box in a sub-report " .

    When I try to assign the RS variable to R.Recordset the function fails. In the de###### it says "R.Recordset =<This feature is not available in an MDB.>". However, if I open the recordset based on a query it works fine, but of course the line numbers aren't sequential. I'm assuming I'm not referencing the Report recordset properly or maybe something with the Report object, but it's late and I can't seem to figure it out. All I'm trying to do is get line numbers on a sub-report. Any help would be appreciated...

    Function GetLineNumber(R As Report, KeyName As String, KeyValue)

    Dim db As DAO.Database
    Dim RS As DAO.Recordset
    Dim CountLines

    On Error GoTo Err_GetLineNumber

    Set db = CurrentDb()


    'Set RS = db.OpenRecordset("SELECT * FROM Item Where Item.ManifestID = '" & Forms!frmMnfstSmryRprt!txtMnfstID & "'" & " ORDER BY ID")
    Set RS = R.Recordset
    ' Find the current record.
    Select Case RS.Fields(KeyName).Type
    ' Find using numeric data type key value.
    Case dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbByte
    RS.FindFirst "[" & KeyName & "] = " & KeyValue
    ' Find using date data type key value.
    Case dbDate
    RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
    ' Find using text data type key value.
    Case dbText
    RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
    Case Else
    MsgBox "ERROR: Invalid key field data type!"
    Exit Function
    End Select

    ' Loop backward, counting the lines.
    Do Until RS.BOF
    CountLines = CountLines + 1
    RS.MovePrevious
    Loop

    Bye_GetLineNumber:
    ' Return the result.
    GetLineNumber = CountLines

    Exit Function

    Err_GetLineNumber:
    CountLines = 0
    Resume Bye_GetLineNumber

    End Function

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

    Re: Report recordset (2002)

    Try using

    Set RS = R.RecordsetClone

    instead of

    Set RS = R.Recordset

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report recordset (2002)

    Didn't get the message, but the same result. Any other ideas?

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

    Re: Report recordset (2002)

    A completely different approach:
    - Place a text box in the detail section.
    - Set its Control Source property to =1
    - Set its Running Sum property to Over All if you want to number all records in the report consecutively, or to Over Groups if you have a group header and want to restart numnering at 1 at each new group.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Posts
    144
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report recordset (2002)

    Much easier, worked great. Still don't understand why the function didn't work though. <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: Report recordset (2002)

    Sorry, RecordsetClone won't work either - it is only available in a form. I guess the code was intended to be used in a form.

Posting Permissions

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