Access 2000 Please Help "Runtime error 13, type mismatch error" When using MyDB.OpenRecordset

[staverts]

Hello all, I am using MS Access 2000, I Found some code to look through a
tables recordset, and send an email to all users in that feild. The Code
errors on this line:

Set MyRS = MyDB.OpenRecordset _
("tblSMSCustomerStorage")

I alredy added DAO 3.6, but it still errors. This was orginally Access 97
Code, Can someone please help me

Here is the full Code:


Private Sub Command10_Click()

Dim MyDB As Database
Dim MyRS As Recordset
Dim objOutlook As Outlook.Application
Dim objOutlookMsg As Outlook.MailItem
Dim objOutlookRecip As Outlook.Recipient
Dim objOutlookAttach As Outlook.Attachment
Dim TheAddress As String

Set MyDB = CurrentDb()
Set MyRS = MyDB.OpenRecordset _
("tblSMSCustomerStorage")

MyRS.MoveFirst

' Create the Outlook session.
Set objOutlook = CreateObject("Outlook.Application")

Do Until MyRS.EOF
' Create the e-mail message.
Set objOutlookMsg = objOutlook.CreateItem(olMailItem)
TheAddress = MyRS![kuemail]

With objOutlookMsg
' Add the To recipient(s) to the e-mail message.
Set objOutlookRecip = .Recipients.Add(TheAddress)
objOutlookRecip.Type = olTo

' Add the CC recipient(s) to the e-mail message.
If (IsNull(Forms!frmMail!CCAddress)) Then
Else
Set objOutlookRecip = .Recipients.Add(Forms!frmMail!CCAddress)
objOutlookRecip.Type = olCC
End If

' Set the Subject, Body, and Importance of the e-mail message.
.Subject = Forms!frmMail!Subject
.Body = Forms!frmMail!MainText
.Importance = olImportanceHigh 'High importance

'Add attachments to the e-mail message.
If Not IsMissing(AttachmentPath) Then
Set objOutlookAttach = .Attachments.Add(AttachmentPath)
End If

' Resolve each Recipient's name.
For Each objOutlookRecip In .Recipients
objOutlookRecip.Resolve
If Not objOutlookRecip.Resolve Then
objOutlookMsg.Display
End If
Next
.Send
End With
MyRS.MoveNext
Loop
Set objOutlookMsg = Nothing
Set objOutlook = Nothing
End Sub

Please Help!

Thanks in Advance!

 

[Allen Browne]

Try:
Dim MyRS As DAO.Recordset

THe ADO library also has a Recordset object, so if you are not specific
about which one you want, you can get the "wrong type" message.

 

[staverts]

Thank-you, I will try this!!

 

[staverts]

hmm..now one other small problem on this line:
TheAddress = MyRS![kuemail]

I get a runtime error 94, invalid use of NULL...

But your other fix seemed to work!

anymore cool suggestions friend

Matt

 

[Allen Browne]

What is TheAddress? String variable probably?

VBA strings cannot be Null. Neither can Integer, Long, Date, or any other
VBA type except Variant.

Either:
Dim TheAddress As Variant
or else:
TheAddress = Nz(MyRS![kuemail], "")

More information in:
Common errors with Null
at:
http://allenbrowne.com/casu-12.html

 

[staverts]

Hi Allen, because of you, my code is coming along very nicely. you've been
such a big help, I would like to pick your brain on this one, in this
expression you showed me how to contruct here:

Nz(Maillist![kuemail], "")

Is there a way, I can use a variable to represent the feild name kuemail,
so that it could always change (be dynamic), if so what would the syntax
for that be, I have tried to make it work, but can't seem to get it... If
I'm not being clear let me know. I am pretty new to all of this, but
making great progress. I've managed to program myself an MS Access
Emailer, using a third party SMTP componenet, so that access does not have
to go through outlook, A fine little program if I so say so myself it
even works ha ha ha

Anyway let me know if the abovr is possible.

 

[Allen Browne]

MailList!kuemail is equivalent to MailList.Fields("kuemail").
Since Fields is the default collection, you can abbreviate that to:
MailList("kuemail")

That allows you to use a string variable in place of the literal string.

 

[staverts]

He Allan, once again, thanks for all the help. One last request for one
other problem I have run into. I was wondering if there is a way to
display Access objects in a combo box, such as List all table names in a
database, in a vb6 combo box. I know it has someithng to do with setting
the AOB...please help...once again...

Thank-you!

 

[Allen Browne]

Undocumented, but works in all versions of Access:

SELECT MsysObjects.Name FROM MsysObjects
WHERE (([Type] = 1) AND ([Name] Not Like "~*") AND ([Name] Not Like
"MSys*"))
ORDER BY MsysObjects.Name;

Other values for Type:
4 ODBC linked tables;
6 linked tables
5 queries
-32768 forms
-32764 reports
-32761 modules

 

[staverts]

WOW, Thank-you so much. you have been a wealth of knowledge. I really do
appreciate your expertise

As usual, you have been an amazing teacher

Matt S
ITSM