Looping through records in a Query

[Guest]

I posted this question on the Forms Coding group but never got an answer. I
thought I would give this board a try.

We publish a monthly trade magazine. I have created a database to help me
manage the monthly advertising. Here is the basic (relevant) structure:

Table: Company - Has all the different companies and individuals that
advertise with us. Includes such info as the phone number and e-mail address.

Table: Issue - Just a table of the months, such as 2006 January, 2006
February and so on.

Table: IssueDetail - This is the nuts and bolts. For each issue, it lists
the company, ad size (full page, half page, etc.), color, page number, and
other info not relevant to my problem.

In the IssueDetail table, I also have two Yes/No fields. One is whether or
not I need a new ad and the other is whether I have received a new ad.
(Fields are: NeedAd and NewAd). I don't get a new ad from every company every
month. Some just run the same ad all the time while others change them every
month. So for the ones that change them constantly, the NeedAd field would be
Yes. When I receive the ad, the NeedAd is changed to No and the NewAd to Yes.
This way when I am getting ready to send the magazine to the printer, I can
make sure that I have inserted all the new ads. I have set up a form to send
an e-mail reminder to everyone I need a new ad from that gives them the
deadline date.

The form is linked to a query (EmailQuery) that only returns companies where
the NeedAd is Yes. The form has a ListBox and the RowSource narrows it down
to only include the companies for that particular issue. I can click a button
to SelectAll in the listbox or I can select individual companies. When I have
the companies selected, I click a button (CreateRecipientString) that opens a
new e-mail with all the companies that are selected as the recipients. This
all works great and is really helping.

Now for my problem. I want to add a text field to my IssueDetail table
called SentEmail. When I click on the button CreateRecipientString, I want it
to loop through all the selected records and update the new SentEmail field
with the current date. (I don't want the field to be a date field, as I will
also send a second e-mail in the same month right before the deadline date
for those companies that have not yet sent a new ad.) I can't for the life of
me figure out how to do this. I hope someone here can help. I hope I provided
enough information.

TIA,
Nancy

 

[Marshall Barton]

I posted this question on the Forms Coding group but never got an answer. I
thought I would give this board a try.

We publish a monthly trade magazine. I have created a database to help me
manage the monthly advertising. Here is the basic (relevant) structure:

Table: Company - Has all the different companies and individuals that
advertise with us. Includes such info as the phone number and e-mail address.

Table: Issue - Just a table of the months, such as 2006 January, 2006
February and so on.

Table: IssueDetail - This is the nuts and bolts. For each issue, it lists
the company, ad size (full page, half page, etc.), color, page number, and
other info not relevant to my problem.

In the IssueDetail table, I also have two Yes/No fields. One is whether or
not I need a new ad and the other is whether I have received a new ad.
(Fields are: NeedAd and NewAd). I don't get a new ad from every company every
month. Some just run the same ad all the time while others change them every
month. So for the ones that change them constantly, the NeedAd field would be
Yes. When I receive the ad, the NeedAd is changed to No and the NewAd to Yes.
This way when I am getting ready to send the magazine to the printer, I can
make sure that I have inserted all the new ads. I have set up a form to send
an e-mail reminder to everyone I need a new ad from that gives them the
deadline date.

The form is linked to a query (EmailQuery) that only returns companies where
the NeedAd is Yes. The form has a ListBox and the RowSource narrows it down
to only include the companies for that particular issue. I can click a button
to SelectAll in the listbox or I can select individual companies. When I have
the companies selected, I click a button (CreateRecipientString) that opens a
new e-mail with all the companies that are selected as the recipients. This
all works great and is really helping.

Now for my problem. I want to add a text field to my IssueDetail table
called SentEmail. When I click on the button CreateRecipientString, I want it
to loop through all the selected records and update the new SentEmail field
with the current date. (I don't want the field to be a date field, as I will
also send a second e-mail in the same month right before the deadline date
for those companies that have not yet sent a new ad.) I can't for the life of
me figure out how to do this. I hope someone here can help. I hope I provided
enough information.

While you are looping through the records to construct your
To: list, add a little code to construct the corresponding
WHERE clause. Then you can use that in an UPDATE query to
set the SentEmail field to Now()

 

[Guest]

You will also need to convert the date Now() into a string, so it would be

CStr(Now())

But are you asking how to convert a date to a string or how to loop thru a
recordset? To loop thru a recordset you can use the While Not
name.EOF...Wend loop. Something like...

Dim rst as dao.recordset
rst = currentdb.openrecordset(QueryName, dbopendynaset)

rst.MoveFirst

While not rst.EOF
rst.Edit
rst![SentEmail] = CStr((Now())
rst.update

rst.MoveNext
Wend

rst.close

 

[Guest]

I posted this yesterday and then realized that I replied to the wrong person.
Hope your alerts are on and this helps.