If field isnt fill then no save

  • Thread starter Thread starter SoggyCashew
  • Start date Start date
S

SoggyCashew

Hello, I have a couple controls "txt1, txt2, txt3" on my form that require
data. I need to have a msgbox come up if a control is empty and give the
option to either close without saving or set focus to the control to finish
filling it out. Also can I put the code in a module and call it from the
onClose event of the form? Thanks!
 
Put the code in the form's BeforeUpdate event, not its Close event.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim intLoop As Integer
Dim strMessage As String

For intLoop = 1 To 3
If Len(Me.Controls("txt" & intLoop) & vbNullString) = 0 Then
strMessage = strMessage & "txt" & intLoop & _
" has no value in it." & vbCrLf
End If
Next intLoop

If Len(strMessage) > 0 Then
MsgBox strMessage
Cancel = True
End If

End Sub
 
Douglas, Where would I put the msgbox that would allow me to either go to the
control that is missing data OR cancel without saving? Thanks!
--
Thanks,
Chad


Douglas J. Steele said:
Put the code in the form's BeforeUpdate event, not its Close event.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim intLoop As Integer
Dim strMessage As String

For intLoop = 1 To 3
If Len(Me.Controls("txt" & intLoop) & vbNullString) = 0 Then
strMessage = strMessage & "txt" & intLoop & _
" has no value in it." & vbCrLf
End If
Next intLoop

If Len(strMessage) > 0 Then
MsgBox strMessage
Cancel = True
End If

End Sub
Click to expand...
 
Doug's code is designed so that it will warn the user of all controls that
have not data, not just one. In this case, it doesn't make sense to set the
focus to a specific control sense more than one may be empty.

SoggyCashew said:
Douglas, Where would I put the msgbox that would allow me to either go to
the
control that is missing data OR cancel without saving? Thanks!
Click to expand...
 
To which control do you want to go if more than one is missing data?

Do you really want to cancel without saving, or do you want to let the user
decide? To do the latter, you'd use something like:

If Len(strMessage) > 0 Then
Cancel = True
strMessage = strMessage & vbCrLf & _
"Cancel the record?)
If MsgBox(strMessage, vbYesNo, vbQuestion) = vbYes Then
Me.Undo
End If
End If

To simply cancel (which I think is a mistake...), just use

If Len(strMessage) > 0 Then
Cancel = True
Me.Undo
End If

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


SoggyCashew said:
Douglas, Where would I put the msgbox that would allow me to either go to
the
control that is missing data OR cancel without saving? Thanks!
Click to expand...
 
Douglas I too dont want to just no save I want to give the option, So I want
to use the first code you have given me. I have a question, What if I have
more than 3 textboxes on the form? Will the code go threw all the text boxes?
Could I put something in the tag for the text boxes that I want required? The
code below is what you have given with the option for the undo.

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim intLoop As Integer
Dim strMessage As String

For intLoop = 1 To 3
If Len(Me.Controls("txt" & intLoop) & vbNullString) = 0 Then
strMessage = strMessage & "txt" & intLoop & _
" has no value in it." & vbCrLf
End If
Next intLoop

If Len(strMessage) > 0 Then
Cancel = True
strMessage = strMessage & vbCrLf & _
"Cancel the record?)
If MsgBox(strMessage, vbYesNo, vbQuestion) = vbYes Then
Me.Undo
End If

End Sub
 
Douglas, I have found the code below that goes threw every control on the
form. Would this work? And if so then how could I add the undo or dont save
msgbox?
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
 
That's going to only identify the first error and stop. If you want it to
identify all errors and then present the option, use

For Each ctl In Me.Controls
If ctl.ControlType = acTextBox Then
If ctl.Tag = "*" And Trim(ctl & "") = "" Then
msg = msg & "Data Required for '" & ctl.Name & "' field!" & nl
End If
End If
Next ctl

If Len(msg) > 0 Then
msg = msg & _
"You can't save this record until this data is provided!" & nl & _
"Choose Yes to enter the data and try again, or No to cancel. . . "
Style = vbCritical + vbYesNo
Title = "Required Data..."
Cancel = True
If MsgBox(msg, Style, Title) = vbNo Then
Me.Undo
End If
End If

Otherwise:

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 & _
"Choose Yes to enter the data and try again, or No to cancel. . .
"
Style = vbCritical + vbYesNo
Title = "Required Data..."
Cancel = True
If MsgBox(msg, Style, Title) = vbYes
ctl.SetFocus
Else
Me.Undo
End If
Exit For
End If
End If
Next ctl
 
Back
Top