If combobox = "value" or "value" then........

allen675

Member
Local time
Today, 04:01
Joined
Jul 13, 2022
Messages
124
Hello,

Hopefully a simple one! How do I make this work please?:

Code:
If Me.ClientStatus.Value = "NPW - No Contact" Or "NPW - Gone Elsewhere" Then
 
Try:

If Me.Combobox = Value1 Or Me.Combobox = Value2 Then
 
You have to repeat the Me.ClientStatus

If Me.ClientStatus.Value = "NPW - No Contact" Or Me.ClientStatus.Value = "NPW - Gone Elsewhere" Then
 
Thanks guys but it must be me and VBA it doesnt work :-(

Proceeding message box still displays with "NPW - No Contact" however nothing happens when selecting "NPW - Gone Elsewhere"

Any ideas?
 
Thanks guys but it must be me and VBA it doesnt work :-(

Proceeding message box still displays with "NPW - No Contact" however nothing happens when selecting "NPW - Gone Elsewhere"

Any ideas?
Show your actual code
 
Code:
Private Sub ClientStatus_Change()
    If Not Me.ClientStatus.Value = "NPW - No Contact" Or Me.ClientStatus.Value = "NPW - Gone Elsewhere" Then
    Exit Sub
    Else
    If Me.ClientStatus.Value = "NPW - No Contact" Or Me.ClientStatus.Value = "NPW - Gone Elsewhere" Then
    If MsgBox("Would you like to send a refund request for this lead?", vbQuestion + vbYesNo + vbDefaultButton1, "Request refund?") = vbYes Then
 
You are missing brackets to evaluate the conditions together since you are using 'Not'
Code:
' ...
    If Not (Me.ClientStatus.Value = "NPW - No Contact" Or Me.ClientStatus.Value = "NPW - Gone Elsewhere") Then
'...
 
Why not simplify:
Code:
Private Sub ClientStatus_Change()
    If Me.ClientStatus.Value = "NPW - No Contact" Or Me.ClientStatus.Value = "NPW - Gone Elsewhere" Then
        If MsgBox("Would you like to send a refund request for this lead?", vbQuestion + vbYesNo + vbDefaultButton1, "Request refund?") = vbYes Then
' ...
(would be more useful if you had posted the whole procedure code!)
 
@cheekybuddha and as if by magic, thank you. This is such a steep learning curve and something as simple as some brackets can make all the difference!

Cheers
 
Why not simplify:
Code:
Private Sub ClientStatus_Change()
    If Me.ClientStatus.Value = "NPW - No Contact" Or Me.ClientStatus.Value = "NPW - Gone Elsewhere" Then
        If MsgBox("Would you like to send a refund request for this lead?", vbQuestion + vbYesNo + vbDefaultButton1, "Request refund?") = vbYes Then
' ...
(would be more useful if you had posted the whole procedure code!)
Tried this but every option I select from the combobox brings up message box?
 
Does this mean anything to you? This is under RowSource in property sheet for combo

Code:
SELECT [ClientStatusQ].[ClientStatus], [ClientStatusQ].[ClientStatusID], [ClientStatusQ].[CustomerID] FROM ClientStatusQ ORDER BY [ClientStatus];
 
Also, which is the value of the 'Bound Column' property?

(also in the Data tab on the combo's property sheet)
 
Tried this but every option I select from the combobox brings up message box?
post the whole Code.
if everything you select from your combo brings the msgbox, there is wrong on your code.
 
Code:
Private Sub ClientStatus_Change()
    If Not (Me.ClientStatus.Value = "NPW - No Contact" Or Me.ClientStatus.Value = "NPW - Gone Elsewhere" Or Me.ClientStatus.Value = "NPW - Unable to Place") Then
    Exit Sub
    Else
    If Me.ClientStatus.Value = "NPW - No Contact" Or Me.ClientStatus.Value = "NPW - Gone Elsewhere" Or Me.ClientStatus.Value = "NPW - Unable to Place" Then
    If MsgBox("Would you like to send a refund request for this lead?", vbQuestion + vbYesNo + vbDefaultButton1, "Request refund?") = vbYes Then
    
Me.Refresh

Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim strSQL As String
Dim clientRST As Variant
Dim salesRST As Variant
Dim strTable As String
Dim i As Variant
Dim strPaths() As String

    
strSQL = "SELECT [CustomerID], [Broker], [Lead_Date], [Client_FN], [Client_SN], [Email_Address], [Mobile_No], [Email_Sent], [SMS/WhatsApp_Sent], [Phone_Call_#1], [Phone_Call_#2], [Phone_Call_#3] FROM Client" _
& " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
Set clientRST = CurrentDb.OpenRecordset(strSQL)

Do While Not clientRST.EOF
    Set appOutlook = CreateObject("Outlook.application")
    Set MailOutlook = appOutlook.CreateItemFromTemplate(Application.CurrentProject.Path & "\RefundRequest.oft")

    strSQL = "SELECT NoteDate, Note" _
              & " FROM NoteHistory" _
              & " WHERE CustomerID = " & clientRST!CustomerID
    Set salesRST = CurrentDb.OpenRecordset(strSQL)

    ' TABLE COLUMNS
    strTable = "<table><th>"
    For i = 0 To salesRST.Fields.Count - 1
        strTable = strTable & "<td>" & "</td>"
    Next i
    strTable = strTable & "</th>"

    ' TABLE ROWS
    salesRST.MoveFirst
    While Not salesRST.EOF
        strTable = strTable & "<tr>"
        For i = 1 To salesRST.Fields.Count
            strTable = strTable & "<td>" & salesRST.Fields(i - 1).Value & "</td>"
        Next i
        strTable = strTable & "</tr>"
        salesRST.MoveNext
    Wend
    strTable = strTable & "</table>"
    salesRST.Close
    
strSQL = "SELECT [FileName] FROM ContactProofT" _
& " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
strPaths = Split(SimpleCSV(strSQL), ",")


    With MailOutlook
        .To = "test@test.com"
        .subject = "Refund Request"
        Dim x As Long
        For x = 0 To UBound(strPaths)
        .Attachments.Add CurrentProject.Path & "\ContactProofs\" & strPaths(x)
        Next
        

        ' REPLACE PLACEHOLDERS
        .HTMLBody = Replace(.HTMLBody, "%date%", clientRST![Lead_Date])
        .HTMLBody = Replace(.HTMLBody, "%first%", clientRST![Client_FN])
        .HTMLBody = Replace(.HTMLBody, "%surname%", clientRST![Client_SN])
        .HTMLBody = Replace(.HTMLBody, "%mobile%", clientRST![Mobile_No])
        .HTMLBody = Replace(.HTMLBody, "%email%", clientRST![Email_Address])
        .HTMLBody = Replace(.HTMLBody, "%Call1%", clientRST![Phone_Call_#1])
        .HTMLBody = Replace(.HTMLBody, "%Call2%", clientRST![Phone_Call_#2])
        .HTMLBody = Replace(.HTMLBody, "%Call3%", clientRST![Phone_Call_#3])
        .HTMLBody = Replace(.HTMLBody, "%unsuccessful%", clientRST!Email_Sent)
        .HTMLBody = Replace(.HTMLBody, "%message%", clientRST![SMS/WhatsApp_Sent])
        .HTMLBody = Replace(.HTMLBody, "%Broker%", clientRST![Broker])
              

        ' ADD SALES TABLE
        .HTMLBody = Replace(.HTMLBody, "%Notes%", strTable)

        .Display
    
    End With


    Set MailOutlook = Nothing
    clientRST.MoveNext
Loop
DoCmd.Close acForm, "CopyExistingLeadF"
    End If
    
    
    Else
    If MsgBox("Would you like to send a refund request for this lead?", vbQuestion + vbYesNo + vbDefaultButton1, "Request refund?") = vbNo Then
    End If
    End If
    End If
End Sub

The above code will seem familiar to @theDBguy 😂
 
i am not familiar, but i will try:
Code:
Private Sub ClientStatus_Change()
Dim sStatus As String
sStatus = Me!ClientStatus & ""
If sStatus <> "NPW - No Contact" And sStatus <> "NPW - Gone Elsewhere" And sStatus <> "NPW - Unable to Place" Then
    Exit Sub
End If
If MsgBox("Would you like to send a refund request for this lead?", vbQuestion + vbYesNo + vbDefaultButton1, "Request refund?") = vbNo Then
    Exit Sub
End If
    
Me.Refresh

Dim appOutlook As Outlook.Application
Dim MailOutlook As Outlook.MailItem
Dim strSQL As String
Dim clientRST As Variant
Dim salesRST As Variant
Dim strTable As String
Dim i As Variant
Dim strPaths() As String


strSQL = "SELECT [CustomerID], [Broker], [Lead_Date], [Client_FN], [Client_SN], [Email_Address], [Mobile_No], [Email_Sent], [SMS/WhatsApp_Sent], [Phone_Call_#1], [Phone_Call_#2], [Phone_Call_#3] FROM Client" _
            & " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
Set clientRST = CurrentDb.OpenRecordset(strSQL)

Do While Not clientRST.EOF
    Set appOutlook = CreateObject("Outlook.application")
    Set MailOutlook = appOutlook.CreateItemFromTemplate(Application.CurrentProject.Path & "\RefundRequest.oft")
    
    strSQL = "SELECT NoteDate, Note" _
    & " FROM NoteHistory" _
    & " WHERE CustomerID = " & clientRST!CustomerID
    Set salesRST = CurrentDb.OpenRecordset(strSQL)
    
    ' TABLE COLUMNS
    strTable = "<table><th>"
    For i = 0 To salesRST.Fields.Count - 1
        strTable = strTable & "<td>" & "</td>"
    Next i
    strTable = strTable & "</th>"
    
    ' TABLE ROWS
    salesRST.MoveFirst
    While Not salesRST.EOF
        strTable = strTable & "<tr>"
        For i = 1 To salesRST.Fields.Count
            strTable = strTable & "<td>" & salesRST.Fields(i - 1).Value & "</td>"
        Next i
        strTable = strTable & "</tr>"
        salesRST.MoveNext
    Wend
    strTable = strTable & "</table>"
    salesRST.Close
    
    strSQL = "SELECT [FileName] FROM ContactProofT" _
    & " WHERE CustomerID = " & Forms!CopyExistingLeadF!CustomerID
    strPaths = Split(SimpleCSV(strSQL), ",")
    
    
    With MailOutlook
        .To = "test@test.com"
        .subject = "Refund Request"
        Dim x As Long
        For x = 0 To UBound(strPaths)
            .Attachments.Add CurrentProject.Path & "\ContactProofs\" & strPaths(x)
        Next
        
            
        ' REPLACE PLACEHOLDERS
        .HTMLBody = Replace(.HTMLBody, "%date%", clientRST![Lead_Date])
        .HTMLBody = Replace(.HTMLBody, "%first%", clientRST![Client_FN])
        .HTMLBody = Replace(.HTMLBody, "%surname%", clientRST![Client_SN])
        .HTMLBody = Replace(.HTMLBody, "%mobile%", clientRST![Mobile_No])
        .HTMLBody = Replace(.HTMLBody, "%email%", clientRST![Email_Address])
        .HTMLBody = Replace(.HTMLBody, "%Call1%", clientRST![Phone_Call_#1])
        .HTMLBody = Replace(.HTMLBody, "%Call2%", clientRST![Phone_Call_#2])
        .HTMLBody = Replace(.HTMLBody, "%Call3%", clientRST![Phone_Call_#3])
        .HTMLBody = Replace(.HTMLBody, "%unsuccessful%", clientRST!Email_Sent)
        .HTMLBody = Replace(.HTMLBody, "%message%", clientRST![SMS/WhatsApp_Sent])
        .HTMLBody = Replace(.HTMLBody, "%Broker%", clientRST![Broker])
        
        
        ' ADD SALES TABLE
        .HTMLBody = Replace(.HTMLBody, "%Notes%", strTable)
        
        .Display
    
    End With
    
    
    Set MailOutlook = Nothing
    clientRST.MoveNext
Loop
clientRST.Close
Set clientRST = Nothing
DoCmd.Close acForm, "CopyExistingLeadF"

End Sub
 

Users who are viewing this thread

Back
Top Bottom