Combo box to change values in another table

[mdmarney]

Hi. I am having difficulty with figuring out how to do the following:
I have a bound combo box (or say a check box for that matter) in a form. When I change the value of this firld in the form, I need a corresponding field in the subform to update to the same value as what was set in the form. It needs to update all corresponding in the subform (not just those displayed, but any tied to the record in the main form). Make sense? Hope someone can help. Thanks! *************
M. MARNEY

http://www.tgw.com

 

[gusbrunston]

[tt]
Hi Marney:

I'm sure that someone can help you. It would help to know a couple of things:

Since the form/subform construct is designed for tables in a one-to-many relationship, is your main form bound to the "one" table (or query), and the sub form bound to the "many" table?

You need to change all the records in the "many" table to the value of one (changed) field in the "one" table; is that right?

If that's all true, then it seems to me that you need some code in the after update event of the combo box on the main form to iterate through the records of the "many" table and update the pertinent field. Does the field in the "many" table match (in type) the related field in the "one" table?

Are all the records you want to update in the same table? (The "one" table could have a similar relationship to other "many" tables. Do those have to be updated also?)
[tt] Gus Brunston - Access2000(DAO)[/tt] Intermediate skills.

 

[chrissie1]

in the afterupdate of that field i would try something like this

docmd.runsql "UPDATE table SET field = '" & value & "' WHERE field2 = '" & expression & "'"
me.requery Christiaan Baes
Belgium
"What a wonderfull world" - Louis armstrong

 

[mdmarney]

Chrissie1

DoCmd.RunSQL "UPDATE tbl_attendants SET Arrival_Verification = '" &Yes& "' WHERE Me.Parish_Group_Arrival = '"&Yes&"'"

Receiving syntax error.
Help... *************
M. MARNEY

http://www.tgw.com

 

[mdmarney]

swamp - yes they are...

the following worked, but with a few complications:
DoCmd.RunSQL "UPDATE tbl_attendants SET Arrival_Verification = TRUE WHERE Parish_Group_Arrival = TRUE"

1) I wants to update the whole table. I need to add something to the where clause to indicate the link between the form and subform. (i.e. Where Parish_Group_ID = " & Me![Parish_Group_ID]) How do I do this?

2) Is there anyway to remove the Update Table verification screen. The code errors out if you hit NO on that screen. I'd prefer to either give a custom dialog or no option at all. Ideas?

Thanks guys!

*************
M. MARNEY

http://www.tgw.com