Help: reading data from table

Hi
This must be another easy one for you guys:

I am trying to read all email addresses in a table into a string variable using "getrows".
I have searched the Internet for a few days and couldn't get it work for me. the following
is the code I come up with. It doesn't work. It stops at OpenRecordset saying type
missmatch.

Please help......

ad

=============================
Sub test()

Dim db As Database
Dim Lrs As Recordset
Dim LSQL As String
Dim email As Variant
Dim c As Integer
Dim strEmail As String

'Open connection to current Access database
Set db = CurrentDb()

'Create SQL statement to retrieve value from GST table
LSQL = "SELECT Contacts.[E-Mail Address] FROM MailingList INNER JOIN Contacts ON MailingList.ContactID = Contacts.[Contact ID];"

Set Lrs = db.OpenRecordset(LSQL)
email = Lrs.GetRows

For c = 1 To 2
strEmail = strEmail & email(c) & "; "
Next c

' Display the results.
MsgBox strEmail

Lrs.Close
Set Lrs = Nothing


End Sub

You don't need the GetRows Method. All you need to do is
to traverse the Recordset and use concatenation to append
to the strEmail as you traverse the Recordset using a loop.

Check Access Help on the MoveNext Method of the Recordset.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
Hi
This must be another easy one for you guys:

I am trying to read all email addresses in a table into a
string variable using "getrows".
I have searched the Internet for a few days and couldn't
get it work for me. the following
is the code I come up with. It doesn't work. It stops at
OpenRecordset saying type
missmatch.

Please help......

ad

=============================
Sub test()

Dim db As Database
Dim Lrs As Recordset
Dim LSQL As String
Dim email As Variant
Dim c As Integer
Dim strEmail As String

'Open connection to current Access database
Set db = CurrentDb()

'Create SQL statement to retrieve value from GST table
LSQL = "SELECT Contacts.[E-Mail Address] FROM
MailingList INNER JOIN Contacts ON MailingList.ContactID =
Contacts.[Contact ID];"

Set Lrs = db.OpenRecordset(LSQL)
email = Lrs.GetRows

For c = 1 To 2
strEmail = strEmail & email(c) & "; "
Next c

' Display the results.
MsgBox strEmail

Lrs.Close
Set Lrs = Nothing


End Sub


.

"Van T. Dinh" wrote in message ...
You don't need the GetRows Method. All you need to do is
to traverse the Recordset and use concatenation to append
to the strEmail as you traverse the Recordset using a loop.

Check Access Help on the MoveNext Method of the Recordset.


Thank you very much Van! It works now. However I had to dim Lrs as
object, not Recordset. If I dim Lrs as Recordset, it gives me type
mismatching error message at OpenRecordset. Why is this?

code that WORKs:
===============
Sub test()
Dim db As Database
Dim Lrs As Object '------
Dim LSQL As String
Dim strEmail As String

'Open connection to current Access database
Set db = CurrentDb()

'Create SQL statement to retrieve value from table
LSQL = "SELECT Contacts.[E-Mail Address] FROM MailingList INNER JOIN Contacts ON MailingList.ContactID = Contacts.[Contact ID];"
Set Lrs = db.OpenRecordset(LSQL)

While Not Lrs.EOF
strEmail = strEmail + Lrs![E-Mail Address] + "; "
Lrs.MoveNext
Wend

' Display the results.
MsgBox strEmail

Lrs.Close
Set Lrs = Nothing
End Sub

Thank you very much Van! It works now. However I had to dim Lrs as
object, not Recordset. If I dim Lrs as Recordset, it gives me type
mismatching error message at OpenRecordset. Why is this?

I have figured it out: set the appriate references, forgot which one,
after trying different stuff, it's working now.

Thanks again.

You probably have both ADO & DAO Library in the References
and the ADO has higher priority than the DAO Library.
When you dim. the Recordset without qualifier, it is
defaulted to ADO Recordset. However, you code needs DAO
Recordset and ADO Recordset / DAO Recordset are not
compatible.

Try the Dim statement:

Dim Lrs As DAO.Recordset

HTH
Van T. Dinh
MVP (Access)




-----Original Message-----
Thank you very much Van! It works now. However I had to
dim Lrs as
object, not Recordset. If I dim Lrs as Recordset, it
gives me type
mismatching error message at OpenRecordset. Why is this?

code that WORKs:
===============
Sub test()
Dim db As Database
Dim Lrs As Object '------
Dim LSQL As String
Dim strEmail As String

'Open connection to current Access database
Set db = CurrentDb()

'Create SQL statement to retrieve value from table
LSQL = "SELECT Contacts.[E-Mail Address] FROM
MailingList INNER JOIN Contacts ON MailingList.ContactID =
Contacts.[Contact ID];"
Set Lrs = db.OpenRecordset(LSQL)

While Not Lrs.EOF
strEmail = strEmail + Lrs![E-Mail Address] + "; "
Lrs.MoveNext
Wend

' Display the results.
MsgBox strEmail

Lrs.Close
Set Lrs = Nothing
End Sub


.

Thanks again