wazz
Super Moderator
- Local time
- Today, 16:50
- Joined
- Jun 29, 2004
- Messages
- 1,711
Showing initial values in cascading comboboxes
i've read several times that it is impossible to show a value in a combobox yet i've been doing it regularly without really thinking about it. (Namely, when adding a new value to a cbobox i usually open another form to add new data. when i close the second form i often make the cbobox on the calling form = the new recordID (Forms!frmCallingForm!cboBox = Me.txtIDControl). so, it is possible to show a value in a cbobox, if you know which value you want to show.
a recent thread triggered an idea as to how to do it with cascading comboboxes.
the following generic code will show the first value in every combobox in your cascade of boxes after selecting from the first. note that showing the value of a cbobox does not call the AfterUpdate event - you have to do that yourself, if you want.
you could alter the code to show every initial value when the form opens, if desired.
*new*:
* short version: assuming the id-field in a combobox matches the id-field on the same form, the code below will - when you select the combobox - automatically select the record in the *combobox* that matches the record you are currently on in the form. (see code.
)
* long version: the code above is a bit esoteric (cascading combos). i doubt many people will use it for the purpose described above, but it could be useful in other situations. i thought i would throw out an example that i started using recently.
if i use a combobox at the top of a form that will allow users to jump to a selected record, then in the afterupdate event i will make that combobox null (an idea i got from helen feddema's book, Access Application Development). this is (only) necessary if the navigation buttons are visible. you would make the combo null because if the user goes to another record with the navigation buttons, the record on the form won't match the record in the combo.
the problem (not a big problem, but a problem) then is this: if the combobox has a lot of records you then have to select the combo and then find the record you want by sliding around a lot. (one of my pet peeves with the combobox is that you can't start sliding down right away, you have to go all the way to the bottom then back up. if there is a setting to stop that, PLZ let me know).
so, what you can do is, when you go back to the combobox, have the current record (of the form) automatically selected in the combobox. of course, if you're only moving one or two records, use the nav buttons. but if you know you want to move 75 or 100 records away and you have 300 records, what will you do? with this code, if you select the combo you're automatically on the current record. then scroll away. a bit (!) wordy, i know. hope you find some use for this:
i've read several times that it is impossible to show a value in a combobox yet i've been doing it regularly without really thinking about it. (Namely, when adding a new value to a cbobox i usually open another form to add new data. when i close the second form i often make the cbobox on the calling form = the new recordID (Forms!frmCallingForm!cboBox = Me.txtIDControl). so, it is possible to show a value in a cbobox, if you know which value you want to show.
a recent thread triggered an idea as to how to do it with cascading comboboxes.
the following generic code will show the first value in every combobox in your cascade of boxes after selecting from the first. note that showing the value of a cbobox does not call the AfterUpdate event - you have to do that yourself, if you want.
you could alter the code to show every initial value when the form opens, if desired.
Code:
Private Sub cbo1_AfterUpdate()
' varID1 is skipped so the ID matches the cbo
Dim varID2 As Variant
Me.cbo2.Requery
varID2 = Me.cbo2.ItemData(0)
Me.cbo2 = varID2
Call cbo2_AfterUpdate
End Sub
Code:
Private Sub cbo2_AfterUpdate()
Me.cbo3.Requery
Dim varID3 As Variant
varID3 = Me.cbo3.ItemData(0)
Me.cbo3 = varID3
End Sub* short version: assuming the id-field in a combobox matches the id-field on the same form, the code below will - when you select the combobox - automatically select the record in the *combobox* that matches the record you are currently on in the form. (see code.
)* long version: the code above is a bit esoteric (cascading combos). i doubt many people will use it for the purpose described above, but it could be useful in other situations. i thought i would throw out an example that i started using recently.
if i use a combobox at the top of a form that will allow users to jump to a selected record, then in the afterupdate event i will make that combobox null (an idea i got from helen feddema's book, Access Application Development). this is (only) necessary if the navigation buttons are visible. you would make the combo null because if the user goes to another record with the navigation buttons, the record on the form won't match the record in the combo.
the problem (not a big problem, but a problem) then is this: if the combobox has a lot of records you then have to select the combo and then find the record you want by sliding around a lot. (one of my pet peeves with the combobox is that you can't start sliding down right away, you have to go all the way to the bottom then back up. if there is a setting to stop that, PLZ let me know).
so, what you can do is, when you go back to the combobox, have the current record (of the form) automatically selected in the combobox. of course, if you're only moving one or two records, use the nav buttons. but if you know you want to move 75 or 100 records away and you have 300 records, what will you do? with this code, if you select the combo you're automatically on the current record. then scroll away. a bit (!) wordy, i know. hope you find some use for this:
Code:
Private Sub cboYourCombobox_Enter()
Dim varFormID As Variant
varFormID = Me.txtFormID
If (varFormID & "") <> "" Then
Me.cboYourCombobox = varFormID
End If
End Sub
Last edited:
