UPDATE QUERY not working

[Will04]

Hi Everyone,

Here's the situation:-

I have a patient record with a specific consultant assigned to the patient. In some instances, the patient may request a different consultant. What I want to do is to allow the user to change the consultant and to save details about the change e.g. file no, old value, new value, date etc.

This is what I have done so far:-

I created a Query to retrieve details from 3 tables and assigned the value to be changed to a variable (oldvalue)

I then allow the user to select from a combo box the new consultant.

Details of these changes are then written to a table called tblchanges.

All the above is working.

The problem occurs when I try to update the consultant table, by replacing the old consultant with the newly selected consultant.

Can someone look at the code and let me know first if I am approaching this problem correctly and then why the code is not working.

Private Sub Combo36_Change()
Dim flno As String
Dim fldnme As String
Dim oldval As String
Dim newval As String
Dim tdate As Date
Dim ttime As Date
Dim sqn As String
Dim yrn As String


flno = PFILENO
fldnme = "Radiation Oncologst"
oldval = Text29
newval = Combo36
tdate = Date
ttime = Time

'Insert change values into tblchanges

strSql = "INSERT INTO tblchanges " _
& "( fileno,fieldname, oldvalue, newvalue, datechg,timechg ) " _
& "VALUES ('" & flno & "','" & fldnme & "','" & oldval & "','" & newval & "',#" & tdate & "#,'" & ttime & "')"


DoCmd.SetWarnings False
DoCmd.RunSQL strSql
DoCmd.SetWarnings True


strFILENO = (Left(Me!PFILENO, 4) & "/" & Right(Me!PFILENO, 4))

'update consultant table with new value

stsql2 = "UPDATE tblatdocts SET roncologist = '" & newval & "' "
stsql2 = stsql2 & "WHERE tblatdocts.FILENO = '" & strFILENO & "' "

DoCmd.SetWarnings False
DoCmd.RunSQL stsql2
DoCmd.SetWarnings True

End Sub

Thanks so much for your help.

Will

 

[spasticus]

if you doing this through a form you dont need the update bit. if you open a record and change details they change anyway. all you need to do is store the old value before its changed