Results 1 to 6 of 6
  1. #1
    programmingnewby is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2019
    Posts
    9

    Error 3144 - Syntax Error in Update Statement (Field is left Blank)

    Hello!

    Currently have a database setup to enter information from drivers. The SOCIAL field cannot be left blank, and I would like it to say a message "Please add drivers social!" And cancel the process.

    As of right now, when adding and or updating an existing record, without a social, the error code 3144 comes up. How do I go about setting up the message and getting rid of the error?

    Code Below;


    Private Sub cmdAdd_Click()
    'when we click on button Add there are two options
    '1. for insert
    '2. for update
    If Me.txtSocial.Tag & "" = "" Then
    ' this is for insert new
    'add data to table
    CurrentDb.Execute "INSERT INTO tblDrivers(tbLast, tbFirst, tbDOB, tbCDL, tbCDLEXP, tbCDLST, tbMC, tbMCEXP, tbCOM, tbHire, tbSocial, tbDrug) " & _
    " VALUES('" & Me.txtLast & "', '" & Me.txtFirst & "', '" & Me.txtDOB & "', '" & Me.txtCDL & "', '" & Me.txtCDLEXP & "', '" & Me.txtCDLST & "', '" & _
    Me.txtMC & "', '" & Me.txtMCEXP & "', '" & Me.txtCMP & "', '" & Me.txtHIRE & "', '" & Me.txtSocial & "', '" & Me.txtDrug & "', '" & Me.txtPhone & "')"

    Else
    'otherwise (Tag of txtSocial store to the Social of Drivers to be notified)
    CurrentDb.Execute "UPDATE tblDrivers " & _
    " SET tbSocial=" & Me.txtSocial & _
    ", tbfirst='" & Me.txtFirst & "'" & _
    ", tblast='" & Me.txtLast & "'" & _
    ", tbDOB='" & Me.txtDOB & "'" & _
    ", tbCDL='" & Me.txtCDL & "'" & _
    ", tbCDLEXP='" & Me.txtCDLEXP & "'" & _
    ", tbMC='" & Me.txtMC & "'" & _
    ", tbMCEXP='" & Me.txtMCEXP & "'" & _
    ", tbCOM='" & Me.txtCMP & "'" & _
    ", tbHire='" & Me.txtHIRE & "'" & _
    ", tbDrug='" & Me.txtDrug & "'" & _
    ", tbPhone='" & Me.txtPhone & "'" & _
    " WHERE tbSocial=" & Me.txtSocial.Tag
    End If
    'clear form
    cmdClear_Click
    'refresh data in list on form
    frmDriverSub.Form.Requery


    End Sub

    Private Sub cmdClear_Click()
    Me.txtLast = ""
    Me.txtFirst = ""
    Me.txtDOB = ""
    Me.txtCDL = ""
    Me.txtCDLEXP = ""
    Me.txtCDLST = ""
    Me.txtMC = ""
    Me.txtMCEXP = ""
    Me.txtCMP = ""
    Me.txtHIRE = ""
    Me.txtSocial = ""
    Me.txtDrug = ""
    Me.txtPhone = ""

    'focus on ID text Box
    Me.txtSocial.SetFocus
    'set button edit to enable
    Me.cmdEdit.Enabled = True
    'change caption of buton add to Add
    Me.cmdAdd.Caption = "Add"
    'clear tag on txtSocial for reset new
    Me.txtSocial.Tag = ""

    End Sub

    Private Sub cmdClose_Click()
    DoCmd.Close
    End Sub

    Private Sub cmdEdit_Click()
    'check whether data exists in list
    If Not (Me.frmDriverSub.Form.Recordset.EOF And Me.frmDriverSub.Form.Recordset.BOF) Then
    'get data to text box control
    With Me.frmDriverSub.Form.Recordset
    Me.txtSocial = .Fields("tbSocial")
    Me.txtLast = .Fields("tbLast")
    Me.txtFirst = .Fields("tbFirst")
    Me.txtDOB = .Fields("tbDOB")
    Me.txtCDL = .Fields("tbCDL")
    Me.txtCDLEXP = .Fields("tbCDLEXP")
    Me.txtCDLST = .Fields("tbCDLST")
    Me.txtMC = .Fields("tbMC")
    Me.txtMCEXP = .Fields("tbMCEXP")
    Me.txtCMP = .Fields("tbCOM")
    Me.txtHIRE = .Fields("tbHIRE")
    Me.txtSocial = .Fields("tbSOCIAL")
    Me.txtDrug = .Fields("tbDRUG")
    Me.txtPhone = .Fields("tbPhone")
    'store social of student in Tag of txtSocial in case Social is modified
    Me.txtSocial.Tag = .Fields("tbSocial")
    'change caption of button add to Update
    Me.cmdAdd.Caption = "Update"
    'disable buton edit
    Me.cmdEdit.Enabled = False
    'refresh data in list on form
    frmDriverSub.Form.Requery


    End With
    End If


    End Sub

    Private Sub Command40_Click()

    End Sub

  2. #2
    Micron is offline Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    you would have to use the Before Update event of the form and test for data as required. You cancel the update if so, and often set the focus to the control in question.

    Please use code tags and indentation (# on menubar) for anything more than a few lines of code. I don't know if I'm alone in saying this, but more often than not I just gloss over that much code posted that way.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    strongly recommend you use the code tags to preserve your formatting/indentation. You code is impossible to read otherwise.

    when you do this, also highlight the line of code which is generating the error

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Welcome to the forum.
    Further to the comments you have received from Micron and Ajax, what is the meaning of your tb prefix in
    "INSERT INTO tblDrivers(tbLast, tbFirst, tbDOB, tbCDL, tbCDLEXP, tbCDLST, tbMC, tbMCEXP, tbCOM, tbHire, tbSocial, tbDrug) "?

    For efficient debugging, using a variable(eg txtSQL) to hold your evolving SQL statement composed of form control values and then using a Debug.Print your txtSQL will show how Access has rendered your statement.

    Also, I realize you are new to the forum, but readers do not know your environment nor needs, so a simple overview in plain English --no jargon, no database terms -- would help orient the reader. What is a "social"?
    Good luck.

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Since you have marked the thread "SOLVED", could you tell those, who may happen upon this thread,
    what the solution was?

  6. #6
    programmingnewby is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jul 2019
    Posts
    9
    Microns reply

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 09-22-2014, 08:54 AM
  2. Run time error 3144 Syntax error
    By gaker10 in forum Programming
    Replies: 13
    Last Post: 09-02-2014, 11:01 AM
  3. Error 3144: Syntax error in UPDATE statement??
    By Paintballlovr in forum Programming
    Replies: 7
    Last Post: 03-26-2014, 12:53 PM
  4. sql update statement syntax error
    By osupratt in forum Queries
    Replies: 7
    Last Post: 10-18-2013, 07:42 AM
  5. Syntax Error 3144 in SQL Update Query.
    By Phred in forum Programming
    Replies: 4
    Last Post: 03-02-2012, 02:39 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums