Results 1 to 6 of 6
  1. #1
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98

    INSERT INTO Statement being a hassle


    Ive finally gotten more used to statement functionality, etc, with Access over the past few days, but I've come along the INSERT INTO statement. And again, I am assuming it depends on proper quotation for the code, but I just cannot find out which quotes to use if at all? Here is the sample code I am working with:

    Code:
    Private Sub cmdadditem_Click()
    
    Dim newrec As String
    
    
        If IsNull([site_add]) Or IsNull([itemquantity_add]) Or IsNull([itemname_add]) Then
    
    
        Else
        
        newrec = "INSERT INTO Master ([Item Name], [Item Quantity], Site) VALUES ('itemname_add', 'itemquantity_add', 'site_add');"
        
        DoCmd.RunSQL newrec
     
        End If
        
    End Sub
    Item Name is a short string
    Item Quantity is a number/integer
    Site is a number/integer

    All help is appreciated! Thank you.

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    That statement will enter the literal strings "itemname_add", "itemquantity_add", "site_add".

    If those are fields/controls on form and you really want the values of those controls then must concatenate.

    "INSERT INTO Master ([Item Name], [Item Quantity], Site) VALUES ('" & itemname_add & "', '" & itemquantity_add & "', '" & site_add & "');"

    If you want to avoid the popup warnings:

    DoCmd.SetWarnings False
    DoCmd.RunSQL newrec
    DoCmd.SetWarnings True

    or instead:

    CurrentDb.Execute newrec
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    if item quantity and site are numbers you do not need the single quotes

    "INSERT INTO Master ([Item Name], [Item Quantity], Site) VALUES ('" & itemname_add & "', " & itemquantity_add & ", " & site_add & ");"

  4. #4
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Now I understand. Thank you @ajax, and again @june7. But now I am curious about the "&" symbols. I haven't come across anything explaining why they are there or why there are two; at the beginning and end? Usually this justifies combining two strings when I use VBA, but it looks weird here.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The & is a concatenation operator.
    They are concatenating literal text strings with variables to construct a valid SQL statement.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    schulzy175 is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2017
    Posts
    98
    Oh, that makes sense. Thanks a bunch!

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

Similar Threads

  1. INSERT INTO statement
    By berderder in forum Programming
    Replies: 4
    Last Post: 06-03-2016, 06:00 PM
  2. Insert into with a WHERE statement
    By hazeleyre23 in forum Access
    Replies: 10
    Last Post: 04-06-2016, 08:28 AM
  3. INSERT statement
    By GraeagleBill in forum Programming
    Replies: 2
    Last Post: 03-29-2013, 12:53 PM
  4. Insert Into statement
    By TimMoffy in forum Programming
    Replies: 7
    Last Post: 07-13-2012, 07:10 AM
  5. Insert statement
    By crowegreg in forum Programming
    Replies: 2
    Last Post: 08-19-2011, 02:20 PM

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