Using Unbound Drop-down/Text Boxes to narrow RowSource

[Futures_Bright]

Hi all,

I'm developing a form to link a clause to one of a selection of internal documents from drop down boxes (of which there are a lot and hence looking for ways to make it more manageable to find).

Now the setup of the internal documents is unfortunately beyond my control (separate SharePoint Libraries, inconsistent fields, you name it they've picked the worst way to do it ).

I figure the best way to do this is to set up some unbound controls - two drop down (to narrow down location/department), two text boxes (doc code and name) with the idea that it will limit the selection to anything 'like' what is typed in.

What I would like is either:

• How to set the RowSource criteria to only apply if the relevant control is not blank (i.e. if all the boxes are blank, all docs will show; if 'Sales' is selected in department and the user types 'Sales Order Entry' only the Sales Documents relating to order entry will appear)

• How to add criteria to the RowSource of a dropdown box via VBA (I've created the relevant 'If then ... else' statements but not sure how to add rowsource criteria in VBA)

All help appreciated.

 

[billmeye]

Typically you would just setup the rowsource of your combobox with the filter criteria based on your form unbound controls.

For example, the criteria for the location column might be:

[Forms]![yourForm]![ctlLocation]

If you want 'Like', do:

Like "*" & [Forms]![yourForm]![ctLocation] & "*"

 

[Futures_Bright]

Wildcards either side of the controls works like a charm - I didn't know if this would work if the unbound boxes were blank (in case others are looking to use similar - me.refresh as AfterUpdate event for each of the search boxes!)