Validate Access Continous Form Fields

[sstime]

I hope someone can help me with problem, I'm getting a little frustrated. I have developed for a customer, a continuous form where they can view their input records and then add new records at the end of the list. The data entry value is set to "No". There are 27 fields within the record and I want to validate 9 of those fields to ensure that they are populated (which I use that data later in macros). I have search for and tried various examples, but none work correctly on my form design. I've tried the Before Update on the form, and tried before update on a field, and tried checking on field exit, also on the "Onblur" which I didn't get any results. But nothing gave me the results of ensuring that I have populated those specific 9 fields before advancing to the next record. Any suggestions with examples would be greatly appreciated.

 

[PHV]

What did you try in the BeforeUpdaite event procedure of the form ?

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

 

[sstime]

I have tried several different versions of the below coding. I also tried it by removing the "Exit Sub" after each group (naturally didn't work either.

Coding segment:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Const CheckCode = 0
Const CheckAlphCode = "A"
Const CheckMiles = 0

If IsNull(Me.OrderNbr.Value) Then
Cancel = False
Me.OrderNbr.SetFocus
Exit Sub
End If

If Me.EndMileage.Value = CheckMiles Then
MsgBox "You must enter the Truck Ending Mileage!", vbExclamation, "Required field is Missing Data"
Me.EndMileage.SetFocus
Cancel = True
Exit Sub

ElseIf Me.EndMileage.Value > CheckMiles Then Exit Sub
MsgBox "You must enter the Truck Ending Mileage!", vbExclamation, "Required field is Missing Data"
Me.EndMileage.SetFocus
Cancel = True
Exit Sub

ElseIf IsNull(Me.TrkNbr.Value) Then
MsgBox "You must enter the Truck Number!", vbExclamation, "Required field is Missing Data"
Me.TrkNbr.SetFocus
Cancel = True
Exit Sub

ElseIf Me.TrkNbr.Value > CheckCode Then Exit Sub
MsgBox "You must enter the Truck Number!", vbExclamation, "Required field is Missing Data"
Me.TrkNbr.SetFocus
Cancel = True
Exit Sub

ElseIf Me.TrbleCd.Value = CheckCode Then
MsgBox "You must enter a Trouble Code!", vbExclamation, "Required field is Missing Data"
Me.TrbleCd.SetFocus
Cancel = True
Exit Sub

ElseIf Me.TrbleCd.Value > CheckCode Then Exit Sub
MsgBox "You must enter a Trouble Code!", vbExclamation, "Required field is Missing Data"
Me.TrbleCd.SetFocus
Cancel = True
Exit Sub

ElseIf Me.OBMiles.Value = CheckMiles Then
MsgBox "Enter number of Out Bound Miles!", _
vbExclamation, "Required field is Missing Data"
Me.OBMiles.SetFocus
Cancel = True
Exit Sub

ElseIf Me.OBMiles.Value > CheckMiles Then Exit Sub
MsgBox "Enter number of Out Bound Miles!", _
vbExclamation, "Required field is Missing Data"
Me.OBMiles.SetFocus
Cancel = True
Exit Sub

ElseIf IsNull(Me.CmpTm.Value) Then
MsgBox "You must enter the Completed Time!", vbExclamation, "Required field is Missing Data"
Me.CmpTm.SetFocus
Cancel = True
Exit Sub

ElseIf Me.CmpTm.Value > CheckCode Then Exit Sub
MsgBox "You must enter the Completed Time!", vbExclamation, "Required field is Missing Data"
Me.CmpTm.SetFocus
Cancel = True
Exit Sub

If IsNull(Me.ArrTm.Value) Then
MsgBox "You must enter an Arrival Time!", vbExclamation, "Required field is Missing Data"
Me.ArrTm.SetFocus
Cancel = True
Exit Sub

ElseIf Me.ArrTm.Value > CheckCode Then Exit Sub
MsgBox "You must enter an Arrival Time!", vbExclamation, "Required field is Missing Data"
Me.ArrTm.SetFocus
Cancel = True
Exit Sub

ElseIf IsNull(Me.DrvrCode.Value) Then
MsgBox "You must enter a Driver Code!", vbExclamation, "Required field is Missing Data"
Me.DrvrCode.SetFocus
Cancel = True
Exit Sub

ElseIf Me.DrvrCode.Value >= CheckAlphCode Then Exit Sub
MsgBox "You must enter a Driver Code!", vbExclamation, "Required field is Missing Data"
Me.DrvrCode.SetFocus
Cancel = True
Exit Sub

ElseIf IsNull(Me.PlusCd.Value) Then
MsgBox "You must enter a Member Type!", vbExclamation, "Required field is Missing Data"
Me.PlusCd.SetFocus
Cancel = True
Exit Sub

ElseIf Me.PlusCd.Value >= CheckAlphCode Then Exit Sub
MsgBox "You must enter a Member Type!", vbExclamation, "Required field is Missing Data"
Me.PlusCd.SetFocus
Cancel = True
Exit Sub

ElseIf IsNull(Me.SvcDt.Value) Then
MsgBox "You must enter Date of Service!", vbExclamation, "Required field is Missing Data"
Me.SvcDt.SetFocus
Cancel = True
Exit Sub

ElseIf Not IsDate(Me.SvcDt.Value) Then
MsgBox "You must enter Date of Service!", vbExclamation, "Required field is Missing Data"
Me.SvcDt.SetFocus
Cancel = True
Exit Sub

ElseIf IsNull(Me.OrderNbr.Value) Then
MsgBox "You must enter an Order Number!", vbExclamation, "Required field is Missing Data"
Me.OrderNbr.SetFocus
Cancel = True
Exit Sub

ElseIf Me.OrderNbr.Value > CheckCode Then Exit Sub
MsgBox "You must enter an Order Number!", vbExclamation, "Required field is Missing Data"
Me.OrderNbr.SetFocus
Cancel = True
Exit Sub
End If
End If
End Sub

 

[TheAceMan1]

How are ya sstime . . .

Your [blue]program flow[/blue] would be alot less confusing if you used the [blue]BeforeUpdate[/blue] events of the 9 controls and the appropriate code. Example for EndMileage:

[blue]   If Trim(Me.EndMileage & "") = "" Or Me.EndMileage = CheckMiles Then
      MsgBox "You must enter the Truck Ending Mileage!", _
             vbExclamation, _
             "Required field is Missing Data"
      Cancel = True
   End If[/blue]

This can certainly be done using the forms [blue]BeforeUpdate[/blue] event, however using the controls relieves the mind boggle! . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[sstime]

Hi AceMan1
Thanks for the response. I changed my coding using the BeforeUpdate on the required fields and removed the forms control coding. When I tested it by tabing or pressing enter past a required field, nothing happened. Am I missing something? Here's a sample of what I entered.

Private Sub ArrTm_BeforeUpdate(Cancel As Integer)
Const CheckCode = 0
If Trim(Me.ArrTm & "") = "" Or Me.ArrTm = CheckCode Then
MsgBox "You must enter Arrival Time!", _
vbExclamation, _
"Required field is Missing Data"
Cancel = True
End If
End Sub

Private Sub CmpTm_BeforeUpdate(Cancel As Integer)
Const CheckCode = 0
If Trim(Me.CmpTm & "") = "" Or Me.CmpTm = CheckCode Then
MsgBox "You must enter Time Completed!", _
vbExclamation, _
"Required field is Missing Data"
Cancel = True
End If
End Sub

Private Sub DrvrCode_BeforeUpdate(Cancel As Integer)
If Trim(Me.DrvrCode & "") = "" Then
MsgBox "You must enter a Driver Code!", _
vbExclamation, _
"Required field is Missing Data"
Cancel = True
End If
End Sub

Help!!!

 

[TheAceMan1]

sstime . . .

No your not missing anything. Actually I hit submit before I realized what I posted wasn't suitable. So remove all that code and perform the following:

In the [blue]Tag[/blue] property of the controls enter the text message for the msgbox. Example: enter [blue]You must enter the Truck Ending Mileage![/blue] in the tag property for [blue]EndMileage[/blue] (no quotations please!).

Then in the forms [blue]BeforeUpdate[/blue] event, copy/paste the following:

[blue]    Dim ctl As Control, Msg As String
    
    Const CheckCode = 0, CheckAlphCode = "A"
    
    For Each ctl In Me.Controls
      If ctl.Tag <> "" Then
         If ctlName = "DrvrCode" Or ctlName = "PlusCd" Then
            If Trim(ctl & "") = "" Or ctl > CheckAlphCode Then
               Msg = ctl.Tag
               Exit For
            End If
         ElseIf ctlName = "SvcDt" Then
            If Trim(ctl & "") = "" Or (Not IsDate(ctl)) Then
               Msg = ctl.Tag
               Exit For
            End If
         Else
            If Trim(ctl & "") = "" Or ctl <= CheckCode Then
               Msg = ctl.Tag
               Exit For
            End If
         End If
      End If
   Next

   If Msg <> "" Then
      MsgBox Msg, vbExclamation, "Required field is Missing Data"
      ctl.SetFocus
      Cancel = True
   End If[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[sstime]

Hi AceMan1,

I made the changes (modified a little), placed the messages in the control tag on certain mandatory fields and placed the following code into the forms BeforeUpdate, tested and didn't get any error messages when a tabbed over two of the mandatory fields. As you can tell, I'm not really sharp on the script coding concepts.

Coding in the forms Before Update.

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim ctl As Control, Msg As String

Const CheckCode = 0, CheckAlphCode = "A"

For Each ctl In Me.Controls
If ctl.Tag <> "" Then
If ctlName = "DrvrCode" Or ctlName = "PlusCd" Then
If Trim(ctl & "") = "" Then
Msg = ctl.Tag
Exit For
End If
ElseIf ctlName = "SvcDt" Then
If Trim(ctl & "") = "" Or (Not IsDate(ctl)) Then
Msg = ctl.Tag
Exit For
End If
ElseIf ctlName = "OrderNbr" Or ctlName = "CmpTm" Or ctlName = "ArrTm" _
Or ctlName = "OBMiles" Or ctlName = "TrkCd" Or ctlName = "TrkNbr" Or ctlName = "EndMileage" Then
If Trim(ctl & "") = "" Or ctl <= CheckCode Then
Msg = ctl.Tag
Exit For
End If
End If
End If
Next

If Msg <> "" Then
MsgBox Msg, vbExclamation, "Required field is Missing Data"
ctl.SetFocus
Cancel = True
End If
End Sub

Private Sub SecondExit_Click()
On Error GoTo Err_SecondExit_Click

DoCmd.Close

Exit_SecondExit_Click:
Exit Sub

Err_SecondExit_Click:
MsgBox Err.Description
Resume Exit_SecondExit_Click
End Sub

 

[TheAceMan1]

sstime . . .

I'm not trying to sound demeaning here, but as you say:

[blue]I made the changes ([purple]modified a little[/purple]) . . . As you can tell, [red]I'm not really sharp on the script coding concepts.[/red][/blue]

Why make changes that your not sure of?

When your asking for help f[blue]rom those you believe are in the know[/blue], its incumbent you follow instructions to the letter as you know not what they have in mind! Its easy to over complicate matters this way! [blue]Let the experts make the mistakes![/blue]

So what controls did you skip over? . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[sstime]

I didn't skip over any controls, just wanted validation on nine fields within the record, not all 27 fields. When I went to test the coding, I type data in all fields except for two, and I expected warning messages on data was required. The coding that I entered in the form "BeforeUpdate" is listed above. Sorry that my previous response wasn't very clear. Just trying to get this resolved. I do appreciate all your help.

 

[sstime]

You are right TheAceMan1, I should have not made any changes. I copied your exact coding into the Forms BeforeUpdate. Then I opened the form to do a data input test and came up with an error, I clicked on debug and it had this line highlighted "If Trim(ctl & "") = "" Or ctl <= CheckCode Then" ,

 

[TheAceMan1]

sstime . . .

Seems my last post just didn't register or couldn't be processed . . . Again . . . I'm not trying to sound demeaning.

[blue]I didn't skip over any controls . . . When I went to test the coding, I type data in all fields [purple]except for two[/purple] . . .[/blue]

So you [red]skipped[/red] entering data into two controls. And [blue]the name of those controls are?[/blue]

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[sstime]

Ooops, I guess I should have told you what the error was.

It stated, "Run Time Error 438"
Object doesn't Support the property or method.

 

[sstime]

Sorry, I didn't skip controls, but entering data into fields for validation during data input.

I have placed the error messages (in Design Mode) into the field controls in the Tag area, just on the fields that I wanted validated with error messages. Then went into forms view to display records with previous data that was entered, started a new record at the bottom to test data input.

 

[TheAceMan1]

sstime . . .

Per youe latest post . . . did you skip entering data into any controls?

[blue]If yes . . . what were the control/field names you skipped?

I still havn't received an answer . . .

See Ya! . . . . . .

Be sure to see FAQ219-2884:

 

[sstime]

Out of the 27 fields, I didn't put an entry into "ArrTm" and "OBMiles", which were two of the mandatory fields out of the nine. I have a message placed into each of the tag controls for those fields.

 

[sstime]

To TheAceMan1, per your post coding at 13:06

I have placed the exact coding into the forms "BeforeUpdate". Went to the forms view and entered data into all fields and when I reach the end of the record, I still get the Run Time Error 438, Object doesn't support the property or method. This is on the last "IF" statement. Coding is as follows:

Dim ctl As Control, Msg As String

Const CheckCode = 0, CheckAlphCode = "A"

For Each ctl In Me.Controls
If ctl.Tag <> "" Then
If ctlName = "DrvrCode" Or ctlName = "PlusCd" Then
If Trim(ctl & "") = "" Or ctl > CheckAlphCode Then
Msg = ctl.Tag
Exit For
End If
ElseIf ctlName = "SvcDt" Then
If Trim(ctl & "") = "" Or (Not IsDate(ctl)) Then
Msg = ctl.Tag
Exit For
End If
Else
If Trim(ctl & "") = "" Or ctl <= CheckCode Then
Msg = ctl.Tag
Exit For
End If
End If
End If
Next

If Msg <> "" Then
MsgBox Msg, vbExclamation, "Required field is Missing Data"
ctl.SetFocus
Cancel = True
End If

 

[sstime]

To "TheAceMan1" - I appreciate all your help, unfortunately I still don't have it working yet and I'm leaving on Vacation for two weeks. I will check again when I get back. Thanks again.