Problem with update query

H

hiwelcome

Registered User.
Local time
Today, 05:10
Joined
Aug 14, 2015
Messages
47
Hello,

I have a split database where I distribute front end copies for people to add their records locally and then append them to the back end tables. I also have given them the capability to modify the back end records that they have appended via update queries. Does anyone know why my append queries work just fine, but the update queries give me a validation rule violation? I have played around with the joins with no luck. Join option 1 warns that I will update 0 records, while options 2 and 3 both give me the validation rule violation. I have opened the front end and back end tables side by side, both General and Lookup tabs, and they are identical.

Here is a sample of the SQL if it will help:

UPDATE DOIHawaiiTbl_FE LEFT JOIN DOIHawaiiTbl ON (DOIHawaiiTbl_FE.Discipline = DOIHawaiiTbl.Discipline) AND (DOIHawaiiTbl_FE.Number = DOIHawaiiTbl.Number) SET DOIHawaiiTbl.Date = [DOIHawaiiTbl_FE].[Date], DOIHawaiiTbl.State = [DOIHawaiiTbl_FE].[State], DOIHawaiiTbl.HI1 = [DOIHawaiiTbl_FE].[HI1], DOIHawaiiTbl.HI2 = [DOIHawaiiTbl_FE].[HI2], DOIHawaiiTbl.HI3 = [DOIHawaiiTbl_FE].[HI3], DOIHawaiiTbl.HI4 = [DOIHawaiiTbl_FE].[HI4], DOIHawaiiTbl.HI5 = [DOIHawaiiTbl_FE].[HI5], DOIHawaiiTbl.HI6 = [DOIHawaiiTbl_FE].[HI6], DOIHawaiiTbl.HI7 = [DOIHawaiiTbl_FE].[HI7], DOIHawaiiTbl.HI8 = [DOIHawaiiTbl_FE].[HI8], DOIHawaiiTbl.HI9 = [DOIHawaiiTbl_FE].[HI9], DOIHawaiiTbl.HI10 = [DOIHawaiiTbl_FE].[HI10], DOIHawaiiTbl.HI11 = [DOIHawaiiTbl_FE].[HI11], DOIHawaiiTbl.HI12 = [DOIHawaiiTbl_FE].[HI12], DOIHawaiiTbl.HI13 = [DOIHawaiiTbl_FE].[HI13], DOIHawaiiTbl.HI14 = [DOIHawaiiTbl_FE].[HI14], DOIHawaiiTbl.HI15 = [DOIHawaiiTbl_FE].[HI15], DOIHawaiiTbl.HI16 = [DOIHawaiiTbl_FE].[HI16], DOIHawaiiTbl.HI17 = [DOIHawaiiTbl_FE].[HI17], DOIHawaiiTbl.HI18 = [DOIHawaiiTbl_FE].[HI18], DOIHawaiiTbl.AnyOtherDOIs = [DOIHawaiiTbl_FE].[AnyOtherDOIs], DOIHawaiiTbl.HIComments = [DOIHawaiiTbl_FE].[HIComments];
 
might be your date field - date is a reserved word and using as a field (or table) name can cause issues - often reporting a misleading error message.

other thought is to change the left join to an inner join - left joins are inappropriate in this case
 
use Right Join to Update and Add at the same time.

on your last SET statement:

DOIHawaiiTbl.HIComments = [DOIHawaiiTbl_FE].[HIComments];

you are updating the joined table, is this correct?
i see you have many like this on your query.
suggest you append/update one table, not two way.
if you want to update [DOIHawaiiTbl_FE], create a query to update this table only.
 
Thank you for the responses.

CJ, the field is actually not "Date", it's "DateOfSomething". Made some edits prior to posting for privacy reasons. I tried switching to an inner join and when I do so, it says I will update 0 records, but gives me no validation rule violation. FYI, I am not too familiar with SQL, so when I say I switched, I did it via clicking on the joins in query design (option 1).

arnelgp, I have forms with record sources tied to the _FE tables. So users enter their data and it goes to the _FE tables. My update queries (as far as I know??) are built to update the back end tables (DOIHawaiiTbl for example) with the new _FE table (DOIHawaiiTbl_FE) information. Using a right join (option 3 in query design) also gives me the same validation rule violation as option 2.
 
in that case implies there is something in your data which breaks one or more validation rules

Made some edits prior to posting for privacy reasons
Click to expand...

