Results 1 to 8 of 8
  1. #1
    TimMoffy is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    29

    Question Insert Into statement

    I am trying to get an INSERT INTO statement going to save some data from a form back to a table.



    The help in Access itself shows the following format for the INSERT INTO when ALL of the fields in a table are going to be saved.
    You can omit the field list, but only if you supply all the values that record can contain.
    INSERT INTO tblCustomers VALUES (1, Kelly, 'Jill', '555-1040', 'someone@microsoft.com')

    What i have put in as code is; INSERT INTO tblReviewers VALUES (Me.txtClaimRef, Me.txtDate, Me.cboReviewer, Me.cboHandler)

    But whenever i put this in I get a "Compile Error: Expected: End of Statment" message box with the text "tblReviewers" highlighted. So far as i can see I have followed the format exactly and i'm not sure what i can do to get around this problem.
    Any great ideas?
    Thanks as always

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

  3. #3
    TimMoffy is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    29
    Thanks for your response and the link you provided, but I still can’t see what I'm doing wrong.
    Can I be really cheeky and give you some info to see if you can help me some more?

    The table the data is to be written to is
    tblReviewers
    this has four fields in it.
    txtClaimRef - text
    DtmDate – Short Date
    txtReviewer - text
    txtClaimHandler - text

    from a form called frmHLStart i have four fields that i want to save to this table.
    The form comprises four fields, laid out in the same order and called
    Text box – txtclaimRef - text
    Text Box – txtDate – short date and defaults to date() but can be amended
    combo – cboReviewer - text
    combo – cboHandler- text

    All the data formats are the same in both table and form.
    I have a command button on the form which runs the query, which I called qryUploadReviewers.

    Given all of the above, I can’t see what I'm doing wrong, especially as Access own help suggests that I do not need to specify the table fields if I am populating all of them.
    Stuck and confused. L

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    You might want to post your VBA code that you are using to create and run your SQL statements.
    Also note that you might need some text and date qualifiers in your code.

    Here is one way to approach your situation. Manually create and run an example query (that reflects what you are are trying to do) to insert hard-coded values, like the one you referenced, i.e.
    Code:
    INSERT INTO tblCustomers VALUES (1, Kelly, 'Jill', '555-1040', 'someone@microsoft.com')
    Test the SQL code, and confirm that it works (if it does not, modify it until it does).

    Then, using VBA, try to program and create the same statement using your variables (text and combo boxes from your form).
    Instead of attempting to run that SQL statement, first spit it out into a Message Box so you can see exactly what it is building. Compare the structure to the example you got working. Is it structured the same? Does it have all necessary text and date qualifiers?

    One you have to the point where it matches, then try running it.

  5. #5
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Further to JoeM's comment

    I would not rely on a default structure - always identify the fields to which you are inserting values.

    especially as Access own help suggests that I do not need to specify the table fields if I am populating all of them.

    That is plain sloppy and will come back as an issue if you change the field order in the table, or insert a new field in that table.
    Always be explicit in your field and value assignments.

  6. #6
    TimMoffy is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    29
    Thanks for your help Gents and I have taken on board comments about identifying fields etc.
    I have constructed the SQL statement step by step as suggested and got to a point where it works just as I want it to

    INSERT INTO tblReviewers (txtClaimRef,txtReviewer,dtmDate, txtClaimHandler )
    VALUES ("ABC126", "reviewer3","01/01/2013","billy");

    having never really used SQL before, relying on the query design grid up to now. What i want to do now is to make each of the pieces of data relate to a field on a form. So for example ABC126 should be forms!frmHLStart!txtClaimRef - but this doesnt seem to work. I think I need to use SELECT but cannot find any examples of this working with the INSERT INTO statement.
    Could i ask you to share your expertise one more time please.
    I have constructed the statement as

    INSERT INTO tblReviewers (txtClaimRef)
    VALUES ("forms!frmHLStart!txtClaimRef")

    but understandably this just inserts the path to the field as text in the table and not as what is stored in the text box if this makes sense. I then removed the quote marks but this meant it didnt work at all.

  7. #7
    TimMoffy is offline Novice
    Windows XP Access 2010 32bit
    Join Date
    May 2012
    Posts
    29
    Gents - tiral and error and google and I got there
    Private Sub cmdsavereview_Click()
    Dim SQLText As String
    SQLText = "INSERT INTO tblReviewers (txtClaimRef,txtReviewer,dtmDate,txtClaimHandler)V ALUES (txtClaimRef,cboReviewer,txtDate,cboHandler)"
    DoCmd.RunSQL SQLText
    End Sub

    The only problem I have now is that the line is one continuous line, not as shown here and whenever i put a break into it before VALUES and use & _ I i still get "Expected End of Statement" message box - but in the scheme of things I can live with this, although wrap would be useful.

    Thanks for your help

  8. #8
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    For debugging you could comment the DoCmd.RunSQL SQLText then
    use Debug.Print SQLText.

    This will Print the contents/value of SQLText in the immediate window.
    You could insert a ; at the end of your statement just before the final "

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 04-25-2012, 08:14 PM
  2. Insert statement
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 08-19-2011, 02:20 PM
  3. Help about access INSERT INTO statement
    By ducthang88 in forum Programming
    Replies: 7
    Last Post: 12-03-2010, 08:40 AM
  4. Insert Into Statement with Line Numbers
    By Cheshire101 in forum Programming
    Replies: 1
    Last Post: 10-11-2010, 02:53 PM
  5. Replies: 4
    Last Post: 09-03-2010, 02:55 PM

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
  •  
Other Forums: Microsoft Office Forums