Solved Autofill Continuous Subform based on Main Form selection

[raziel3]

Hello all,

On my main form, I have a combo box for all the vendors and a PurchaseDate text box. Once a vendor is selected I want the subform to list out all the products purchased from that vendor then all I will need to do is enter the quantities.

The subform's record source contains the following fields; VendorID, PurchaseDate, Quantity, and Cost. Additionally, I would like the subform to be able to add new items for example:

Main Form
Vendor Selected: ABC Ltd
Date : 6/14/21

Subfrom

Date	ProductID	Quantity	Cost
6/14/21	Apples	10	$6.00
6/14/21	Coca Cola	2	$10.00
6/14/21	**Enter new data***

 

[theDBguy]

Sounds like a normal mainform/subform setup. What is the problem? If you link the forms properly, the only records you will see in the subform are the ones specific to the record on the main form. If you didn't disable new records, then you should be able to add new records in the subform.

 

[raziel3]

My main form and subform are linked by VendorID and PurchaseDate. I've turn off 'Data Entry' but I only get all the records relating to each by that specific date and I cannot add new purchases. I don't want to have to keep typing the products field, if a vendor supplies 10 products when I select the vendor in the main form I want those 10 products listed in the subform, and then if we are buying let's say 3 of the products this week, then we enter the quantities for just those 3 products and append it to the table before moving on to the next vendor.

 

[theDBguy]

My main form and subform are linked by VendorID and PurchaseDate. I've turn off 'Data Entry' but I only get all the records relating to each by that specific date and I cannot add new purchases. I don't want to have to keep typing the products field, if a vendor supplies 10 products when I select the vendor in the main form I want those 10 products listed in the subform, and then if we are buying let's say 3 of the products this week, then we enter the quantities for just those 3 products and append it to the table before moving on to the next vendor.

Sounds like you want a data entry form that is not directly bound to your main tables. Instead, you want to use temporary tables. Correct?

 

[raziel3]

From reading some similar posts, using temporary tables would be the best approach but most of the threads went dead before posting an actual solution.

 

[theDBguy]

From reading some similar posts, using temporary tables would be the best approach but most of the threads went dead before posting an actual solution.

Some of them will probably don't apply to your situation anyway. The best approach, I think, is for you to post a sample copy of your db, so if someone posts a solution, we know it will directly apply to your needs.

 

[raziel3]

The form that I am using is frmPurchases.

 

[arnelgp]

check and test your purchase form.

 

[raziel3]

Thank you @arnelgp that is exactly what I'm looking for. The products not purchased (ie Quantity = 0) are also being saved to the table but I think I'll be able to fix that by myself. Thanks again.

 

[arnelgp]

you can "delete" those items not needed to purchase.