Autonumber SQL INSERT INTO

[eww]

I'm developing an Access front end with a SQL back end and I'm trying to Insert a record into one of the tables.

I'm trying to use the SQL INSERT INTO command but I keep running into an error with the key field (I've run into problems with this field all over the place, trying the INSERT INTO command now and the story is...different but still the same if you know what I mean).

The user is supposed to input the information into the right fields and then click a save button at which point this code runs:

DoCmd.RunSQL _
"INSERT INTO dbo_tblEmpTime(DetDBID, EmpNum, EmpName, JobNum, WorkDate, ActivityType, SAPLoc, SAPCostCtr, WBSElem, Hours, Segment, Comments)" & _
"VALUES ('" & strDetDBID & "', " & Me.txtEmpNum & ", '" & Me.txtEmpName & "', '" & Me.txtJobNum & "', #" & Me.txtWorkDate & "#, '" & Me.txtActivityType & "', '" & Me.txtSAPLoc & "', " & Me.txtSAPCostCtr & ", '" & Me.txtWBSElem & "', " & Me.txtHours & ", '" & Me.txtSegment & "', '" & Me.txtComments & "')"

Now from what I'm reading it's standard practice to leave the autonumber field out and the table will then autonumber it. I did this and so far it seems to have gotten me the closest, I finally get to a "you are about to append 1 row" msgbox and then when I click yes another status msgbox comes up saying 1 record was not added due to key violations. I just don't know what to do here, I'm stressin...

[pbaldy]

You are correct about leaving it out. The key violation error could mean a couple of things. Either a value would be duplicated that can't be, or a required field isn't being populated. You'll often get a better error message if you copy the final SQL into a blank query and try to run it.

[eww]

Paul, I tried what you said and you were right about getting a little bit more specific info from a blank query. It returned a syntax error around the date which I think is right (the error, not the syntax). I changed it to --> '#" & Me.txtWorkDate & "#'

However I didn't expect the query to function fully once right since these are linked to controls on the form, and once I made the correction to the form's code it still gave me the same error (which the query is now giving me too).

I was wondering if you see anything funny with the syntax around the controls. Most of them are strings, except the date one, and then the ones with one set of quotation marks are all integers (the hours is actually a Real data type). I thought it was right with the syntax on these, but if I am I have no idea what else it could be...

[eww]

I forgot to mention that I checked to make sure that none of these were null when being appended and they aren't, so I don't think that's the problem here.

[pbaldy]

Is there a required field in the table that isn't included in this query? A way to structure this that makes it easier to debug is:

Dim strSQL As String
strSQL = "INSERT..."
Debug.Print strSQL
CurrentDb.Execute strSQL, dbFailOnError

The Debug line will print the finished SQL out to the VBA Immediate window, where you can examine it or copy/paste to a blank query to run it (you would take that out once everything was working). The Execute method is considered to be more efficient than RunSQL and doesn't throw the warnings (though sometimes you may want the warnings). If that doesn't solve it, can you post the db?

[eww]

Thanks for the debug tip, that helped a bit more. Now I'm hitting a snag on the line

CurrentDb.Execute strSQL, dbFailOnError

It will run up to this point and give me an error saying the ODBC call failed. Unfortunately this database is for the company I work for so I don't think it would be a good idea for me to post it, however I can give you my current lines of code plus the debug output (I'll be replacing some of the text with substitute)
Code:

Dim strSQL As String
strSQL = "INSERT INTO dbo_tblEmpTime(DetDBID, EmpNum, EmpName, JobNum, WorkDate, ActivityType, SAPLoc, SAPCostCtr, WBSElem, Hours, Segment, Comments) " & _
"VALUES('" & strDetDBID & "', " & Me.txtEmpNum & ", '" & Me.txtEmpName & "', '" & Me.txtJobNum & "', #" & Me.txtWorkDate & "#, '" & Me.txtActivityType & "', '" & Me.txtSAPLoc & "', " & Me.txtSAPCostCtr & ", '" & Me.txtWBSElem & "', " & Me.txtHours & ", '" & Me.txtSegment & "', '" & Me.txtComments & "')"
Debug.Print strSQL
dbCMC.Execute strSQL, dbFailOnError

