Results 1 to 4 of 4
  1. #1
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Hello Folks,

    I've never done much with SQL and I'm trying to use a SQL string to append a record to a file from a button on a form. I have a user who is computer challenged so I can't just use the add record button. Here's the code I've cobbled together from doing an append query looking at the SQL and looking at some of the SQL posts on the board. I keep getting the attached message.

    Code:
    Dim iNewLotNo As Integer
    Dim zSqlStr   As String
    
    On Error GoTo Err_cmdNewRecord_Click
    
        iNewLotNo = InputBox("Enter the New Storage Lot Number", "Add Storage Lot", 0)
        
        If iNewLotNo = 0 Then
          GoTo Exit_cmdNewRecord_Click
        Else
          'SQL Code here
          zSqlStr = "INSERT INTO StorageLots ( [StorageLotNo], [OwnerID] )" & _
                    "WHERE ([StorageLotNo]=" & iNewLotNo & _
                    ", [StorageLots.OwnerID]=600);"
          
          DoCmd.RunSQL zSqlStr
          
        End If
    So, I figure I'm missing a qoute or comma somewhere?

    Thanks,

    RG
    Attached Images Attached Images
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The trick in debugging this sort of stuff is to put a breakpoint in VBA on the DoCmd.RunSQL line and then do a print of the SQL String and that will usually tell you where the error is when you look at the string. If it isn't obvious just looking at it, paste it into a new query in the SQL view and then switch to Design View and that will show you where the error is.
    Wendell

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I am not quite sure what the WHERE bit of your SQL is doing.

    I am assuming you want to Insert a Couple of VALUES to the Table StorageLots as an Append Query
    But you have NO values to Append in your SQL

    Syntax would be

    Code:
    zSqlStr = "INSERT INTO StorageLots ( [StorageLotNo], [OwnerID] )  SELECT  " &  iNewLotNo & "  AS [NewNo],  600 As [OwnerID] ; "
    Note [NewNo] and [OwnerID] are just Temporary names for the SQL, they do not represent REAL fields
    Andrew

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    AKW,

    Thanks much works like a charm.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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