If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Unbound combobox AfterUpdate
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 he 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 |
#2
|
|||
|
|||
Unbound combobox AfterUpdate
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 "Arvi Laanemets" wrote in message ... 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 he 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 |
#3
|
|||
|
|||
Unbound combobox AfterUpdate
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 "Arvi Laanemets" wrote in message ... 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 he 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 |
#4
|
|||
|
|||
Unbound combobox AfterUpdate
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 -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!) "Arvi Laanemets" wrote in message ... 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 he 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 |
#5
|
|||
|
|||
Unbound combobox AfterUpdate
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" kirjutas sõnumis news: ... 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 |
#6
|
|||
|
|||
Unbound combobox AfterUpdate
There should be no need for Nz(Me.sfObjektRuumid.Form.Filter, "")
Filter is a string value, which means its value can never be Null. -- Doug Steele, Microsoft Access MVP http://I.Am/DougSteele (no private e-mails, please) "Arvi Laanemets" wrote in message ... 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" kirjutas sõnumis news: ... 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 |
Thread Tools | |
Display Modes | |
|
|