Autofeed field in subform based on combobox from parent form. (1 Viewer)

Leo_Polla_Psemata

Registered User.
Local time
Today, 06:57
Joined
Mar 24, 2014
Messages
364
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

AWF VIP
Local time
Today, 05:57
Joined
Mar 9, 2014
Messages
5,423
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

AWF VIP
Local time
Today, 06:57
Joined
Apr 13, 2010
Messages
1,401
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

Registered User.
Local time
Today, 06:57
Joined
Mar 24, 2014
Messages
364
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

Registered User.
Local time
Today, 06:57
Joined
Mar 24, 2014
Messages
364
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

Member
Local time
Today, 16:57
Joined
Jun 26, 2022
Messages
243
Use Dlookup function on field2. It will give you the result what you have required.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:57
Joined
Feb 19, 2002
Messages
42,970
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.
 

Leo_Polla_Psemata

Registered User.
Local time
Today, 06:57
Joined
Mar 24, 2014
Messages
364
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:57
Joined
Feb 19, 2002
Messages
42,970
It seems that you don't understand how tables are related. There is only ONE relationship between tbl1 and tbl2 and it is accomplished by placing the PK of tbl1 into a field we refer to as the FK in tbl2. Just because two tables contain duplicate data doesn't mean they are related. What it means is that your schema is not properly normalized. Each piece of data is defined in one and only one table. The reason you don't need to duplicate the customer name in the order table is because the order table should contain a FK that contains the CustomerID and that points to a unique customer record where you can obtain the customer's name whenever you need it.

Newcomers don't intuitively understand this and frequently make the mistake of duplicating data because they don't know any other solution.

When the majority of "child" records will have ValueX in a particular field but some will differ, then the mistake is what you are currently doing. If the default is general and no matter what the parent is, the default is always ValueX, then, make that the default at the table level. If the default is somehow tied to the parent record, then add a field to the parent record to hold "ValueX". Then in the BeforeInsert event of the child record, copy the value from the parent record to the child record. If you later want to change the default in the parent record to ValueZ, that doesn't affect any child records. New records get "ValueZ" in the BeforeInsert event. Existing records don't change. If you want to change existing records, you need to run an update query that changes "ValueX" to "ValueZ" for all child records with the FK equal to the parent record. In all other cases, the value changes manually one record at a time by the user.

If you want to filter the subform, add an unbound combo that selects the list of values. When you choose "ValueZ" from the combo, only the "ValueZ" records will show in the subform. Is that what you are looking for?

If you would be specific about what data we are talking about and the business process you are modelling, we can probably give better advice. Currently, I question the logic of what you are doing because I can't envision the business process you are modelling. Labeling things fld1 and fld2 is absolutely useless.
 

Users who are viewing this thread

Top Bottom