Running Update Query

[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

 

[Ken Snell \(MVP\)]

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?

 

[accessuser1308]

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!!

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>

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