SELECT and INSERT query

[accesslevel]

Hey all

When I click a button on my form I want it to add the title, firstname and lastname from the fields on the form to my existing customers table but I am stuck when it comes to the autonumber.

I tried using:
DoCmd.RunSQL "INSERT INTO Table1 values(cboTitle.value, txtFirstname, txtLastname)" but the ID field I have in my table doesn't create its own number and the query fails

I also tried creating the ID myself:
Dim SQL As String
randNumber = Int((9999999 - 1000000 + 1) * Rnd + 1000000)
SQL = "SELECT * FROM Table1 WHERE ID = randNumber;"
DoCmd.RunSQL SQL

but it fails saying its not a valid SQL statement.

Help please

 

[Nouba]

You must not set a value for an AutoNumber field. Try the following code and adjust the field names in the code to the appropriate names of your table.

Dim strSQL          As String

strSQL = vbNullString

' assuming all 3 fields are of type text
strSQL = strSQL & "INSERT INTO Table1" & vbCrLf
strSQL = strSQL & "  (TitleFieldname, Firstname, Lastname)" & vbCrLf
strSQL = strSQL & "Values("
strSQL = strSQL & "  '" & Me!cboTitle & "', " & vbCrLf
strSQL = strSQL & "  '" & Me!txtFirstname & "', " & vbCrLf
strSQL = strSQL & "  '" & Me!txtLastname & "')"

Debug.Print strSQL

' you need a reference set to DAO library
DBEngine(0)(0).Execute strSQL, dbFailOnError

 

[Rich]

Why not just bind the form to the table?

 

[accesslevel]

Thanks

Thanks Nouba

You sorted my problem

I had made a work around using a random number generator to create my ID field but now I can use the unique autonumber instead.

I had gone wrong because I was missing the center :

(Title, Firstname, lastname)

:/

Regards