Help with SQL String

Topic

New Reply

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

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

AKW,

Thanks much works like a charm.

RG

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote