Page 1 of 2 12 LastLast
Results 1 to 15 of 17

Autonumber SQL INSERT INTO

  1. #1
    eww
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125

    Autonumber SQL INSERT INTO

    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:

    Code:
    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...

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,323
    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.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  3. #3
    eww
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    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...

  4. #4
    eww
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    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.

  5. #5
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,323
    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?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  6. #6
    eww
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    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:
    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:
    Code:
    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')

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,323
    Understandable; what happens if you copy that SQL into an empty query and try to run it?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  8. #8
    eww
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    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.

  9. #9
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,323
    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?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  10. #10
    eww
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    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.

  11. #11
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,323
    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.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  12. #12
    eww
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    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:

    Code:
    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

  13. #13
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,323
    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.
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

  14. #14
    eww
    eww is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2010
    Posts
    125
    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.

  15. #15
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    12,323
    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?
    Paul (wino moderator)
    MS Access MVP
    www.BaldyWeb.com

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. INSERT query: insert new data only
    By drh in forum Access
    Replies: 2
    Last Post: 04-04-2014, 04:31 PM
  2. Help with autonumber
    By GDubbs780 in forum Programming
    Replies: 15
    Last Post: 02-27-2011, 12:27 PM
  3. Autonumber with Text
    By jgelpi16 in forum Access
    Replies: 6
    Last Post: 01-21-2011, 12:36 PM
  4. AutoNumber Info
    By anoob in forum Access
    Replies: 3
    Last Post: 01-12-2011, 03:35 PM
  5. AUTONUMBER
    By J A F F A in forum Database Design
    Replies: 1
    Last Post: 10-03-2007, 09:30 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums