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

A

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?
 
allen675 said:
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?
Click to expand...
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
 
cheekybuddha said:
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!)
Click to expand...
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)
 
allen675 said:
Tried this but every option I select from the combobox brings up message box?
Click to expand...
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
 
You must log in or register to reply here.

Similar threads

brucemc777
How to populate a combobox with multiselect filepicker results, then save results?
Replies
3
Views
273
brucemc777
brucemc777
W
Notify the user, without sound
Replies
2
Views
138
woef22
W
D
Solved Clearing Recordset property value in Combobox
Replies
15
Views
174
DalynSoft
D
D
Solved If...then or loop help
Replies
13
Views
366
Pat Hartman
P
C
When accHitTest generates an 'invalid procedure call or argument' error
Replies
4
Views
148
CJ_London
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom