2

The datasheet subform pictured below has a combo box in every cell. If you select a new value in one of the cells to the left, the cells to the right need to requery and be set to null (as the combo boxes to the right are filtered by the ones on the left). If there is only one row in the datasheet, my code works well. If there's more than one, however, my code resets all the combo boxes to the right, across all rows (which is bad). What do I need to change in my code (listed further below) to only reset the fields on the same row as the combo box being selected?

Screenshot of the sub-formed datasheet

Here's the VBA on the first combo box:

Private Sub cbo_LOA_Segment1_AfterUpdate()
' requery all subsequent LOA combo boxes to
' show only LOAs matching the selected Agency AND that match the earlier LOA entries
    With Me
        !cbo_LOA_Segment2 = Null
        !cbo_LOA_Segment2.Requery
        !cbo_LOA_Segment3 = Null
        !cbo_LOA_Segment3.Requery
        !cbo_LOA_Segment4 = Null
        !cbo_LOA_Segment4.Requery
        !cbo_LOA_Segment5 = Null
        !cbo_LOA_Segment5.Requery
        !cbo_LOA_Segment6 = Null
        !cbo_LOA_Segment6.Requery
        !cbo_LOA_Segment7 = Null
        !cbo_LOA_Segment7.Requery
        !cbo_LOA_Segment8 = Null
        !cbo_LOA_Segment8.Requery
        !cbo_LOA_Segment9 = Null
        !cbo_LOA_Segment9.Requery
        !cbo_LOA_Segment10 = Null
    End With


End Sub
David Maddox
  • 1,884
  • 3
  • 21
  • 32
  • That will happen, as the controls are the same, just with different data for each. In the past, I've used multiple rows of textboxes, mimicking a subform, and then use things like LOA_Segment2_Row1.....5 etc. – Nathan_Sav Mar 07 '16 at 15:51

1 Answers1

0

A Datasheet view is similar to a Continuous Form, in that the control only actually exists once. So, as you've noticed, with multiple records this will effect every record once the contents of the combo box are changed.

The only way around this is to have only 1 record display at a time. This may not be practical for your application, depending on how many records you have and what your editing process is. Then again, it may be very practical if you only edit specific records which can be found using a unique key, as you can filter the data quickly.

You could possibly use Nathan's suggestion above, although that would require either transposing your data (a major detriment) or a whole lot of code to first read your table into an array, write that array out to the screen and then (working backwards) update the table with one call per record. Not very efficient, but doable.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • It's not that tricky to implement my method once you know what you want horizontally, i.e. fields and how many you want to display, you can control it through an RST, the same as the subform, you could have multiple subforms and control those instead. I agree, that this would be a last step once the data/layout is finalised otherwise you start chasing your tail :) – Nathan_Sav Mar 07 '16 at 19:11
  • Hmm... both seem a little clunky, though. Can something like ActiveDatasheet help? – David Maddox Mar 07 '16 at 19:41