Also, since you have modified your code before posting, how do we know that what you have posted hasn't got a typo or something that hides the real problem?
 
Actually that's the only field name I edited, but to your broader point, I suppose you'd just have to take my word for it.

I am willing to post a stripped down version of the database if it would help?
 
i think you got this in reverse:

UPDATE DOIHawaiiTbl_FE LEFT JOIN DOIHawaiiTbl ON (DOIHawaiiTbl_FE.Discipline = DOIHawaiiTbl.Discipline) AND (DOIHawaiiTbl_FE.Number = DOIHawaiiTbl.Number)

should be:


UPDATE DOIHawaiiTbl LEFT JOIN DOIHawaiiTbl_FE ON (DOIHawaiiTbl.Discipline = DOIHawaiiTbl_FE.Discipline) AND (DOIHawaiiTbl.Number = DOIHawaiiTbl_FE.Number) SET DOIHawaiiTbl.Date = [DOIHawaiiTbl_FE].[Date], DOIHawaiiTbl.State = [DOIHawaiiTbl_FE].[State], DOIHawaiiTbl.HI1 = [DOIHawaiiTbl_FE].[HI1], DOIHawaiiTbl.HI2 = [DOIHawaiiTbl_FE].[HI2], DOIHawaiiTbl.HI3 = [DOIHawaiiTbl_FE].[HI3], DOIHawaiiTbl.HI4 = [DOIHawaiiTbl_FE].[HI4], DOIHawaiiTbl.HI5 = [DOIHawaiiTbl_FE].[HI5], DOIHawaiiTbl.HI6 = [DOIHawaiiTbl_FE].[HI6], DOIHawaiiTbl.HI7 = [DOIHawaiiTbl_FE].[HI7], DOIHawaiiTbl.HI8 = [DOIHawaiiTbl_FE].[HI8], DOIHawaiiTbl.HI9 = [DOIHawaiiTbl_FE].[HI9], DOIHawaiiTbl.HI10 = [DOIHawaiiTbl_FE].[HI10], DOIHawaiiTbl.HI11 = [DOIHawaiiTbl_FE].[HI11], DOIHawaiiTbl.HI12 = [DOIHawaiiTbl_FE].[HI12], DOIHawaiiTbl.HI13 = [DOIHawaiiTbl_FE].[HI13], DOIHawaiiTbl.HI14 = [DOIHawaiiTbl_FE].[HI14], DOIHawaiiTbl.HI15 = [DOIHawaiiTbl_FE].[HI15], DOIHawaiiTbl.HI16 = [DOIHawaiiTbl_FE].[HI16], DOIHawaiiTbl.HI17 = [DOIHawaiiTbl_FE].[HI17], DOIHawaiiTbl.HI18 = [DOIHawaiiTbl_FE].[HI18], DOIHawaiiTbl.AnyOtherDOIs = [DOIHawaiiTbl_FE].[AnyOtherDOIs], DOIHawaiiTbl.HIComments = [DOIHawaiiTbl_FE].[HIComments];
 
arnelgp - When copying your version in, it works. Thank you very much. My question is then, how could I have fixed this without knowing SQL? In design view, the arrows are reversed which looks exactly like it did when I tried a right join, yet it is not a right join.
 
must be a very meaningful field name to have to disguise it:D

@arnelgp - implication of the left join is that DOIHawaiiTbl could be updated with nulls where an equivalent record does not exist in DOIHawaiiTbl_FE - this may be allowed (i.e. if field validation allows nulls) but if field does not allow nulls (e.g. a primary key) then you will get a validation error - the point I made originally

Plus does hiwelcome really want to effectively delete data from a record in DOIHawaiiTbl when the record is not in DOIHawaiiTbl_FE? If so, would be better to use a delete query
 
CJ, you are correct. When I tested it just now, if I have 3 records in _FE and the same 3 in the BE, they update fine. When I deleted one in _FE, it updated the other two in the BE but also got a validation rule error (presumably for the one deleted). And the vast majority of use will be like this - many files in the BE and few in the FE. So it works but not ideally. Argh.

And furthermore, I've just discovered that another one of my sets of tables/update query works just fine the way I had it, yet this Hawaii one does not. Double argh. Would either of you be willing to look at a stripped down sample?
 
before posting your db, try using an inner join, that will get rid of the 'null records'
 
CJ, just tried that. It gave me the same "error" as at first - it says I'm going to update 0 records.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom