Auto Populate Sub Form fields based on Combo box on Main Form

[Topflite66]

I have a form that has a sub-form that contains 4 fields, a combo box (Model), 2 text boxes(Allocation & Allocation Used), and a calculated field (Remaining Allocation). The Model information changes based on the selection made in the combo box (Model Year) on the main form. The sub-form is in datasheet view. Currently I need to manually select each item in the list for the Model, make adjustments to the Allocation and Allocation Used fields and then go to the next row and do the same. Is there a way to have all of the available Models automatically populate in the sub form when I select the the Model year on the main form? Then I could just go down the list and adjust the Allocation and Allocation Used fields as necessary. I need to have the Model Year and Model combo boxes dynamic as the Models can change each year. Thank you

[ranman256]

bind the subform properties to the combo boxes:
LINK MASTER FIELDS: cboModel, txtAlloc, txtAllocUsed , txtRemCalc (what they're named in the master form)

LINK CHILD FIELDS: cboModel, txtAlloc, txtAllocUsed , txtRemCalc

(what they're named in the child form)

this will default all entries with these fields values.

[June7]

Controls used to enter filter criteria should be UNBOUND, otherwise you change data in record.

I think should be saving jct_Model Model_JctID into jct_WholesaleOrders.

If you want all model/year combinations available in subform, this means 'batch' creating records for each OrderID. Then filling in the allocation data after records are created. Use an INSERT SELECT action SQL. In VBA like:

CurrentDb.Execute "INSERT INTO jct_WholesaleOrders(OrderID, ModelID) SELECT " & Me.OrderID & ", Model_JctID FROM jct_Model"

Then refresh or requery the subform to retrieve records.

To prevent duplicate records in jct_WholesaleOrders, set compound index on OrderID and ModelID

[Topflite66]

bind the subform properties to the combo boxes:
LINK MASTER FIELDS: cboModel, txtAlloc, txtAllocUsed , txtRemCalc (what they're named in the master form)

LINK CHILD FIELDS: cboModel, txtAlloc, txtAllocUsed , txtRemCalc

(what they're named in the child form)

Thank you for your response. Previously I had the Link Master and Child fields set to OrderID. I changed both the Master and Child Links to what you suggested, however when I open the form I receive an Enter Parameter Value message box for each field. If I hit Cancel the form will open but the subform is a blank box. Not sure how to correct this. Thank you.

[June7]

Did you see post #3?

[Topflite66]

Did you see post #3?

Your post appeared after I posted my response. Timing is everything!! LOL I will give that a shot. Thank you.

[Topflite66]

Controls used to enter filter criteria should be UNBOUND, otherwise you change data in record.

I think should be saving jct_Model Model_JctID into jct_WholesaleOrders.

If you want all model/year combinations available in subform, this means 'batch' creating records for each OrderID. Then filling in the allocation data after records are created. Use an INSERT SELECT action SQL. In VBA like:

CurrentDb.Execute "INSERT INTO jct_WholesaleOrders(OrderID, ModelID) SELECT " & Me.OrderID & ", Model_JctID FROM jct_Model"

Sorry, but I'm a little confused. So I shouldn't have the fields Allocation and AllocationUsed in the jct_WholesaleOrders table? Also, I'm not understanding the jct_Model Model_JctID.

The Model combobox in subform is pulling the data entries from qry_WSModel which contains tbl_MdlYr, tbl_Model, and jct_Model.

Sorry for being so dense. Thank you.

[June7]

I did not say should not have Allocation and AllocationUsed fields in table.

Don't you want allocation assigned to model/year combination? This would require saving Model_JctID value into jct_WholesaleOrders. Otherwise, what purpose does jct_Model table serve?

Combobox RowSource:

SELECT jct_Model.Model_JctID, tbl_Model.Model, tbl_MdlYr.MdlYr
FROM tbl_MdlYr RIGHT JOIN (tbl_Model RIGHT JOIN jct_Model ON tbl_Model.ModelID = jct_Model.ModelID) ON tbl_MdlYr.MdlYrID = jct_Model.MdlYrID;


Means I think your relationships are set up wrong. As is, there is no way to determine which year allocations belong to. Assuming database will eventually be multi-year.

[Topflite66]

I did not say should not have Allocation and AllocationUsed fields in table.

Don't you want allocation assigned to model/year combination? This would require saving Model_JctID value into jct_WholesaleOrders. Otherwise, what purpose does jct_Model table serve?

Combobox RowSource:

SELECT jct_Model.Model_JctID, tbl_Model.Model, tbl_MdlYr.MdlYr
FROM tbl_MdlYr RIGHT JOIN (tbl_Model RIGHT JOIN jct_Model ON tbl_Model.ModelID = jct_Model.ModelID) ON tbl_MdlYr.MdlYrID = jct_Model.MdlYrID;


Means I think your relationships are set up wrong. As is, there is no way to determine which year allocations belong to. Assuming database will eventually be multi-year.

Sorry I misunderstood. When you said that the controls used to enter filter criteria should be UNBOUND, i thought you were talking about the Allocation and AllocationUsed fields.

As for the jct_Model table, I have that set up for a different portion of the db. That is a storage table for when I enter the models for each year. This is only a portion of my db that I made so that I could try to incorporate the wholesale orders into my main db. The main db is pretty large so I wasn't able to upload it because of the file size limitation.

I may have wholesale entries for different model years for different models, but I never have multiple years of the same Model at the same time. For example I may have wholesale entry for 2019 F-150 and 2020 Super Duty at the same time but never 2019 F-150 and 2020 F-150. So my thought was to enter everything for the 2019 model year and then have an entry for the 2020 model year. I have a report that I will be using to keep track of the Remaining Allocation. So the form I have works fine now, however I was just hoping to have it display all of the available models when I select the model year. That way I don't have to manually enter them. I hope this clears up any confusion.

[June7]

Then modify the suggested INSERT SELECT action SQL as you wish.