Check for Empty Fields

[Hotwheels59]

Hi all,

I am trying to run code through a form to ensure fields are populated. If the fields are populated, the code needs to allow a series of queries to run. If not, the code should stop the user cold until the fields are populated. Here's the code thus far...

Private Sub Command8_Click()
Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If Nz(ctl.Value, vbNullString) = vbNullString Then
MsgBox "Missing required information including the job code and title." & vbCrLf & "Please fill in all missing information and try again.", vbCritical, "Can't Save"
End If
Case Else
End Select
Next ctl

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendAuthority"
DoCmd.OpenQuery "qryAppendBudget"
DoCmd.OpenQuery "qryAppendCertifications"
MsgBox "New Job Added", vbInformation, "New Job Added"
DoCmd.Close acForm, "frmAddNewJob"
DoCmd.Close acForm, "frmCheckJobCode"
DoCmd.SetWarnings True
End Sub

However, being new to this, the quieries run regardless of the fatal error. I suspect the issue is there needs to be code where the blank line is after "Next ctl." Any help provided is much appreciated.

 

[pbaldy]

Sure, you haven't told it to do otherwise. Try this right after the message box:

Exit Sub

 

[Hotwheels59]

That did it. Thanks much for your reply!

 

[pbaldy]

Happy to help! The frustrating thing about code is that it does exactly what you tell it to do.

 

[Hotwheels59]

Oh, so true! That said, I should have tested this before my reply earlier... If the fields ARE populated, it won't progress to the query string. What am I missing? Thanks again!

 

[pbaldy]

Where exactly did you put it? It should be right after the message box, inside the If/Then block.

 

[Hotwheels59]

Hi Paul,
That's where I put it... Looks like this:
Private Sub Command8_Click()
Dim ctl As Control
For Each ctl In Me.Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox, acListBox, acCheckBox
If Nz(ctl.Value, vbNullString) = vbNullString Then
MsgBox "Missing required information including the job code and title." & vbCrLf & "Please fill in the new job code and the new title and try again.", vbCritical, "Can't Add Job"
Exit Sub
End If
Case Else
End Select
Next ctl
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryAppendAuthority"
DoCmd.OpenQuery "qryAppendBudget"
DoCmd.OpenQuery "qryAppendCertifications"
DoCmd.OpenQuery "qryAppendChemicals"
MsgBox "New Job Added", vbInformation, "New Job Added"
DoCmd.Close acForm, "frmAddNewJob"
DoCmd.Close acForm, "frmCheckJobCode"
DoCmd.SetWarnings True
End Sub

Thanks so much for your interest and help (again)!

 

[pbaldy]

Are you saying that doesn't work? You should only get the message box and exit if one of the controls is empty. If they all have values, your queries should run.

 

[Hotwheels59]

When the controls are populated, the message box appears nonetheless and the queries fail to run... doing what I told it to do, unfortunately.

 

[pbaldy]

Something is Null. Add the control to the message box:

MsgBox "Missing required information including the job code and title." & vbCrLf & "Please fill in the new job code and the new title and try again. " & ctl.Name, vbCritical, "Can't Add Job"

Which will add the offending control's name to the message so you can identify it.

 

[Hotwheels59]

OK... that helped. I discovered what was going on. I have other controls on the same form that were null. The other controls need to be deleted from the form in order to get the code to work. That should do it. Thanks again for your assistance!

 

[pbaldy]

No problem. An alternative is to use the Tag property of the controls. You'd put something in the Tag property of the controls you want tested, like "TestMe". Then in your code instead of the Select/Case structure you'd have:

If ctl.Tag = "TestMe" Then

and then your test.

 

[Hotwheels59]

That'll work even better. Saves some time too. Thanks again, Paul. Much appreciated.

 

[pbaldy]

Happy to help!