Related Combo boxes on datasheet form

[Erika(NewUser)]

Hello,

I was hoping someone could help me. I am new to Access and have a combo box/
datasheet question. Thanks you your information, I have been able to link 3
combo boxes to each other with the selection coming from the previous combo
box. The problem is that when I go to a new row, the combo box information
for the second combo box is set to the record on the first row.

Category(CBO 1) Item (CBO2) ItemSize(CBO3)
Beams Standard Beams Size Selection1
Problem is here -> Can only select based on info from the first row.

I know I am probably missing some info so please excuse me. I would
appreciate any help.

Thanks

 

[John Spencer (MVP)]

You would need to requery the dependent comboboxes in the current event of the
form. So your VBA code would look something like the following:

Private Sub Form_Current()
Me.CBO2.Requery
Me.CBO3.Requery
End Sub

What you see on a continuous form is multiple images of the same combobox
control. So each image has the same list available to it. The requery will
update the list as you move from record to record.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Erika(NewUser)]

Thank you so much!

You would need to requery the dependent comboboxes in the current event of the
form. So your VBA code would look something like the following:

Private Sub Form_Current()
Me.CBO2.Requery
Me.CBO3.Requery
End Sub

What you see on a continuous form is multiple images of the same combobox
control. So each image has the same list available to it. The requery will
update the list as you move from record to record.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Erika(NewUser)]

I do have a another question. The dependent combo boxes are now refreshed as
I move from record to record; however, the selection I made for the previous
row is not visible. It is only visible when I click on it (the selection for
combo 1 and 3 is visible). I am trying to use this datasheet form as a form
where people would select which products they want to order.

Thank you for your help.

 

[John Spencer]

Read the last paragraph in my original reply for an explanation.

I would use this trick.

Stacking two combobox controls on top of each other. For illustration I
will call them Combobox2 and Combobox2a

ComboBox2 (top layer) would not be filtered, but would have all choices
available.
Combobox2A (bottom layer) would be filtered as you are doing now.

Use the got focus event of Combobox2 to set the focus to combobox2A.
Me.combobox2A.SetFocus

You would need to set combobox2a's tab stop property to false so it
would not be in the tab order and you would not tab into it directly.

Side effect: If you back tab into combobox2 the focus will go to
combobox2a and you may find yourself in a loop if you continue to try to
back tab.

Another option
If what you want is to display the value associated with what is in the
combobox, your best bet is to use a text box bound to the source table
you are using in the comboboxes. When the user needs to edit the
record, you might need to open another form to have an editable record.

Another option would be to not filter combobox2 and combobox3 but to
display all the options available. Use the got focus and lost focus
events of combobox2 and combobox3 to change the row sources. If you are
doing this in a continuous form you would see the comboboxes in the
other rows show blanks (in some cases) while the combobox had the focus.




'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Erika(NewUser)]

Hello John,

Thanks for all your help. What I ended up doing was creating the combo box
you suggested and changed the layout to split form so that the users could
see their selection in a locked combo box. I did have a problem with the
second dependent field not gathering the information through the requery I
had set to current so instead I just has a macro run a refresh on the after
update. This seems to be working so far and I hope I don't run into any
issues soon.

Erika