Form data entry issues and Yes/No drop down issues

[HGoodloe]

Here are a couple of problems I'm having with my data entry form and also with the Yes/No drop down field.

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]

I your form is bound then use the BeforeUpdate event procedure to enforce all your validation rules, playing with the Cancel parameter and the SetFocus method.

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

 

[TheAceMan1]

How are ya HGoodloe . . .

In parallel with [blue]PHV[/blue], try the following:
[ol][li]In the tag property of the controls that require data, enter a question mark [blue]?[/blue] [red](no quotations please!)[/red][/LI]
[li]In THE forms [blue]BeforeUpdate[/blue] event, copy/paste the following:

[blue]   Dim ctl As Control
   Dim Msg As String, Style As Integer, Title As String, DL As String
   
   DL = vbNewLine & vbNewLine
   
   For Each ctl In Me.Controls
      If ctl.Tag = "?" Then
         If Trim(ctl & "") = "" Then
            Msg = "All fields are required before a record can be saved!" & DL & _
                  "'" & ctl.Name & "' has no data!" & DL & _
                  "You'll have to go back and enter approriate data! . . ."
            Style = vbInformation + vbOKOnly
            Title = "Required Data Missing! . . ."
            MsgBox Title, Style, Title
            Me(ctl.Name).SetFocus
            Cancel = True
            Exit For
         End If
      End If
   Next[/blue]

[/li][/ol]
You need to disable or remove any other code to handle this to prevent interaction!

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

See Ya! . . . . . .

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

 

[HGoodloe]

Your suggestion seems to be working to a certain point. I am a bit unclear about the ctl control. Am I supposed to replace the ctl you suggested with the actual text box controls on my data entry form?

If you could clarify a little more concerning the code that you suggested I put in the BeforeUpdate event, that may be a little more helpful. Perhaps maybe you could also use part of my code as an example for what you were suggesting in regards to your ctl control code.

There were also some setfocus issues when using the code you suggested in the beforeUpdate Event. I believe everything will workout ok, it's just that I need you to clarify a little more and use more examples and as I suggested maybe including the code I provided as part of your examples.

Thank you very much.

 

[TheAceMan1]

HGoodloe . . .

I didn't mention it, but you need to disable or remove all your other code to handle this, to prevent interaction!

Make it so and test again!

The code should stop on every field requiring data until all fields are complete . . .

See Ya! . . . . . .

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

 

[HGoodloe]

Hello, the code you suggested I put in the BeforeUpdate event still doesn't appear to be working and that also includes disabling all other code as was also suggested.
I'm still getting a setfocus error relative to both the Date_Completed field and Date_Incomplete Field.