Form Subform Make Sure Fields are Completer (1 Viewer)

[tmort]

I have a form/subform. The subform is partially filled in and some fields have to be entered manually. This form/subform is based on a temporary table. There is a command button to run an append query to take these values and put them in their final destinations.

I'd like to make sure all the fields that require data are filled in. Because I'm using the command button to update the final table the putting this checking in the afterupdate event doesn't work.

I'd like to check to see if there is a value and if not set the focus to each field on at a time that needs a value.

I've tried putting this code in a public sub and calling if from the command button, but, after a missing entry is flagged, the code keeps on running and the record is added before any corrections can be made.

I've also tried just putting this code in the command button. It flags the first instance and sets the focus and I can add a value, but, when I press the button again to continue checking the other fields or proceding forward, nothing happens

Does anyone have any suggestions

 

[pbaldy]

Why don't you post the code that fails and we'll try to fix it?

 

[John Big Booty]

As part of the code in your Public Sub, put the line;

Exit Sub

as part of any procedure where validation fails, this will Exit/Stop the code at that point.

 

[tmort]

Here's the public sub:

Public Sub Form_afterupdate()



Dim stChildLastName As String
Dim stChildFirstName As String
Dim stGender As String
Dim stStreetAddress As String
Dim stCity As String
Dim stState As String
Dim stZip As String
Dim dBirthday As Date
Dim stGrade As String
Dim stAdultShirtSize As String
Dim stInsInfoComplete As String
Dim stNoIns As String
Dim stImmunizations As String
Dim stSignature As String

Dim stmsg As String



stChildLastName = Nz(Forms![duplicate2]!Dupenewchild.Form![Last Name], "none")
stChildFirstName = Nz(Forms![duplicate2].Dupenewchild![Child First Name], "none")
ststGender = Nz(Forms![duplicate2]!Dupenewchild.Form![Gender], "none")
stStreetAddress = Nz(Forms![duplicate2]!Dupenewchild.Form![Street Address], "none")
stCity = Nz(Forms![duplicate2]!Dupenewchild.Form![City], "none")
stState = Nz(Forms![duplicate2]!Dupenewchild.Form![State], "none")
stZip = Nz(Forms![duplicate2]!Dupenewchild.Form![Zip], "none")
stBirthday = Nz(Forms![duplicate2]!Dupenewchild.Form![Birthday], "1/1/1900")
stGrade = Nz(Forms![duplicate2]!Dupenewchild.Form![Grade Next Year], "none")
stAdultShirtSize = Nz(Forms![duplicate2]!Dupenewchild.Form![Adult Shirt Size], "none")
stInInfoCompleter = Nz(Forms![duplicate2]!Dupenewchild.Form![Insurance Info Complete], "none")
stNoIns = Nz(Forms![duplicate2]!Dupenewchild.Form![No Insurance Coverage], "none")
stImmunizations = Nz(Forms![duplicate2]!Dupenewchild.Form![Immunizations], "none")
stSignature = Nz(Forms![duplicate2]!Dupenewchild.Form![Parent/Guardian Signature], "none")




If Nz(Forms![duplicate2]!Dupenewchild.Form![SFSP]) = 0 Then


MsgBox "SFSP information not entered. Please enter SFSP information", vbOKOnly, "SFSP"
Forms![duplicate2]![Dupenewchild].Form![SFSP].SetFocus

Exit Sub

'End If

Else

If Forms![duplicate2].Dupenewchild![Parent/Guardian Signature] = "N" Then
MsgBox "Parent/Guardian signature is missing", vbOKOnly, "Signature"
Forms![duplicate2]![Dupenewchild].Form![Signature].SetFocus

Exit Sub
'End If


Else

If stChildLastName = "none" Then
stmsg = "You forgot to enter a last name for child" & Chr(13) & Chr(13) & "Please be sure to enter a last name"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[Last Name].SetFocus

Exit Sub

Else

If stChildFirstName = "none" Then
stmsg = "You forgot to enter a first name for child" & Chr(13) & Chr(13) & "Please be sure to enter a first name"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[Child First Name].SetFocus

Exit Sub

Else

If stGender = "none" Then
stmsg = "You forgot to enter a gender for child" & Chr(13) & Chr(13) & "Please be sure to enter a gender"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[Gender].SetFocus

Exit Sub

Else

If stStreetAddress = "none" Then
stmsg = "You forgot to enter a street address for child" & Chr(13) & Chr(13) & "Please be sure to enter a street address"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[Child Street Address].SetFocus

Exit Sub

Else

If stCity = "none" Then
stmsg = "You forgot to enter a city for child" & Chr(13) & Chr(13) & "Please be sure to enter a city"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[City].SetFocus

Exit Sub

Else

If stState = "none" Then
stmsg = "You forgot to enter a state for child" & Chr(13) & Chr(13) & "Please be sure to enter a state"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[State].SetFocus

'End If

Exit Sub

Else


If stZip = "none" Then
stmsg = "You forgot to enter a zip code for child" & Chr(13) & Chr(13) & "Please be sure to enter a zip code"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[ZipCode].SetFocus

