UPDATE QUERY not updating data

M

Morris

Hi all,

Is there something obvious I should know about or is it plain magic?

I've got a Access 2000 file format database in Access 2003.

I've got a table called [Jobs] and I'm developing a way to edit a Job.
So I've created an identical table called [EditedJob]. After clicking
EDIT I clear the table [EditedJob], then populate it with all the
details of a current job and open a form frmEditJob which is bound to
[EditedJob] table.

Now, I am able to change all the details I want and when clicking
"save" button I run this code:


Dim qryUpdate As QueryDef
Set qryUpdate =
CurrentDb.QueryDefs("qryUpdateJobsWithDetailsFromEditedJob")
qryUpdate.Execute dbFailOnError
MsgBox qryUpdate.RecordsAffected

Which gives me a messagebox saying '1'.

But nothing happens - the table isn't updated. Now - if I run the
query manually (double clicking it in the Access control panel) it
also says it will update one record. But this time it does update it.

This is how the Query's sql code looks like:

UPDATE EditedJob INNER JOIN Jobs ON EditedJob.ID=Jobs.ID SET
Jobs.CompanyID = EditedJob.CompanyID, Jobs.JobTitle =
EditedJob.JobTitle, Jobs.FieldworkCountryID =
EditedJob.FieldWorkCountryID, Jobs.ExecResponsible =
EditedJob.ExecResponsible, Jobs.IRC = EditedJob.IRC, Jobs.DateProposed
= EditedJob.DateProposed, Jobs.DateAccepted = EditedJob.DateAccepted,
Jobs.AgreedFieldworkFrom = EditedJob.AgreedFieldworkFrom,
Jobs.AgreedFieldworkUntil = EditedJob.AgreedFieldworkUntil,
Jobs.ActualFieldworkFrom = EditedJob.ActualFieldworkFrom,
Jobs.ActualFieldworkUntil = EditedJob.ActualFieldworkUntil,
Jobs.NoOfLocationsUsed = EditedJob.NoOfLocationsUsed,
Jobs.InterviewsDuration = EditedJob.InterviewsDuration,
Jobs.CurrencyExchangeRate = EditedJob.CurrencyExchangeRate,
Jobs.JobCurrency = EditedJob.JobCurrency, Jobs.GrossCostAccepted =
EditedJob.GrossCostAccepted, Jobs.AmountSaved = EditedJob.AmountSaved,
Jobs.SampleAgreed = EditedJob.SampleAgreed, Jobs.SampleAchieved =
EditedJob.SampleAchieved, Jobs.FieldworkTypeID =
EditedJob.FieldWorkTypeID, Jobs.RespondentTypeID =
EditedJob.RespondentTypeID, Jobs.MethodologyID =
EditedJob.MethodologyID, Jobs.BriefingMethodID =
EditedJob.BriefingMethodID, Jobs.FieldworkCost =
EditedJob.FieldworkCost, Jobs.IncentiveCost = EditedJob.IncentiveCost,
Jobs.OtherCost = EditedJob.OtherCost
WHERE (((Jobs.ID)=EditedJob.ID));

So you can see it's joined on ID which is identical for both tables -
but somehow the data doesn't get updated.

I would be very grateful on any help on this matter,

Morris
 
S

Steve Sanford

Are you sure the query you run manually is the same query that runs when you
click the "save" button?

The reason I ask is that the SQL for the query you posted has:
UPDATE EditedJob ....................................
Click to expand...

Seems to me that you would want:


UPDATE JOBS ..............................
^^^^

if you want to update table *Jobs* with the changed data in table EditedJob.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


Morris said:
Hi all,

Is there something obvious I should know about or is it plain magic?

I've got a Access 2000 file format database in Access 2003.

I've got a table called [Jobs] and I'm developing a way to edit a Job.
So I've created an identical table called [EditedJob]. After clicking
EDIT I clear the table [EditedJob], then populate it with all the
details of a current job and open a form frmEditJob which is bound to
[EditedJob] table.

Now, I am able to change all the details I want and when clicking
"save" button I run this code:


Dim qryUpdate As QueryDef
Set qryUpdate =
CurrentDb.QueryDefs("qryUpdateJobsWithDetailsFromEditedJob")
qryUpdate.Execute dbFailOnError
MsgBox qryUpdate.RecordsAffected

Which gives me a messagebox saying '1'.

But nothing happens - the table isn't updated. Now - if I run the
query manually (double clicking it in the Access control panel) it
also says it will update one record. But this time it does update it.

This is how the Query's sql code looks like:

UPDATE EditedJob INNER JOIN Jobs ON EditedJob.ID=Jobs.ID SET
Jobs.CompanyID = EditedJob.CompanyID, Jobs.JobTitle =
EditedJob.JobTitle, Jobs.FieldworkCountryID =
EditedJob.FieldWorkCountryID, Jobs.ExecResponsible =
EditedJob.ExecResponsible, Jobs.IRC = EditedJob.IRC, Jobs.DateProposed
= EditedJob.DateProposed, Jobs.DateAccepted = EditedJob.DateAccepted,
Jobs.AgreedFieldworkFrom = EditedJob.AgreedFieldworkFrom,
Jobs.AgreedFieldworkUntil = EditedJob.AgreedFieldworkUntil,
Jobs.ActualFieldworkFrom = EditedJob.ActualFieldworkFrom,
Jobs.ActualFieldworkUntil = EditedJob.ActualFieldworkUntil,
Jobs.NoOfLocationsUsed = EditedJob.NoOfLocationsUsed,
Jobs.InterviewsDuration = EditedJob.InterviewsDuration,
Jobs.CurrencyExchangeRate = EditedJob.CurrencyExchangeRate,
Jobs.JobCurrency = EditedJob.JobCurrency, Jobs.GrossCostAccepted =
EditedJob.GrossCostAccepted, Jobs.AmountSaved = EditedJob.AmountSaved,
Jobs.SampleAgreed = EditedJob.SampleAgreed, Jobs.SampleAchieved =
EditedJob.SampleAchieved, Jobs.FieldworkTypeID =
EditedJob.FieldWorkTypeID, Jobs.RespondentTypeID =
EditedJob.RespondentTypeID, Jobs.MethodologyID =
EditedJob.MethodologyID, Jobs.BriefingMethodID =
EditedJob.BriefingMethodID, Jobs.FieldworkCost =
EditedJob.FieldworkCost, Jobs.IncentiveCost = EditedJob.IncentiveCost,
Jobs.OtherCost = EditedJob.OtherCost
WHERE (((Jobs.ID)=EditedJob.ID));

So you can see it's joined on ID which is identical for both tables -
but somehow the data doesn't get updated.

I would be very grateful on any help on this matter,

Morris
Click to expand...
 
Top