Missing step to make two Combo boxes update sourcing one table

[CTdbdev]

Hello,

I have spent two days trying to get this to work, and I'm starting to think it's not possible, but I'm a novice. It's 3am now, so I figure it's time to seek some advice. It seems any forum I see where this sort of thing is asked, is only in cases where a combo box is based on another, but in all those examples each combo box sources a different table, and it's down to the relationship between each that changes the result. Here, I'm sourcing one table, but two columns. I don't believe this is poor database design, but rather maybe my method isn't right for what I'm trying to do.
I have two unbound combo boxes in form ProductF. Both combo boxes source data from a particular column in table ProductT. Basically, the two combo boxes are the same, just that the source column is different between the two. This is so one can be used to lookup an item based on it's part number, and the other to lookup based on it's SKU. They work individually, but I just can't get them to update each other when a different item is selected in one, or vice versa, and so that they update when the bottom button to advance or go back a record is pressed. Basically, both combo boxes should display the same row data at all times, while acceptable to scroll around to find the next entry you want...

Here is what the rowsource looks like for each.

SELECT ProductsT.ID, ProductsT.PN (or .SKU for the other box) FROM ProductsT;

Both have a SearchForRecord macro of
Record: First
Where Condition

="[ID] = " & Str(Nz([Screen].[ActiveControl],0))

That's how the relevant settings sit now. I tried requery in a variety of ways (combo box and/or form, on current, on click, after update, etc), requery within the macro for the comboboxes, to no avail. The closest I got is when I make the ControlSource the ID field. Then I can at least advance records and they update themselves accordingly, but I can't scroll and select anything in the combo boxes.



I guess if we're on the subject, could I have one combo box select what column you want to search, and then in the other combo box it will only show that selected column where you can scroll/search/click within? I was initially trying to get that to work, but it seemed like I had to resort to separate column source combo boxes, as described above...

Many thanks,
Alex

[Bob Fitz]

Take a look at the attached file. I think this is what you are trying to do. Post back if you need explanation of anything or not what you want.

[CTdbdev]

Hi Bob,

Thank you for the response, and sample file! Sorry, I've just been away from this project for a few days, but yes, this is what I'm trying to do. I guess I'm a little surprised Access doesn't have this capability build into the Combo box wizard, but for software like this you can't cover all the bases. I'm studying the macro to understand this, so something new for me to learn.

Thank you very much for your help.
Alex

[Bob Fitz]

Hi Bob,

Thank you for the response, and sample file! Sorry, I've just been away from this project for a few days, but yes, this is what I'm trying to do. I guess I'm a little surprised Access doesn't have this capability build into the Combo box wizard, but for software like this you can't cover all the bases. I'm studying the macro to understand this, so something new for me to learn.

Thank you very much for your help.
Alex

OK. Post back if you have any problems understanding the code in the sample file.