Combo box that doesn't display values already used (1 Viewer)

chuckcoleman · Apr 22, 2019

My brain is fried and I'm drawing a blank.

I have a single form that has a subform, which is continuous. I want a combo box, (on the continuous form), query's all of the values from a table/query. After the user selects the value, like maybe "Boots", I want the AutoNumber for "Boots" stored in the underlying table for the continuous form along with the Parent forms autonumber. When the user clicks on the combo box again, I DON'T want "Boots" to show up again, because it has already been used. When the user moves to another record on the parent form, the Link Master/Link Child keeps the continuous form in sync with the parent.

I've drawn a blank on making sure that "Boots" in my question doesn't show up again for that "Parent".

June7 · Apr 22, 2019

Combobox RowSource has to be a 'find unmatched' query that joins the data table with the lookup table. And also combobox needs to be 'conditional' based on the 'Parent' ID. This is a 'cascading' combobox. Code has to requery the combobox after record committed.

theDBguy · Apr 22, 2019

Hi. Unfortunately, one side effect of "hiding/eliminating" already chosen items from the combo dropdown is if you go back to all the previous records in the subform, you could potentially see the combobox as "empty" even though there is data in the table. The reason being is because the data in the table is excluded from the combo's row source at this point.

MajP · Apr 22, 2019

To avoid what theDBGuy is stating see the below thread and example DB. It does what you are asking. It removes selected serial numbers.

https://www.access-programmers.co.uk/forums/showpost.php?p=1607704&postcount=21

chuckcoleman · Apr 23, 2019

Thank you all for replying. I downloaded the database that MajP offered and while the code was relatively short, it's not trivial. After spending time "translating" it to my project, I was able to get it to work. Thank you all!

theDBguy · Apr 23, 2019

chuckcoleman said:
Thank you all for replying. I downloaded the database that MajP offered and while the code was relatively short, it's not trivial. After spending time "translating" it to my project, I was able to get it to work. Thank you all!

Hi. Congratulations! Glad to hear you got it to work. Good luck with your project.

MajP · Apr 23, 2019

The code provided by CJ_London works very well in datasheet view. In continuous form view it can be OK but a little quirky and things may momentarily disappear. The "fake" combo box underneath a textbox works better in continuous form view.

Uncle Gizmo · Apr 23, 2019

An alternative approach is to have a pop-up form, triggered when the combo box is pressed. Code in the pop-up, to restrict the options it shows depending on which row it's called from.

Sent from my SM-G925F using Tapatalk

theDBguy · Apr 23, 2019

Uncle Gizmo said:
An alternative approach is to have a pop-up form, triggered when the combo box is pressed. Code in the pop-up, to restrict the options it shows depending on which row it's called from.

Sent from my SM-G925F using Tapatalk

And the one I tend to prefer is to "flag" (with an asterisk or something) the invalid options and sort them to the bottom of the list. Then, add a validation check in the BeforeUpdate event of the combo to not allow the user from selecting the invalid items. To each his/her own... Cheers!

Combo box that doesn't display values already used (1 Viewer)

chuckcoleman

Registered User.

June7

AWF VIP

theDBguy

I’m here to help

MajP

You've got your good things, and you've got mine.

chuckcoleman

Registered User.

theDBguy

I’m here to help

MajP

You've got your good things, and you've got mine.

Uncle Gizmo

Nifty Access Guy

theDBguy

I’m here to help

Similar threads

Users who are viewing this thread