Appending a record in Access using VBA NO SQL

I could not believe how hard it was to find a simple example on how to append a record on condition (in this cast the date has change) for Access, so after I got the code working I thought why not post it.

VBA is not my favourite language by any stretch of the imagination, so excuse any inappropriateness:

Code Sample:


' Append a record to the a Table if it is a new day for tracking purpose
Dim db As Database
Dim rsTracker As Recordset
Dim bDoAppend As Boolean
Dim iLastCount As Long

Set db = CurrentDb
Set rsTracker = db.OpenRecordset("TotalCountTrackerTbl", dbOpenDynaset)

' check if the database has a record, and if the date is valid
bDoAppend = IsNull(rsTracker("TrackDate"))
If rsTracker.RecordCount <> 0 Then
  rsTracker.MoveLast
Else
  ' no records so do append
  bDoAppend = True
End If

' still must not append then check if it is a new date since last record stored
If Not bDoAppend Then
  bDoAppend = DateDiff("d", rsTracker("CountDate"), Now) > 1
End If

' if we want to append then if the form data is valid append
' replace formwithdata with the form name and the DataItemWeWant with the field
If bDoAppend Then
  If Not IsNull(FormWithData.DataItemWeWant.Text) Then
    iLastCount = CLng(FormWithData.DataItemWeWant.Text)
    rsTracker.AddNew
    rsTracker("TrackDate") = Now
    rsTracker("TotalCount") = iLastCount
    ' Tell the database who put it there
    rsTracker("Comments") = "AutoAdd"
    rsTracker.Update
  End If
End If

' remember to close the rs
rsTracker.Close


Comments

Anonymous said…
Thanks. Seems so simple -- once you see the code.

John in Olympia

Popular Posts