Debug:

INSERT INTO dbo_tblEmpTime(DetDBID, EmpNum, EmpName, JobNum, WorkDate, ActivityType, SAPLoc, SAPCostCtr, WBSElem, Hours, Segment, Comments) VALUES('CTX', 55555, 'Eric', '1123705000', #2/16/2011#, 'DETLB1', 'Company', 100000, '11237050000300000002', 2, 'Foundation', 'blah')

[pbaldy]

Understandable; what happens if you copy that SQL into an empty query and try to run it?

[eww]

It goes back to the first error saying...set 0 fields to Null due to a type conversion failure, and it didn't add 1 record to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations.

[pbaldy]

That leads me back to the usual suspects; a key field being duplicated or a required field being left out. Can you import the "dbo_tblEmpTime" table into an empty db, empty it of records and post it so I can see the structure?

[eww]

Well, the thing is that we moved the tables to SQL which I'm not as familiar with. There's another guy helping me some with that part, he's the one that recreated the tables in SQL.

I have a feeling that the EmpTimeID is the root of all these problems and might be required like you said. I'm thinking about having him turn the requirement off for right now, maybe even the "not null" requirement, see if we can get around this for the time being. Thanks for your help Paul, I'll post back if I figure anything else out.

[pbaldy]

If it's an autonumber field (Identity in SQL Server), that shouldn't be the problem. You don't include an autonumber field in the SQL. Otherwise, it would have to be included if it's set to Not Null. Anyhow, let me know what you find out and if I can help. You can probably import it into Access and maintain the relevant settings, if you want to post it.

[eww]

We set the field to accept null values, now I can get the form to add the data, but for some reason it accepted the data the first time and then every time I hit the Save button now it just enters the data from the first time, even though I enter new data. I'm not sure how to reset it, I tried going to a new record but that doesn't seem to work. Here's my code for the button:

Private Sub btnNew_Click()
Dim dbCMC As DAO.Database
Dim rstTime As DAO.Recordset
Set dbCMC = CurrentDb
Dim strSQL As String
strSQL = "INSERT INTO dbo_tblEmpTime(DetDBID, EmpNum, EmpName, JobNum, WorkDate, ActivityType, SAPLoc, SAPCostCtr, WBSElem, Hours, Segment, Comments) " & _
"VALUES('" & strDetDBID & "', " & Me.txtEmpNum & ", '" & Me.txtEmpName & "', '" & Me.txtJobNum & "', #" & Me.txtWorkDate & "#, '" & Me.txtActivityType & "', '" & Me.txtSAPLoc & "', " & Me.txtSAPCostCtr & ", '" & Me.txtWBSElem & "', " & Me.txtHours & ", '" & Me.txtSegment & "', '" & Me.txtComments & "')"
dbCMC.Execute strSQL, dbFailOnError
DoCmd.GoToRecord acDataForm, "frmEntrTime", acNewRec

[pbaldy]

Most of your values are coming from the form, so should be current. Where is strDetDBID set? It isn't in this code, so perhaps it's the same value.

[eww]

it is a public variable, it is set upon entering the database, but the other values are different. I think this has something to do with opening the form or something, it literally saved those first values and won't enter anything else but those values now.

Maybe you can tell me if I'm doing this correctly since I'm not as familiar with the SQL side of things. I have a form that is unbound, all the fields are unbound. I'm using the code to insert the values of those textboxes into a table so I don't see how it could have saved any of these values, or why it wouldn't insert the new ones in.

[pbaldy]

If the form is unbound I'm not sure the new record code will do anything. I'd clear the form controls manually, either one by one or with a loop. Can you post the db?