update a field in a table from a form

[klwu]

I have a Maintenance form in my database, where the user can change the ref. no of a particular supplier. This form contains one comboBox (list all the current RefNo of suppliers), and one text field for user to key in preferred RefNo.

I have wrote the following code for the 'Assign' button OnClick Event:

Private Sub cmdAssign_Click()
On Error GoTo Exit_cmdAssign_Click

Dim sSql As String
Dim dbs As Database

If Not IsNull(cmbSuppRef) And txtNewSuppRef <> "" Then
      sSql = "UPDATE M_Suppliers SET SuppRef = '" & txtNewSuppRef & "'" & _
               "WHERE SuppRef = '" & cmbSuppRef & "';"
Set dbs = CurrentDb
dbs.Execute sSql
End If

Exit_cmdAssign_Click:
      Exit Sub

Err_cmdAssign_Click:
      MsgBox Err.Description
      Resume Exit_cmdAssign_Click

End Sub

However, this code doesn't work. So, I have no idea how should I write the code. Anyone could guide me on this? Please! Thanks a bunch

 

[Dugantrain]

txtNewSuppRef & "'" & _
"WHERE

needs a space in front of "WHERE" i.e. " WHERE". Give that a try, hopefully it'll resolve your issue.

 

[klwu]

Thanks for that! However, it doesn't work.

 

[KenHigg]

Step thru your code with the debugger and when you get past the appropriate line, examine your sql statement in the immediate window. It will be easier to see any errors this way...

kh

 

[klwu]

There was no error message.
When I click on the button, it just do nothing.
I tried to compile the code, and it went fine.
So I couldn't find where I did wrongly.

 

[Pat Hartman]

Why would you run an update query to update the current record? You can do this with a single line of code in the AfterUpdate event of the txtNewSuppRef control:

Me.SuppRef = Me.txtNewSuppRef