Solved: insert query

Can anyone tell me how to amend my query please to account for the fact that my first field is an autonumber

Thanks

Gibbo

[VBA]
strSQL = "INSERT INTO tbl_Test VALUES ('" & _
Me.TextBox1 & "', '" & _
Me.TextBox2 & "', '" & _
Me.TextBox3 & "', '" & _
Me.TextBox4 & "', '" & _
Me.TextBox5 & "')"
cn.Execute strSQL
[/VBA]

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:

Code:

---

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]

Thats looks really good

I ll have a play tomorrow

Thanks for looking

Gibbo

Also can you tell me the meaning of Option Compare Database as its a new one on me

Cheers

Gibbo

http://support.microsoft.com/default...kb;en-us;98227

HTH! :)

Thanks works great