Solved Autofill Continuous Subform based on Main Form selection (1 Viewer)

raziel3

Registered User.
Local time
Today, 09:01
Joined
Oct 5, 2017
Messages
273
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
DateProductIDQuantityCost
6/14/21Apples10$6.00
6/14/21Coca Cola2$10.00
6/14/21**Enter new data***
 

theDBguy

I’m here to help
Staff member
Local time
Today, 06:01
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 09:01
Joined
Oct 5, 2017
Messages
273
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

I’m here to help
Staff member
Local time
Today, 06:01
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 09:01
Joined
Oct 5, 2017
Messages
273
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

I’m here to help
Staff member
Local time
Today, 06:01
Joined
Oct 29, 2018
Messages
21,358
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

Registered User.
Local time
Today, 09:01
Joined
Oct 5, 2017
Messages
273
The form that I am using is frmPurchases.
 

Attachments

  • Test.zip
    982.4 KB · Views: 296

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:01
Joined
May 7, 2009
Messages
19,175
check and test your purchase form.
 

Attachments

  • Test (1).zip
    1 MB · Views: 550

raziel3

Registered User.
Local time
Today, 09:01
Joined
Oct 5, 2017
Messages
273
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

..forever waiting... waiting for jellybean!
Local time
Today, 21:01
Joined
May 7, 2009
Messages
19,175
you can "delete" those items not needed to purchase.
 

Users who are viewing this thread

Top Bottom