DAO 3.6 Library

Now we'll add a function which will allow use to add a new record to any table we wish.

NB: A function is a special type of procedure, which returns a value. Most of you will have used functions in VB, maybe unwittingly. MsgBox is a function but most of the time, its return value is disregarded. Because a function provides a return value, it (like a variable) has a data type.

Here's the code we'll use for our AddNewRecord Function:

Public Function AddNewRecord(rstTable As Recordset) As Integer
   Dim intCount As Integer
   Dim intNextRecordID As Integer

   With rstTable
      .Requery
      If .BOF = True And .EOF = True Then
         intNextRecordID = 1
      Else
         .MoveLast
         .MoveFirst
         For intCount = 1 To .RecordCount
            If .Fields("ID").Value <> intCount Then
               intNextRecordID = intCount
               Exit For
            End If
            .MoveNext
         Next intCount
         If intNextRecordID = 0 Then intNextRecordID = .RecordCount + 1
      End If
      .AddNew
      .Fields("ID").Value = intNextRecordID
      .Update
   End With

   AddNewRecord = intNextRecordID
End Function

As you can see, this function returns an Integer. This is determined by the As Integer statement at the end of the first line. This function will be passed just one variable, a reference to the RecordSet (a posh name for a table) which we want to add the record to.

The With statement just means that everywhere I put a . VB replaces it with a rstTable. This makes the code smaller and easier to read.

The next thing the code does is to requery the table - this just means VB makes sure it is looking at the latest copy of the table. The IF statement checks to see if there are any records in the current table, if not then obviously the record number of the new record will be 1. Otherwise, VB cycles through every record and looks for a gap. Eg, say we have records 1,2,3,5,6,7 - this code will spot that there is a gap - ie record number 4.

If no gaps are found, the new record is given the number of the highest record + 1.

The next chunk of code, adds a new record and sets the ID field to the value that we decided to use and updates the changes.

The last line ensures that the function returns the ID of the new record.

So, in summary to add a new record to a table use:

Dim intReturn As Integer
intReturn = AddNewRecord(rstTableToAddRecordTo)

Here, intReturn will contain the ID of the record which has been added. rstTableToAddRecordTo is the recordset of the table which you wish to add the record to. Don't worry about this too much, we'll cover this later.