If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How to select the contents of different Combo boxes
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 |
#2
|
|||
|
|||
How to select the contents of different Combo boxes
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 http://I.Am/DougSteele (no e-mails, please!) "Ray C" wrote in message ... 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 |
#3
|
|||
|
|||
How to select the contents of different Combo boxes
Thanks so much Doug. sometimes, you jast can not see the wood for the trees
Ray C "Douglas J. Steele" wrote: 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 http://I.Am/DougSteele (no e-mails, please!) "Ray C" wrote in message ... 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 . |
#4
|
|||
|
|||
How to select the contents of different Combo boxes
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="", "*") "Douglas J. Steele" wrote in message ... 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 http://I.Am/DougSteele (no e-mails, please!) "Ray C" wrote in message ... 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 |
#5
|
|||
|
|||
How to select the contents of different Combo boxes
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 Ed Robichaud wrote: 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] Could anyone help? thanks Ray C -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...arted/200912/1 |
Thread Tools | |
Display Modes | |
|
|