Keep form open until loop completes

[0 1]

I have a close button on several forms. All close buttons refer to a
global close function that checks a few things and closes the form.
But I'd also like to incorporate a validate function that loops
through all controls that have an asterisk (*) in the Tag Property and
prompts the user to address any that are Null.

No matter where I add the "ValidateData" line (my function name) to
the close code below, the loop executes only once (encounters the
first control with an *) but then the form closes. Any ideas how to
keep the form open until the loop completes?

Function CloseForm()

###

Dim frm As Form
Set frm = Screen.ActiveForm

Dim strForm As String
strForm = frm.Name

If IsNull(frm.FlagID) Then
If MsgBox ... = vbYes Then
'some code here runs a SQL insert
Else
DoCmd.RunCommand acCmdUndo
EndIf
ElseIf frm.Dirty = True Then
If MsgBox("Save changes?, vbYesNo) = vbYes Then
DoCmd.Save
Else
DoCmd.RunCommand acCmdUndo
End If
End If

DoCmd.Close acForm, strForm

End Function

###

Function ValidateData()

Dim frm As Form
Set frm = Screen.ActiveForm

'Place an asterisk (*) in the Tag Property of the text
'boxes you wish to validate.
Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control

nl = vbNewLine & vbNewLine

For Each ctl In frm.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = "Data Required for '" & ctl.Name & "' field!" & nl & _
"You can't save this record until this data is
provided." & nl & _
"Enter the data and try again."
Style = vbCritical + vbOKOnly
Title = "Required Data..."
MsgBox msg, Style, Title
ctl.SetFocus
DoCmd.CancelEvent 'Cancel = True
Exit For
End If
End If
Next

End Function

 

[John W. Vinson]

I have a close button on several forms. All close buttons refer to a
global close function that checks a few things and closes the form.
But I'd also like to incorporate a validate function that loops
through all controls that have an asterisk (*) in the Tag Property and
prompts the user to address any that are Null.

No matter where I add the "ValidateData" line (my function name) to
the close code below, the loop executes only once (encounters the
first control with an *) but then the form closes. Any ideas how to
keep the form open until the loop completes?

Put your code in the form's BeforeUpdate event. It'll need to be customized
per form anyway, I'm guessing, so it doesn't cost you much extra code.
BeforeUpdate is called before the form closes, and if you set its Cancel
argument to True the update won't happen and the form won't close.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[0 1]

Put your code in the form's BeforeUpdate event. It'll need to be customized

per form anyway, I'm guessing, so it doesn't cost you much extra code.

I originally had it there (in a global BeforeUpdate function,
actually, because the per-form customization is minimal). But I had to
move out out of the BeforeUpdate event because one piece of code that
might need to run ('some code here runs a SQL insert) requires the
form/record to be saved, which obviously can't happen if the
BeforeUpdate event is still processing.

(I was just about to post about this issue, too, hoping there's a
workaround because I'd like to take advantage of the BeforeUpdate
properties.)

Any ideas?