Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2004
    Posts
    364

    Unanswered: What is wrong with my Insert code?

    Im trying to insert the values of two columns of two listboxes on my form into a seperate table when I hit a command button.

    Code:
    DoCmd****nSQL "INSERT INTO tblThree (userID, groupID )SELECT [Forms]![MainFrm]!list1.column(0) AS Expr1, [Forms]![MainFrm]!list2.column(0) AS Expr2;"
    My code isn't correct because im getting the error message "undefined function" but Im unsure what I need to alter. What am I doing wrong?

  2. #2
    Join Date
    Aug 2004
    Posts
    364
    Ive sorted it
    Code:
    DoCmd****nSQL "INSERT INTO tblLinkUserGroups ( userID, groupID )SELECT [Forms]![visits_frm]![list146] AS Expr1, [Forms]![visits_frm]![list26] AS Expr2;"

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    a suggestion
    place the sql into a variable, then run the macro

    Code:
    Dim strSQL as string
    strSQL = "INSERT INTO tblThree (userID, groupID )SELECT [Forms]![MainFrm]!list1.column(0) AS Expr1, [Forms]![MainFrm]!list2.column(0) AS Expr2;"
    DoCmd****nSQL strSQL
    place a watch/breakpoint on the code and examine the SQL

    looking at it your SQL makes no sense for a single row insert.
    as per INSERT INTO Statement (Microsoft Access SQL)*[Access 2007 Developer Reference] I'd expect to see something like

    Code:
    Dim strSQL as string
    strSQL = "INSERT INTO tblThree (userID, groupID ) Values (" & [Forms]![MainFrm]!list1.column(0) &", " & [Forms]![MainFrm]!list2.column(0) & ");"
    DoCmd****nSQL strSQL
    the usage of a table called tblthree scares me a bit
    ...why
    well its not a meaningful or useful name, it doesn't describe whats stored in that table.. I'd expect something like Users (some people prefer prefoxing the object name soem dont'
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Aug 2004
    Posts
    364
    Hello thankyou for that helpful info. Dont worry about the table name, I changed it just for posting the message here, it isnt the real name of the table, the real name is tblLinkUserVisits, but at the time I couldnt be bothered to type it all out

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by moss2076 View Post
    the real name is tblLinkUserVisits, but at the time I couldnt be bothered to type it all out
    I would very, very strongly recommend you copy and paste the exact code you are working with when asking for help on the forums. You would not believe the number of posts we have made correcting bugs that are only in what was typed into the post and aren't actually bugs in the real world code.
    You haven't here: this is just for ref.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Aug 2004
    Posts
    364
    I know that, dont worry I knew the bug wasnt anything to do with the table name, and I am aware of naming conventions in other circumstances. Dont worry.

Posting Permissions

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