Question about Udtate Query

S

steveatdb

Hi I have a form used to edit the details in a table.
In the form I have a bunch of text boxes which display the current text held
in the cells from the table specified by critera that the user has selected
from a combo box above. I then have a second set of textboxes to the right of
these which the user can use to edit the detail displayed on the left. I then
have a cmd button which when clicked runs a update query. The update query
that I thought I made updates all of the feilds in the table to what is
entered into the textboxes unless they are Null. However when I run the query
it wont up date any of the feilds if any of the textboxes are null. I created
it using design mode however here is a copy of the query in SQL code. If
anyone could help me rewrite the code so that it works as desired or if they
have any other suggestions as a workaround that would be most appreciated.
UPDATE FACILITY_DESCRIPTION SET FACILITY_DESCRIPTION.SectionCode =
[Forms]![frmeditfacility]![txtNSectionCode], FACILITY_DESCRIPTION.Description
= [Forms]![frmeditfacility]![txtNDescription], FACILITY_DESCRIPTION.ShortID =
[Forms]![frmeditfacility]![txtNShortID],
FACILITY_DESCRIPTION.DocCollection_ID =
[Forms]![frmeditfacility]![txtNDocument_CollectionID],
FACILITY_DESCRIPTION.SectionHead =
[Forms]![frmeditfacility]![txtNSectionHead], FACILITY_DESCRIPTION.Sorter =
[Forms]![frmeditfacility]![txtNSorter], FACILITY_DESCRIPTION.Details =
[Forms]![frmeditfacility]![txtNDetails]
WHERE
(((FACILITY_DESCRIPTION.SectionCode)=[Forms]![frmeditfacility]![txtSectionCode])
AND
((FACILITY_DESCRIPTION.Description)=[Forms]![frmeditfacility]![txtDescription])
AND ((FACILITY_DESCRIPTION.ShortID)=[Forms]![frmeditfacility]![txtShortID])
AND
((FACILITY_DESCRIPTION.DocCollection_ID)=[Forms]![frmeditfacility]![txtDocument_CollectionID])
AND
((FACILITY_DESCRIPTION.SectionHead)=[Forms]![frmeditfacility]![txtSectionHead])
AND ((FACILITY_DESCRIPTION.Sorter)=[Forms]![frmeditfacility]![txtSorter]) AND
((FACILITY_DESCRIPTION.Details)=[Forms]![frmeditfacility]![txtDetails]) AND
(([Forms]![frmeditfacility]![txtNSectionCode]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDescription]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNShortID]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDocument_CollectionID]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNSectionHead]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNSorter]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDetails]) Is Not Null));

Thanks
 
J

John W. Vinson

In the form I have a bunch of text boxes which display the current text held
in the cells from the table specified by critera that the user has selected
from a combo box above. I then have a second set of textboxes to the right of
these which the user can use to edit the detail displayed on the left.
Click to expand...

ok... but WHY!?

You're going to a whole lot of extra work to display every editable field in
your table twice. Could you explain why this is necessary, vs. the (in my
opinion, much more user friendly) bound form where the user simply sees what's
there and edits it?

John W. Vinson [MVP]
 
M

Maurice

Steve,

Can you tell me what the error is you are getting. What's wrong with the
code? Doesn't it update at all or what.

Reading the statement I would suggest breaking it down to smaller pieces
first to see if the Update works without any Where clause. If that works you
know the error is in the Where section of you statement.

If the first piece works then add a where statement step by step.

Needs no explanation that you should do this on a copy of the database
instead of the production!!

hth
--
Maurice Ausum


