Requery a combobox in a subform (1 Viewer)

Shecky

New member
Local time
Today, 11:52
Joined
Jul 29, 2021
Messages
25
First post! I have read similar threads with a question close to mine, yet still unable to solve this challenge :(
I have attached an image of my form.
I have a purchase order form and a PO details form within.
When the user selects a Vendor from the VendorIDcombo, the combo box in the subform only displays products that belong to that vendor.

The issue is that If the user changes the Vendor I would like the Product code combobox to Requery and only show products related to the new vendor.
I have tried combinations of onclicks events, afterupdates, setting to null etc.

The perfect outcome would be that...
All product codes in the subform would get reset to null if the Vendor is changed, but NOT if the user clicks the combo, but decides not to change the vendor.

Any help would be great.
po_form.png
 

mike60smart

Registered User.
Local time
Today, 15:52
Joined
Aug 6, 2017
Messages
1,899
Hi
If you can upload a zipped copy of the database it would make it easier to give you an example of how to do this.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:52
Joined
Oct 29, 2018
Messages
21,358
Hi. Welcome to AWF!

You could either use the AfterUpdate event of the Vendor combo to requery the Product combo or simply use the GotFocus event of the Product combo to requery itself.

Have you tried either of those?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:52
Joined
Feb 19, 2013
Messages
16,553
the other thing to consider is if you have already selected products from supplier A, then change to supplier B. You need to include a routine to delete those records for supplier A
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:52
Joined
May 7, 2009
Messages
19,169
You don't Allow changing of vendor when there is a
existing Detail (subform) records for the current vendor.

you must delete all records for this po-vendor, since
there can exists only 1 po-vendor .
and create new record for the new vendor.

if you allow change in vendor when there is already
a detail records (subform), it will make your code
much complicated.
 

Shecky

New member
Local time
Today, 11:52
Joined
Jul 29, 2021
Messages
25
Thanks for the input people.
After reading the suggestions my plans was to:
1) put a Me.Requery on the Product Code combo
2) null all the values on all product codes on the subform in the Vendor ID combo was changed (OnDirty?)

So I am stuck on step one:( The result of the Me.Requery is that the combo box does not really dropdown anymore. I am guessing the requery keeps resetting the combobox as you click on it and the box can not load the contents? Just for Clarity, the code on my TP_Prod_code combo is:

Private Sub TP_Prod_Code_GotFocus()
Me.Requery
End Sub

Hoping there is something I am missing or neglecting, as this seems simple enough.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
I think there is something else going on. Yes you should be able to requery the combo on enter or got focus. That should not cause the problem. can you post a sample.
 

theDBguy

I’m here to help
Staff member
Local time
Today, 08:52
Joined
Oct 29, 2018
Messages
21,358
Thanks for the input people.
After reading the suggestions my plans was to:
1) put a Me.Requery on the Product Code combo
2) null all the values on all product codes on the subform in the Vendor ID combo was changed (OnDirty?)

So I am stuck on step one:( The result of the Me.Requery is that the combo box does not really dropdown anymore. I am guessing the requery keeps resetting the combobox as you click on it and the box can not load the contents? Just for Clarity, the code on my TP_Prod_code combo is:

Private Sub TP_Prod_Code_GotFocus()
Me.Requery
End Sub

Hoping there is something I am missing or neglecting, as this seems simple enough.
Hi. Me.Requery affects the Form, not the Combo. Instead, try:
Code:
Me.TP_Prod_Code.Requery
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:52
Joined
May 7, 2009
Messages
19,169
now, if you continue with the "path" you want, you
need to delete the "child" (detail) records of previous
vendor, otherwise they become "orphaned".
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:52
Joined
May 7, 2009
Messages
19,169
After reading the suggestions my plans was to:
1) put a Me.Requery on the Product Code combo
2) null all the values on all product codes on the subform in the Vendor ID combo was changed (OnDirty?)
even if you Requery, when the "subform" has Master/Child Field Links and the
link is in the PO Number, it will never happen.
unless you add the VendorID to yout Link Fields.
and again, will orphan your previous details.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
Sorry for the confusion, I did not fully read it and thought you already were requerying the combo box and not the form as TheDBGuy pointed out.
Yes you should be able to requery the combo on enter or got focus. That should not cause the problem. can you post a sample.
 

Shecky

New member
Local time
Today, 11:52
Joined
Jul 29, 2021
Messages
25
Update! I bring good tidings!
DBbadguy, changed the requery as you stated and it works fine. Thanks! Step one, DONE
( I thought the "Me." would be enough to make it specify the control that was being focused upon, but I guess that is not how it works:D)

Since we never have more than 4 items on a PO (usually 1 or 2), Nulling all the Prod_Codes on a Vendor_ID change will be fine.
How would someone Null all prod_codes for that PO number, could you just specify the form.subform.combox.value ? How to affect all sub form records?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
Why would you null the Prod_Codes and not delete the record? Does not make sense to me.
You pick 10 of product A from Vendor 0001 at 15 dollars. Now you change vendor to 0002. So they do not carry product A, but you want a dummy record left showing an unspecified item from vendor 0002 at 15 dollars?
 

Shecky

New member
Local time
Today, 11:52
Joined
Jul 29, 2021
Messages
25
MajP, good point. I will delete the detail records. I was planning to use an AfterUpdate on the Vendor_ID but...
If the user simply clicks the dropdown, but doesnt change the Vendor will it trigger the event? How would I target all the records on the subform for deletion after a vendor change?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
If you are going to delete or update multiple child records for a give purchase order you would build an update or delete query for those child records with that PO number.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 11:52
Joined
May 21, 2018
Messages
8,463
Something like this (untested)

Code:
dim strSql as string
strSql = "Delete * from PO_Details Where PO_Number = '" & me.PO_Number & "'"
currentdb.execute strSql
me.subform.PO_Details.form.requery  'use name of your subform control
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 11:52
Joined
Feb 19, 2002
Messages
42,970
I don't like this design at all. I would not allow the Vendor to be changed at all once there are child records. I would make the user start again. However, if you want to delete the child records in the AfterUpdate event of the Vendor using a delete query, that's fine but be prepared to get an angry call from the users about his data being deleted because he accidentally changed the vendor. At least prompt the user and make him confirm that changing the vendor will delete the child records.

The problem might be solved with a better schema design. You would have a product table and then have a child table that contains the information for each vendor such as price. That way the subform records don't actually connect directly to the vendor, they connect to the product and requerying the subform on a vendor change would update the prices and other vendor specific info.
 

mike60smart

Registered User.
Local time
Today, 15:52
Joined
Aug 6, 2017
Messages
1,899
I agree with Pat.

A vendor has a Number of Purchase Orders and Each Purchase Order has a number of Products.

Far easier to manage.
 

Shecky

New member
Local time
Today, 11:52
Joined
Jul 29, 2021
Messages
25
Thanks for the input all. Form is working. In the end we are talking about choosing a vendor and maybe 2 products.
Theory vs practice. Normal procedure as a buyer is to, choose your vendor, then choose your products. It is the rational process.
 

mike60smart

Registered User.
Local time
Today, 15:52
Joined
Aug 6, 2017
Messages
1,899
Your current Form might be working but all we are suggesting is that it is not a very good design.
 

Users who are viewing this thread

Top Bottom