Validation on Data Entry Form

[ProgEvalJAA]

I have a data entry form with 12 fields.

The first 11 fields are completed by the users.

The last field asks the user if the entry is complete.

What is the easiest method to use to automate the last field to populate Y for Yes if the first 11 fields are completed?

Should I use a validation rule on the control for the 12th field? Or, is there an easier method such as the Before Update event or On Dirty event? I am not that familiar with VBA code, so I am trying to do this in as easy a method as possible.

 

[TheAceMan1]

How are ya ProgEvalJAA . . .

[blue]As a programmer, never ask a user if primary data has been entered! ... [purple]you do the checking and put them in the position for correction if necessary![/purple][/blue]

The following code checks for missing data ... just follow the steps:
[ol][li]Put a question mark [purple]?[/purple] in the [blue]Tag[/blue] property of the 11 controls ([red]no quotations please![/red]).[/li]
[li]Remove all the code for the last field then remove the last field from the form and if it exist in the table, there also. [purple]Its no longer needed since your performing the validation![/purple][/li]
[li]In the forms [blue]Before Update[/blue] event ... copy/paste the following:

[blue]   Dim ctl As Control
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If Trim(ctl & "") = "" Then
            MsgBox "No Data Entry in '" & ctl.Name & "'", _
                   vbCritical + vbOKOnly, _
                   "Missing Data Error! ..."
            ctl.SetFocus
            Cancel = True
         End If
      End If
   Next[/blue]

[/li][/ol]
The way the code works ... all controls with a [purple]?[/purple] are parsed. When one is detected with no data, a message is displayed warning of the missing data. When the user clicks OK in the messagebox, focus is set to the control with missing data (for correction) and the [blue]Before Update[/blue] event is cancelled! ... preventing saving of the record. This secnario repeats until all controls with missing data are corrected.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ProgEvalJAA]

This is helpful, AceMan1; however, what if I want that 12th field to appear on a report and not prevent saving of records?

The values in that 12th field (Y or N) are sent to a report that lists all customers. Users look for No indicators on the report to find customers for which they still need to key forms into the database.

 

[ProgEvalJAA]

This additional information might help in forming an answer to my question.

The Data Entry form needs to autopopulate that 12th field as "Y" if the following is true:

IF 1 OR 1 NA is completed AND 2, 3, 4, 5, 6, 7, 8, and 9 are completed AND 10 OR 10 N/A is completed AND 11 OR 11 N/A is completed.

Any other scenarios should result in "N" on that 12th field.

I plan to lock the control for that 12th field so the users can merely see it, but not change it.

 

[MajP]

Public Function isComplete(ParamArray myFields() As Variant) As Boolean
  Dim itm As Variant
  isComplete = True
  For Each itm In myFields
    If Trim(itm & " ") = "" Then
      isComplete = False
    End If
  Next itm
End Function

Do not save the value, but calculate in your report query.

Something like
Select
Field1Name,
Field2Name,
..
isComplete([field1Name],[Field2Name],...[Field12Name]) AS InputComplete


You could also reuse this function in lieu of ACEMan's code in a calculated control on a form to show if complete.

Also you can use this for any amount of fields.

 

[MajP]

Can you restate?
IF 1 OR 1 NA is completed AND 2, 3, 4, 5, 6, 7, 8, and 9 are completed AND 10 OR 10 N/A is completed AND 11 OR 11 N/A is completed

Makes little sense.

You could simply turn AceMan's code into a function that returns True if it makes it through the code.

If it makes it through the code then
me.field12 = True

Or do what I say and never save 12 just calculate it.

 

[ProgEvalJAA]

The statement lists my fields. The fields are question numbers.

Completed = Not Null

Three questions have two separate fields and cannot be combined into one field due to links to other programs, etc. The three questions are 1 and 1 N/A, 10 and 10 N/A, and 11 and 11 N/A. If one of the two fields are completed, that is, Not Null, then that question has been answered.

So, restating my statement:

If Question 1 OR Question 1 N/A is completed (Not Null) AND Questions 2-9 are completed AND Question 10 or 10 N/A is completed AND Question 11 or 11 N/A is completed, then I want that 12th field to auto populate as "Y", meaning the entire data entry form has been keyed.

Does this change any of your directions?

 

[MajP]

