Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Nov 2011
    Posts
    413

    Unanswered: Insert Into Table

    I am having trouble with my syntax for some reason. The names are correct and the number of fields are correct! Do you see something I don't?
    All fields are Text fields.

    Dim strSQL As String

    strSQL = "INSERT INTO tblSession (EmpID,pcid)"
    strSQL = strSQL & "VALUES ('" & Me.txtUserID & "','" & Me.txtcomputer & ")"

    CurrentDb.Execute strSQL, dbFailOnError

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    As suggested before EXAMINE the value of the sql you are sending to the SQL engine.
    make cettain that text/string columns are delimited. Make certain that text or string columns that contain the symbol you are using to delimt is escaped.
    make certain you have separated key words.

    its one thing ti create vba code that generates SQL, its another thing to generate valud SQL.


    EXAMINE the value of strSQL.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2011
    Posts
    413
    Well, I get NO error when compiling and error 3075 error in string when form loads?

    strSQL = "INSERT INTO tblSession (EmpID,pcid)"
    strSQL = strSQL & (" & Me.txtUserID & "" & Me.txtcomputer & """)
    is my latest attempt with NO success.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok
    did you examine the value of strSQL?

    Does the SQL match what you believe you wrote.

    put a watch or breakpoint on the first assignment of a value to strSQL and step through the code

    without understanding where or why your code is failing making 'another attempt' is futile. You may get a solution but that may well be down to trial and error or guesswork as opposed to uaing the tools available to you (the IDE's excellent debugger) to see and hopefully understand what you code is doing as opposed to what you think its doing
    Last edited by healdem; 07-19-14 at 20:22.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2011
    Posts
    413
    I have examined and see nothing wrong. I did step thru the code with no errors.
    It says out of context whatever that means.
    Not defined or somewthing else depending on where I place the watch.

    I don't understand, this is beyond me skill set.
    I get the watch error but don't understand it or know how to correct it.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Ok
    can you paste the value of strSQL here.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The code step through allows you to see what values are in controls and variables.
    The debugger allows you to overwrite those values
    Italso allows you to reposition the next statement to be executed.

    if you are developing in Access, in my books you need to understand how to use the debugger. It tells you precisely what yourcode us doing as oppised to trying to guess wgat its doing. Its easy to skim over code, especially SQL and 'know' what it does. However its very common to presume you have written is what you meant.

    always, always examine the actual sql being sent to the sql engine
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Nov 2011
    Posts
    413
    Dim strSQL As String

    strSQL = "INSERT INTO tblSession (EmpName,pcid)"
    strSQL = strSQL & (" & Me.txtUserID & "" & Me.txtcomputer & ")

    CurrentDb.Execute strSQL, dbFailOnError

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    No
    I dont want to see the vba code tbat assigns a value to strSQL. What id like you to do us pist the actual value of strSQL, taken from tbe debugger.

    hint: its not what you think it is. You are assuming that because the vba code compiles the SQL is also valid.

    there is a reason why I keep asking you to examine the value of tbe variable
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Burrina,

    You're missing the end single-quote:

    Code:
    Dim strSQL As String
    
    strSQL = "INSERT INTO tblSession (EmpID,pcid)"
    strSQL = strSQL & "VALUES ('" & Me.txtUserID & "','" & Me.txtcomputer & "')"
    
    CurrentDb.Execute strSQL, dbFailOnError
    Wayne

  11. #11
    Join Date
    Dec 2013
    Posts
    43
    Provided Answers: 1
    Burrina,

    You might want to also add a space (not an error here, but sometimes can be):

    strSQL = "INSERT INTO tblSession (EmpID,pcid) " <-- space before double-quote

    Wayne

  12. #12
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Good point
    but its not just a missing quote.
    its also a missing separator between the values
    a missing bracketing around the values
    possibly a missing leading space in front of the word values

    ...but that was why I was trying to persuade the op to use the debugger to analyse what his code is doing as opposed to presuming that by magic crap SQL will work out ok. After nearly two and a half years it shouldnt be tbat hard to compare your SQL against the references that show the syntax of the insert statement

    no developer should be unable to examibe theircide and understand wgat uts actually doing. If you are going to code in vba then I cannot see hiw you can hope to do so without understanding how to use the debugger. Its clear that the OP doesnt understand how to nor seemingly does he care... which us sad. It akso means he is reliant on gimme answers rather than analysing the fault, duagnosing tne oroblem and coming up with a fix. The current development process lacks any understanding if hiw to duagnose the fault, relues on a hail mary guess without understanding yhe syntax, and ehen that fails an ecpectation that a solution will be privided
    I'd rather be riding on the Tiger 800 or the Norton

  13. #13
    Join Date
    Nov 2011
    Posts
    413
    The code worked with NO errors but did not add EmpName ???

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Go backto the sql you have just been provided by Wayne...
    where does it refer to the empname, empid yes, empname no
    I'd rather be riding on the Tiger 800 or the Norton

  15. #15
    Join Date
    Nov 2011
    Posts
    413
    I'm not really looking for the FREE lunch program. I always try to research and do whatever I can before I post for help on any forum. I realize I lack some skills.

    Thanks for the help.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •