VB coding for date comparison. help urgently needed!!!!!!!!

[Lawee]

Hi,
I will really appreciate it if I can get help as soon as possible o
my problem. I am trying to create a database for a library as m
personal project. I have a return date field which has a date variable
The form is supposed to be open all the time. In my Vb coding I want th
database to automatically go through all the forms and find the one
that have a return date matching the present date (system date) an
send a reminder to the borrower. what is the most effective way (i
terms of procedure and event)to write the VB code for this. I am
newbie at MS access and VB coding and most of the materials i have rea
haven't been of much help to me. I will really appreciate it if someon
can help me out. i look forward to all your favorable replies.

Lawe

 

[ChrisJ]

I would suggest creating a report which is in effect your reminder letter.
Base this report on a query which picks up overdue loans.
The report could be run on demand (daily?)
You may need to record that a reminder has been sent and not resend it
unless it is still overdue a week? later

 

[Lawee]

ChrisJ,
Thanks for your reply. I will appreciate it if you can help m
further cos i'm very new at this. I have no idea how to create a quer
based on anything. Can you tell me what steps to take. I have created
table for the database which includes the following fields:

Name of borrower
Email Address of Borrower
Name of Book
Publisher
Checkout date
Return Date
Returned (which is a checkbox).

I will like to know how to create the query, what it will be based o
and the VB code that will determine when to send the email. All othe
assistance are welcome. thank you

 

[Ronald Roberts]

1. Click the Query tab on the database window and click New to
create the query, then click design view.
2. Select the table that contains the fields below and click Add.
3. Add each field required by double clicking on the field name.
4. In the Criteria Row of the Return Date, enter < Date()
5. In the Criteria Row of the Returned, enter False
6. Save the query and run it.

It should return all of the records with a return date less
than todays date and a return status of false.
Use the query as the record source for any processing or reports
you need.

From the sound of your post, I think you have other issues, but
as you build this application you will learn what they are. As
an example, you may want to create a return schedule table that
contains a catagory for different materials that can be loaned out
and a number of days. Then you can calculate the date of return
and check that date to the current date and check the return status
to see if they have returned the item. If the calculated date returned
is less than todays date and the return status is false, send them a
letter or e-mail. This way if the library changes the return schedule,
all you need to change is the return schedule table. Also, the user
will not need to come up with the return date, the program will do that
based on the return schedule table. All the user will need to enter is
the catagory for the material being loaned out. This way the user
doesn't have to calculate in their head or lookup the return date, they
can look at what is be loaned out and determine the catagory. Much
easier for the user.

In your future post tell us what version of Access you are using. It
will help in answering your question. The above procedure for creating
a query is for Access 97. In each version the detail steps for doing
something are different.


Good luck,...Hope this helps.
Ron

 

[John Vinson]

Hi,
I will really appreciate it if I can get help as soon as possible on
my problem. I am trying to create a database for a library as my
personal project. I have a return date field which has a date variable.
The form is supposed to be open all the time. In my Vb coding I want the
database to automatically go through all the forms and find the ones
that have a return date matching the present date (system date) and
send a reminder to the borrower. what is the most effective way (in
terms of procedure and event)to write the VB code for this. I am a
newbie at MS access and VB coding and most of the materials i have read
haven't been of much help to me. I will really appreciate it if someone
can help me out. i look forward to all your favorable replies.

Lawee

Whoa. You're working perpendicular to the logic of an Access database!
You don't want to search through forms, and you probably don't need
much or any VB code.

Your Forms don't contain any due dates. Forms are just windows,
displaying data that's stored in Tables.

VB is one possible way to retrieve data from Tables, but it's far from
the best: a Query is much the better choice. You can create a Query
based on your loans table (you don't describe your table structure so
I have no idea how you have it set up), with a criterion of

=Date()

on the due date field (not sure what you mean by "a date variable").
This Query could then be used as the recordsource for a report, which
could be sent to the borrower.

Please describe the table structure of your database; a query to get a
"due items" report should be very straightforward.

John W. Vinson[MVP]