Automate append button

[Guest]

I have a form which has 2 buttons that append updated info to each its own
tables; I have to manually press these buttons each day to update
information, is there a way to have this with VBA Code?

Thank
Octet

 

[Arvin Meyer [MVP]]

Build a table (mine is called tblLoadDates, here) which loads the current
date. Then you can check and run the following code when you open your form:

Public Sub RunData()
On Error GoTo Error_Handler

Dim dtmCurrent As Date
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select * From tblDataLoadDates Where LoadDate =
Date();")

If Not rst.EOF Then rst.MoveLast

If rst.RecordCount > 0 Then
GoTo Exit_Here
Else
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeleteCurrentData"
DoCmd.OpenQuery "qryMakeCurrentData"

With rst
.AddNew
!LoadDate = Date
.Update
End With

BuildRptData

MsgBox "Done"
End If

Exit_Here:
DoCmd.SetWarnings True
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub
Error_Handler:
MsgBox Err.Number & ": " & Err.Description
'Call ErrorLog("basDataModule", "RunData")
Resume Exit_Here
End Sub

 

[Rob Parker]

Hey Arvin,

That's so spooky ...

How do you know all that stuff to answer a question with no detail in it???

Rob

 

[Arvin Meyer [MVP]]

Because I've been doing this every day for 15 years. All I do is databases,
mostly Access, and I've done this same thing before. (notice the commented
out error code near the bottom, and it's replacement code for this post). It
starts to get very easy after the first 5 or 6 years, to the point that I
literally have millions of bytes of code written by now. I remember where I
used it and can find it quickly, or in many cases, I look it up in a code
library database that I've written.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com