combobox rowsource per row on a subform

[kowalski]

I've go a subform with 2 comboboxes. It's a datasheet view.


The combobox controls are called cmb1 and cmb2 for the sake of this...

cmb2 is dependent on cmb1. So when you select a certain value in cmb1, cmb2.rowsource should get updated to use a certain table.

But the kicker is that this should be on a per row basis. So if on row 1 of the subform datasheet you set cmb1=Manager, cmb2.RowSource should get set to 'tblManagers'.
Then on the next row, cmb1 is set to Directors, cmb2.RowSource should get set to tblDirectors.
But the cmb2.rowsource on row 1 is still set to tblManagers.

Is this possible????

Thanks!!!

[Rod]

If the comboboxes are unbound - which I assume they are - then this is normal behaviour for datasheets, continuous forms and the like. If you set an unbound control to a value, then all records/rows in the list have that value in that control.

What you want to do may be quite complex and it sounds suspiciously like your db is not in normal form. I can't for the moment think of a straightforward way to achieve your requirement. Thinking cap is on; I'll get back to you. Meanwhile tell us more about the comboboxes, particularly the first one.

[Rod]

Cmb2 must be bound. It looks better if Cmb1 is also bound. Do not 'Limit to list' on Cmb2

Option Compare Database
Option Explicit

Private Sub Cmb1_BeforeUpdate(Cancel As Integer)
    Select Case Nz(Me.Cmb1, "")
    Case "A"
        Me.Cmb2.RowSource = "whatever"
    Case "B"
        Me.Cmb2.RowSource = "something else"
    End Select
    
End Sub

Private Sub Form_Current()
    Dim i As Integer
    Cmb1_BeforeUpdate i
    
End Sub

Not as fearsome as I thought.