Looping through code for every record in a table

[keri]

Hi,

I have a table with 2 fields - flddate and category. There are 999
records in the table. Currently I use the below code to create an
outlook appointment from one of the records in the table. However I
would like to loop through each of the records to create an outlook
appointment for each of the records. I guess I need to create an ID
for each record and loop through the reocrd numbers but I don't know
where to start.

Private Sub Command6_Click()
DoCmd.Save
Dim objFolder As MAPIFolder
Dim spobj As Variant
Set spobj = CreateObject("Outlook.Application")
Dim outobj As Outlook.Application
Dim objAppt As Outlook.AppointmentItem
Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Set outobj = CreateObject("outlook.application")
Set objAppt = outobj.CreateItem(olAppointmentItem)
With objAppt
objAppt.Start = [fldDate]
objAppt.AllDayEvent = True
.Subject = category
.ReminderSet = False
.Save
DoCmd.RunCommand acCmdSaveRecord
End With
End Sub

Thanks,

 

[Guest]

Keri,
That code looks like you're working from Access, not Excel? Am I correct?
If I am not, then do 2 things:
Let me know,
Ignore what I'm about to write.

To loop through records in a recordset in Access, you can do something like
this:

Sub SaveRecordsAsAppointments()
Dim myDB as DAO.Database
Dim myRST as DAO.Recordset
Dim RecordLoop as Long
Set myDB = LocalDB
Set myRST = myDB.OpenRecordset("name of your table or query")
'... your existing declarations here also
'...all the way down to the With objAppt

myRST.MoveLast
myRST.MoveFirst
For RecordLoop = 1 to myRST.RecordCount
With objAppt
'.... will pickup current record info
'.... and make appointment from it
'....
' no need to Save the Record as that's
' already saved in Access, presumably
End With
myRST.MoveNext ' loop to next Access Record
Next ' RecordLoop
myRST.Close
Set myRST = Nothing
myDB.Close
Set myDB = Nothing

End Sub