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