Results 1 to 2 of 2
  1. #1
    helpityhelphelphelp is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    May 2016
    Posts
    1

    insert query from form

    Hi, I have a form with unbound textboxes but have named them bxnbarcode bxntitle etc. The names of the Me.bxn etc all are correct because they work with the Clear button. Here is the form code and the table structure:


    Click image for larger version. 

Name:	accesshelp.png 
Views:	9 
Size:	25.8 KB 
ID:	24650

  2. #2
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Welcome to the forum........


    Is there a question??

    I notice you are missing an apostrophe in the VALUES line. You have
    Code:
    VALUES(" & Me.bxnbarcode
    should be
    Code:
    VALUES('" & Me.bxnbarcode


    I don't like to create my SQL string like that. I use a separate variable; then I can use the command "Debug.Print" to see if the SQL string is formatted correctly.
    Code:
    Private Sub bxnAdd_Click()
      'add data to table
      'refresh data in list on form
      Dim sSQL As String
    
      sSQL = "INSERT INTO tblMovies(MovieBarcode, Title, Category, Hiretype, VideoType)"
      sSQL = sSQL & " VALUES( '" & Me.bxnbarcode & "', '" & Me.bxnname & "', '" & Me.bxncategory
      sSQL = sSQL & "', '" & Me.bxnhiretype & "', '" & Me.bxnvideotype & "');"
    '   Debug.Print sSQL
    
      CurrentDb.Execute sSQL, dbfailonerror
    End Sub
    Results of the debug statement
    Code:

    Code:
    INSERT INTO tblMovies(MovieBarcode, Title, Category, Hiretype, VideoType) VALUES( 'GH123456', 'The Programmer', 'Drama', 'Overnight', 'DVD');
    BTW, in this statement "DoCmd.Close acForm, "frmAddMovie", acSaveYes, the argument "acSaveYes" does not save the data, it is for form design changes.
    All you need is

    DoCmd.Close acForm, "frmAddMovie"

    or

    DoCmd.Close acForm, Me.Name (closes the active form)

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

Similar Threads

  1. Replies: 3
    Last Post: 05-27-2015, 01:04 PM
  2. Replies: 5
    Last Post: 09-02-2014, 10:07 AM
  3. INSERT query: insert new data only
    By drh in forum Access
    Replies: 2
    Last Post: 04-04-2014, 05:31 PM
  4. INSERT INTO query in VBA
    By jgelpi16 in forum Programming
    Replies: 5
    Last Post: 09-17-2010, 02:19 PM
  5. Replies: 0
    Last Post: 02-24-2010, 12:56 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
  •  
Other Forums: Microsoft Office Forums