Type mismatch (3 Viewers)

M

Momma

Member
Local time
Today, 17:21
Joined
Jan 22, 2022
Messages
127
Hi everyone
I'm getting a run-time error 13 Type mismatch on line 60 of my vba code.
I'm very confused and not sure what is causing the error.
Any help will be highly appreciated.


Code: 
Private Sub Form_Load()
ContactList.RowSource = ""
    Dim SearchSQL As String
    
    SearchSQL = "SELECT tblContacts.ContactID, tblContacts.FirstName, tblContacts.Email, tblContacts.Followup " & _
        "From tblContacts " & _
        "WHERE (((tblContacts.FollowUp) = True)) " & _
        "ORDER BY tblContacts.FirstName;"

        ContactList.RowSource = SearchSQL
End Sub

Private Sub SendTheEmailSMTP_Click()

          Dim MResponse As Integer
          Dim msgBody As String
          Dim strSQL As String
          Dim Contact As String
          Dim ContactID As Long
          Dim db As Database
          Dim i As Long
          Dim strSubject As String
          Dim msgTo As String
          Dim msgFrom As String
          Dim msgReplyTo As String
          Dim msgSubject As String
          
10         If Forms!frmContactEmails!ContactList.ItemsSelected.Count < 1 Then
20            Exit Sub
30        End If
          
40        For i = 0 To Forms!frmContactEmails!ContactList.ListCount - 1
              
50        Contact = Forms!frmContactEmails!ContactList.Column(1, i)
60        ContactID = Forms!frmContactEmails!ContactList.Column(0, i)
70        msgFrom = "houseofpaws@redrobiena.com.au"
80        msgTo = Forms!frmContactEmails!ContactList.Column(2, i)
90        msgReplyTo = "houseofpaws@redrobiena.com.au"
100       msgBody = "Dear " & Forms!frmContactEmails!ContactList.Column(1, i) & "<br><br>" _
 
Code: 
Dim ContactID As Long
...
ContactID = Forms!frmContactEmails!ContactList.Column(0, i)

You are trying to assign ContactID, which is a Long, another variable's value. That variable is not a Long, nor one that converts to a long. You can find out what type it is by using the VarType function.
 
plog said:
Code: 
Dim ContactID As Long
...
ContactID = Forms!frmContactEmails!ContactList.Column(0, i)

You are trying to assign ContactID, which is a Long, another variable's value. That variable is not a Long, nor one that converts to a long. You can find out what type it is by using the VarType function.
Click to expand...
Thank you for your quick reply. Can you give me an example how to test it, please?
 
Momma said:
Forms!frmContactEmails!ContactList.Column(0, i)
Click to expand...

I'm just guessing here, but I'm thinking that a contact list's 0th column is going to be text, like perhaps a name? ContactID is clearly DIM'd as LONG. Text names don't map well to LONG integers. Unless that contact list is based on a table that has a PK in its first column, that would be your type mismatch. According to this article,

VarType Function - Microsoft Support

support.microsoft.com support.microsoft.com

A VarType of 0 means "uninitialized" - which because of the type mismatch in that context, makes sense. A DIM statement doesn't usually set a value in a variable - though if it is a CONST statement, it might do so. You don't have a CONST in that context so that means you have allocated space for ContactID but have not yet put anything it - because of the run-time error.
 
The_Doc_Man said:
I'm just guessing here, but I'm thinking that a contact list's 0th column is going to be text, like perhaps a name? ContactID is clearly DIM'd as LONG. Text names don't map well to LONG integers. Unless that contact list is based on a table that has a PK in its first column, that would be your type mismatch. According to this article,

VarType Function - Microsoft Support

support.microsoft.com support.microsoft.com

A VarType of 0 means "uninitialized" - which because of the type mismatch in that context, makes sense. A DIM statement doesn't usually set a value in a variable - though if it is a CONST statement, it might do so. You don't have a CONST in that context so that means you have allocated space for ContactID but have not yet put anything it - because of the run-time error.
Click to expand...
Column 0 in my listbox is ContactID which is LONG. I'm using the exact structure on a different form without an issue.
I removed the ContactID line using the debugger and it returns the fieldname, which is FirstName, in line 50 instead of the value. The same with line 80, which is EmailAddress, instead of the actual value. That's what's happening with the ContactID as well, which then returns a run-time error.
The Contact and EmailAddress fields are in Columns 1 and 2 of the listbox.
Do I miss something after it enters the loop (Row 40) ?
 
When this error occurs, if you are set up to break on errors, it should stop and highlight the offending line. At that time, if you hover the mouse over each variable you will see the current value of that variable. I would be interested to know what value is in "i" when that error occurs.
 
The_Doc_Man said:
When this error occurs, if you are set up to break on errors, it should stop and highlight the offending line. At that time, if you hover the mouse over each variable you will see the current value of that variable. I would be interested to know what value is in "i" when that error occurs.
Click to expand...
"i" = 0, which tells me that it doesn't see the highlighted record in the listbox
 
I would never rely on data previously loaded into ListBox columns to perform an operation that matters. A ListBox is for selection in the UI. The format of the data it contains, if it contains anything, will all be strings. This is prone to error, as you are finding, and subject to change without notice or type-checking.

If you know the ID of the row, which selection in the List yields, then use that to open a Recordset that returns the exact data you need. Then use the Recordset, its named Fields collection, and its strongly-typed field data, to perform your operation that matters.

IMO.
 
Both the index and row number are 0-based, so at least you are looking for something that is inside the table's range. If you wanted to see which record was selected, use the combo box's .ListIndex property.

learn.microsoft.com

ComboBox.ListIndex property (Access)

Office VBA reference topic
learn.microsoft.com
 
The_Doc_Man said:
Both the index and row number are 0-based, so at least you are looking for something that is inside the table's range. If you wanted to see which record was selected, use the combo box's .ListIndex property.

learn.microsoft.com

ComboBox.ListIndex property (Access)

Office VBA reference topic
learn.microsoft.com
Click to expand...
So what you suggest is I should use a combo box instead of a list box. I want to be able to select multiple records which I understand can only be done with a list box.
What do you suggest as an alternative?
 
remove your For..Next Loop and replace it with this code:
Code: 
Private Sub SendTheEmailSMTP_Click()

          Dim MResponse As Integer
          Dim msgBody As String
          Dim strSQL As String
          Dim Contact As String
          Dim ContactID As Long
          Dim db As Database
          Dim i As Long
          Dim strSubject As String
          Dim msgTo As String
          Dim msgFrom As String
          Dim msgReplyTo As String
          Dim msgSubject As String
          
10         If Forms!frmContactEmails!ContactList.ItemsSelected.Count < 1 Then
20            Exit Sub
30        End If
          
40        i = Forms!frmContactEmails!ContactList.ListIndex
          
50        Contact = Forms!frmContactEmails!ContactList.Column(1, i)
60        ContactID = Forms!frmContactEmails!ContactList.Column(0, i)
70        msgFrom = "houseofpaws@redrobiena.com.au"
80        msgTo = Forms!frmContactEmails!ContactList.Column(2, i)
90        msgReplyTo = "houseofpaws@redrobiena.com.au"
100       msgBody = "Dear " & Forms!frmContactEmails!ContactList.Column(1, i) & "<br><br>" _
 
if your listbox is Multi-select listbox:
Code: 
Private Sub SendTheEmailSMTP_Click()

          Dim MResponse As Integer
          Dim msgBody As String
          Dim strSQL As String
          Dim Contact As String
          Dim ContactID As Long
          Dim db As Database
          Dim i As Long
          Dim strSubject As String
          Dim msgTo As String
          Dim msgFrom As String
          Dim msgReplyTo As String
          Dim msgSubject As String
          
10         If Forms!frmContactEmails!ContactList.ItemsSelected.Count < 1 Then
20            Exit Sub
30        End If
          
          Dim var
          For Each var In Forms!frmContactEmails!ContactList.ItemsSelected
          
50        Contact = Forms!frmContactEmails!ContactList.Column(1, var)
60        ContactID = Forms!frmContactEmails!ContactList.Column(0, var)
70        msgFrom = "houseofpaws@redrobiena.com.au"
80        msgTo = Forms!frmContactEmails!ContactList.Column(2, var)
90        msgReplyTo = "houseofpaws@redrobiena.com.au"
100       msgBody = "Dear " & Forms!frmContactEmails!ContactList.Column(1, var) & "<br><br>" _
 
arnelgp said:
remove your For..Next Loop and replace it with this code:
Code: 
Private Sub SendTheEmailSMTP_Click()

          Dim MResponse As Integer
          Dim msgBody As String
          Dim strSQL As String
          Dim Contact As String
          Dim ContactID As Long
          Dim db As Database
          Dim i As Long
          Dim strSubject As String
          Dim msgTo As String
          Dim msgFrom As String
          Dim msgReplyTo As String
          Dim msgSubject As String
         
10         If Forms!frmContactEmails!ContactList.ItemsSelected.Count < 1 Then
20            Exit Sub
30        End If
         
40        i = Forms!frmContactEmails!ContactList.ListIndex
         
50        Contact = Forms!frmContactEmails!ContactList.Column(1, i)
60        ContactID = Forms!frmContactEmails!ContactList.Column(0, i)
70        msgFrom = "houseofpaws@redrobiena.com.au"
80        msgTo = Forms!frmContactEmails!ContactList.Column(2, i)
90        msgReplyTo = "houseofpaws@redrobiena.com.au"
100       msgBody = "Dear " & Forms!frmContactEmails!ContactList.Column(1, i) & "<br><br>" _
Click to expand...
Thank you for your reply, ArnelGP. I get the same error without the For...Next Loop. I also want to be able to send the email to multiple clients.
 
Momma said:
Thank you for your reply, ArnelGP. I get the same error without the For...Next Loop. I also want to be able to send the email to multiple clients.
Click to expand...
Sorry, I did not see your last reply. I'll try that out now!
 
You must log in or register to reply here.

Similar threads

M
Hyperlink in Email
Replies
1
Views
399
Edgar_
E
M
Solved Invalid use of Null
Replies
5
Views
490
Momma
M
M
Solved Creating individual reports and send to multiple customers via email
Replies
13
Views
1,366
Momma
M
mike60smart
Insert
Replies
3
Views
434
Pat Hartman
P
R
data type mismatch at query, that should supply data to form
2
Replies
23
Views
1,135
ReginaF
R

Users who are viewing this thread

Total: 4 (members: 1, guests: 3)
Back
Top Bottom