CODEWITHSUNDEEP
sqlvbams-accessparameters
EchoByke
EchoByke

Reputation: 11

ms access update query fails missing parameters?

here is sub

Private Sub btn_Archive_Click()

    Dim dbs As Database
    Dim sqlStr As String

    sqlStr = "Update [T Prayer Requests] Set [close date] = '" & Date & "' where " & _
    "[T Prayer Requests].[Last Name] = '" & Me.List6. _
    Column(0) & "' " & "And [T Prayer Requests].[First Name] = '" & Me.List6. _
    Column(1) & "' " & "And [T Prayer Requests].[Phone] = '" & Me.List6. _
    Column(2) & "';"

'(data items come from selected row in listbox list6 where col(0) = last name, col(1) = first name, 'col(2) = phone

On Error GoTo Handler
    
    If MsgBox("Archive: All prayer requests for this person will be closed. " & _
    "They will still be on file, but inactive. Execute Archive?", vbYesNo, "Delete Person") = vbYes Then
        Set dbs = CurrentDb
        Debug.Print sqlStr
        dbs.Execute sqlStr, dbFailOnError
        Set dbs = Nothing
    End If
    
    Exit Sub

Handler:
    MsgBox "Unexpected error encountered. Delete was aborted. Error #" & Err.Number & " " & Err.Description
    Set dbs = Nothing

End Sub

Here is a test data row

ID  FK Last Name  FK First Name   FK Phone          Header      Details    Notes    Start Date  Close Date

40  delete last   delete firstl   (383) 838-3838    delete this delete fast     12/13/2020

("delete last" = 1 item, "delete firstl" = 1 item, "delete this" = 1 item, "detlete fast" = 1 item, notes = null, start date = 12/13/2020, close date = Null, phone stored with no formatting)

here is assembled sqlstr according to debug.print

Update [T Prayer Requests] Set [close date] = '12/14/2020' where [T Prayer Requests].[Last Name] = 

'delete last' And [T Prayer Requests].[First Name] = 'delete firstl' And [T Prayer Requests].[Phone] 

= '3838383838';

However when I run it gives error saying it expected 3 parameters, but I don't see anything missing in the assembled sqlStr. I also tried it with #'s enclosing the date. No Go. What's the deal?

thnx

Upvotes: 1

Views: 106

Answers (1)

Jonathan Willcock
Jonathan Willcock

Reputation: 5245

According to your sample row data, the column names are [FK Last Name}, [FK First Name], and [FK Phone], but your update statement is missing the FK. In such a case Access will automatically assume that these are parameters. If you copy your update statement into a new Query and try to run it, you will find that Access automatically prompts you for these parameters. Because you are running it as a script, it cannot do this, instead it complains about the three missing parameters.

Upvotes: 1

Related Questions