Email each record a copy of their record report

[Ruegen]

I am tackling something I have put off for a while.

I have a report that shows what each school has for the year.

I need to email each school their own report - as there are over 1000 schools I would like to make this automated.

The reports go by date between.

What would be the best way to do this?

I've had in mind to loop through the records, count them, then run a command with next with sendobject until it has run through and emailed every school until the final one (from the count).

Any suggestions?

[June7]

Open a recordset in VBA and cycle through the recordset, opening and closing report filtered to criteria from the record.

[Ruegen]

Open a recordset in VBA and cycle through the recordset, opening and closing report filtered to criteria from the record.

So open report, then it runs through and emails filtered criteria each individually, then closes?

[Ruegen]

Also could I use a for each?

for each item in recordset

docmd.sendobject?

[ItsMe]

Also could I use a for each?

I doubt it. The only way I know to do it is to move around inside the recordset with something like dao. If you have the content of a list box in a variant you could use for each. But then you are limited to getting info from one column. Maybe you could nest the for each and make something work. It is better to grab the recordset and get specific info from each field every time you .Movenext.

Haven't you tried some DAO stuff yet?

[Ruegen]

I doubt it. The only way I know to do it is to move around inside the recordset with something like dao. If you have the content of a list box in a variant you could use for each. But then you are limited to getting info from one column. Maybe you could nest the for each and make something work. It is better to grab the recordset and get specific info from each field every time you .Movenext.

Haven't you tried some DAO stuff yet?

not yet

I'm mucking around with a test database

Private Sub Form_Load()
Dim db As Database
Dim rs As Recordset
Dim strSQL As String


strSQL = "SELECT tblPersons.ID, tblPersons.Title " & vbCrLf & _
"FROM tblPersons;"


Set db = CurrentDb
Set rs = CurrentDb.OpenRecordset(strSQL)


rs.MoveFirst


Do While Not rs.EOF
If Absent = 1 Then
PersonChecked = 1
End If
rs.MoveNext
Loop






End Sub

as you can see here it doesn't do anything and I don't quite know what to do.

the basic idea here is to look through the sql, and check a checkbox on condition of another checkbox then move to the next

I figure if I can do this I can do the larger detailed database

I can't seem to find any good videos/tutorials for it.

[ItsMe]

First, you don't need vbcrlf in your SQL

Just put a table name in your Open recordset

Set rs = CurrentDb.OpenRecordset("tblName" dbopendynaset)

Then you can movefirst

rs.Movefirst

Then you can get info from a field

dim strSomething as string

strSomething = rs![FieldOne]

If you need to edit a field then you need to .Edit

rs.Movefirst
rs.Edit
rs![FieldOne] = strSomething
rs.Update

rs.Close
set rs = nothing

[Ruegen]

First, you don't need vbcrlf in your SQL

Just put a table name in your Open recordset

Set rs = CurrentDb.OpenRecordset("tblName" dbopendynaset)

Then you can movefirst

rs.Movefirst

Then you can get info from a field

dim strSomething as string

strSomething = rs![FieldOne]

If you need to edit a field then you need to .Edit

rs.Movefirst
rs.Edit
rs![FieldOne] = strSomething
rs.Update

rs.Close
set rs = nothing

But what happens when I'm not using a table but a query, or select - that is why I was trying to use strSQL...?

couple of questions

also rs.edit <-- do you need to do that to edit the recordset?

rs![fieldone]=strsomething <-- whatever the field in the record set is change to this (whatever the strSomething is.)

rs.Update <-- you need to update the rs once done? not automatic?

[ItsMe]

also rs.edit <-- do you need to do that to edit the recordset?

rs![fieldone]=strsomething <-- whatever the field in the record set is change to this (whatever the strSomething is.)

rs.Update <-- you need to update the rs once done? not automatic?

This is an example of editing a field to equal the value of strSomething. After you move to the record you want to edit. You need to .Edit. Then after you have the various fileds equal something you need to .Update.

Also, you can put whatever you want as the recordset. You can put a query name an SQL string, a table name.

[Ruegen]

This is an example of editing a field to equal the value of strSomething. After you move to the record you want to edit. You need to .Edit. Then after you have the various fileds equal something you need to .Update.

Also, you can put whatever you want as the recordset. You can put a query name an SQL string, a table name.

Ok sounds good to me. So as I take it I am making a recordset in vba, editing the record set then closing the record set.

