CODEWITHSUNDEEP
ms-access-2010
HelloWorld
HelloWorld

Reputation: 283

Access 2010 Update Query Error

i am trying to create an update query that accepts parameters for the values that need updating. My query:

PARAMETERS FirstName Text ( 255 ), LastName Text ( 255 ), AccessLevel Short;
UPDATE   
tblUser SET tblUser.FName = [FirstName],   
tblUser.LName = [LastName],   
tblUser.AccessLevelID = [AccessLevel]
WHERE (((tblUser.UserID)=[Forms]![frmEditUser]![subfrmUsers].[Form]![UserID]));

And my VBA code that runs when the edit button is pushed by the user: 

If valid Then
    DoCmd.SetParameter "FirstName", Forms!frmEditUser!txtFName
    DoCmd.SetParameter "LastName", Forms!frmEditUser!txtLName
    DoCmd.SetParameter "AccessLevel", Forms!frmEditUser!cmbAccessLevel
    DoCmd.OpenQuery ("qryEditUserRecord")
    MsgBox "Success: Record edits have been applied", _
        vbOKOnly, "Record Edits Success"
    Me.Refresh
End If

valid is just a boolean that is true when all controls have data. The error is: 

The object does not contain the Automation object "Drew"

Where Drew is the current records FName value. This error occurs at the first SetParameter command and I am unaware of the problem or how to fix it. The query runs as expected if I prompt outside the VBA code, but that is what I need for this to work.

Upvotes: 0

Views: 957

Answers (1)

HelloWorld
HelloWorld

Reputation: 283

Turns out there is a very unintuitive solution that I found here http://social.msdn.microsoft.com/Forums/en-ZA/accessdev/thread/8538bd05-abc3-4cad-b284-bcbf7fa3dd69

When dealing with a string parameter the DoCmd.Parameter "para", """" & Me.txtName & """"

I have no idea why it needs the four double quotes and an & but it does.

Upvotes: 0

Related Questions