insert query peoblem

[warmduvet]

Hi, can anyone tell me why this query isn't working?
the error message is: runtime error 3134 Syntax error in insert statement.

Private Sub btncommit_Click()
    
    Dim invdate As Variant
    Dim sid As Variant
    Dim total As Variant
    invdate = Me.txtdate.Value
    sid = Me.txtstuid.Value
    total = Me.txttotal.Value
    
    Dim sql As String
    'sql = " insert into invoice (invoicenum, date, total, stuid,orgid) values (Null,invdate,total,stuid, Null)"
    DoCmd.RunSQL " insert into invoice (invoicenum, date, total, stuid, orgid)" & "values" & " (Null,invdate,total,stuid, Null)"
    
End Sub

TIA

WD

 

[Jon K]

Hi, can anyone tell me why this query isn't working?

Date is a reserved word. Put it inside a pair of square brackets in the code or use another field name.
Put the values from the text boxes outside the SQL string.

Private Sub btncommit_Click()
    
    Dim invdate As Variant
    Dim sid As Variant
    Dim total As Variant
    invdate = Me.txtdate.Value
    sid = Me.txtstuid.Value
    total = Me.txttotal.Value
    
    DoCmd.RunSQL " insert into invoice" & _
       " (invoicenum, [date], total, stuid, orgid)" & _
       " values (Null,'" & invdate & "'," & total & ",'" & sid & "',Null)"
    
End Sub

The code assumes stuid is a text field and delimits its value with a pair of single quotes.
The single quote (rather than the # sign) is used to delimit the date value so that it can correctly work on systems using the US or the UK date formats.


An alternative, much simpler method:

Private Sub btncommit_Click()
      
    Dim SQL As String

    SQL = " insert into invoice (invoicenum, [date], total, stuid, orgid)" & _
          " values (Null, [txtdate], [txttotal], [txtstuid], Null)"
  
    DoCmd.RunSQL SQL    

End Sub

 

[warmduvet]

Hi thanks for the reply, this what I got to work.

Dim invdate As Variant
Dim stuid As Variant
Dim total As Variant
invdate = Me.txtdate.Value
stuid = Me.txtstuid.Value
total = Me.txttotal.Value

DoCmd.RunSQL "INSERT INTO invoice ([date], total, stuid) VALUES (#" & invdate & "# ," & total & ", " & stuid & ")"

WD