Autofeed field in subform based on combobox from parent form.

[Leo_Polla_Psemata]

Hi
In the example, one simple form-subform one to many
I would like, once i update the combobox on form, to auto fill the field fld2 in subform.

I can link parent to child but i don't want this method because i may need to change the combo to another value and then all records on subform will disapear.
I can make one update query on after update event.

Is there any other method for this simple task ?
Subform may have 1000 records

 

[June7]

Form/subform linking should be dependent on primary/foreign keys, not some other fields. Why would changing a value on main form field2 affect records displayed in subform? Something wrong with your design.

 

[bastanu]

Agree with June7, I think you're oversimplifying your question; if you want to change the values in fld2 in all records of the subform every time you change the combo why bother to store it anyway? Just bind the textbox on the subform to the combo on the main form.

Cheers,

 

[Leo_Polla_Psemata]

Agree with June7, I think you're oversimplifying your question; if you want to change the values in fld2 in all records of the subform every time you change the combo why bother to store it anyway? Just bind the textbox on the subform to the combo on the main form.

Cheers,

Because i may still have to change the value in few records.

 

[Leo_Polla_Psemata]

Form/subform linking should be dependent on primary/foreign keys, not some other fields. Why would changing a value on main form field2 affect records displayed in subform? Something wrong with your design.

Yes but design allow to link more than one field, so, i can link the primary to foreign key and the field2 to fld2.
However, IF i change the field2 the records on subform will disappear because of link.
Anyway, i create an update query, my question is if there is any other method

 

[ahmad_rmh]

Use Dlookup function on field2. It will give you the result what you have required.

 

[Leo_Polla_Psemata]

If fld2 is the glue that binds the two tables, then of course if you change the value, you orphan the records.

You need to fix your schema to have a proper relationship that won't be affected by changing a combo. If you post it, we will help you.

Using a dlookup() from the subform to the main form will always get the current value of the field in the main form which is the same effect has changing a field in the main form to update all child records. It also makes no sense at all to store a field in a child table that will always be the same value. That only happens with a Foreign key and the FK in the subform points to the PK in the main form, not some random data field.

Hi,
The tables behind form subform are linked through id and fld0, not appeared on the image.
User can relate form subform with a second pair of fields, such as field2 to fld2 on top of id-fld0
but IF i change the field2 then we get "orphan" records that disappear.

Dlookup from subform, again, i may have 5000 records and may need to change the value in just 5 records out of 5000 toanothe value.
I may need to upload those 5000 records today but don't know yet the value of fld2/field2 which i might find tomorrow, so
the most workable would be , type the value and update immediately ALL fields, then find manually the exceptions and type manually the value.

Now, i do this using an update query,however, i was thinking IF there is any easier method.