Efficient events on forms (2000/XP)

Topic

New Reply

(Same database as my earlier posting by the way)

I’ve inherited a database that needs the data entry form tidying up.

The values chosen in a number of combo boxes have a knock on effect on others, for example:

Combo box with choice or 8 values (including “none”) – When the forms opens for a new record, “none” is the default value, six other combos are disabled with their values set to “N/A”. When the combo is updated to a value other than “none” the dependent combos should be enabled (their values left at “N/A” until changed by the user)
If the combo is changed back to “none” any dependent combos should be disbaled again and any chosen values reset to “N/A”.

When the form is re-opened the dependent combos should be in the appropriate state (according to whether the parent says “none” or something else)

As you can see there will be a lot of repetitive code just for this set of controls. There are a large number of similarly dependent control groups like this so I’m wondering what is the most efficient way to do it:

case statements?
if statements?

Reply | Quote

Replies

You can often create a generalized routine to handle various cases, perhaps passing a control or control name, and/or a flag as arguments. See for example post 360863 and replies, and also your other recent thread. Depending on the exact situation, Select Case or If Then Else statements might be useful, but it’s difficult to give specific advice.

Reply | Quote

Its the passing of the control I’m not grasping.

Private Sub ChangeDependentControls(ctl As Control)

With ctl
If .Enabled = True Then
.Enabled = False
.Value = “N/A”
Else
.Enabled = True
End If
End With

End Sub

I’m trying ChangeDependentControls(cboSet2) in the calling procedure but this tries to pass the value of the control (which makes sense) but my mind has gone blank and I can’t think how to pass the control name

Reply | Quote

When you pass a control, you pass everything about it, including its value, its parent form or report, and all its properties and methods, including Name. However, when you pass a control, you don’t have to worry about the name unless you want to because your routine already knows that it’s *this* particular control that you’re currently dealing with, regardless of its name.

Reply | Quote

So should the above code have worked?

I’m getting a run time error (424 – Object required) when I use

Private Sub cboType_AfterUpdate()
ChangeDependentControls(cboSet2)
End sub

Reply | Quote

Is cboSet2 the name of a control on that particular form or subform? If so it should have worked, but you can reduce Access confusion by referencing the control as Me!cboSet2. That makes it perfectly clear that you are passing a control object. If the control isn’t on the current form or subform, you have to fully reference the control as Forms!FormName!cboSet2.

Reply | Quote

It was actually cboSet2Dip (but my typo was in the posting, not my code)

This is copied directly from vba but I still get the same error

Private Sub cboSet2Type_AfterUpdate()
ChangeDependentControls (Me!cboSet2Dip)
End Sub

Reply | Quote

Thanks Mark, I’ll try that but I’d like to know why the previous code is throwing an error.

The situation isn’t quite a straightforward as I may have suggested, and I may need to handle things differently if the dependent combos have none “N/A” values, perhaps warning the the user they are about to effectively wipe their data (not sure about this yet)

Also “N/A” is not actually on the item list and is only a valid value if the main combo if set to “None”.
This is something I am unable to change and it may have impllications in someone else’s code.

So I was thinking this

Private Sub SetComboEnabled()
cbo1.Enabled = (cboMain “none”)
cbo2.Enabled = (cboMain “none”)
‘ etc.
End Sub

Is likely to have to end up more like this

Private Sub SetComboEnabled()
If Me!cboMain = “None” Then
cbo1.Enabled = False
cbo1.Value = “N/A”
cbo2.enabled = false
cbo2.value = “N/A”
etc
Else
cbo1.Enabled = True
cbo2.enabled = True
etc
End if

which isn’t which as neat

I don’t really want to keep setting enabled to true if they are already true but haven’t figured th ebest way to deal with that either. But I think I need to get the previous value.

Reply | Quote

You could try something like this:

Private Sub SetComboEnabled()
Dim f As Boolean
Dim i As Long
f = (Me!cboMain “None”)
For i = 1 To 8
Me.Controls(“cbo” & i).Enabled = f
If f = False Then
Me.Controls(“cbo” & i).Value = “N/A”
End If
Next i
End Sub

Reply | Quote

You could try something like this:

Private Sub SetComboEnabled()
Dim f As Boolean
Dim i As Long
f = (Me!cboMain “None”)
For i = 1 To 8
Me.Controls(“cbo” & i).Enabled = f
If f = False Then
Me.Controls(“cbo” & i).Value = “N/A”
End If
Next i
End Sub

Reply | Quote

Thanks Mark, I’ll try that but I’d like to know why the previous code is throwing an error.

The situation isn’t quite a straightforward as I may have suggested, and I may need to handle things differently if the dependent combos have none “N/A” values, perhaps warning the the user they are about to effectively wipe their data (not sure about this yet)

Also “N/A” is not actually on the item list and is only a valid value if the main combo if set to “None”.
This is something I am unable to change and it may have impllications in someone else’s code.

So I was thinking this

Private Sub SetComboEnabled()
cbo1.Enabled = (cboMain “none”)
cbo2.Enabled = (cboMain “none”)
‘ etc.
End Sub

Is likely to have to end up more like this

Private Sub SetComboEnabled()
If Me!cboMain = “None” Then
cbo1.Enabled = False
cbo1.Value = “N/A”
cbo2.enabled = false
cbo2.value = “N/A”
etc
Else
cbo1.Enabled = True
cbo2.enabled = True
etc
End if

which isn’t which as neat

I don’t really want to keep setting enabled to true if they are already true but haven’t figured th ebest way to deal with that either. But I think I need to get the previous value.

Reply | Quote

The problem with

ChangeDependentControls (Me!cboSet2Dip)

