ms access - Error de sintaxis en la inserción en la declaración: ¿cuál es el error?

ms-access

Question

Este es el código completo aquí para un botón para agregar datos a 2 tablas diferentes. Lo extraño es que tengo otra forma con un botón con una función similar y funciona bien con el mismo enunciado Opción Comparar base de datos

 Private Sub addbutton_Click()
CurrentDb.Execute "INSERT INTO Clients(F_Name,L_Name,Phone_Number,E-mail,Gender,Date_of_Birth) " & "VALUES(" & Me.textfname & ",''" & Me.textlname & "'',''" & Me.textpnumber & "'',''" & Me.textemail & "'',''" & Me.textgender & "'',''-" & Me.textdob & "'')"
CurrentDb.Execute "INSERT INTO Accounts(F_Name,L_Name,Username,Password,accounttype) " & "VALUES(" & Me.textfname & ",''" & Me.textlname & "'',''" & Me.textusername & "'',''" & Me.textpassword & "'',''" & Me.textaccountype & "'')"
clientssubform.Form.Requery
End Sub

Answer 1

Intenta cambiar esta parte del código

"VALUES(" & Me.textfname & ",''" &

a esto

"VALUES(''" & Me.textfname & "'',''" &

en ambas lineas

Answer 2

Primero, Contraseña es una palabra reservada, así que use [Password] .

En segundo lugar, una expresión de fecha debe estar envuelta en octothorpes: #2017/04/24# 2017/04/24 #2017/04/24#

Además, si no va a usar parámetros, aunque sea recomendado, use una función como esta para concatenar sus valores:

'' Converts a value of any type to its string representation.
'' The function can be concatenated into an SQL expression as is
'' without any delimiters or leading/trailing white-space.
''
'' Examples:
'' SQL = "Select * From TableTest Where [Amount]>" & CSql(12.5) & "And [DueDate]<" & CSql(Date) & ""
'' SQL -> Select * From TableTest Where [Amount]> 12.5 And [DueDate]< #2016/01/30 00:00:00#
''
'' SQL = "Insert Into TableTest ( [Street] ) Values (" & CSql(" ") & ")"
'' SQL -> Insert Into TableTest ( [Street] ) Values ( Null )
''
'' Trims text variables for leading/trailing Space and secures single quotes.
'' Replaces zero length strings with Null.
'' Formats date/time variables as safe string expressions.
'' Uses Str to format decimal values to string expressions.
'' Returns Null for values that cannot be expressed with a string expression.
''
'' 2016-01-30. Gustav Brock, Cactus Data ApS, CPH.
''
Public Function CSql( _
ByVal Value As Variant) _
As String
Const vbLongLong As Integer = 20
Const SqlNull As String = " Null"
Dim Sql As String
Dim LongLong As Integer
#If Win32 Then
LongLong = vbLongLong
#End If
#If Win64 Then
LongLong = VBA.vbLongLong
#End If
Select Case VarType(Value)
Case vbEmpty '' 0 Empty (uninitialized).
Sql = SqlNull
Case vbNull '' 1 Null (no valid data).
Sql = SqlNull
Case vbInteger '' 2 Integer.
Sql = Str(Value)
Case vbLong '' 3 Long integer.
Sql = Str(Value)
Case vbSingle '' 4 Single-precision floating-point number.
Sql = Str(Value)
Case vbDouble '' 5 Double-precision floating-point number.
Sql = Str(Value)
Case vbCurrency '' 6 Currency.
Sql = Str(Value)
Case vbDate '' 7 Date.
Sql = Format(Value, " /#yyyy//mm//dd hh/:nn/:ss/#")
Case vbString '' 8 String.
Sql = Replace(Trim(Value), "''", "''''")
If Sql = "" Then
Sql = SqlNull
Else
Sql = " ''" & Sql & "''"
End If
Case vbObject '' 9 Object.
Sql = SqlNull
Case vbError '' 10 Error.
Sql = SqlNull
Case vbBoolean '' 11 Boolean.
Sql = Str(Abs(Value))
Case vbVariant '' 12 Variant (used only with arrays of variants).
Sql = SqlNull
Case vbDataObject '' 13 A data access object.
Sql = SqlNull
Case vbDecimal '' 14 Decimal.
Sql = Str(Value)
Case vbByte '' 17 Byte.
Sql = Str(Value)
Case LongLong '' 20 LongLong integer (Valid on 64-bit platforms only).
Sql = Str(Value)
Case vbUserDefinedType '' 36 Variants that contain user-defined types.
Sql = SqlNull
Case vbArray '' 8192 Array.
Sql = SqlNull
Case Else '' Should not happen.
Sql = SqlNull
End Select
CSql = Sql & " "
End Function

Estudie los comentarios en línea para su uso.