Reminder in access?

[redsupra101]

hi all.
there's a new db im making and i'd like to add in a 'follow up reminder' feature where; after 1 month of job completion (as updated by a auto update date field) we can give a 1 month follow up call with the customer just to say 'hey, just wanted to make sure your stuff is working' or so to speak. the reminder must say something to the likes of '1 month follow up required for customer vin deisel acct # 5777'... in other words the popup box gotta have the option to capture data from the db as well!

about 2 or 3 guys in here (total access noobs-myself included) are the ones making the db and we'd like some assistance here.

*hopes i posted this in the right forum!*

 

[EdFred]

Two things to do - at least the way I would do it.

#1, add a new yes/no field to your table, and then make a checkbox control on your form that the reminder is. Then #2 in code you need to just run a (oh man, I'm gonna get flamed for the way I write code) For/Next loop.

strSQL = SELECT * FROM

Where [ReminderYesNo] = 0 AND [DateField] < Now() -30
set rs = db.openrecordset(strSQL)
rs.movefirst
For i = 1 to rs.recordcount
Msgbox("Call " & [CustomerNameField] & " and follow up.")
rs.edit
rs![ReminderYesNo] = -1
rs.update
rs.movenext
Next i

I left all the dim statements out, but that's the sort of thing I would do. I would tie that code to either a button on the form, or on the Form Load.

**Puts on Nomex Suit**

 

[Bat17]

How to do it will also vary on how many reminders you expect to generate. one a week and a Msg is fine, 25 a day and a msg box will be a real pain.
I would just create a form based on similar SQL to Fred's and have it pop up when the db opens, you could also get clever and add code to pop it up at various times of the day if they have not all been followed up

Peter

 

[Dennisk]

if the table is linked the record count will return 0
the correct code for moving through a recordset line by line is

do Until myrecordset.eof
myrecordset.movenext
loop