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.
Comments