Unbound combobox AfterUpdate

[Arvi Laanemets]

Hi

On some form I have several unbound combobox controls. Whenever some of them
is changed, I want:
1. reset the values for other combos on same form;
2. reset Filter and FilterOn properties for subform on same form;
3. reset Rowsource for a record locating combobox on this subform.

Currently I use combo's AfterUpdate's events for this - like here:

Private Sub Combo1_AfterUpdate()
Me.Combo2 = 0
Me.Combo3 = 0
If Nz(Me.Combo1, 0) = 0 Then
Me.MySubform.Form.Filter = ""
Me.MySubform.Form.FilterOn = False
Me.MySubform.Form.SubformCombo.RowSource= "SELECT MyTable.ID,
MyTable.SomeText FROM MyTable ORDER BY 2;"
Else
Me.MySubform.Form.Filter = "ID1 = " & Me.Combo1
Me.MySubform.Form.FilterOn = True
Me.MySubform.Form.SubformCombo.RowSource = "SELECT MyTable.ID,
MyTable.SomeText FROM MyTable WHERE MyTable.ID1 = " & Me.Combo1 & " ORDER
BY 2;"
' different combos set WHERE condition to different ID-field
here
End If
End Sub


The problem is, those events are fired whenever something on combo is
selected - even when the combos value really remains same.

I want, that event will be fired only, when some combo's value was really
changed, and I can't use OldValue property here, as this works only with
bound controls (for unbound controls it is always same as controls current
value). So I need something along lines:

Private Sub Combo1_AfterUpdate()
If Combo1 was changed Then
' Code above will be inserted here
End If
End Sub


Some ideas anyone?
Thanks in advance!
Arvi Laanemets

 

[Jeanette Cunningham]

Have you looked into using a search form instead of trying the difficult
task of keeping the combos updated every time one of them is changed.
It would be much easier and less time consuming for you.
Here is a link to a sample
http://allenbrowne.com/ser-62.html



Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Jon Lewis]

Other than what Jeanette said you could try storing the values of the combos
in Module level variables and comparing these values in the combo after
update events. I've assumed the combos values are long integers.

So in the Declarations section of the Form module:
Dim mlngCbo1 As Long, mlngCbo2 As Long etc

If the combos have default values when the form opens then set these
variables in the Form's Load event:
mlngCbo1 = Whatever etc.

Then in the combos' After Update events compare the module level variables
with the combo values and update as necessary

Jon

 

[Douglas J. Steele]

What about

Private Sub Combo1_AfterUpdate()
If (Nz(Me.Combo1, 0) = 0 And Me.MySubform.Form.FilterOn = True) _
OR (Me.MySubform.Form.Filter <> "ID1 = " & Me.Combo1) Then
' You need a new filter
Me.Combo2 = 0
Me.Combo3 = 0
If Nz(Me.Combo1, 0) = 0 Then
Me.MySubform.Form.Filter = ""
Me.MySubform.Form.FilterOn = False
Me.MySubform.Form.SubformCombo.RowSource= _
"SELECT ID, SomeText " & _
"FROM MyTable ORDER BY 2;"
Else
Me.MySubform.Form.Filter = "ID1 = " & Me.Combo1
Me.MySubform.Form.FilterOn = True
Me.MySubform.Form.SubformCombo.RowSource = _
"SELECT ID, SomeText FROM MyTable " & _
"WHERE ID1 = " & Me.Combo1 & " ORDER BY 2;"
End If
End If
End Sub

 

[Arvi Laanemets]

Thanks! And this info was there all the time - directly under my nose ))

I modified your approach a little, and now it is :

Private Sub Combo1_AfterUpdate()
If Nz(Me.Combo1, 0) = 0 _
And Nz(Me.sfObjektRuumid.Form.Filter, "") <> "" _
And Left(Nz(Me.sfObjektRuumid.Form.Filter, ""), 3) =
"ID1" Then
' There was a filter based on this control earlier -
' so the control was not zero - and the and it was set to zero,
' ergo the filter has to be removed.
Me.Combo2= 0
Me.Combo3= 0
Me.MySubform.Form.Filter = ""
Me.MySubform.Form.FilterOn = False
Me.MySubform.Form.SubformCombo.RowSource = _
"SELECT ID, SomeText FROM MyTable ORDER BY 2;"
ElseIf Nz(Me.Combo1, 0) <> 0 _
And Nz(Me.sfObjektRuumid.Form.Filter, "") <> "ID1 = "
& Me.Combo1Then
' The control was zero or it had previous value different from
current one,
' and a non-zero value was selected - so a (new) filter is applied.
Me.Combo2= 0
Me.Combo3= 0
Me.MySubform.Form.Filter = "ID1 = " & Me.Combo1
Me.MySubform.Form.FilterOn = True
Me.MySubform.Form.SubformCombo.RowSource = _
"SELECT MyTable.ID, MyTable.SomeText FROM MyTable
WHERE MyTable.ID1 = " _
& Me.Combo1 & " ORDER BY 2;"
End If
' Any remaining contitions mean not covered above indicate,
' that control value was not changed - so no action is taken.
End Sub


Arvi Laanemets

 

[Douglas J. Steele]

There should be no need for Nz(Me.sfObjektRuumid.Form.Filter, "")

Filter is a string value, which means its value can never be Null.