Results 1 to 8 of 8
  1. #1
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96

    Error 3144: Syntax error in UPDATE statement??

    I have some code that I'm trying to modify to work with a new table and forms and I'm getting the 3144 error message. The query is a SELECT so I'm guessing the error name might be generic, but I'm not sure what is wrong with it. The process should be that you enter account information on form "Update Individual Account - NEW" and the system would pull up the record and display it in the correct form, based off of the Doc Type on the form. I've attached a sample of the forms and data in the database as I thought that might be easier to work with then me pasting the code in here. I'm not sure if the issue is having spaces in table and field names or if the problem lies elsewhere. The original code works fine with the original tables and forms, so I'm hoping it's just something I'm overlooking or typed wrong. For the sample data, you would have to use the APC record on the form, as I didn't copy in any of the other forms from the full database. Any help would be greatly appreciated.

    Error 3144.accdb

  2. #2
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    Can you go ahead and post the code involved?

  3. #3
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    Here is the modified code:

    Option Compare Database 'Use database order for string comparisons
    Option Explicit
    Private Sub cmdCancel_Click()
    On Error GoTo Err_cmdCancel_Click

    'Make this dialog box invisible. This has the effect of "hiding" this
    'form, except that you can't select Unhide from the Window menu for it
    '(unless the form was opened in design view), and you also get the
    'message "Command not available: Design." when you try to bring it up
    'in design view until you close the window from its control menu.
    Visible = False
    Exit_cmdCancel_Click:
    Exit Sub
    Err_cmdCancel_Click:
    MsgBox "Error" & Str$(Err) & " in cmdCancel_Click: " & Error$
    Resume Exit_cmdCancel_Click

    End Sub
    Public Function RecordsSelected() As Integer
    On Error GoTo Err_RecordsSelected
    Dim dbs As Database
    Dim RecCount As Long
    Dim RecSet As Recordset
    Dim strAccountCriteria As String
    Dim strAcctDocTypeCriteria As String
    Dim strFirstNameCriteria As String
    Dim strLastNameCriteria As String
    Dim strMsg As String
    Dim strSQL As String

    DoCmd.Hourglass True
    ' Default return value: no records selected
    RecordsSelected = 0

    ' Return Database object variable pointing to current database.
    Set dbs = CurrentDb

    ' Open recordset on query to select records matching selection criteria.
    ' The following statement didn't work using query Update Individual Account:
    ' Set RecSet = dbs.OpenRecordset("Update Individual Account")
    ' It generates error 3061: Too few parameters. Expected 5.
    ' The Help explanation is: "This parameter query requires the
    ' specified number of parameters, which you didn't supply."
    ' This is unfortunate because the query works just fine from the
    ' database window (as long as the form containing the parameter
    ' values is open). It appears that the OpenRecordset method can't
    ' resolve parameters passed from forms; e.g.,
    ' Like [Forms]![Update Individual Account]![cboDocType]
    ' The following (ugly) SQL string is the result of copying the string
    ' from the SQL View of the query and tweaking it until it worked:
    ' substituting embedded quotation marks with apostrophes (single quotes)
    ' and separating out the parameter values so they get resolved properly.

    If IsNull([Forms]![Update Individual Account - NEW]![txtLastName]) Then
    strLastNameCriteria = "'*'"
    Else
    strLastNameCriteria = "'" & [Forms]![Update Individual Account - NEW]![txtLastName] & "'"
    End If

    If IsNull([Forms]![Update Individual Account - NEW]![txtFirstName]) Then
    strFirstNameCriteria = "'*'"
    Else
    strFirstNameCriteria = "'" & [Forms]![Update Individual Account - NEW]![txtFirstName] & "'"
    End If

    ' Acct Doc Type must be selected in order to determine which form to open
    strAcctDocTypeCriteria = "'" & [Forms]![Update Individual Account - NEW]![cboDocType] & "'"
    If IsNull([Forms]![Update Individual Account - NEW]![txtAccount]) Then
    strAccountCriteria = "'*'"
    Else
    strAccountCriteria = "'" & [Forms]![Update Individual Account - NEW]![txtAccount] & "'"
    End If


    strSQL = "SELECT * FROM [Denials - NEW] " & _
    "WHERE ([Denials - NEW].[Patient Last Name] Is Null Or [Denials - NEW].[Patient Last Name] Like " & strLastNameCriteria & ") " & _
    "And ([Denials - NEW].[Patient First Name] Is Null Or [Denials - NEW].[Patient First Name] Like " & strFirstNameCriteria & ") " & _
    "And ([Denials - NEW].[BDC External Reason Code] Like " & strAcctDocTypeCriteria & ") " & _
    "And ([Denials - NEW].[BDC Hospital Account ID] Like " & strAccountCriteria & ") " & _
    "ORDER BY [Denials - NEW].[Patient Last Name], [Denials - NEW].[Patient First Name]"

    strSQL = "SELECT * FROM Denials " & _
    "WHERE (Denials.[Last Name] Is Null Or Denials.[Last Name] Like " & strLastNameCriteria & ") " & _
    "And (Denials.[First Name] Is Null Or Denials.[First Name] Like " & strFirstNameCriteria & ") " & _
    "And (Denials.[Acct Doc Type] Like " & strAcctDocTypeCriteria & ") " & _
    "And (Denials.Account Like " & strAccountCriteria & ") " & _
    "And (Denials.BU Like " & strBillingUnitCriteria & ") " & _
    "ORDER BY Denials.[Last Name], Denials.[First Name]"

    'MsgBox strSQL ' For debugging

    Set RecSet = dbs.OpenRecordset(strSQL)
    'MsgBox "RecCount = " & RecSet.RecordCount ' For debugging
    ' Do for all records in the query - For debugging
    'Do Until RecSet.EOF
    ' With RecSet
    ' MsgBox ![Last Name] & "," & ![First Name] & "," & ![Acct Doc Type] & "," & !Account & "," & !BU
    ' .MoveNext ' Move to next record
    ' End With
    'Loop
    RecordsSelected = RecSet.RecordCount

    Exit_RecordsSelected:
    RecSet.Close
    ' Disassociate object variable from actual object.
    Set dbs = Nothing ' Same as dbs.Close
    DoCmd.Hourglass False
    Exit Function
    Err_RecordsSelected:
    Beep
    strMsg = "Error " & Str(Err.Number) & ": " & Err.Description
    MsgBox strMsg, , "Unexpected Error", Err.HelpFile, Err.HelpContext
    Resume Exit_RecordsSelected

    End Function
    Private Sub cmdOK_Click()
    On Error GoTo Err_cmdOK_Click
    Dim strFormName As String
    Dim strMsg As String

    If IsNull(Me![cboDocType]) Then
    Beep
    MsgBox "Please enter Doc Type."
    Me![cboDocType].SetFocus
    Else
    ' Call function RecordsSelected to determine
    ' if any records found
    If RecordsSelected = 0 Then
    Beep
    MsgBox "No records found matching selection criteria."
    Else
    Visible = False ' Make this dialog box invisible. This has the
    ' effect of "hiding" this form, except that
    ' you can't select Unhide from the Window
    ' menu for it (unless the form was opened in
    ' design view), and you also get the message
    ' "Command not available: Design." when you
    ' try to bring it up in design view until you
    ' close the window from its control menu.

    Select Case Me![cboDocType] ' Open form based on selected account doc type
    ' 2010 03 12 MJ: Updated Case Select to match the
    ' available document types that can be updated
    ' ("ADMD";"AUTH";"DOS";"DRG";"EXIN";"LTCG";"MRR";
    ' "NDOC";"NMN";"NREF";"ROOM";"TIME";"UPRC")
    ' EXCLUDED "RVUP" since it is not currently updated.
    Case "ADMD"
    strFormName = "ADMD - NEW"

    Case "AUTH"
    strFormName = "AUTH - NEW"

    Case "DOS"
    strFormName = "DOS - NEW"

    Case "DRG"
    strFormName = "DRG - NEW"

    Case "EXIN"
    strFormName = "EXIN - NEW"

    Case "LTCG"
    strFormName = "LTCG - NEW"

    Case "MRR"
    strFormName = "MRR - NEW"

    Case "NDOC"
    strFormName = "NDOC - NEW"

    Case "NMN"
    strFormName = "NMN - NEW"

    Case "NREF"
    strFormName = "NREF - NEW"

    Case "ROOM"
    strFormName = "ROOM - NEW"

    Case "TIME"
    strFormName = "TIME - NEW"

    Case "UPRC"
    strFormName = "UPRC - NEW"

    Case Else
    strFormName = Me![cboDocType] & " - NEW"
    End Select

    DoCmd.OpenForm strFormName

    ' Change record source to query Update Individual Account
    Screen.ActiveForm.RecordSource = "Update Individual Account - NEW"
    End If
    End If

    Exit_cmdOK_Click:
    Exit Sub
    Err_cmdOK_Click:
    Beep
    strMsg = "Error " & Str(Err.Number) & " in cmdOK_Click: " & Err.Description
    MsgBox strMsg, , "Unexpected Error", Err.HelpFile, Err.HelpContext
    Resume Exit_cmdOK_Click

    End Sub


    If you take out any " - NEW" and replace the SQL with the code in red, that should be the original code I started modifying.

  4. #4
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    Where specifically does the error happen? when clicking what? I couldn't replicate the error, not even when looking at the test database you posted. I got no error.

  5. #5
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    Open the table and form Update Individual Account - NEW. Enter the info from the APC entry in the table on the form. When you hit OK, it is supposed to open the APC form and populate it with the information entered on the Update Individual Account - NEW form.

  6. #6
    ipisors is offline Access Developer
    Windows XP Access 2007
    Join Date
    Sep 2013
    Posts
    119
    Since you didn't mention what to enter, I entered random things and usually got "no records found matching criteria" or similar.

    Finally I just used blanks ... and was able to trap the error. The code is erring on this line:

    Code:
    Screen.ActiveForm.RecordSource = "Update Individual Account - NEW"
    (clicking Debug will tell you that too...)

    Which makes sense. Access can't find any table or query named "Update Individual Account - NEW", so the next thing it guesses is that the string is a SQL statement. Since it starts with UPDATE, Access thinks it is an Update query.

    You also shouldn't be using Screen.ActiveForm. but rather explicitly referring to the correct form, or using a variable and setting it to a meaningful value. But anyway, that's not the immediate problem.

    What DO you want to set the form's recordsource to?

  7. #7
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    I'm not very experienced, so it's not surprising, but your response still wasn't making any sense. I re-read it several times before I locked in on your comment about Access not being able to fine any table or query named "Update Individual Account - NEW". I didn't realize what the code was doing and I made the assumption that the call was looking to the FORM with the same name. Only after your response did I look and realize there is a query with the same name, that I did not copy and modify. Let me go back to square one, incorporate a modified version of this query and see if it works.

    While I don't understand the implications of the code using the screen.activeform (it was already there), I trust you in the fact that it shouldn't be done that way. I'm hesitant to change it at this point, since it seems to work in it's original form. If I can get this part working, I will come back at a later point and get educated further on better way(s) to perform that function.

    I will let you know the outcome. Thanks for all of your assistance!

  8. #8
    Paintballlovr is offline Advanced Beginner
    Windows 7 32bit Access 2007
    Join Date
    Jul 2013
    Posts
    96
    I feel SO stupid, but that was the issue. I copied query "Update Individual Account" and modified for the new table and it works fine.

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

Similar Threads

  1. sql update statement syntax error
    By osupratt in forum Queries
    Replies: 7
    Last Post: 10-18-2013, 07:42 AM
  2. Compile Error: Syntax Error in DoCmd.RunSQL Statement
    By Evilferret in forum Programming
    Replies: 1
    Last Post: 08-27-2012, 12:32 PM
  3. Syntax Error 3144 in SQL Update Query.
    By Phred in forum Programming
    Replies: 4
    Last Post: 03-02-2012, 02:39 PM
  4. Syntax error in UPDATE statement HELP!
    By asmith78 in forum SQL Server
    Replies: 5
    Last Post: 09-07-2011, 05:50 PM
  5. Run Time Error 3144 for UPDATE
    By KrenzyRyan in forum Programming
    Replies: 12
    Last Post: 05-20-2011, 10:28 AM

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