Date Entry Form validation Issues

[HGoodloe]

I thought I'd try getting some assistance one last time before abandoning this project altogether.

I have a data entry for that has various validation text boxes.

I've placed the validation code in the BeforeUpdate Event as suggested, but still no luck.

Ok, here's what I'm working with: Problem number one, I created a data entry form to enter and save data into the related table. Here are the field names for that form.

LA Code
Date Prepped
Box Completed (Yes/No field)
Date_Completed
Date_Incomplete


After all data has been entered, the add record button is click to add the data to the table. However, no data should be added to the table if the entire form has not been filled out.

Example: if data is only entered in LA_Code text box and the rest of fields are blank, and the add record button has been clicked, a message box will appear asking that Date_Prepped be entered or if Date_Prepped has been entered but LA_Code text box is left blank, a message will occur indicating that an LA_Code needs to be entered an so on with the rest of the fields.

The problem I'm having when testing the add record button, if any of those fields are left blank and the add record button is clicked, the message box will appear indicating the other text boxes that needs to be filled out. However, it will still send data to the table. In other words, if data have been entered in the LA_Code text box and the add record button is clicked, although the message box will make reference to the other fields that need to be filled out, that LA_Code number will still show up in the table and the same case with the rest of the fields. I don't want any records showing up in the table if the data entry form had not been filled out correctly.

The following is the add record code:

Private Sub cmdAdd_Click()
'On Error GoTo Err_cmdAdd_Click
If Trim(Me!LA_Code & "") = "" Then
MsgBox "Please enter LA Code", vbInformation
Me!LA_Code.SetFocus
Exit Sub
ElseIf Trim(Me!Date_Prepped & "") = "" Then
MsgBox "Enter Date Prepped", vbInformation
Me!Date_Prepped.SetFocus
Exit Sub
ElseIf Trim(Me!Completed_Box & "") = "" Then
MsgBox "Select Yes or No", vbInformation
Me!Completed_Box.SetFocus
Exit Sub
ElseIf Me!Date_Completed.Enabled Then
If Not IsDate(Me!Date_Completed) Then
MsgBox "Enter a valid completed date", vbInformation
Me!Date_Completed.SetFocus
Exit Sub
End If
ElseIf Not Me!Date_Incomplete.Enabled Then
If Not IsDate(Me!Date_Incomplete) Then
MsgBox "Enter a valid Incompleted date", vbInformation
Me!Date_Incomplete.SetFocus
Exit Sub
End If
Else
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRecordsGoToNew
End If

End Sub DoCmd.RunCommand acCmdRecordsGoToNew
End If


Problem number two is in the Completed_Box Yes/No drop down field. Once the LA Code and Date Prepped text boxes have been filled out, a Yes or No option will be selected from the Completed_Box drop down field.

If Yes has been selected then the Date_Completed label and text box will become visible. Same thing will apply to the Date_Incomplete text box. (Both Date_Completed and Date_Incompleted text boxes are invisible until Yes or No has been selected).

The problem I'm running into when selecting Yes or No from the drop down when the rest of the form haven't been filled out, a blank record seems to show up in the table. I don't want any null or blank or any other records showing up in the table if the other fields haven't been field out.

Does anyone have any suggestions? I'm in need of a little help

The following is the code used in the Completed_Box Yes/No drop drop down field.

Private Sub Completed_Box_Click()

LA_Code.SetFocus

If Completed_Box = "Yes" And Me.LA_Code.Text = "" Then

MsgBox "Please enter LA Code", vbOKOnly
Completed_Box = ""
Date_Completed.Visible = False
Date_Completed_Label.Visible = False
'Date_Completed.SetFocus
' Date_Incomplete.SetFocus
ElseIf Completed_Box = "Yes" And Me.LA_Code.Text <> "" Then
Date_Completed.Visible = False
Date_Completed_Label.Visible = False
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRecordsGoToNew
End If

Date_Prepped.SetFocus
If Completed_Box = "Yes" And Date_Prepped.Text = "" Then

MsgBox "Please enter date", vbOKOnly
Completed_Box = ""
Date_Completed.Visible = False
Date_Completed_Label.Visible = False
ElseIf Completed_Box = "Yes" And Date_Prepped.Text <> "" Then
Date_Completed.Visible = False
Date_Completed_Label.Visible = False
DoCmd.RunCommand acCmdSaveRecord
DoCmd.RunCommand acCmdRecordsGoToNew

End If

LA_Code.SetFocus
If Completed_Box = "No" And LA_Code.Text = "" Then

Completed_Box = ""
MsgBox "Please enter LA Code", vbOKOnly
Date_Completed.Visible = False
'Date_Completed.SetFocus
Date_Completed_Label.Visible = False
ElseIf Completed_Box = "No" And LA_Code.Text <> "" Then
Date_Completed.Visible = False
Date_Completed_Label.Visible = False
End If

Date_Prepped.SetFocus
If Completed_Box = "No" And Date_Prepped.Text = "" Then

Completed_Box = ""
MsgBox "Please enter date", vbOKOnly
Date_Completed.Visible = False
Date_Completed_Label.Visible = False
End If


If Completed_Box = "Yes" Then
Date_Completed.Visible = True
Date_Completed_Label.Visible = True
Date_Incomplete.Visible = False
Date_Completed.SetFocus
ElseIf Completed_Box = "No" Then
Date_Completed.Visible = False
Date_Completed_Label.Visible = False
Date_Incomplete.Visible = True
Label76.Visible = True
Date_Incomplete.SetFocus

End If



End Sub

 

[PHV]

Is your form bound ?
ie, what is the RecordSource property ?

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

 

[HGoodloe]

Yes, it's bound to a table.
Table name: Land_Acquisition

 

[HGoodloe]

Question: If all validations are supposed to be in the BeforeUpdate procedure, what code should be in the add record button? Just as a reminder, after the entire form have been filled out, the add record button is clicked.

Also, I'm getting a set focus error from the code in the BeforeUpdate procedure.