Select record in form_load?

[brightshadow]

I have a super simple Access DB with four tables and one form.. I'm an old hand at Excel VBA but am just starting with Access, so this is likely a totally stupid question, but here goes anyway.

In Form_Load() for the form, I have a time based trigger that lets the DB execute a macro and automatically quit when the macro is completed so I can run overnight maintenance where it runs some SQL queries on external databases, and builds an Excel workbook and a Word document based on the query.

It (form_load()) opens a recordset, and works with the recordset.

However, the macros that generate the Excel and Word files actually work with the FORM itself.

Here's the problem -- when my user manually runs the macros via commandbuttons on the form, they work great; it pulls the data from the selected record, generates the files, and they giggle and clap.

When I schedule a task to execute it via form_load(), however, it opens the recordset, scans through it, and then it runs the macros.. but the macros don't select any item on the form, so it always generates data for record 1 or 0 or...whatever.

How do I tell form_load() to actually go through the motions of selecting the form record as it processes the recordset fields so it's pointed at the right data?

[Rabbit]

If your recordset is the same as the record source for the form, then you can set them to the same record using:

Me.Recordset.Bookmark = YourRecordset.Bookmark
 

[brightshadow]

It doesn't seem to like Me.Recordset.Bo okmark. (I'm using Access 2000, if that makes a difference.)

If I just add Me.Bookmark = rs.Bookmark, I get an error of "Not a valid bookmark."

Watches on these show the following:
Me.Bookmark:
Me.Bookmark(0) = 0
Me.Bookmark(1) = 2
Me.Bookmark(2) = 0
Me.Bookmark(3) = 0

rs.Bookmark:
rs.Bookmark(0) = 0
rs.Bookmark(1) = 21
rs.Bookmark(2) = 0
rs.Bookmark(3) = 0

Here's my form_load(), modified a little for debugging:

Private Sub Form_Load()
 
'On Error GoTo Unattended_Error
 
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Org As Object '// Org Name
Dim State As Object '// State/Province
Dim TBP As Object '// Dummy checkbox (To Be Processed)
Dim LCD As Object '// Last Count Date
Dim LID As Object '// Last Invoice Date
Dim SecondQD As Object '// Second Quarter Date
Dim ManID As Object '// Manual Process Date
Dim BP As Object '// Billing Period
Dim Counts As Object '// Counts?
Dim Inv As Object '// Invoice?
Dim InvoiceMonth As Integer
Dim InvoiceDay As Integer
 
'If Hour(Now) = 1 Then '// If it's 1:00AM-1:59AM then run the automatic process check
 
    Set db = CurrentDb()
    Set rs = db.OpenRecordset("Contract")
    Set Org = rs.Fields(0)
    Set State = rs.Fields(44)
    Set TBP = rs.Fields(48)
    Set LCD = rs.Fields(49)
    Set LID = rs.Fields(50)
    Set SecondQD = rs.Fields(3)
    Set BP = rs.Fields(28)
    Set ManID = rs.Fields(51)
    Set Counts = rs.Fields(52)
    Set Inv = rs.Fields(53)
 
    If Month(SecondQD) > 3 Then
        InvoiceMonth = Month(SecondQD) - 3
    Else
        InvoiceMonth = Month(SecondQD) + 9
    End If
 
    InvoiceDay = Day(SecondQD)
 
    rs.MoveFirst
    Do Until rs.EOF
 
        Me.Bookmark = rs.Bookmark '// FIXME: Does this work?
 
        If IsNull(LCD) Then
            rs.Edit
            LCD = 0
            rs.Update
        End If
 
        If IsNull(LID) Then
            rs.Edit
            LID = 0
            rs.Update
        End If
 
        If IsNull(ManID) Then
            rs.Edit
            ManID = 0
            rs.Update
        End If
 
        If IsNull(Counts) Then
            rs.Edit
            Counts = 0
            rs.Update
        End If
 
        If IsNull(Inv) Then
            rs.Edit
            Inv = 0
            rs.Update
        End If
 
        ManualInvoiceDay = Day(ManID)
 
        If Day(Now) = InvoiceDay Then
            If BP = "Monthly" Then
                    QueryLeniCounts
                    CreateWordLetter
            ElseIf BP = "Annual" Then
                If Month(Now) = InvoiceMonth Then
                    QueryLeniCounts
                    CreateWordLetter
                End If
            ElseIf BP = "Quarterly" Then
                If Month(Now) = InvoiceMonth Or _
                   Month(Now) = InvoiceMonth + 3 Or _
                   Month(Now) = InvoiceMonth + 6 Or _
                   Month(Now) = InvoiceMonth + 9 Or _
                   Month(Now) = InvoiceMonth - 3 Or _
                   Month(Now) = InvoiceMonth - 6 Or _
                   Month(Now) = InvoiceMonth - 9 Then
                        QueryLeniCounts
                        CreateWordLetter
                End If
            ElseIf BP = "Semi-Annual" Then
                If Month(Now) = InvoiceMonth Or _
                   Month(Now) = InvoiceMonth + 6 Or _
                   Month(Now) = InvoiceMonth - 6 Then
                        QueryLeniCounts
                        CreateWordLetter
                End If
            End If
        End If
 
        If Day(Now) = ManualInvoiceDay Then
            If Counts = True Then
                QueryLeniCounts
            End If
            If Inv = True Then
                CreateWordLetter
            End If
        End If
 
    rs.MoveNext
    Loop
    Application.Quit
'Else
    GoTo Exit_Sub
'End If
 
Exit_Sub:
    Exit Sub
 
Unattended_Error:
    LogMessage "-------------------------" & vbNewLine & Now & ":: An error occurred in unattended processing: " & _
             Err.Number & ", " & Err.Description & vbNewLine & "Org: " & Org & vbNewLine & "State: " & State & vbNewLine & _
             "Last Count Date: " & LCD & vbNewLine & "Last Invoice Date: " & LID & vbNewLine & _
             "Second Quarter Date: " & SecondQD & vbNewLine & "Billing Period: " & BP & vbNewLine & _
             "InvoiceDay: " & InvoiceDay & vbNewLine & "InvoiceMonth: " & InvoiceMonth & vbNewLine & _
             "-------------------------"
    GoTo Exit_Sub
 
End Sub
 

[Rabbit]

Sorry, I was trying to go off the top of my head. I think you have to use:

Set Me.Bookmark = rst.Bookmark
 

Edit: Scratch that. You don't need the Set. Are you sure your recordset is the same as the form? You might want to try:

Set rs = Me.RecordsetClone
 

[brightshadow]

Eureka! RecordsetClone worked, OpenRecordset didn't. :)

Thanks much, Rabbit! That made my 4 day xmas weekend much nicer.. no having to remote in from home to click a button once a day! :)

[Rabbit]

Not a problem, good luck.