Combobox Search but only partial string

[calvinle]

Hi,

My working floor is divided in many Unit, and 2 locations (Ontario & Alberta)

I have a table that contains all data of sales per department (tblSales)
[Unit] [Items] [Price]

There are many Units such as: AB, AC, A3, A5, B2, B4, B5, BG, D4, DV, W2, WE, X1, XD, XS, etc..

However, all the unit that start with W and X belongs to Ontario.
All other Unit belongs to Alberta.

On my main form, I have a subform (tblSales) that contain all data. How can I place a combobox that if I choose "Alberta", it will list all the sales for all the unit in Alberta? or if I choose "Ontario", then it will list all sales for all unit in Ontario?

Thanks

 

[Cronk]

Add a combo to your main form. Call it cboLocation. In the AfterUpdate event of the combo, add the following code

 if me.cboLocation="Ontario" then
    me.yoursubformContol.form.filter = "left(Unit,1) ='W' or left(Unit,1)='X'"
 else
    me.yoursubformContol.form.filter = "not(left(Unit,1) ='W' or left(Unit,1)='X')"
 endif
 me.yoursubformContol.form.filteron=true

NB You need to substitute yoursubformContol with the actual name of the subform control in your main form.

 

[calvinle]

That way will hardcode the W and X.

Is it possible to create another table to set the location for each first letter unit and reference it?

Example:
tblUnit
[Unit] [Location]
A - Alberta
B - Alberta
C - Alberta
W - Toronto
X - Toronto

Now, reference the filter base on the field Location of this tblUnit?
Reason for so if in case the unit change location, I can do it via tblUnit and not recode the vba.

 

[Cronk]

Yes, it is possible to use a lookup table; in fact, preferred IMO.

 

[calvinle]

Know how to implement the code in that way?

 

[Cronk]

Use a query on which to base your form's data source. Just join the Unit in the sales to the Unit in the location.

 

[calvinle]

Add a combo to your main form. Call it cboLocation. In the AfterUpdate event of the combo, add the following code

 if me.cboLocation="Ontario" then
    me.yoursubformContol.form.filter = "left(Unit,1) ='W' or left(Unit,1)='X'"
 else
    me.yoursubformContol.form.filter = "not(left(Unit,1) ='W' or left(Unit,1)='X')"
 endif
 me.yoursubformContol.form.filteron=true

NB You need to substitute yoursubformContol with the actual name of the subform control in your main form.

This works good!

However, I just Wonder instead of setting the unit W, and X into the code, can I set them in a Public Const String?

Like:
Public Const strUnitC As String = "C"

 

[Cronk]

Yes, I wonder about a lot of things too.

You could even have

Public Const strOntarioFilter ="left(Unit,1) ='W' or left(Unit,1)='X'"

 

[calvinle]

Thanks, but I found this way works too:

Left([UNIT_PERSON],1) = """ & UNIT & """