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.