By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,475 Members | 1,372 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,475 IT Pros & Developers. It's quick & easy.

Select record in form_load?

brightshadow
P: 10
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?
Dec 21 '07 #1
Share this Question
Share on Google+
5 Replies


Rabbit
Expert Mod 10K+
P: 12,315
If your recordset is the same as the record source for the form, then you can set them to the same record using:
Expand|Select|Wrap|Line Numbers
  1. Me.Recordset.Bookmark = YourRecordset.Bookmark
  2.  
Dec 21 '07 #2

brightshadow
P: 10
It doesn't seem to like Me.Recordset.Bookmark. (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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_Load()
  2.  
  3. 'On Error GoTo Unattended_Error
  4.  
  5. Dim db As DAO.Database
  6. Dim rs As DAO.Recordset
  7. Dim Org As Object '// Org Name
  8. Dim State As Object '// State/Province
  9. Dim TBP As Object '// Dummy checkbox (To Be Processed)
  10. Dim LCD As Object '// Last Count Date
  11. Dim LID As Object '// Last Invoice Date
  12. Dim SecondQD As Object '// Second Quarter Date
  13. Dim ManID As Object '// Manual Process Date
  14. Dim BP As Object '// Billing Period
  15. Dim Counts As Object '// Counts?
  16. Dim Inv As Object '// Invoice?
  17. Dim InvoiceMonth As Integer
  18. Dim InvoiceDay As Integer
  19.  
  20. 'If Hour(Now) = 1 Then '// If it's 1:00AM-1:59AM then run the automatic process check
  21.  
  22.     Set db = CurrentDb()
  23.     Set rs = db.OpenRecordset("Contract")
  24.     Set Org = rs.Fields(0)
  25.     Set State = rs.Fields(44)
  26.     Set TBP = rs.Fields(48)
  27.     Set LCD = rs.Fields(49)
  28.     Set LID = rs.Fields(50)
  29.     Set SecondQD = rs.Fields(3)
  30.     Set BP = rs.Fields(28)
  31.     Set ManID = rs.Fields(51)
  32.     Set Counts = rs.Fields(52)
  33.     Set Inv = rs.Fields(53)
  34.  
  35.     If Month(SecondQD) > 3 Then
  36.         InvoiceMonth = Month(SecondQD) - 3
  37.     Else
  38.         InvoiceMonth = Month(SecondQD) + 9
  39.     End If
  40.  
  41.     InvoiceDay = Day(SecondQD)
  42.  
  43.     rs.MoveFirst
  44.     Do Until rs.EOF
  45.  
  46.         Me.Bookmark = rs.Bookmark '// FIXME: Does this work?
  47.  
  48.         If IsNull(LCD) Then
  49.             rs.Edit
  50.             LCD = 0
  51.             rs.Update
  52.         End If
  53.  
  54.         If IsNull(LID) Then
  55.             rs.Edit
  56.             LID = 0
  57.             rs.Update
  58.         End If
  59.  
  60.         If IsNull(ManID) Then
  61.             rs.Edit
  62.             ManID = 0
  63.             rs.Update
  64.         End If
  65.  
  66.         If IsNull(Counts) Then
  67.             rs.Edit
  68.             Counts = 0
  69.             rs.Update
  70.         End If
  71.  
  72.         If IsNull(Inv) Then
  73.             rs.Edit
  74.             Inv = 0
  75.             rs.Update
  76.         End If
  77.  
  78.         ManualInvoiceDay = Day(ManID)
  79.  
  80.         If Day(Now) = InvoiceDay Then
  81.             If BP = "Monthly" Then
  82.                     QueryLeniCounts
  83.                     CreateWordLetter
  84.             ElseIf BP = "Annual" Then
  85.                 If Month(Now) = InvoiceMonth Then
  86.                     QueryLeniCounts
  87.                     CreateWordLetter
  88.                 End If
  89.             ElseIf BP = "Quarterly" Then
  90.                 If Month(Now) = InvoiceMonth Or _
  91.                    Month(Now) = InvoiceMonth + 3 Or _
  92.                    Month(Now) = InvoiceMonth + 6 Or _
  93.                    Month(Now) = InvoiceMonth + 9 Or _
  94.                    Month(Now) = InvoiceMonth - 3 Or _
  95.                    Month(Now) = InvoiceMonth - 6 Or _
  96.                    Month(Now) = InvoiceMonth - 9 Then
  97.                         QueryLeniCounts
  98.                         CreateWordLetter
  99.                 End If
  100.             ElseIf BP = "Semi-Annual" Then
  101.                 If Month(Now) = InvoiceMonth Or _
  102.                    Month(Now) = InvoiceMonth + 6 Or _
  103.                    Month(Now) = InvoiceMonth - 6 Then
  104.                         QueryLeniCounts
  105.                         CreateWordLetter
  106.                 End If
  107.             End If
  108.         End If
  109.  
  110.         If Day(Now) = ManualInvoiceDay Then
  111.             If Counts = True Then
  112.                 QueryLeniCounts
  113.             End If
  114.             If Inv = True Then
  115.                 CreateWordLetter
  116.             End If
  117.         End If
  118.  
  119.     rs.MoveNext
  120.     Loop
  121.     Application.Quit
  122. 'Else
  123.     GoTo Exit_Sub
  124. 'End If
  125.  
  126. Exit_Sub:
  127.     Exit Sub
  128.  
  129. Unattended_Error:
  130.     LogMessage "-------------------------" & vbNewLine & Now & ":: An error occurred in unattended processing: " & _
  131.              Err.Number & ", " & Err.Description & vbNewLine & "Org: " & Org & vbNewLine & "State: " & State & vbNewLine & _
  132.              "Last Count Date: " & LCD & vbNewLine & "Last Invoice Date: " & LID & vbNewLine & _
  133.              "Second Quarter Date: " & SecondQD & vbNewLine & "Billing Period: " & BP & vbNewLine & _
  134.              "InvoiceDay: " & InvoiceDay & vbNewLine & "InvoiceMonth: " & InvoiceMonth & vbNewLine & _
  135.              "-------------------------"
  136.     GoTo Exit_Sub
  137.  
  138. End Sub
  139.  
Dec 21 '07 #3

Rabbit
Expert Mod 10K+
P: 12,315
Sorry, I was trying to go off the top of my head. I think you have to use:
Expand|Select|Wrap|Line Numbers
  1. Set Me.Bookmark = rst.Bookmark
  2.  
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:
Expand|Select|Wrap|Line Numbers
  1. Set rs = Me.RecordsetClone
  2.  
Dec 21 '07 #4

brightshadow
P: 10
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! :)
Dec 21 '07 #5

Rabbit
Expert Mod 10K+
P: 12,315
Not a problem, good luck.
Dec 21 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.