Your data should be normalized and it would make this much easier. Also you should use descriptive field names I hope your fields are not really (2,3,4..)

Anyways

Public Function isComplete(q1, q1na, q2, q3, q4, q5, q6, q7, q8, q9, q10, q10na, q11, q11na) As Boolean
  Dim q1q1na As Boolean
  Dim q10q10na As Boolean
  Dim q11q11na As Boolean
  Dim q2toq9 As Boolean
  
  q2toq9 = isFilled(q2) And isFilled(q3) And isFilled(q4) And isFilled(q5) And isFilled(q6) And isFilled(q7) And isFilled(q8) And isFilled(q9)
  q1q1na = isFilled(q1) Or isFilled(q1na)
  q10q10na = isFilled(q10) Or isFilled(q10na)
  q11q11na = isFilled(q11) Or isFilled(q11na)
  isComplete = q2toq9 And q1q1na And q10q10na And q11q11na
End Function

Public Function isFilled(q) As Boolean
  If Not (Trim(q & " ") = "") Then isFilled = True
End Function

so you can call this function from code, a query, or a calculated control by passing the field names or if on a form the control names.

from a query
isComplete(field1,field1na,...filed11na) as Complete

from a form control
=iscomplete([txtBoxq1],[txtBoxq2],...[txtBoxq11na])

or in code
me.field12 = iscomplete(field1,field1na,...filed11na)

 

[TheAceMan1]

ProgEvalJAA . . .

Hmmmmmmmm ... as far as [blue]Question 1[/blue] and [blue]Question 1 N/A[/blue] are concerned, ... are these the actual names of the fields/controls? ... a total of 14 controls?

or

Are you saying there are 11 controls? ... and the three controls mentioned have [blue]NA[/blue] entered?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ProgEvalJAA]

1. Question 1 and Question 1 N/A are actually named:

CF1 Days
CF1 NA

There are two possible boxes (fields) in which users can enter data.

2. Yes, there are actually 14 controls for the questions. I forgot about the three sets of controls (Questions 1, 10 and 11) when I started this thread.

3. I know the data should be normalized and not have two fields for each of the three questions. I am working with a database I inherited that I am continually making enhancements to. I want to normalize these fields eventually, but right now, I do not want to disturb the links to reports in Excel, Publisher and Crystal. So, that is a project for another day

 

[MajP]

Regardless of your names, just pass them to the function and it will properly return if the questions are answered.

 

[TheAceMan1]

[blue] ... what if I want that 12th field to appear on a report and not prevent saving of records?[/blue]

In this case the user needs to be given the choice to continue or make corrections. This can be done by modifying my message the user see's in my code. The user is given two buttons [blue]'Yes'[/blue] to continue ... [blue]'No'[/blue] to go back and edit the field with missing data. The Code works as follows (Bear in mind ... the forms [blue]Before Update[/blue] event is only triggered if you've edited a record ... new record or not and you attempt to change records by going to an alternate record ... new or not):
[ol][li]The controls with [purple]?[/purple] in their [blue]Tag[/blue] property are parsed.[/li]
[li]If all controls have data, [blue]Completed[/blue] is set to '[blue]Y[/blue]' and you move to where you set the focus.[/li]
[li]On the 1st field that comes up empty, the user is given the message (showing duals proper). If user clicks Yes ... [blue]Completed[/blue] is set to '[blue]N[/blue]' and the event is allowed to cmplete ... saving the record & moving to where you set the focus.[/li]
[li]The user clicks '[blue]No[/blue]' and focus is set to the field in question and the event is cancelled and exited for correction to take place ... record is not yet saved![/li][/ol]
Having to display dual controls verses single added more complexity than I thought. However it works great. To install the code:
[ol][li]In the [blue]Before Update[/blue] event of the form, copy/paste the following:

