Changing a private function to public

[Phil Hood]

Hello,

I wonder if anyone can help with this problem.

When I create the following event procedure, the code
works as expected i.e the attempt to update the combo box
is 'undone' and the original value is restored.

-----------------------------------------------
Private Sub Combo65_BeforeUpdate(Cancel As Integer)

Dim RetValue As Integer

RetValue = MsgBox("This employee has already been selected
for the team so cannot be added. Please select again.",
vbApplicationModal, "Warning")
If RetValue = vbOK Or vbCancel Then
Screen.ActiveControl.Undo
Cancel = True

End If
End If

End Function
-----------------------------------------------

However, when I create it as a public function (because
there are a number of combo boxes on the form and I want
to make it accessible to all), I get two errors:

1. Compile error: Variable not defined (Cancel)
2. The value in the combo box is not restored to its
orginal value

-----------------------------------------------
Public Function AddEmpeeToTeam()

Dim RetValue As Integer

RetValue = MsgBox("This employee has already been selected
for the team so cannot be added. Please select again.",
vbApplicationModal, "Warning")
If RetValue = vbOK Or vbCancel Then
Screen.ActiveControl.Undo
Cancel = True

End If
End If


End Function

 

[Douglas J. Steele]

Try changing Cancel = True to AddEmpeeToTeam = True

You'll still need to have the BeforeUpdate event for each of your
comboboxes. However, all you'll need there now will be:

Private Sub Combo65_BeforeUpdate(Cancel As Integer)

Cancel = AddEmpeeToTeam()

End Function

 

[Phil Hood]

Hi,

Thanks for the advice but I could use some clarification.

As I said in my post, the procedure works fine when it is
placed as a private (before update) function for each of
the combo boxes.

I then replaced all the individual procedures with a
single public function. The public procedure works fine
(there is much more to the procedure than I've pasted
here) with the exception of restoring back the original
value in the combo box.

To access the public function, I pasted =AddEmpeeToTeam()
into the before update line of the properties box for each
of the combo boxes.

Having done this, does it invalidate your suggestion of
keeping a before update expression for each of the check
boxes? i.e. won't the only code executed be AddEmpeeToTeam
()?

Is there a way round this?

 

[Bernie]

Whenever a FUNCTION is called, a value must be assigned
to the function. Since you want to invoke an action, a
SUB should be used. Change your code as follows:

'For FUNCTION Call

Public Function AddEmpeeToTeam()

Dim RetValue As Integer

RetValue = MsgBox("This employee has already been
selected
for the team so cannot be added. Please select again.",
vbApplicationModal, "Warning")

AddEmpeeToTeam=RetValue

End Function

'For SUB Call (If your call sends more than one
parameter, use CALL AddEmpeeToTeam when invoking the sub)

Public Sub AddEmpeeToTeam()

Dim RetValue As Integer

RetValue = MsgBox("This employee has already been
selected
for the team so cannot be added. Please select again.",
vbApplicationModal, "Warning")

If RetValue = vbOK Or RetValue=vbCancel Then

Screen.ActiveControl.Undo
Cancel = True
Else

Exit Sub

End If

End Sub

I would suggest placing this code in the ON EXIT property
of the control.

 

[Phil Hood]

Hi,

Thanks very much for this. Can I ask a couple more
questions?

I'm not sure I understand the difference between a
function and a sub? They both seem to contain a lot of the
same code? For example, the following is in both.

--------------------------------
Dim RetValue As Integer

RetValue = MsgBox("This employee has already been
selected
for the team so cannot be added. Please select again.",
vbApplicationModal, "Warning")

--------------------------------


How do they interact with each other?

Do I add both or just one?:
Public Function AddEmpeeToTeam()
Public Sub AddEmpeeToTeam()

Thanks again for your help.