'End If

Exit Sub

Else

If dBirthday = "1/1/1900" Then
stmsg = "You forgot to enter a birthday for child" & Chr(13) & Chr(13) & "Please be sure to enter a birthday"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[txtSelectDate].SetFocus

'End If

Exit Sub

Else

If stGrade = "none" Then
stmsg = "You forgot to enter a grade next year for child" & Chr(13) & Chr(13) & "Please be sure to enter a grade"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form![Grade Next Year].SetFocus

'End If

Exit Sub

Else

If stAdultShirtSize = "none" Then
stmsg = "You forgot to enter an adult shirt size for child" & Chr(13) & Chr(13) & "Please be sure enter a shirt size"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[Adult Shirt Size].SetFocus

'End If

Exit Sub

Else

If stInsInfoComplete = "none" Then

stmsg = "You forgot to enter whether insurance information is complete" & Chr(13) & Chr(13) & "Please be sure to enter insurance status"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[InsInfoComplete].SetFocus

'End If

Exit Sub

Else

If stNoIns = "none" Then
stmsg = "You forgot to enter whether there is no insurance" & Chr(13) & Chr(13) & "Please be sure to enter whether there is insurance or not"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[NoIns].SetFocus

'End If

Exit Sub

Else

If stImmunizations = "none" Then
stmsg = "You forgot to enter immunizations information" & Chr(13) & Chr(13) & "Please be sure to enter immunization status"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[Immunizations].SetFocus

'End If

Exit Sub

Else

If stSignature = "none" Then
stmsg = "You forgot to enter whether there is a parent signature" & Chr(13) & Chr(13) & "Please be sure to enter signature status"
MsgBox stmsg
Forms![duplicate2].[Dupenewchild].Form.[Signature].SetFocus

'End If

Exit Sub

Else

End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End If
End Sub

Here's the code for the command button that continues on:

Private Sub BtnDuplicate_Click()



Call Form_afterupdate



DoCmd.SetWarnings False
DoCmd.OpenQuery "Duplicate Record2"
'DoCmd.SetWarnings True

'Requery the subform to display the newly appended records.
Me![Dupenewchild].Requery

DoCmd.RunMacro "msgboxtimer"
DoCmd.OpenQuery "qrydelete"
DoCmd.OpenQuery "qrydeletenull"

DoCmd.Close


DoCmd.OpenForm "Duplicate"

Exit_btnduplicate_Click:
Exit Sub


Err_btnDuplicate_Click:
MsgBox Error$
Resume Exit_btnduplicate_Click



End Sub

 

[John Big Booty]

Firstly when you post code please use the Code tag, that the button on the top of the posting window with the hash mark (#).

You should be running this code in the Before Update event rather than the After Update event.

Also use;

Cancel = True

Prior to each of your Exit Sub commands. This will cancel the action and prevent the record being committed until it has been successfully validated.

 

[Thales750]

You're using a public sub and referring to specific controls on a form.

Plus you have space in your field and text box names.


Why don’t you just put this as the action

[FONT=Verdana][COLOR=black][COLOR=black][FONT=Verdana]If isnull(me.[Last Name]) then            [/FONT][/COLOR]
[COLOR=black][FONT=Verdana]me.[Last Name].setfocus[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]exit sub[/FONT][/COLOR]
[COLOR=black][FONT=Verdana]end if [/FONT][/COLOR]
 
[/COLOR][/FONT]

Repeat that for all of the controls

 

[tmort]

It works for:

If IsNull(Forms![duplicate2]![Dupenewchild].Form![SFSP]) Then
MsgBox "SFSP information not entered.  Please enter SFSP information", vbOKOnly, "SFSP"
Forms![duplicate2]![Dupenewchild].Form![SFSP].SetFocus

Exit Sub

End If

I get a prompt and then the control is selected.

However for:

If IsNull(Forms![duplicate2]![Dupenewchild].Form![Child First Name]) Then
stmsg = "You forgot to enter a first name for child" & Chr(13) & Chr(13) & "Please be sure to enter a first name"
MsgBox stmsg
Forms![duplicate2]![Dupenewchild].Form![Child First Name].SetFocus

Exit Sub

End If

I get a prompt but the cursor doesn't appear in the controls

and for:

If IsNull(Forms![duplicate2]![Dupenewchild].Form![Parent/Guardian Signature]) Then
MsgBox "Parent/Guardian signature is missing", vbOKOnly, "Parent/Guardian Signature"
Forms![duplicate2]![Dupenewchild].Form![Parent/Guardian Signature].SetFocus

Exit Sub

End If

I get an error saying that object doesn't support this property or method. I've checked and that is both the name of the control and the control source

 

[John Big Booty]

With the control on the sub-form, try setting the focus first to the sub form holder and then to the required control.

 

[tmort]

what's the subform holder?

 

[John Big Booty]

When you have you form set in design view, you will notice that to get to a control on the sub form you need to click on the form twice the first time the Subform holder is selected the second time you get the control you where after.