Strange Update problem

[Swillsy]

Hi All,

i've got a very strange problem with an update query.
I have a linked table that I grab data from and put into another table using a select query - this works fine.

I thne run a query to update info from this table to another table using the code below.
The problem that is occuring is that the code works fine if I input the ID number close the form, repoen the form and run it.
However if I type in the code and then run the query it will not work.
Here is the code, altohugh i suspect this is aproblem with the form.

UPDATE Applicants INNER JOIN Students_Courses ON Applicants.Person_Code=Students_courses.Person_Code SET Applicants.Course_Title = Students_Courses.Course_Title
WHERE Students_Courses.Person_Code=applicants.Person_Code;

The query works however when I get to the point of updating for somereason it ins't recognising the record in the temporary table.

 

[Pat Hartman]

Whenever you need to use values from the current record in a query or to open a form or report, you need to save the current record first. Otherwise, the current value showing in the form may not be the same as what is stored in the table.

If Me.Dirty Then
Docmd.RunCommand acCmdSaveRecord
End if

 

[Swillsy]

Hiya Pat,

Thanks for your help working out the problem, the ondirty saverecord didn't work but I realised that the updates would be looknig at blank records so Halfway through I paused the code then me.recalc and its working now.

Thanks again

James

 

[Pat Hartman]

Me.Recalc isn't the correct solution. It may work but that is accidental. Use the code that I posted. Put it where I told you to put it. It does exactly what you need and ONLY what you need.

Note that I didn't say to use the on dirty event. That event fires when the first character is typed into the form to dirty it. It doesn't happen again so anything more than a single character change will not be saved if you used this event.