UPDATE Query not working

[justme1310]

Hi Everyone,

Can someone look at this code and help me figure out why its not working, Please???

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

 

[lespaul]

Is tblatdcots.FILENO a numeric field? If so, you'll need to remove the ':

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

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[justme1310]

Hi Leslie,

Thanks so much for replying.
I checked - tblatdocts.fileno is a text field.

Any other suggestions?

Will

 

[dhookom]

Do you have any docs name O'Brien or O'Malley or O'Connor or O'My? If so, you should change your code to:

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

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]