flexibility in searching by adding “search by†combo box

[Masoud]

Hello,
In my unbound form there are some text boxes and combo boxes, that i can
type in them (text boxes) or select item from combo boxes and then by this
criteria, i can search in my table. So every of these text boxes and com
boxes have referred to one field in the table and searching is very smooth.
Now i like to delete some of these controls and replace them with 1 or 2
(search by combo box), i mean in the “search by†combo box i have selection
for my favourite in searching. For example before i had 4 combo boxes
separately for
1-unit
2-discpline
3-type
4-vendor
Now i want to delete them and replace with 2 “search by combo box†that
record source of these combos is “unitâ€, “disciplineâ€, “typeâ€, and vendor, so
i can select in the first “search by†one of these items and then in the next
combo in front of this i can select appropriate unit. And also in the the
second “search by combo box†i can select for example discipline and in the
combo box in front of that, i select appropriate discipline. Please help me
how can i do it? My code event in searching how should be?
What change in structure and coding of my unbound form i have to do for this
flexibility?
Thanks in advanced.

 

[vanderghast]

The first combo box list is filled with four constants strings, but the
second combo box LIST has to change. On the other hand, that list is not
visible until the second combo box go the focus, so, using the GotFocus
event handler of this second combo box:


Dim str As string

str= "SELECT numberID FROM " _
& Switch(Combo1="Unit", "UnitsTable", _
Combo1="discpline", "DisciplineTable", _
Combo1="type", "TypesTable" , _
Combo1="vendor", "VendorsTable", _
true, "defaultTableToBeUsed") _
& " ORDER BY numberID ASC " ' see note 2

With Me.Combobox2
If .RowSource = str Then ' see note 1
'do nothing, already the good list
else
.RowSource = str
end if
End With


-- Note 1: Specifying the RowSource forces a read from the database, a
'refresh', but if that refresh is not necessary, as I assumed it is, we can
avoid it, as I did in the example, testing for it SQL statement, before
'executing' it by an assignation.

-- Note 2: I don't like the SWITCH statement, here. I would rather use a
TABLE, even if it is a very small one:

tableToBeNamed
Option OpenTable ' fields
Unit UnitsTable
Discipline DisciplinesTable
... ... ' data


and I would use a DLookup("OpenTable", "tableToBeNamed",
"Option=FORMS!FormName!Combobox1" )

instead. Why? because that is DATA and a kind of data which may eventually
change. Having to touch CODE to change DATA is not a very good sign that the
code is robust. After all, anyone can change data in a table, but if that
anyone has to open YOUR code to modify it... Sure, if you will be the only
person ever using your application, that is not a problem, but if you ever
distribute it at large, have data in a table is much more preferable.


Vanderghast, Access MVP