where does

do while until rs.eof <-- from what you told me this loops through all the records until it gets to the end/last record

loop

get used as apposed to above?

I assume rs.movefirst puts you on the first record and then rs.movenext moves you to the next record in the recordset rs


does rs.movenext get put before the "loop"?

when using a

dim db as database

why not

dim db as dao.database

what is the main difference?

[ItsMe]

Here are some examples. As for using just database or just recordset, I really don't know. I would guess it all has to do with functionality. What do you want to do with the recordset, database, object, etc.

'This will open a recordset for all of the fields in tblMain
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblMain", dbOpenDynaset)

'Make sure some records exist. You want to
'do something like this especialy if
'using a query or applyng a filter or
'using .Findfirst, etc
If rs.EOF = True Then
MsgBox "No Records found"
Set rs = Nothing
Exit Sub
End If
'Since there are records we can move around
rs.MoveFirst
'Let's edit this record
rs.Edit
rs![FieldOne] = "Something"
rs![FieldTwo] = 200
'while we are here, let's collect some info
strSomething = rs![FieldThree]
'Don't forget to save the changes we made
rs.Update
'We are done so let's tidy up
rs.Close
Set rs = Nothing

'Here is an example of looping through al the records in a form's recordset
Set rs = Me.RecordsetClone
If rs.EOF = False Then
rs.MoveFirst    'We are at the first record so do not move next yet
    Do Until rs.EOF
    rs.Edit
    rs![FieldOne] = 200
    rs.Update
    rs.MoveNext
    Loop
    
Else
MsgBox "No records Found!"
Set rs = Nothing
End If
'Here is another way to loop through
Set rs = Me.RecordsetClone
If rs.EOF = False Then  'Make sure there are records here
rs.MoveFirst    'We are at the first record so do not move next yet
    While rs.EOF = False
    rs.Edit
    rs![FieldOne] = 200
    rs.Update
    rs.MoveNext
    Wend
    
Else
MsgBox "No records Found!"
Set rs = Nothing
End If

[Ruegen]

Here are some examples. As for using just database or just recordset, I really don't know. I would guess it all has to do with functionality. What do you want to do with the recordset, database, object, etc.

'This will open a recordset for all of the fields in tblMain
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblMain", dbOpenDynaset)

'Make sure some records exist. You want to
'do something like this especialy if
'using a query or applyng a filter or
'using .Findfirst, etc
If rs.EOF = True Then
MsgBox "No Records found"
Set rs = Nothing
Exit Sub
End If
'Since there are records we can move around
rs.MoveFirst
'Let's edit this record
rs.Edit
rs![FieldOne] = "Something"
rs![FieldTwo] = 200
'while we are here, let's collect some info
strSomething = rs![FieldThree]
'Don't forget to save the changes we made
rs.Update
'We are done so let's tidy up
rs.Close
Set rs = Nothing

'Here is an example of looping through al the records in a form's recordset
Set rs = Me.RecordsetClone
If rs.EOF = False Then
rs.MoveFirst    'We are at the first record so do not move next yet
    Do Until rs.EOF
    rs.Edit
    rs![FieldOne] = 200
    rs.Update
    rs.MoveNext
    Loop
    
Else
MsgBox "No records Found!"
Set rs = Nothing
End If
'Here is another way to loop through
Set rs = Me.RecordsetClone
If rs.EOF = False Then  'Make sure there are records here
rs.MoveFirst    'We are at the first record so do not move next yet
    While rs.EOF = False
    rs.Edit
    rs![FieldOne] = 200
    rs.Update
    rs.MoveNext
    Wend
    
Else
MsgBox "No records Found!"
Set rs = Nothing
End If

Thanks, this makes easy reading

[Ruegen]

Hey it works!

I used

Private Sub Form_Load()


Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone


rs.MoveFirst
Do Until rs.EOF
rs.Edit
If rs!Absent = True Then
rs!PersonChecked = True
End If
rs.Update
rs.MoveNext
Loop


rs.Close
Set rs = Nothing




End Sub

it ran through the code and performed the correct actions.

I would like a message box at the end to say done when it gets to the end of .eof though...

[ItsMe]

I would suggest keeping msgboxes outside of the loop, maybe right before end sub

[Ruegen]

I would suggest keeping msgboxes outside of the loop, maybe right before end sub

Thanks ItsMe