emailing when required fields aren't filled in

[teiben]

Can someone help me out here, I have db a few w/required fields and a command button that sents out a simple email msg from a module. Problem is users are clicking the email button when all the fields aren't being filled in. How would I code it so this doesn't occur?

This is my code on the command button
OpenEmailRequestFraser

which opens

Option Compare Database
Option Explicit

Sub OpenEmailRequestFraser()
Dim rsEmail As DAO.Recordset
Dim strEmail As String

Set rsEmail = CurrentDb.OpenRecordset("tblEmailRequestFraser") 'table where email is
strEmail = rsEmail.Fields("dEmailAddress").Value
rsEmail.MoveNext
Do While Not rsEmail.EOF
strEmail = strEmail & " ; " & rsEmail.Fields("dEmailAddress").Value
rsEmail.MoveNext
Loop

DoCmd.SendObject , , , strEmail, , , "Maintenance Work Request", "Maintenance request Number #" _
& " " & Forms!frmRequestFraser.RequestNo & " " & "has been entered into the system by" _
& " " & Forms!frmRequestFraser.RequestedBy & " on " & " " & Format(Forms!frmRequestFraser.DateSubmitted, _
"dddd mmm d, yyyy") & " at" & " " & Format(Forms!frmRequestFraser.TimeIn, "hh:mm AMPM") & _
" " & "which is due for completion" & " " & Format(Forms!frmRequestFraser.CompletionRequestDate, "dddd mmm d, yyyy") _
& "." & vbCrLf & "The Suspect problem is: " & Forms!frmRequestFraser.Combo22.Column(1) & " , " & "Equipment Location" & _
Forms!frmRequestFraser.LocationEquipment & ", " & Forms!frmRequestFraser.SuspectedProblem & ", ", False, ""

' _put a comma after the machine type, there was a " ", second line from the button

Set rsEmail = Nothing
MsgBox "Email notifications have been sent"
End Sub

This works fine!
Private Sub Form_BeforeUpdate(Cancel As Integer)
'ea of 3 controls under properties: All: Tags set to Required

I tried using the line, borrowed from another db, it was placed right after OpenEmailRequestFraser, but the messages still get sent


DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70


Dim blnContinue As Boolean
Dim ctl As Control
blnContinue = True
For Each ctl In Me.Controls
If ctl.Tag = "Required" Then
If IsNull(ctl) Then
MsgBox "Field(s)indicated in red must contain data. ", vbCritical + vbOKOnly + vbDefaultButton1, "Required Information Missing"

Cancel = True
ctl.SetFocus
Exit For
End If
End If
Next ctl
Set ctl = Nothing
End Sub

 

[pbaldy]

Add

Exit Sub

if the test fails so code doesn't continue down to the email sending part.

 

[teiben]

clarification

sorry to be stupid, but where would Exit Sub get placed at?

 

[pbaldy]

Right after a failed test, so here if you use that code:

If IsNull(ctl) Then

 

[teiben]

I thought it was working, but it's not...this is my code on a command button:

The ctl.Tag = "Required" on a the combobox, does anyone think it make a difference?


Private Sub cmdSendEmail_Click()
'ea of 3 controls under properties: All: Tags set to Required

Dim blnContinue As Boolean
Dim ctl As Control
blnContinue = True
For Each ctl In Me.Controls
If ctl.Tag = "Required" Then
If IsNull(ctl) Then
MsgBox "Field(s)indicated in red must contain data. ", vbCritical + vbOKOnly + vbDefaultButton1, "Required Information Missing"
Exit Sub
'Cancel = True
ctl.SetFocus
Exit For
End If
End If
Next ctl
Set ctl = Nothing

OpenEmailRequestFraser


End Sub

 

[pbaldy]

Define "not working". Using the tag property is a common way to only check certain controls, and shouldn't cause a problem.

 

[teiben]

Sorry, the email messages are being sent with missing information

 

[pbaldy]

Are you getting the message box?

You might change your test to this, so you also test for ZLS:

If Nz(ctl, "") = "" Then

 

[teiben]

I replaced the if isnull(ctl) then line w/ if nz(ctl,"") = "" then
and the email still gets sent, seems there has to be a way to stop the command on click event to validate

 

[pbaldy]

You didn't say whether you get the message box. That will tell us if the code is getting where we think it should.

 

[teiben]

The message box, is the one in my code:
MsgBox "Email notifications have been sent", not sure if that's what you meant

 

[pbaldy]

I'm asking if this message box fires, which would indicate that the code is executing through that section:

MsgBox "Field(s)indicated in red must contain data. ", vbCritical + vbOKOnly + vbDefaultButton1, "Required Information Missing"

 

[teiben]

yes it fires!

 

[pbaldy]

That's very strange then, since the next line is the Exit Sub. That should kick it out and it should never get down to the email code. Can you post a sample db?

 

[teiben]

I can Monday, its a work thing; I thought the exit sub would stop it, then I tried the docmd.cancel event. thank you