-----Original Message-----
In that case, just change the Row Source of the other 3 combo boxes. Place a
SQL statement in the Row Source similar to
strSQL = "SELECT [Field2] FROM [Table1] WHERE [Field1]=" & Me.cboCombo1
Me.cboCombo2.RowSource = strSQL
You would need one event to set all four combos to their default Row Source
until a selection is made in one of them. Once this is done, the AfterUpdate
event of the combo that the selection was made in will need to reset the
RowSource of the other three combos. Changing the RowSource will
automatically cause the other combos to requery. Assuming that there is only
one value left that is going to be returned though, you shouldn't need a
combo for the other three once a selection has been made. If you want the
item in the first row to be shown in the textbox part of the combo box, then
after you've set the RowSource, issue the following command:
Me.cboCombo2 = Me.cboCombo2.Column(0,0)
adjust the column index appropriately for the bound column. As mentioned
before, both of the index numbers are zero based. The first number is the
column number and the second is the row number.
Another option would be to leave the Row Source of the combo boxes alone and
hide the other 3 combo boxes and unhide 3 textboxes that have been placed in
the same location on the form. You would then use your DLookup statement to
assign a value to each of these textboxes.
Me.cboCombo2.Visible = False
Me.txtText2.Visible = True
Me.txtText2 = DLookup(....etc.)
Since the other three are for display only after a selection has been made,
you will probably want to lock the other three controls so that the user
can't make a selection in them. You would unlock them again in the same
event that you reset the RowSource back to its default.
Me.cboCombo2.Locked = True
--
Wayne Morgan
MS Access MVP
Each one of the four combos are used to search in some
field of the table. When a match is made the fields of
the form are updated. The " filter " in the code updates
the bound fields of the form. Because the combos are
unbound the unused combos (in the search)have to be be
updated "manually". I have looked at the Trade Winds (in
particular the invoices) example. Since I have four
combos this makes things a little difficult. Your idea of
having one combo with the values of the other combos in
the drop down list will not work for me. Therefore, when
a selection is made on one of the combos, part of the
form is updated by the "filter" part and the combos
updated by "DLookup" part. The other option is to hard
wire my procedure for each case. This I do not want to do
as seek a more economical way of updating the combos.
Besides this will give me more insight to the workings of
access. Experiment - the key to understanding.
Thanks for the reply
-----Original Message-----
I don't fully follow. If all of the combos are getting their value from the
same table, then the relationship between the combos is probably one-to-one.
If that is the case, it may be easier to just have one combo box with 4
columns. You can have all 4 columns display when you open the drop down, but
only the first visible column will display in the textbox portion of the
combo box. To have the other 3 columns display when the drop down is closed,
place 3 textboxes next to the combo box, set their Locked property to Yes,
and set the control source to the appropriate column of the combo box.
Example:
=cboMyCombo.Column(1)
The index number for the Column property is zero based, so 0 is the first
column, 1 is the second, 2 is the third, and 3 is the fourth.
--
Wayne Morgan
MS Access MVP
Please help. I am fairly new to databases and access.
I have a form with four combo boxes ( Combo1, Combo2,
Combo3, Combo4) amongst other controls. These combos are
unbound., but access data from a single table (and so do
the other controls on this form). When a selection is
made in Combo1, I update Combo2, Combo3 and Combo4 using
values from my table. If a selection is made on Combo2,
then 1,3,4 are updated in the same way. My problem is
that I need to create a procedure that will allow me to
update any three combos using the one combo as selection.
The procedure looks as follows
Private Sub LoadRecord(Dim ComboCtrl As Object)
On Error Resume Next
ComboCtrl.SetFocus
If ComboCtrl.Column(0) > 0 Then
Me.Filter = "[Field1]=" & ComboCtrl.Column(0)
Me.FilterOn = True
End If
Me.Combo2 = DLookup
("[Field2]", "Tbl_1", "[Field1]
="
& ComboCtrl.Column(0))
Me.Combo3 = DLookup
("[Field3]", "Tbl_1", "[Field1]
="
& ComboCtrl.Column(0))
Me.Combo4 = DLookup
("[Field4]", "Tbl_1", "[Field1]
="
& ComboCtrl.Column(0))
End Sub
I dont know how to pass the combo control to the
procedure and what data type to use for the combo control
Thanks in anticipation
.
.
