How to select the contents of different Combo boxes

[Ray C]

This may sound basic but I am a beginer. I have 4 Combo Boxes set up to make
selections on 4 different fields of a table dependong on how the user wants
to search the Table, they are called cmb_Find_Box_1 to cmb_Find_Box_4. In
order to search the table, I need to know which Fieald the user wants to
search and which Record the user wants to find.
I use the after_Update event to send a number to a proccessing routine as
follows :-
Private Sub cmb_Find_Box_1_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(1): End Sub
Private Sub cmb_Find_Box_2_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(2): End Sub
Private Sub cmb_Find_Box_3_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(3): End Sub
Private Sub cmb_Find_Box_4_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(4): End Sub
Thereby the Number of the selected Combo gets sent to the sub
"cmb_Find_Box_AfterUpdate" in the form of an Integer called "Findbox" and the
routine knows which field to search but also needs to know which record to
look for. This information is stored in the appropiate Combo box in the form
so I currently use the following to get that information into a string called
"Box" :-
If Findbox = 1 Then Box = [Cmb_Find_Box_1]
If Findbox = 2 Then Box = [cmb_Find_Box_2]
If Findbox = 3 Then Box = [cmb_Find_Box_3]
If Findbox = 4 Then Box = [cmb_Find_Box_4]
Surely there must be a way to write this in one line?
Something like Box = 'the contents of the combo identified by the number
held in the integer "FindBox"'

Could anyone help?
thanks Ray C

 

[Douglas J. Steele]

Why not pass the value rather than which combo box?

Private Sub cmb_Find_Box_1_AfterUpdate()
Call cmb_Find_Box_AfterUpdate(Me!cmb_Find_Box_1)
End Sub

Alternatively, don't pass anything, and use the ActiveControl property:

Private Sub cmb_Find_Box_1_AfterUpdate()
Call cmb_Find_Box_AfterUpdate()
End Sub

Private Sub cmb_Find_Box_AfterUpdate()
Dim Box As Variant

Box = Screen.ActiveControl
....

 

[Ray C]

Thanks so much Doug. sometimes, you jast can not see the wood for the trees
Ray C

Why not pass the value rather than which combo box?

Private Sub cmb_Find_Box_1_AfterUpdate()
Call cmb_Find_Box_AfterUpdate(Me!cmb_Find_Box_1)
End Sub

Alternatively, don't pass anything, and use the ActiveControl property:

Private Sub cmb_Find_Box_1_AfterUpdate()
Call cmb_Find_Box_AfterUpdate()
End Sub

Private Sub cmb_Find_Box_AfterUpdate()
Dim Box As Variant

Box = Screen.ActiveControl
....


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This may sound basic but I am a beginer. I have 4 Combo Boxes set up to
make
selections on 4 different fields of a table dependong on how the user
wants
to search the Table, they are called cmb_Find_Box_1 to cmb_Find_Box_4. In
order to search the table, I need to know which Fieald the user wants to
search and which Record the user wants to find.
I use the after_Update event to send a number to a proccessing routine as
follows :-
Private Sub cmb_Find_Box_1_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(1): End Sub
Private Sub cmb_Find_Box_2_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(2): End Sub
Private Sub cmb_Find_Box_3_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(3): End Sub
Private Sub cmb_Find_Box_4_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(4): End Sub
Thereby the Number of the selected Combo gets sent to the sub
"cmb_Find_Box_AfterUpdate" in the form of an Integer called "Findbox" and
the
routine knows which field to search but also needs to know which record to
look for. This information is stored in the appropiate Combo box in the
form
so I currently use the following to get that information into a string
called
"Box" :-
If Findbox = 1 Then Box = [Cmb_Find_Box_1]
If Findbox = 2 Then Box = [cmb_Find_Box_2]
If Findbox = 3 Then Box = [cmb_Find_Box_3]
If Findbox = 4 Then Box = [cmb_Find_Box_4]
Surely there must be a way to write this in one line?
Something like Box = 'the contents of the combo identified by the number
held in the integer "FindBox"'

