UPDATE query question

[mattpfc]

hi all,

i have the following simple update query, which i hope to run on a form that will take values from that form and update a table.

i have got the query working, but i am not sure how to reference values from the form into the query.

UPDATE Stalls SET stallholder_id = ([lst_stallholder_id])
WHERE stalls.event_id=[cbo_event_choice] And stalls.stall_number=[lst_avaliable_stalls];

The values i want to pass in are the valuse the user selects from list boxes and combo boxs on the form.

Is it possible to refernce the form that i wish to take thes values from?

eg WHERE stalls.event_id = formname.[controlname]

Or is there another way i can take multiple values from a form and update a table with them?

thanks in advance

 

[dk]

Hi,

Use this syntax to refer to your form in the SQL statement:-

[Forms]![Formname]![ControlName]

You can use the Expression Builder when designing your query and select Forms, All Forms and then choose the actual control you want.

 

[mattpfc]

This is what i have altered it too, yet it still asks for a value

UPDATE Stalls SET stallholder_id = [forms]![frmPitch_Allocation2]![lst_ID]

i am doing something wrong?

 

[Russell Hauf]

If you try to run an update query using a control on a form as the value to update a field to, every field in the table will update to the same value. The reason for this is that the value in the control won't change while the query is running. You'll need to use the query or table that the form is based on in your update query.

The reason that it was asking for a value may have been because the form was not open when you ran the query (but see what I wrote above about this).

Hope this helps,

Russell

 

[dk]

If you try to run an update query using a control on a form as the value to update a field to, every field in the table will update to the same value. The reason for this is that the value in the control won't change while the query is running. You'll need to use the query or table that the form is based on in your update query.

The reason that it was asking for a value may have been because the form was not open when you ran the query (but see what I wrote above about this).

Hope this helps,

Russell

Russell,

I've probably misunderstood what you're saying but can you expand on this? From what I understand, you're saying that you can't use an append query that refers to a control's value as it's 'update' value without overwriting all other records in the table. This is true, but that would be the case with any query - one would need to include a WHERE clause to ensure that only the desired records are updated. The WHERE clause can also refer to a control value.

 

[Russell Hauf]

What I'm saying is that if you run an update query that updates one field from a value on a form (yes, you can do this), it will update each record with the value that is currently on the form. So even if the form has 999 records, only the one showing at the time of the update will be the "update" value. So say you are updating the field "DateDelivered". If the form that you are referencing has a control (text box) named txtDate, and the value in that TextBox is 1/1/2003. Every record you choose to update (yes, you can selectively update certain records only) will have the value of 1/1/2003. And I don't think this is what the OP wanted. Rather, I think that they probably wanted to update values based on the form's underlying record source (otherwise, you could just hard-code the update value into the query). I could be wrong about the desired result here, but again - you'd only be updating TO one value if you reference a control on a form.

Hope that's clear,

Russell