CODEWITHSUNDEEP
ms-accessvba
user2002716
user2002716

Reputation: 120

AfterUpdate combobox find and replace

I'm trying to figure out how to use AfterUpdate on a combo box to have it find a record from table "Userdata" where field "Recall_position" equals "1" and replace it with "0".

Then find the record selected in the combobox (that it was updated to) and in "Userdata" under "Recall_position" put the number "1"

Upvotes: 0

Views: 139

Answers (2)

user2002716
user2002716

Reputation: 120

So using that code I have

Private Sub Combo1_AfterUpdate()
    CurrentDb.Execute "UPDATE Userdata SET Recall_position = 0 WHERE Recall_position = 10"
CurrentDb.Execute "UPDATE Userdata SET Recall_position = 10 WHERE Lastname = '" & Me.Combo1.Column(1)  & "'"
End Sub

Which does clear out the 10 and updates the right row to 10 as well. However, it is also updating a second row with a random number in the Lastname

Upvotes: 0

Christian Specht
Christian Specht

Reputation: 36421

Like this?

Private Sub cmbTest_AfterUpdate()

    CurrentDb.Execute "update Userdata set Recall_position = 0 where Recall_position = 1"
    CurrentDb.Execute "update Userdata set Recall_position = 1 where ID = " & Me.cmbTest.Column(0)

End Sub

The first query updates all records with Recall_position = 1 to Recall_position = 0.

The second query updates the record with the ID in the first column of the selected item in the combo box to Recall_position = 1.
(I assumed that it's a numeric value and that the column in the table is called ID)

Upvotes: 1

Related Questions