Validation Checking conflict with Open Args

[Hulm1]

I am checking for completed fields within a form in the Before Update event. However within the Form Unload event I am also using open args code to close the form and open the previous form. The problem is that when my MsgBox comes up and I check the OK button, the darn form closes!

Can anyone please advise? Thanks in advance

BEFORE UPDATE CODE
Private Sub Form_BeforeUpdate(Cancel As Integer)

'Place an asterisk (*) in the Tag Property of the text
'boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:


Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


nl = vbNewLine & vbNewLine

For Each ctl In Me.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
Cancel = True
Exit For
End If
End If
Next

End Sub

FORM UNLOAD CODE

Private Sub Form_Unload(Cancel As Integer)
On Error Resume Next
Forms(Me.OpenArgs).Visible = True

Forms!Project_Edit.cboCompanyID.Requery
End Sub

 

[PHV]

A starting point:

[!]Public boolCancel As Boolean[/!]
Private Sub Form_BeforeUpdate(Cancel As Integer)
...
    [!]boolCancel = False[/!]
    For Each ctl In Me.Controls
      If ctl.ControlType = acTextBox Then
        If ctl.Tag = "*" And Trim(ctl & "") = "" Then
         [!]boolCancel = True[/!]
...
End Sub

Private Sub Form_Unload(Cancel As Integer)
  On Error Resume Next
  [!]Cancel = boolCancel
  If Not boolCancel Then[/!]
    Forms(Me.OpenArgs).Visible = True
    Forms!Project_Edit.cboCompanyID.Requery
  [!]End If[/!]
End Sub

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Hulm1]

Sorry
Didn't work. Could be I have not done this right. I note the location of Public boolcancel as boolean. Above the Private Sub Form_Unload event. That right?

I seem to have two bugs. One highlights Public Boolcancel as boolean, the other highlights Cancel as boolean in the Unload event

Here is my revised code.

BEFORE UPDATE

Public boolcancel As Boolean
Private Sub Form_BeforeUpdate(Cancel As Integer)
'Place an asterisk (*) in the Tag Property of the text
'boxes you wish to validate.
'Then in the BeforeUpdate Event of the form, copy/paste the following:


Dim msg As String, Style As Integer, Title As String
Dim nl As String, ctl As Control


nl = vbNewLine & vbNewLine

boolcancel = False
For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
boolcancel = True
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
Cancel = True
Exit For
End If
End If
Next

End Sub

FORM UNLOAD

Private Sub Form_Unload(Cancel As Integer)

On Error Resume Next
Cancel = boolcancel
If Not boolacncel Then
Forms(Me.OpenArgs).Visible = True
Forms!Project_Edit.cboCompanyID.Requery
End If

End Sub

 

[missinglinq]

I think the problem here is that the OpenArgs are supposed to be used when the form, well, Opens! Which is to say whatever you're going to do with it has to be done in the Form_Open or Form_Load event. I believe the value is essentially lost after that. You could, I suppose,assign its value to a module level variable in one of these events and then reference in the Form_Unload.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Hulm1]

Thank you both for your help. It is most appreciated.

Missinglinq: Thanks, however, sadly I have to inform you that I am insufficiently experienced to completely understand your comments! I lost it with your reference to the "Module level". (much of my code is straight out of "Access for dummies" or google)! Has worked pretty well so far!

I don't think that you are telling me to move my existing openArgs code to Form_Open event as it is at present. Could you be a little more, well basic! in your description?