Select with mainfrm combo & position on subform

[caferacer]

Hi,

Following pr2-eugin's comments below, I have re jigged this thread to hopefully make it a bit easier to understand.

The essence of my problem is summarised in the summary directly below and the attached DB example.

Main Form (Products): The user needs to choose a Product from a combo box. The combo box lists all Products and Supplier combinations, so you may have in the combo list; ProdA-SuppA, ProdA-SuppB, ProdA-SuppC, ProdB-SuppA, ProdB-SuppB, ProdB-SuppC......,. All combinations are listed in the same combo. Once a selection is made, the main form displays the detail about the Product, with a subform displaying the details of the Supplier.

At present if you choose ProdA, the subform may display and set focus to VenA, VenB or VenC.

I have now attached a DB which illustrates the problem. Essentially if you open frmProducts and select ProductA from SupplierB in the combo, I want the active record to move to ProductA-SupplierB. At present it just remains at the ProductA-SupplierA. You have the ability to scroll back and forth in the subform to the correct Supplier, but I want this to automatically be positioned at the correct Supplier straight away, but retain the ability to scroll to the other Suppliers, therefore do not want it to be filtered.

There are a couple of websites which have helped, but haven't quite got there.


Thanks very much

__________________________________________________________



I have three find record combo boxes (third option on the wizard) on a main form (which is based on a table [Equipment]), which finds records via an unsaved query (specified in combo Row Source) based on the main underlying table [Equipment] and another table [VendorEquipmentJunction]. There is a many to many relationship between Vendor and Equipment. The use of the [VendorEquipmentJunction] table in the query enables the user to choose the Equipment from the correct vendor.

I would like to be able to select an Equipment record on the main form using one of the combo’s and then my subform [Vendor] display the vendor for the equipment chosen from the combo. At the moment on selection of an Equipment record on the main form, the subform doesn’t display the correct vendor. I can scroll back and forth in the subform to find the correct vendor for the Equipment, but need the correct one shown initially first of all. The user must still be able to scroll back and forth to view the other vendors, so it is just initial positioning at the correct record I need to happen.

Using the Access 2010 wizard has created the combos with Macros, but converting them to code came out with this;

DoCmd.SearchForRecord , "", acFirst, "[EquipmentID] = " & Str(Nz(Screen.ActiveControl, 0))

I have tried to mess about with multiple Master and Child links between the main form and subform frame, but doesn’t have any effect.

For info, I have recently solved another similar problem, but this was to open another form using a command button with OpenArgs. I know I can’t use this here, but something similar I can apply to a combo. http://www.access-programmers.co.uk/forums/showthread.php?p=1344996#post1344996.


Any ideas??

Thanks.

 

[pr2-eugin]

Is your problem dealing with Cascading comboboxes? If so try to understand these links :

http://www.fmsinc.com/MicrosoftAccess/Forms/combo-boxes/cascading.html
http://www.fontstuff.com/access/acctut10.htm
http://www.baldyweb.com/CascadingCombo.htm

 

[caferacer]

Thanks for the reply

No, not cascading.

The three combos just allow the user to select by either (1) End User Part Number, (2) System Integrators Part Number or (3) Manufacturers Part Number. So one doesn't limit the range of another, just alternative ways of selecting.

Regards

 

[pr2-eugin]

Okay, so explain in simple terms (no access jargon or terminology) explain what you want to do.

 

[caferacer]

Hi,

Main Form: The user needs to choose a piece of equipment from a combo box (100's of Equipments to choose from). The combo box lists all Equipments and Vendor combinations, so you may have in the combo list; EquipA-VenA, EquipA-VenB, EquipA-VenC, EquipB-VenA, EquipB-VenB, EquipB-VenC......,. All combinations are listed in the same combo. Once a selection is made, the main form displays the details about the Equipment, with a subform displaying the details of the vendor.

At present if you choose EquipA, the subform may display VenA, VenB or VenC. I have the ability to scroll back and forth in the subform to find the correct vendor, but want this to automatically be positioned at the correct vendor straight away.

Thanks very much

 

[caferacer]

Solved in the thread below.

http://www.access-programmers.co.uk/forums/showthread.php?t=263638