is in the brackets ( ). You are calling ChangeDependentControls as a procedure, not as a function. The brackets tell VBA to evaluate the value of Me!cboSet2Dip, instead of passing it as a control object. It should work OK if you use

ChangeDependentControls Me!cboSet2Dip

or, if you prefer, call it like this:

Call ChangeDependentControls(Me!cboSet2Dip)

Reply | Quote

Hans, you beat me to it!

I just realised that and have banged my head against the wall a few times for good measure

Reply | Quote

I’m going to try to add the code I want to the main combo before update event so the user has the chance to cancel. Looking at values for combo controls I can see .value and .old value. I’ve looked at how these change and it seems that ‘old value’ doesn’t change until you save the record – is this correct?

How can I check what the previous choice was in the combo (say if the user if particulary indecisive)?

Reply | Quote

You’d have to store the selected value in a variable, for example:

Private strPreviousItem As String

Private Sub cboMain_AfterUpdate()
strPreviousItem = cboMain
End Sub

Private Sub cboMain_Enter()
strPreviousItem = cboMain
End Sub

Private Sub cboMain_BeforeUpdate(Cancel As Boolean)
If MsgBox(“Do you want to change from ” & strPreviousItem & ” to ” & cboMain & “?”, _
vbQuestion + vbYesNo, “Annoying question”) = vbNo Then
Cancel = True
cboMain.Undo
End If
End Sub

I would hate to be asked this every time!

Reply | Quote

You’d have to store the selected value in a variable, for example:

Private strPreviousItem As String

Private Sub cboMain_AfterUpdate()
strPreviousItem = cboMain
End Sub

Private Sub cboMain_Enter()
strPreviousItem = cboMain
End Sub

Private Sub cboMain_BeforeUpdate(Cancel As Boolean)
If MsgBox(“Do you want to change from ” & strPreviousItem & ” to ” & cboMain & “?”, _
vbQuestion + vbYesNo, “Annoying question”) = vbNo Then
Cancel = True
cboMain.Undo
End If
End Sub

I would hate to be asked this every time!

Reply | Quote

I’m going to try to add the code I want to the main combo before update event so the user has the chance to cancel. Looking at values for combo controls I can see .value and .old value. I’ve looked at how these change and it seems that ‘old value’ doesn’t change until you save the record – is this correct?

How can I check what the previous choice was in the combo (say if the user if particulary indecisive)?

Reply | Quote

Hans, you beat me to it!

I just realised that and have banged my head against the wall a few times for good measure

Reply | Quote

The problem with

ChangeDependentControls (Me!cboSet2Dip)

is in the brackets ( ). You are calling ChangeDependentControls as a procedure, not as a function. The brackets tell VBA to evaluate the value of Me!cboSet2Dip, instead of passing it as a control object. It should work OK if you use

ChangeDependentControls Me!cboSet2Dip

or, if you prefer, call it like this:

Call ChangeDependentControls(Me!cboSet2Dip)

Reply | Quote

It was actually cboSet2Dip (but my typo was in the posting, not my code)

This is copied directly from vba but I still get the same error

Private Sub cboSet2Type_AfterUpdate()
ChangeDependentControls (Me!cboSet2Dip)
End Sub

Reply | Quote

Is cboSet2 the name of a control on that particular form or subform? If so it should have worked, but you can reduce Access confusion by referencing the control as Me!cboSet2. That makes it perfectly clear that you are passing a control object. If the control isn’t on the current form or subform, you have to fully reference the control as Forms!FormName!cboSet2.

Reply | Quote

So should the above code have worked?

I’m getting a run time error (424 – Object required) when I use

Private Sub cboType_AfterUpdate()
ChangeDependentControls(cboSet2)
End sub

Reply | Quote

When you pass a control, you pass everything about it, including its value, its parent form or report, and all its properties and methods, including Name. However, when you pass a control, you don’t have to worry about the name unless you want to because your routine already knows that it’s *this* particular control that you’re currently dealing with, regardless of its name.

Reply | Quote

Its the passing of the control I’m not grasping.

Private Sub ChangeDependentControls(ctl As Control)

With ctl
If .Enabled = True Then
.Enabled = False
.Value = “N/A”
Else
.Enabled = True
End If
End With

End Sub

I’m trying ChangeDependentControls(cboSet2) in the calling procedure but this tries to pass the value of the control (which makes sense) but my mind has gone blank and I can’t think how to pass the control name

Reply | Quote

You can often create a generalized routine to handle various cases, perhaps passing a control or control name, and/or a flag as arguments. See for example post 360863 and replies, and also your other recent thread. Depending on the exact situation, Select Case or If Then Else statements might be useful, but it’s difficult to give specific advice.

Reply | Quote

It seems to be you are making this much harder than it is. All you need is a procedure that changes all the other combo boxes based on whether or not that main combo box has a non-“none” value. You sub would be something like this (I’m guessing at your combo box names):

Private Sub SetComboEnabled()
cbo1.Enabled = (cboMain “none”)
cbo2.Enabled = (cboMain “none”)
‘ etc.
End Sub

In your form’s current event, you call this sub. You also call it in the AfterUpdate event of cboMain. The default value for cboMain is “none”, the default value for the others is “N/A”.

Reply | Quote

It seems to be you are making this much harder than it is. All you need is a procedure that changes all the other combo boxes based on whether or not that main combo box has a non-“none” value. You sub would be something like this (I’m guessing at your combo box names):

Private Sub SetComboEnabled()
cbo1.Enabled = (cboMain “none”)
cbo2.Enabled = (cboMain “none”)
‘ etc.
End Sub

In your form’s current event, you call this sub. You also call it in the AfterUpdate event of cboMain. The default value for cboMain is “none”, the default value for the others is “N/A”.

Reply | Quote