Add the column names to your SQL statement for which you're inserting values, but omit the field with the autonumber. It'll be incremented automatically. The same applies for any columns that have default values that you don't want to override. Example:
CREATE TABLE tblExample
(
lngAutoNumber AUTOINCREMENT,
strFirstName TEXT(30),
strLastName TEXT(50),
lngNumber INT
);
INSERT INTO tblExample (strFirstName, strLastName, lngNumber)
VALUES ('John', 'Smith', 66);
Li'l helper routine:
[VBA]Option Compare Database
Option Explicit
'Long names
Function SingleEnquotedString(StringToEnquote As String) As String
SingleEnquotedString = "'" & StringToEnquote & "'"
End Function
'Abbreviations
Function EnqStr1(Data As String) As String
EnqStr1 = "'" & Data & "'"
End Function
Sub DoStuff()
Dim strSQL As String
strSQL = "INSERT INTO tblExample (strLastName, strFirstName, lngNumber)" _
& vbNewLine & "VALUES (" _
& EnqStr1(Me.TextBox1.Value) & "," & vbNewLine _
& EnqStr1(Me.TextBox2.Value) & "," & vbNewLine _
& EnqStr1(Me.TextBox3.Value) & ");"
End Sub[/VBA]