Circulating information from one form to another...

[maura]

I am building a database for Car Rentals. I have two tables that I want to
communicate: tblRentalAgreement and tblCar. The relationship between the
tables in a one to many (one Car to many Rental Agreements). I need certain
information to flow back an forth between the two tables. For example the
field MileageIn from the frmRentalAgreement needs to populate my frmCar
MilesFromLastKnownRental. And then it would be nice to have frmCar
MilesFromLastKnownRental to populate frmRentalAgreement MileageOut (when you
select a CarNumber from a combo box).

Is there a way to "circulate" the information from one form to another?

 

[Damon Heron]

First, you have tables. Then you have forms based on the tables. You
should not have any reason to move data back and forth between the tables.
If I was designing a car rental system, I would start with a form based on
the car table that has the make, model etc, and then a subform that has the
table for rental info as the recordsource. The form and subform are tied
together by the CarID. the subform would have the mileage in, mileage out,
dates, etc. -all the info you need for the rental agreement. As you add
information to the subform, the table for rental info is updated.

Damon

 

[maura]

I had posted another question regarding Database Design and Andy Hull had
given me some guidelines (listed below)... and maybe I didn't understand it.
Moving along...

I have the two tables... tblRentalAgreement and tblCar. Given what you're
telling me, I think that the Rental Agreement will be my primary form and the
Car would be the subform... but I am still hung up on how to do this
(probably because I had a whole other layout in my head)... Can I utilize
these two tables and do what you are talking about? I am not opposed to step
by step instructions. There are actually 4 things that I need to watch:
Location of Car, Mileage In and Out, Status of the Car (whether it is On
Rent, Available, etc...). And I think I am starting to get what you are
saying, but still would like some more advice. THANKS!!!!!

______from Andy Hull, Database Design_____
1: Input car details including location - presumably stored in tblCar

Now the car is available for rental and can have a rental agreement - it
couldn't before (so location first exists in tblCar)

2: The car is to be rented & will eventually be booked out but not until an
agreement has been set up. You will have a form to create the agreement which
may work in 1 of 2 ways...

(i) Allow selection of any (available) car and set the agreement "location
out" equal to the car's current location (in tblCar)

(ii) If the database is a stand alone version in one physical location then
default the agreement location to the physical office location and allow
selection of cars only at that location.

3: Now car is booked out - no change to tblAgreement locations or tblCar
location (assuming tblCar location stays equal to "where it was when booked
out" until it is booked in again).

4:
Car is booked returned and booked in - Booking In form allows selection of
car & defaults "location in" to tblAgreement location in (you may allow this
to be overridden if it is possible to return a car to a different location
than agreed). On clicking OK on the booking in form update tblCar location to
the form's "location in"

hth

Andy Hull

 

[Damon Heron]

This can get very complicated. If you have multiple locations, for example,
where the rental can be dropped off.
Secondly, how about future reservations? You also should account for repeat
customers, so you don't have to re-enter the same data.

So I would have at the very least three tables:
1) table for cars, with ID, current mileage, rental rate, other info, size,
color, etc., Y/N fields for currently out and future reservation
2) table for customers, name, address, other pertinent facts
3) table for rentals, date from, date to, carID, customerID.
possible location table, if you have multiple locations. This would a
foreign key in the cars table.
possible reservations table, with carID, dates reserved (in and out).

The critical area will be the from and to dates. If a car is reserved for
future dates then it won't be available, even though it is at your location.
For this I would look at a popup form (on your rental form) tied to the
reservations table, so you could determine if the car will be available
during the current rental agreement. Also, look at some of the calendar
mdbs or links to Outlook's calendar. Google MS Access calendars.

I would really focus on the tables, as they are the foundation of a good
database. The forms will come easy if you have a good foundation.

Damon