Solved insert into table error 3134 (1 Viewer)

basilyos

Registered User.
Local time
Today, 05:53
Joined
Jan 13, 2014
Messages
252
am inserting data into a table
table fields name is correct
form textboxes names is correct

Code:
strsql101 = "insert into tbl_Clients (ClientName, Username, Phone, RegistrationDate, RechargeDate, ExpiryDate, Address, Section, Account, AccountDealerCost, AccountResellerCost, AccountResellerSell, Netflix, Sama, Reseller, Company, Gift, Double, Linked) Values ('" & Me.txt_ClientName & "', '" & Me.txt_Username & "', '" & Me.txt_Phone & "', '" & Me.txt_Registration_Date & "', '" & Me.txt_Recharge_Date & "', '" & Me.txt_Expiry_Date & "', '" & Me.txt_Address & "', '" & Me.cbo_Section & "', '" & Me.txt_Account & "', '" & Me.txt_Account_Dealer_Cost & "', '" & Me.txt_Account_Reseller_Cost & "', '" & Me.txt_Account_Price & "', '" & Me.txt_Netflix & "', '" & Me.txt_Sama & "', '" & Me.txt_Reseller & "', '" & Me.cbo_Company & "', '" & Me.tgl_Gift & "', '" & Me.tgl_Double & "', '" & Me.tgl_Linked & "')"
        DoCmd.SetWarnings False
        DoCmd.RunSQL strsql101

what's wrong?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:53
Joined
May 21, 2018
Messages
6,440
You have a lot of fields that I would think are numeric by their name, but you have them delimited with single quotes.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:53
Joined
Aug 30, 2003
Messages
35,743
And values for date fields need to be delimited with # rather than '.

This may also help:

 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:53
Joined
Jul 9, 2003
Messages
14,649
The notorious insert INTO SQL Statement raises its ugly head again!

It's a difficult thing to get right. I blogged about it on Nifty Access here:-


You might find the divide-and-conquer approach useful.

PS:- The download is available for free. PM me for details....
 
Last edited:

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:53
Joined
May 21, 2018
Messages
6,440
If you want to make this easy I recommend using might CSQL function, this will take care of all delimiters
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:53
Joined
May 21, 2018
Messages
6,440
Even easier is to use my parameter insert. No delimiters needed.

Code:
Public Function ParamInsert(TableName As String, TheFields As String, ParamArray TheValues() As Variant) As String
  Dim qdf As QueryDef
  Dim i As Integer
  Dim MyParams As New Collection
  Dim strMyParams As String
  Dim strSql As String
  If Left(TheFields, 1) <> "(" Then TheFields = "(" & TheFields & ")"
 
  For i = 0 To UBound(TheValues)
    MyParams.Add "Param" & i, "Param" & i
    If strMyParams = "" Then
      strMyParams = "[" & MyParams(i + 1) & "]"
    Else
      strMyParams = strMyParams & ", " & "[" & MyParams(i + 1) & "]"
    End If
  Next i
  strSql = "INSERT INTO " & TableName & " " & TheFields & " VALUES ( " & strMyParams & ")"
  ParamInsert = strSql
  Set qdf = CurrentDb.CreateQueryDef("TempQuery", strSql)
   For i = 0 To UBound(TheValues)
    qdf.Parameters(i) = TheValues(i)
  Next i
  qdf.Execute
  CurrentDb.QueryDefs.Delete ("tempquery")
End Function

Public Sub TestParamInsert()
  Dim TheFields As String
  Dim FirstName As Variant
  Dim LastName As Variant
  FirstName = Null
  LastName = "Smith"
  TheFields = "(FirstName, LastName, OrderID, OrderDate)"
  ParamInsert "MyTable", TheFields, FirstName, LastName, 1, Date
End Sub
 

basilyos

Registered User.
Local time
Today, 05:53
Joined
Jan 13, 2014
Messages
252
thank you all.
I rewrite my code in the best and healthy way
but the main problem that I found is the toggle name (Double) reserved name

everything work right now and sorry for taking your time
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 13:53
Joined
Jul 9, 2003
Messages
14,649
main problem that I found is the toggle name (Double) reserved name

Oops! Didn't realize Double was a reserved word!

As a general rule, when making naming decisions, try and use a non-word. So in this case use Dbl or fDouble.

This is one of the reasons a naming convention is always a good idea. If double were a string variable then you would want to name it strDouble.

Otherwise surround it in square brackets.... [Double]....
 

Users who are viewing this thread

Top Bottom