Requery a combobox in a subform

[Shecky]

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.

 

[mike60smart]

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]

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]

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]

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]

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]

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]

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:

Me.TP_Prod_Code.Requery

 

[arnelgp]

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]

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]

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]

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)

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]

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]

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]

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]

Something like this (untested)

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]

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]

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]

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]

Your current Form might be working but all we are suggesting is that it is not a very good design.