Update Query Not Working

Question

Okay, I will admit is has been a very long time since I've used Access but I'm trying to create an Update Query that will take values from a form and update a table, but no matter what I try I can't seem to make it work so I'm looking for some quick help.

I have a table with two fields - LoginID and Status.  I then have a form with two combo boxes (Login and Status) and a button.  When the user clicks on the button I want it to update my table with the new Status for the login.  My code for the button is:

DoCmd.OpenQuery, "Users_Update_Status_Query", acViewNormal

Then, my code for the Query is:

Status - Update To: Forms!MyForm!ComboStatus

LoginID - Criteria: Forms!MyForm!ComboLogin

The query seems to run by my table is just not updating.  Where am I going wrong?

Answer 1

>>>The query seems to run by my table is just not updating.  Where am I going wrong?

According to your description, I suggest that you could get Sql string from query object to check what is this Sql string:

CurrentDb.QueryDefs("Users_Update_Status_Query").SQL

For more information, click here to refer about QueryDef Object (DAO)

Answer 2

Is there a reason you cannot have the table as the form's record source? 

Answer 3

I'd normally do this sort of thing entirely in code rather than creating a query which references the controls as parameters:

Const MESSAGE_TEXT = " Both a login ID and a status must be selected."
Dim strSQL As String

If Not IsNull(Me.ComboStatus) And Not IsNull(Me.ComboLogin) Then
    strSQL = "UPDATE YourTableNameGoesHere " & _
        "SET Status = """ & Me.ComboStatus  & """ " & _
        "WHERE LoginID = " & MeComboLogin

    CurrentDb.Execute strSQL, dbFailOnError
Else
    MsgBox MESSAGE_TEXT, vbExclamation, "Invalid operation"
End if

I've assumed that the Status column is of a text data type, and the LoginID column is of a number data type.

---

Ken Sheridan, Stafford, England

Answer 4

Sorry, I'm not quite sure how that would work - perhaps I need to explain it better.

In my Table, I have two fields:  LoginID which is a string and Access which is selected from a Combo (either User or Admin).

On my form, I have two Combo Fields:  LoginName which is a combo which is drawing data from the LoginID field in the table; and Access which is also a combo using the two values above.

When I click the button on the form, I want the update to run as:

UPDATE Users Set Access = Me.ComboAccess.Value WHERE LoginName = Me.ComboLoginName.Value

The query runs fine, but nothing is actually happening.

Answer 5

If you build and execute the SQL statement in code you will be able to debug it and examine the statement which is built to see if it is what you expect.  Also you can trap any error by means of the dbFailOnError option.

The OpenQuery method of the DoCmd object can be used to call a querydef object which executes an UPDATE statement, but it does not give you the above control.  Also why are you using the acViewNormal option?  You are not opening a datasheet.

---

Ken Sheridan, Stafford, England

Answer 6

Hi goodhealthit,

I have made a smple to try to reproduce this issue, unfortunately, I am not able. I am able to update table successfully.

Create table:

Create Form:

Create Query:

Open Form input data:

This result:

So I suggest that you could make sure your query and update data is correct.

In addition you could upload your database file on OneDrive, we will try to reproduce and resolve this issue.

Thanks for your understanding.

Answer 7

You wrote: "

The query runs fine, but nothing is actually happening."

- - it would seem your query is a Select Query; change it to an Append Query.

Answer 8

- - it would seem your query is a Select Query; change it to an Append Query.

That would explain the behaviour, but the original post doesn't really support that hypothesis.

---

Ken Sheridan, Stafford, England