[blue]   Dim ctl As Control, DL As String, flgComplete As String
   Dim Nam1 As String, Nam2 As String, strDuals As String, Idx As Integer
  
   DL = vbNewLine & vbNewLine
   strDuals = "DF1 DaysDF1 NADF10 DaysDF10 NADF11 DaysDF11 NA"
  
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If InStr(1, strDuals, ctl.Name) Then
            If InStr(1, ctl.Name, " ") = 5 Then
               Nam1 = Mid(ctl.Name, 1, 5) & "Days"
               Nam2 = Mid(ctl.Name, 1, 5) & "NA"
            Else
               Nam1 = "DF1 Days"
               Nam2 = "DF1 NA"
            End If
            
            Idx = MsgDF(Nam1, Nam2)
         Else
            Nam1 = ctl.Name
            Idx = MsgDF(Nam1)
         End If
         
         If Idx = vbNo Then
            Me(ctl.Name).SetFocus
            Cancel = True
            Exit Sub
         ElseIf Idx = vbYes Then
            flgComplete = "N"
            Exit For
         End If
      End If
   Next

   If flgComplete = "" Then
      Me!Completed = "Y"
   Else
      Me!Completed = flgComplete
   End If[/blue]

[/li]
[li]While your in the forms code module, copy/paste the following function:

[blue]Public Function MsgDF(ByVal Name1 As String, Optional Name2) As Integer
   Dim Msg As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   If IsMissing(Name2) Then
      If Trim(Me(Name1) & "") = "" Then Msg = "No Data Entry in '" & Name1 & "'"
   Else
      If Trim(Me(Name1) & "") = "" And Trim(Me(Name2) & "") = "" Then Msg = "No Data Entry in '" & Name1 & "' or '" & Name2 & "'"
   End If
   
   If Msg <> "" Then
      MsgDF = MsgBox(Msg & DL & _
                    "Press 'Yes' if this entry is complete." & DL & _
                    "Press 'No' to go back and make corrections.", _
                    vbCritical + vbYesNo, _
                   "Missing Data Detected! ...")
   End If
   
End Function[/blue]

[/li]
[li]Thats it ... perform your testing![/li][/ol]
[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ProgEvalJAA]

AceMan,

After going through some reference books and reviewing the line code by code, I have a question about the section pasted here.

Is the Mid function being utilized for all three sets of my dual controls, or just the first one that lists CF1 Days or CF1 NA?

For Each ctl In Me.Controls
If ctl.Tag = "?" Then
If InStr(1, strDuals, ctl.Name) Then
If InStr(1, ctl.Name, " ") = 5 Then
Nam1 = Mid(ctl.Name, 1, 5) & "Days"
Nam2 = Mid(ctl.Name, 1, 5) & "NA" Else
Nam1 = "DF1 Days"
Nam2 = "DF1 NA

 

[TheAceMan1]

[blue]Is the Mid function being utilized for all three sets of my dual controls, or just the first one that lists CF1 Days or CF1 NA?[/blue]

No, only for DF10 & DF11 ...

[blue]If InStr(1, strDuals, ctl.Name) Then
   [green]'Dual Control Name Detected!
   'Note that all 6 of the controls are in strDuals.
   '------------------------------------------------
   '"[red][b]DF1 Days[/b][/red]DF1 NADF10 DaysDF10 NADF11 DaysDF11 NA"
   '"DF1 Days[red][b]DF1 NA[/b][/red]DF10 DaysDF10 NADF11 DaysDF11 NA"
   '"DF1 DaysDF1 NA[red][b]DF10 Days[/b][/red]DF10 NADF11 DaysDF11 NA"
   '"DF1 DaysDF1 NADF10 Days[red][b]DF10 NA[/b][/red]DF11 DaysDF11 NA"
   '"DF1 DaysDF1 NADF10 DaysDF10 NA[red][b]DF11 Days[/b][/red]DF11 NA"
   '"DF1 DaysDF1 NADF10 DaysDF10 NADF11 Days[red][b]DF11 NA[/b][/red]"[/green]
   
   If InStr(1, ctl.Name, " ") = 5 Then
      [green]'This IF Statement is mainly for setting-up display for the
      'messagebox where 1 or 2 names can be displayed.
      'Note that DF10 & DF11 have a space in the 5th character position!
      '---------
      '1234[red][b]5[/b][/red]6789
      '---------
      'DF10 Days
      'DF11 Days
'     'DF1 Days[/green]
      Nam1 = Mid(ctl.Name, 1, 5) & "Days"
      Nam2 = Mid(ctl.Name, 1, 5) & "NA"
   Else [green]'Has to be DF1[/green]
      Nam1 = "DF1 Days"
      Nam2 = "DF1 NA"
   End If
[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[ProgEvalJAA]

Thanks for all the help. This has all been helpful.