Update a specific record based on value in a textbox (1 Viewer)

[anb001]

I would like to update a specific record using below code. However it needs a bit of modification, as it updates all records, as it is right now.

I need it to update a record based on a value which is in a textbox.

I have tried including a WHERE clause, but that doesn't work. It just goes directly to the msgbox then.

Cam some one tell me how to get that included?

Private Sub cmdAdd1_Click()
Dim strAdd1 As String
    
If Me.cboSJANumber1 = Null Or Me.cboSJANumber1 = "" Then
    
    strAdd1 = "UPDATE tblUSerDetails SET tblUserDetails.Favourite1 = '" & Me.cboSJANumber1 & "'"
    CurrentProject.Connection.Execute strAdd1

Else
MsgBox ("Please choose a SJA first!")

Exit Sub
End If


End Sub

 

[Pat Hartman]

The update statement requires a where clause if you want to limit it to a single record. If you post the one that doesn't work. We'll help with the syntax.

FYI - I hope you're not using this update query to update the record your form is currently displaying. If it is, you'll get a conflict message from Access telling you that someone else is editing the record and that someone would be you

 

[VilaRestal]

If Nz(Me.cboSJANumber1,"") = "" Then

You can't test for null with If x = Null. Null isn't a value. You must use IsNull() or Nz(,) functions.

 

[anb001]

Pat,

I found the mistake myself. The IF ... THEN part was wrong. It is changed now, and the code, in a working state, looks like this:

Private Sub cmdAdd1_Click()
Dim strAdd1 As String
    
If Not IsNull(Me.cboSJANumber1) Or Me.cboSJANumber1 = "" Then
    
    strAdd1 = "UPDATE tblUSerDetails SET tblUserDetails.Favourite1 = '" & Me.cboSJANumber1 & "' _
WHERE tblUserDetails.LoginID = '" & Me.txtGetUser & "'"
    CurrentProject.Connection.Execute strAdd1

Else
MsgBox ("Please choose a SJA first!")

Exit Sub
End If


End Sub

No, it is a different table/record which is being updated