VBA

[James]

Hello,

Right my problem is that I am trying to write some code
that will go through each record in a table and send them
an e-mail with an attachment based on who they are.

At the moment I am trying to do this by using VBA to go
through each record by using For Each command. At the
moment I can only find out how to get it to go through
the field names not the recores. However it still doesnt
work. It comes up with errors like "Type mismatch"
or "operation is not supported for this type of object".
Below is my code, I have copied it from the VBA help file.

Dim dbs As Database, tbl As TableDef, fld As Field

Set dbs = CurrentDb
Set tbl = dbs.TableDefs("tutor")
For Each fld In tbl.Fields
SendObject etc....
Next fld

Is it obvious what I am doing wrong, do I need any
references I am not using?

Thanks in advance if you can help.

James

 

[Dirk Goldgar]

Hello,

Right my problem is that I am trying to write some code
that will go through each record in a table and send them
an e-mail with an attachment based on who they are.

At the moment I am trying to do this by using VBA to go
through each record by using For Each command. At the
moment I can only find out how to get it to go through
the field names not the recores. However it still doesnt
work. It comes up with errors like "Type mismatch"
or "operation is not supported for this type of object".
Below is my code, I have copied it from the VBA help file.

Dim dbs As Database, tbl As TableDef, fld As Field

Set dbs = CurrentDb
Set tbl = dbs.TableDefs("tutor")
For Each fld In tbl.Fields
SendObject etc....
Next fld

Is it obvious what I am doing wrong, do I need any
references I am not using?

Thanks in advance if you can help.

At least some of the things you're doing wrong are pretty obvious. You
don't use the TableDef object to work with the records in tables, you
use it to work with the design of the table. A Recordset object is what
you need. And you can't use the For Each syntax to loop through the
records in the table; for that you must use the record-movement methods
of the Recordset object, in a loop. It might look something like this:


' Send a message to each "EmailAddress" in table "tutor".

Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tutor")

With rs
Do Until .EOF
DoCmd.SendObject To:=!EmailAddress, ...
.MoveNext
Loop
.Close
End With

Set rs = Nothing
Set dbs = Nothing

You need to fill out more arguments on the DoCmd.SendObject line.

 

[James]

-----Original Message-----


At least some of the things you're doing wrong are pretty obvious. You
don't use the TableDef object to work with the records in tables, you
use it to work with the design of the table. A Recordset object is what
you need. And you can't use the For Each syntax to loop through the
records in the table; for that you must use the record- movement methods
of the Recordset object, in a loop. It might look something like this:


' Send a message to each "EmailAddress" in table "tutor".

Dim dbs As DAO.Database
Dim rs As DAO.Recordset

Set dbs = CurrentDb
Set rs = dbs.OpenRecordset("tutor")

With rs
Do Until .EOF
DoCmd.SendObject To:=!EmailAddress, ...
.MoveNext
Loop
.Close
End With

Set rs = Nothing
Set dbs = Nothing

You need to fill out more arguments on the DoCmd.SendObject line.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

Thanks your a life saver!!!!