problem passing current form to function

[abby]

I am trying to create a generic function for use in the before update event
of forms to check whether a user has made any changes before allowing them to
move to another record, or between subforms / main forms.

The code I have is as follows:

Public Function save_prompt(formObj As Form)

If SavePromptOn = True And formObj.Dirty = True Then
result = Msgbox("would you like to save your changes? Changes must
be either " & _
"saved or discarded before moving between the main form and a subform,
and " & _
"before closing, or moving to a different counterparty record.",
vbYesNoCancel)
If (result = 7) Then
Form_fcounterparty.Undo
Else
If (result = 2) Then
Cancel = True
End If
End If
End If
End Function

When I try to call this function in the before update event of my subform
using ‘save_prompt (Form)’ I get a type mismatch error (runtime error ‘13’).
I have also tried ‘save_prompt (Me)’, and ‘save_prompt (form_fcounterparty)’
but had the same problem.
I have also tried modifying the function so that it does not take a parameter,
but just calls the current form however was unable to get this to work. I
tried two different ways. The first way is:

Public Function save_prompt()

If SavePromptOn = True And me.Dirty = True Then
… as per above

I get a compile error: invalid use of me keyword. The other was I have tried
is:

Public Function save_prompt()

If SavePromptOn = True And screen.activeform.Dirty = True Then
… as per above

This works for my main form, but does not work for my subform as screen.
activeform picks up the main form when a subform is active.

Any help would be appreciated!!

 

[Guest]

Hi Abby

The error is because when you are not returning a value from a called
function you don't need brackets. So the error 13 will stop if you use...

save_prompt Form

BUT!!!! This will stop the error but your function won't do what you want!
You have tried to use Cancel = True but your Cancel isn't valid inside your
function.
The Cancel = True has to be issued within the before update routine.

So you need your function to tell the before update routine whether to
cancel or not. I have modified your code to do this...

Public Function save_prompt(formObj As Form) As Boolean

If saveprompton = True And formObj.Dirty = True Then
result = MsgBox("would you like to save your changes? Changes must
be either " & _
"saved or discarded before moving between the main form and a
subform, and " & _
"before closing, or moving to a different counterparty record.",
vbYesNoCancel)
If (result = 7) Then
formObj.Undo
save_prompt = True
ElseIf (result = 2) Then
save_prompt = True
Else
save_prompt = False
End If
End If
End Function

Then you need a line in your before update routine to say to cancel or not
which is the same line that you use to call save_prompt, like this...

Cancel = save_prompt(Form)

Note that now we are returning a value from the function we need the
brackets again.

HTH

Andy Hull

 

[nlwatts via AccessMonster.com]

Thanks so much, it now works perfectly!!