Results 1 to 8 of 8
  1. #1
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78

    Inserting and deletinc items in a database with VBA

    Hi, i m struggling for sometimes now to get the real syntax for inserting field into a table using vba:


    Those are the code i v been using and it s telling me syntax error.
    Dim strSQ As String
    Dim rst As DAO.Recordset
    Dim fact As Integer
    Dim factdate As Date
    Dim produitnom As String
    Dim prixvendu As Integer
    Dim quantite As Integer
    factdate = Me.factInsideDate
    produitnom = Me.Modifiable15.Column(1)
    prixvendu = Me.prixvenduI
    quantite = Me.quantiteSortiI
    strSQ = "INSERT INTO FactureInside(FactInsideId,factInsideDate,produitN om,prixvenduI,quantiteSortiI) _
    VALUES (('" & fact & "') ,( '" & factdate & "'),('" & prixvendu & "'),( '" & quantite & "');"
    CurrentDb.Execute strSQ, dbFailOnError

    Can anyone help pleqse: This is just for inserting and what about deleting.

    Regards

  2. #2
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    It s telling me syntax error all the time

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    You are missing an ampersand, double quotes, your delimiters are wrong and you don't need the parenthesis in the VALUES clause:

    Your way:
    Code:
    strSQ = "INSERT INTO FactureInside(FactInsideId,factInsideDate,produitNom,prixvenduI,quantiteSortiI)" & _  '<-missing ampersand and  double quote at the end
    " VALUES (" & fact & ", #" & factdate & "#, " & prixvendu & ", " & quantite & ");"  '<-missing double quote before VALUES


    I prefer to create SQL strings like this:
    Code:
    strSQ = "INSERT INTO FactureInside(FactInsideId,factInsideDate,produitNom,prixvenduI,quantiteSortiI)"
    strSQ = strSQ & " VALUES (" & Fact & ", #" & factdate & "#, " & prixvendu & ", " & quantite & ");"
    Last edited by ssanfu; 04-15-2016 at 12:56 PM.

  4. #4
    Business is offline Advanced Beginner
    Windows 8 Access 2010 64bit
    Join Date
    Apr 2016
    Posts
    78
    Thanks it has worked.What about the one for deleting

  5. #5
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    What about the one for deleting
    All I saw was code for inserting a new record.

    To create SQL for deleting a record, create a select query, add the criteria to select one record, then change the query to a delete query.
    Switch to SQL View and copy the SQL.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    ssanfu, I'm so surprised this produitN om worked without enclosing the space [produitN om]
    Do you know why?

  7. #7
    ssanfu is offline Master of Nothing
    Windows XP Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I think the space was added in the post by the forum program. The space was not in the code in my Access text file.

    I edited the post and removed the space.


    Good catch! I missed that when I posted.

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Now I remember some posts where I asked about this because no matter what, I could not get rid of a space when the post was saved. The forum puts in a space after the 50 character mark. You can only beat it by using code or quote tags.
    edit: which I see you did anyway, so I'll have to watch for it in my own posts.

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

Similar Threads

  1. Inventory Database Items
    By DataWarrior in forum Misc
    Replies: 7
    Last Post: 01-06-2016, 04:37 PM
  2. Replies: 3
    Last Post: 01-06-2012, 03:30 PM
  3. Replies: 3
    Last Post: 05-15-2011, 10:52 PM
  4. Inserting into a database
    By Cined in forum Programming
    Replies: 1
    Last Post: 04-14-2010, 12:33 PM
  5. Inserting data into Access database
    By bhanu in forum Access
    Replies: 1
    Last Post: 12-21-2009, 09:07 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