If Then Statement or Dlookup? (1 Viewer)

Samantha · Aug 17, 2019

I have a form called frmProposalDetails in which I can click a button and another form opens allowing me to pass the JobNumber on that form to another table called tblWOH. This allows us to add an approved job to be scheduled and so on. This has grown over time most recently adding send email function for anything that is tax exempt which begins the source of my problem. It used to not be a big deal if I attempted to add a duplicate to tblWOH - it wouldn't notify me and no harm no foul. Now that I have added this email function it will produce another email being duplicate so I am trying to end it before it gets to that point.
Which brings me to this portion:

Code:

If ((JobStatus) = "1" or "2" or "3" or "4") Then
MsgBox "This job is already on WOH.", vbOKOnly, "Error"
Else
GoTo cmdAddWOH_Click_Exit
End If

Now either way it tells me the job is already there. I am sure this is an amateur mistake as I am. Please guide me

I'm lost!

Here is the full run of code:

Code:

'------------------------------------------------------------
' cmdAddWOH_Click
'
'------------------------------------------------------------
Private Sub cmdAddWOH_Click()
'On Error GoTo cmdAddWOH_Click_Err

Dim PMEmail As String
Dim PMName As String
Dim Signature As String
Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim rs As DAO.Recordset
Set appOutlook = CreateObject("Outlook.Application")
Set MailOutlook = appOutlook.CreateItem(olMailItem)

'Signature = MailOutlook.Body

If ((JobStatus) = Null) Then
MsgBox "This job is already on WOH.", vbOKOnly, "Error"
Else
GoTo cmdAddWOH_Click_Exit
End If


   'set PMEmail and Name
        If ((Manager) = "MEG") Then
            PMEmail = "m@somewhere.net"
            PMName = "Marc"
        ElseIf ((Manager) = "MLM") Then
            PMEmail = "m@somewhere.net"
            PMName = "Matt"
        ElseIf ((Manager) = "EA") Then
            PMEmail = "e@somewhere.net"
            PMName = "Eduardo"
               End If
'openWOH form
DoCmd.OpenForm "frmAddWOH", acNormal, "", "", , acNormal
Forms!frmAddWOH!JobNumber = Me.JobNumber
    
    If Me.Tax_Exempt = -1 Then
    With MailOutlook
    .To = "j@somewhere" & ";" & "s@somewhere" & ";" & PMEmail
    .Subject = JobNumber & "- Tax Exempt Project"
    .Body = "Hi Judy and " & PMName & "," & vbCrLf & vbCrLf & "I am notifying you that I've added this job to WOH and the property is tax exempt. Please consider what vendors you are using as purchases must be coordinated." & vbCrLf & ([tblProposals.JobNumber] & " - " & [tblProposals.Company] & " - " & [tblProposals.ServiceAddress] + " - " & [tblProposals.ProjectDescription]) & vbCrLf & vbCrLf & "This message is being automatically generated by Access!" & vbNewLine & vbCrLf & "Thanks," & vbCrLf & "Samantha"
    .Save
    .Close olSave
End With
  End If
    

cmdAddWOH_Click_Exit:
    Exit Sub

cmdAddWOH_Click_Err:
    MsgBox Error$
    Resume cmdAddWOH_Click_Exit

End Sub

theDBguy · Aug 17, 2019

Hi. Using something like:

Code:

If ((JobStatus) = "1" or "2" or "3" or "4") Then

is probably not doing what you thought it's supposed to do. When you use an OR operator, you'll have to apply it to a complete expression. For example, try using it this way:

Code:

If [JobStatus] = "1" or [JobStatus] = "2" or [JobStatus] = "3" or [JobStatus] = "4" Then

The_Doc_Man · Aug 17, 2019

I'm not sure the syntax you listed is correct for a chain of comparisons.

Try:

Code:

If ( (JobStatus >= "1" ) AND (JobStatus <= "4") ) Then

arnelgp · Aug 18, 2019

on this part of the code, you should exit the sub before the e-mail is created.
you can't compare anything to null, you passed it to IsNull() function to test it.
and you don't create the Outlook mail until you have validated it first:

Code:

'Signature = MailOutlook.Body

If Trim([JobStatus] & "") Like "[1-4]" Then
    MsgBox "This job is already on WOH.", vbOKOnly, "Error"
    GoTo cmdAddWOH_Click_Exit
End If

full code:

Code:

'------------------------------------------------------------
' cmdAddWOH_Click
'
'------------------------------------------------------------
Private Sub cmdAddWOH_Click()
'On Error GoTo cmdAddWOH_Click_Err

Dim PMEmail As String
Dim PMName As String
Dim Signature As String
Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim rs As DAO.Recordset

If Trim([JobStatus] & "") Like "[1-4]" Then
	MsgBox "This job is already on WOH.", vbOKOnly, "Error"
	GoTo cmdAddWOH_Click_Exit
End If

'Signature = MailOutlook.Body
'set PMEmail and Name
If ([Manager] = "MEG") Then
	PMEmail = "m@somewhere.net"
	PMName = "Marc"
ElseIf ([Manager] = "MLM") Then
	PMEmail = "m@somewhere.net"
	PMName = "Matt"
ElseIf ([Manager] = "EA") Then
	PMEmail = "e@somewhere.net"
	PMName = "Eduardo"
End If
'openWOH form
DoCmd.OpenForm "frmAddWOH", acNormal, "", "", , acNormal
Forms!frmAddWOH!JobNumber = Me.JobNumber
    
If Me.Tax_Exempt = -1 Then
	Set appOutlook = CreateObject("Outlook.Application")
    	Set MailOutlook = appOutlook.CreateItem(olMailItem)
    
	With MailOutlook
		.To = "j@somewhere" & ";" & "s@somewhere" & ";" & PMEmail
		.Subject = [JobNumber] & "- Tax Exempt Project"
		.Body = "Hi Judy and " & PMName & "," & vbCrLf & vbCrLf & "I am notifying you that I've added this job to WOH and the property is tax exempt. Please consider what vendors you are using as purchases must be coordinated." & vbCrLf & ([tblProposals.JobNumber] & " - " & [tblProposals.Company] & " - " & [tblProposals.ServiceAddress] + " - " & [tblProposals.ProjectDescription]) & vbCrLf & vbCrLf & "This message is being automatically generated by Access!" & vbNewLine & vbCrLf & "Thanks," & vbCrLf & "Samantha"
		.Save
		.Close olSave
	End With
End If
    
cmdAddWOH_Click_Exit:
	Set MailOutlook = Nothing
	Set appOutlook = Nothing
    	Exit Sub

cmdAddWOH_Click_Err:
    	MsgBox Error$
    	Resume cmdAddWOH_Click_Exit

End Sub

If Then Statement or Dlookup? (1 Viewer)

Samantha

still learning...

theDBguy

I’m here to help

The_Doc_Man

Immoderate Moderator

arnelgp

..forever waiting... waiting for jellybean!

Similar threads

Users who are viewing this thread