Auto detecting a date

[Cillies]

Hi, I was wonder if anyone knew how to manipulate access on start-up to check a certain date field on a form, and if that date on the form is the same date as the day the database was opened a message would appear asking the user to take action or at least bring up the records with the same date as 'today'.

i.e.
If I open the db today and I have several records within my db with today’s date written in a text box, then I would like access to make me aware of this by form of a message and possibly bringing up the records.

Hope this message is understandable, if not please do not hesitate to contact me.

Thanks

p.s.
could you maybe use something like autoexec or something??

 

[BobStubbs]

If you are using a start up form such as the Switchboard, a good place to put a call to your date checking routine is in the Form_Load event of this form.

You can get today's date in VBA code using the Date() function.

You could use e.g. a DCount statement to look for records in a table with today's date. Something like this:

Dim intCount as Integer

intCount = DCount("[RecordID]","tblYourTable","[DateField]='" & Format$(Date, "dd/mm/yyyy") & "'")

if intCount > 0 then
    'put action code here
end if

For your 'action code' you could display a message, run a query to list the records, etc.

In my DCount example:

[RecordID] is the key field of the table - so it will always contain a value
tblYourTable - is your table name
[DateField] - is the date field you want to check

My example assumes the date is stored in a text field, in the format dd/mm/yyyy. You will need to adjust the syntax depending on your date storage format.



Bob Stubbs