Could anyone help?
thanks Ray C

.

 

[Ed Robichaud]

A less elegant, but simpler solution would be to use a single query as the
recordsource of your form. Use the values of the combo boxes as the
criteria for that query; that way you can use the combobox values combined,
or in isolation (IIF Forms!MyForm!cmbFindBox1="", "*")

Why not pass the value rather than which combo box?

Private Sub cmb_Find_Box_1_AfterUpdate()
Call cmb_Find_Box_AfterUpdate(Me!cmb_Find_Box_1)
End Sub

Alternatively, don't pass anything, and use the ActiveControl property:

Private Sub cmb_Find_Box_1_AfterUpdate()
Call cmb_Find_Box_AfterUpdate()
End Sub

Private Sub cmb_Find_Box_AfterUpdate()
Dim Box As Variant

Box = Screen.ActiveControl
...


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

This may sound basic but I am a beginer. I have 4 Combo Boxes set up to
make
selections on 4 different fields of a table dependong on how the user
wants
to search the Table, they are called cmb_Find_Box_1 to cmb_Find_Box_4. In
order to search the table, I need to know which Fieald the user wants to
search and which Record the user wants to find.
I use the after_Update event to send a number to a proccessing routine as
follows :-
Private Sub cmb_Find_Box_1_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(1): End Sub
Private Sub cmb_Find_Box_2_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(2): End Sub
Private Sub cmb_Find_Box_3_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(3): End Sub
Private Sub cmb_Find_Box_4_AfterUpdate(): Call
cmb_Find_Box_AfterUpdate(4): End Sub
Thereby the Number of the selected Combo gets sent to the sub
"cmb_Find_Box_AfterUpdate" in the form of an Integer called "Findbox" and
the
routine knows which field to search but also needs to know which record
to
look for. This information is stored in the appropiate Combo box in the
form
so I currently use the following to get that information into a string
called
"Box" :-
If Findbox = 1 Then Box = [Cmb_Find_Box_1]
If Findbox = 2 Then Box = [cmb_Find_Box_2]
If Findbox = 3 Then Box = [cmb_Find_Box_3]
If Findbox = 4 Then Box = [cmb_Find_Box_4]
Surely there must be a way to write this in one line?
Something like Box = 'the contents of the combo identified by the number
held in the integer "FindBox"'

Could anyone help?
thanks Ray C

 

[KenSheridan via AccessMonster.com]

I don't think its any less elegant, Ed, if anything its more so; but using
the wild-card asterisk character isn't very reliable as it would exclude any
rows where the column position in question is Null (Null doesn't match
anything, not even Null). A more reliable approach is to test for the each
parameter having the selected value or being Null, e.g.

SELECT *
FROM MyTable
WHERE (SomeField = Forms!MyForm!cmbFindBox1
OR Forms!MyForm!cmbFindBox1 IS NULL)
AND (SomeOtherField = Forms!MyForm!cmbFindBox2
OR Forms!MyForm!cmbFindBox2 IS NULL)
AND (AnotherField = Forms!MyForm!cmbFindBox3
OR Forms!MyForm!cmbFindBox3 IS NULL)
AND (YetAnotherField = Forms!MyForm!cmbFindBox4
OR Forms!MyForm!cmbFindBox4 IS NULL);

Note that each OR operation is wrapped in parentheses to force it to evaluate
independently of the AND operations. A value can be selected from any one
combo box, or any number in combination (or even from none to return all rows)
. All that's necessary is to requery the form in the AfterUpdate event
procedure of each.

Ken Sheridan
Stafford, England

A less elegant, but simpler solution would be to use a single query as the
recordsource of your form. Use the values of the combo boxes as the
criteria for that query; that way you can use the combobox values combined,
or in isolation (IIF Forms!MyForm!cmbFindBox1="", "*")

Why not pass the value rather than which combo box?

[quoted text clipped - 51 lines]