steveatdb said:
Hi I have a form used to edit the details in a table.
In the form I have a bunch of text boxes which display the current text held
in the cells from the table specified by critera that the user has selected
from a combo box above. I then have a second set of textboxes to the right of
these which the user can use to edit the detail displayed on the left. I then
have a cmd button which when clicked runs a update query. The update query
that I thought I made updates all of the feilds in the table to what is
entered into the textboxes unless they are Null. However when I run the query
it wont up date any of the feilds if any of the textboxes are null. I created
it using design mode however here is a copy of the query in SQL code. If
anyone could help me rewrite the code so that it works as desired or if they
have any other suggestions as a workaround that would be most appreciated.
UPDATE FACILITY_DESCRIPTION SET FACILITY_DESCRIPTION.SectionCode =
[Forms]![frmeditfacility]![txtNSectionCode], FACILITY_DESCRIPTION.Description
= [Forms]![frmeditfacility]![txtNDescription], FACILITY_DESCRIPTION.ShortID =
[Forms]![frmeditfacility]![txtNShortID],
FACILITY_DESCRIPTION.DocCollection_ID =
[Forms]![frmeditfacility]![txtNDocument_CollectionID],
FACILITY_DESCRIPTION.SectionHead =
[Forms]![frmeditfacility]![txtNSectionHead], FACILITY_DESCRIPTION.Sorter =
[Forms]![frmeditfacility]![txtNSorter], FACILITY_DESCRIPTION.Details =
[Forms]![frmeditfacility]![txtNDetails]
WHERE
(((FACILITY_DESCRIPTION.SectionCode)=[Forms]![frmeditfacility]![txtSectionCode])
AND
((FACILITY_DESCRIPTION.Description)=[Forms]![frmeditfacility]![txtDescription])
AND ((FACILITY_DESCRIPTION.ShortID)=[Forms]![frmeditfacility]![txtShortID])
AND
((FACILITY_DESCRIPTION.DocCollection_ID)=[Forms]![frmeditfacility]![txtDocument_CollectionID])
AND
((FACILITY_DESCRIPTION.SectionHead)=[Forms]![frmeditfacility]![txtSectionHead])
AND ((FACILITY_DESCRIPTION.Sorter)=[Forms]![frmeditfacility]![txtSorter]) AND
((FACILITY_DESCRIPTION.Details)=[Forms]![frmeditfacility]![txtDetails]) AND
(([Forms]![frmeditfacility]![txtNSectionCode]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDescription]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNShortID]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDocument_CollectionID]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNSectionHead]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNSorter]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDetails]) Is Not Null));

Thanks
Click to expand...
 
S

steveatdb

Hi Maurice,
I'm not actully getting a error, It just will not update if anyone of the
feilds is left empty. I tried sperating it into several smaller querys each
only updating one feild and this worked however it is quite bulky but I'm
trying to see how it goes for now I was just trying to find away to stick it
all into one query.
And yes this is on a copy version of the actual database

Maurice said:
Steve,

Can you tell me what the error is you are getting. What's wrong with the
code? Doesn't it update at all or what.

Reading the statement I would suggest breaking it down to smaller pieces
first to see if the Update works without any Where clause. If that works you
know the error is in the Where section of you statement.

If the first piece works then add a where statement step by step.

Needs no explanation that you should do this on a copy of the database
instead of the production!!

hth
--
Maurice Ausum


steveatdb said:
Hi I have a form used to edit the details in a table.
In the form I have a bunch of text boxes which display the current text held
in the cells from the table specified by critera that the user has selected
from a combo box above. I then have a second set of textboxes to the right of
these which the user can use to edit the detail displayed on the left. I then
have a cmd button which when clicked runs a update query. The update query
that I thought I made updates all of the feilds in the table to what is
entered into the textboxes unless they are Null. However when I run the query
it wont up date any of the feilds if any of the textboxes are null. I created
it using design mode however here is a copy of the query in SQL code. If
anyone could help me rewrite the code so that it works as desired or if they
have any other suggestions as a workaround that would be most appreciated.
UPDATE FACILITY_DESCRIPTION SET FACILITY_DESCRIPTION.SectionCode =
[Forms]![frmeditfacility]![txtNSectionCode], FACILITY_DESCRIPTION.Description
= [Forms]![frmeditfacility]![txtNDescription], FACILITY_DESCRIPTION.ShortID =
[Forms]![frmeditfacility]![txtNShortID],
FACILITY_DESCRIPTION.DocCollection_ID =
[Forms]![frmeditfacility]![txtNDocument_CollectionID],
FACILITY_DESCRIPTION.SectionHead =
[Forms]![frmeditfacility]![txtNSectionHead], FACILITY_DESCRIPTION.Sorter =
[Forms]![frmeditfacility]![txtNSorter], FACILITY_DESCRIPTION.Details =
[Forms]![frmeditfacility]![txtNDetails]
WHERE
(((FACILITY_DESCRIPTION.SectionCode)=[Forms]![frmeditfacility]![txtSectionCode])
AND
((FACILITY_DESCRIPTION.Description)=[Forms]![frmeditfacility]![txtDescription])
AND ((FACILITY_DESCRIPTION.ShortID)=[Forms]![frmeditfacility]![txtShortID])
AND
((FACILITY_DESCRIPTION.DocCollection_ID)=[Forms]![frmeditfacility]![txtDocument_CollectionID])
AND
((FACILITY_DESCRIPTION.SectionHead)=[Forms]![frmeditfacility]![txtSectionHead])
AND ((FACILITY_DESCRIPTION.Sorter)=[Forms]![frmeditfacility]![txtSorter]) AND
((FACILITY_DESCRIPTION.Details)=[Forms]![frmeditfacility]![txtDetails]) AND
(([Forms]![frmeditfacility]![txtNSectionCode]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDescription]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNShortID]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDocument_CollectionID]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNSectionHead]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNSorter]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDetails]) Is Not Null));

