Running Update Query

  • Thread starter Thread starter accessuser1308
  • Start date Start date
A

accessuser1308

I actually have two questions:

Question 1
I have a combo box on my form. In the after update event of the combo box I
am running an update query...

UPDATE table1 SET table1.Avail = Yes
WHERE (((table1.Plate)=[frmform1].[identity));

After I select a choice from the combo box the update query runs, but it
prompts me to enter the identity instead of looking to the field 'identity'
on the form. If I type the value (which is located in identity) the query
works correctly. It, however, will not run without me typing the value. Is
there a way to get the query to run by matching the value in the identity
field on my form without being prompted to enter the value.

Question 2
When the update query mentioned above is run it prompts me, telling me it is
going to run the query. When I click yes it prompts me to enter the value in
the identity field. If I type the value it prompts me again, stating it will
update x# of records. My question is, how can I prevent Access from posting
both of these messages. I would like to make my selection from the combo
box, have the query run and not be prompted at any point.

Thank you
 
Question 1: Syntax issue:

UPDATE table1 SET table1.Avail = Yes
WHERE (((table1.Plate)=[Forms]![frmform1].[identity));


Question 2: Turn off the warnings using DoCmd.SetWarnings -- but,
caution, be sure you turn them back on:

' turn warnings off
DoCmd.SetWarnings False
' run your update query -- this is your SQL statement, not
' a valid VBA code step (shown for example)
UPDATE table1 SET table1.Avail = Yes
WHERE (((table1.Plate)=[Forms]![frmform1].[identity));
' turn warnings back on
DoCmd.SetWarnings True


An additional question for you. Normally, I would consider it premature to
run an update query from the AfterUpdate event of a combo box. What if the
user selects the wrong item from the combo box's dropdown list?
 
Hi Ken,

Thank you for your help. Everything works perfectly now.

Thank you for your suggestion about running the update in the after update
event. I had already factored this in. After the update is performed my
combo box is no longer visible and a command button appears. When the
command button is clicked it reverses the update and the combo box is visible
again, while the command button disappears. I have tested everything and
with your help it now works perfectly.

Thank you very much for the help!!

Ken Snell (MVP) said:
Question 1: Syntax issue:

UPDATE table1 SET table1.Avail = Yes
WHERE (((table1.Plate)=[Forms]![frmform1].[identity));


Question 2: Turn off the warnings using DoCmd.SetWarnings -- but,
caution, be sure you turn them back on:

' turn warnings off
DoCmd.SetWarnings False
' run your update query -- this is your SQL statement, not
' a valid VBA code step (shown for example)
UPDATE table1 SET table1.Avail = Yes
WHERE (((table1.Plate)=[Forms]![frmform1].[identity));
' turn warnings back on
DoCmd.SetWarnings True


An additional question for you. Normally, I would consider it premature to
run an update query from the AfterUpdate event of a combo box. What if the
user selects the wrong item from the combo box's dropdown list?
--

Ken Snell
<MS ACCESS MVP>



accessuser1308 said:
I actually have two questions:

Question 1
I have a combo box on my form. In the after update event of the combo box
I
am running an update query...

UPDATE table1 SET table1.Avail = Yes
WHERE (((table1.Plate)=[frmform1].[identity));

After I select a choice from the combo box the update query runs, but it
prompts me to enter the identity instead of looking to the field
'identity'
on the form. If I type the value (which is located in identity) the query
works correctly. It, however, will not run without me typing the value.
Is
there a way to get the query to run by matching the value in the identity
field on my form without being prompted to enter the value.

Question 2
When the update query mentioned above is run it prompts me, telling me it
is
going to run the query. When I click yes it prompts me to enter the value
in
the identity field. If I type the value it prompts me again, stating it
will
update x# of records. My question is, how can I prevent Access from
posting
both of these messages. I would like to make my selection from the combo
box, have the query run and not be prompted at any point.

Thank you
Click to expand...
Click to expand...
 
Back
Top