Skip to main content

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
  ' 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("TrackDate") = Now
    rsTracker("TotalCount") = iLastCount
    ' Tell the database who put it there
    rsTracker("Comments") = "AutoAdd"
  End If
End If

' remember to close the rs


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

John in Olympia

Popular posts from this blog

SMTP servers of South Africa

SMTP Settings Below is a list of SMTP sites in South Africa, using this and the ISP Map you can try and find which one works best for you. Telkom (ADSL) (56k dial up) Internet Solutions (ADSL) (56k dial up on IS) (3g backbone) Vodacom MTN Cell C (GPRS) (also used by Virgin) ABSA iBurst @lantic (ADSL,Dialup, ISDN) Sentech MWEB (ADSL) - this is to be retired End June 2012, use below instead (56k dial-up & ADSL & business) iAfrica Neotel Tiscali NOW MWeb Netactive NOW MWeb Global Hertzner Use y

Fixing winmail.dat problem - specifically in Quickbooks

For months we have had problems with attachments from Quickbooks. Having looked down many avenues I think we have found a fix or few: (See below for update) Here are a few websites that help out: Microsoft Outlook/Exchange MS-TNEF handling (aka "Winmail.dat", "Win.dat", or "Part 1.2" problem of unopenable email attachments) KB958012 : When you use Outlook 2007 to send an e-mail message, the recipient of the message sees an attachment that is called Winmail.dat Essentially the summary is that there is a problem with Outlook trying to force Rich Text Format. so you need to turn that off. Mail users not receiving email in an Outlook derivative will have a problem. This format is called So: Go into Outlook On the "Tools" menu, click "Options", then click the "Mail Format" tab, and then the "Internet Format" button. Set "When sending Outlook Ri

Programmatically send email in Access using VBA and Outlook

When I first wrote my early Access system I used a function I setup in Outlook and then I called that. On upgrading I had a few problems with the older method then I found some help on the MSDN website here... and the related video. The first thing to do before starting the code is go into access, access the VBA (press Ctl+G) then click on "Tools" The "References" and then make sure you scroll down to "Microsoft Outlook ..." the version there will be determined by what version you have installed. This the code that I ended up with after looking at the site above Private olApp As Outlook.Application Private olNameSpace As Outlook.NameSpace ' The basics of this from MSDN site ' Private Sub InitOutlook()  ' Initialize a session in Outlook   Set olApp = New Outlook.Application   'Return a reference to the MAPI layer   Set olNameSpace = olApp.GetNamespace("MA