Thanks
Click to expand...
Click to expand...
 
S

steveatdb

Hi John,
I had it that way before but the higher ups asked me to change it because
they felt it was to easy to make a mistake.
I'm not really sure why they were so worried about it as you can just go
back and change it again but they didnt like it and asked me to have a
display column and a Edit Column with the display column having the allow
edits turned off.
 
J

John W. Vinson

Hi I have a form used to edit the details in a table.
In the form I have a bunch of text boxes which display the current text held
in the cells from the table specified by critera that the user has selected
from a combo box above. I then have a second set of textboxes to the right of
these which the user can use to edit the detail displayed on the left. I then
have a cmd button which when clicked runs a update query. The update query
that I thought I made updates all of the feilds in the table to what is
entered into the textboxes unless they are Null. However when I run the query
it wont up date any of the feilds if any of the textboxes are null. I created
it using design mode however here is a copy of the query in SQL code. If
anyone could help me rewrite the code so that it works as desired or if they
have any other suggestions as a workaround that would be most appreciated.
UPDATE FACILITY_DESCRIPTION SET FACILITY_DESCRIPTION.SectionCode =
[Forms]![frmeditfacility]![txtNSectionCode], FACILITY_DESCRIPTION.Description
= [Forms]![frmeditfacility]![txtNDescription], FACILITY_DESCRIPTION.ShortID =
[Forms]![frmeditfacility]![txtNShortID],
FACILITY_DESCRIPTION.DocCollection_ID =
[Forms]![frmeditfacility]![txtNDocument_CollectionID],
FACILITY_DESCRIPTION.SectionHead =
[Forms]![frmeditfacility]![txtNSectionHead], FACILITY_DESCRIPTION.Sorter =
[Forms]![frmeditfacility]![txtNSorter], FACILITY_DESCRIPTION.Details =
[Forms]![frmeditfacility]![txtNDetails]
WHERE
(((FACILITY_DESCRIPTION.SectionCode)=[Forms]![frmeditfacility]![txtSectionCode])
AND
((FACILITY_DESCRIPTION.Description)=[Forms]![frmeditfacility]![txtDescription])
AND ((FACILITY_DESCRIPTION.ShortID)=[Forms]![frmeditfacility]![txtShortID])
AND
((FACILITY_DESCRIPTION.DocCollection_ID)=[Forms]![frmeditfacility]![txtDocument_CollectionID])
AND
((FACILITY_DESCRIPTION.SectionHead)=[Forms]![frmeditfacility]![txtSectionHead])
AND ((FACILITY_DESCRIPTION.Sorter)=[Forms]![frmeditfacility]![txtSorter]) AND
((FACILITY_DESCRIPTION.Details)=[Forms]![frmeditfacility]![txtDetails]) AND
(([Forms]![frmeditfacility]![txtNSectionCode]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDescription]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNShortID]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDocument_CollectionID]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNSectionHead]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNSorter]) Is Not Null) AND
(([Forms]![frmeditfacility]![txtNDetails]) Is Not Null));
Click to expand...

Ok... sometimes the Boss tells you to do something that you know is wrong, but
the boss is the boss and ya just gotta do it.

Solution: remove the Where clause terms except for one field, the field
uniquely identifying the record you want to update.

What you're specifying in this query is that the values already in the Table
fields for SectionCode, Description, ShortID, DocCollection_ID, SectionHead,
Sorter and Details must exactly match those on the form (so you would be
updating them to themselves, doing nothing); and similarly insisting that the
txtN<fieldname> codes must ALL have data in them. That is why it won't update
if any field is left blank - you're explicitly telling it to update *only* if
each of these form controls Is Not Null. You're also unnecessarily checking
multiple fields against the old value - you only need to check *one* field if
it's the unique ID in your table.

If you want to update (say) SectionHead to txtNSectionHead if there is data in
txtNSectionHead, and leave it alone if not, update to

NZ([Forms]![frmEditFacility]![txtNSectionHead], [SectionHead])

NZ will return the value in the first argument (the value in the form control)
if there is data there, otherwise the second argument - in this case, updating
the value in SectionHead to itself.


John W. Vinson [MVP]
 
Top