Keeping track of report dates

[Gus Chuch]

I need help again; I have a mailing list (table) that I use to generate new
business. From this table I have a FORM and a REPORT, the report prints out
the flyer that I use. Based on the filters on the form (mainly customer type
and city) the report will only print flyers based on thoughts criteria’s.
But I need a way to update the DateOfLastFlyer in the table and set up a
criteria on the report so I don’t send a flyer to the same business twice in
a year. When the report prints I would like the table to up date
automatically. Then do a date function to let a year go by before printing
that business again.

 

[Allen Browne]

It would be possible to create an Update query statement string, building
the WHERE clause from the filter on the form. But it might be better to
record the mailings instead.

Mock up a query using the table that has the LastFlyer field as its source.
Enter any old values as criteria on the fields your form will use.
Change it to an Update query (Update on Query menu.)
Switch to SQL View (View menu.)
There's an example of the string you have to create in your code.
You can run the query with RunSQL or Execute.
The differences and examples are shown in this article:
Action queries: suppressing dialogs, while knowing results
at:
http://allenbrowne.com/ser-60.html

A better solution would be to create 2 tables:
1) Mail table (one record for each time you send out a mailing):
MailID AutoNumber primary key
MailDate Date/Time when the mailing was sent.

2) MailDetail table (one record for every flyer sent in that mailing):
MailDetailID AutoNumber primary key
MailID Number which mailing this record covers.
ClientID Number which client this was sent to.
FlyerID Number which flyer was sent.

To create a mailing, you would add one new record to the Mail table, and
then use that MailID to add all the related records to the MailDetail table
by using an Append query.

This gives you a complete record of what was sent when, and has several
advantages:
- If anything goes wrong with the printing, it's easy to reprint the MailID.

- You can delete a mailing (e.g. to recreate it if something's not right),
and you have not lost the previous date (which you would have if you simply
tried to record the LastFlyerDate.)

- You can track how many of each flyer are sent in any period, which helps
predict re-ordering needs.

- You are set up to mine the data further (e.g. to see which flyers are most
effective for you.

 

[Gus Chuch]

sounds simple enough I’ll be working on it this week end.
would there a way to read the printer queue to confirm each print?

 

[Gus Chuch]

Well so far so good, But, I can get the Update Query to work OK if I use the
runsql.
But if I set it up as a module as in your example I get a compile error –
expected variable or procedure, not module?
Why??

 

[Allen Browne]

Rename the module so it does not have the same name as the procedure.

Even if possible, reading the Windows print queue won't be effective. If the
printer has its own RAM, Windows may succeed in downloading the report to
the printer without anything coming out on paper.

 

[Gus Chuch]

I renamed the module and now I get a compile error – user defined type not
defined
And it stops at the line of code ‘Dim db As DAO.Databaseâ€
Do I do something worng?

 

[Allen Browne]

Add a reference to the DAO library.

Details:
http://allenbrowne.com/ser-38.html