Database for Mileage Reports

[luvsmel]

I am at a loss on this database.

I have 2 tables that contain data that I want to be used to create a "Mileage Log". None of the tables are really relatable and I'm use to working on a system where everything has data integrity.

I attached 2 pictures:
Tables - Show the Tables
Form - Shows how I would like the Form to be setup.

Form:


No Problem with these fields:
Date - From MyLog and default value =todayReason - From MyLog and is a Listbox w/Value list
Rounding - From MyLog and is a checkbox
Problems:
I want these three fields to populate 1 at a time, They pick a Region and it populates StartingLocAbbrev with only the ones with the matching Regions. They pick a StartingLocAbbrev and it populates StartingLocClinic. Same thing with Destination Fields. I can't seem to find this function.

5 Regions with approx 5 StartingLocAbbrev each and those have approx 10 StartingLocClinic's

Region - ListBox - Based on a query from ClinicDirectory, Region - Grouped by.
StartingLocAbbrev - ListBox - Based on a query from ClinicDirectory, StartingLocAbbrev - Grouped by.
StartingLocClinicBased - ListBox - Based on a query from ClinicDirectory, StartingLocAbbrev


Save and Calculate button - I have a query that will take the StartLocAbbrev + EndingDestAbbrev, compare to Mileage Table and it gets the right milage, I'm stumped on the save part. I want it to save all the data from the form in a new field and clear the form.

Any ideas on how better to construct the database/form I'd appriciate.

In addition.... Once this is complete I will be duplicating the MyLog to Six Employees so they each have their own log but the clinic/mileage directory can be updated only once.

Thanks,
Melanie

[June7]

Sounds like you want cascading (dependent) comboboxes. Review http://datapigtechnologies.com/flash...combobox2.html

Is this a split Form? Form is bound to MyLog table? Controls are bound to fields of table? If so, data is going directly into table. It will be committed to table when form closes. As for the 'clear form', set the form to DataEntry Yes and can only add new records, won't display records already in table. Do a Requery of the form after calcs in the Save and Calc button event. This should refresh the form to a 'blank' state.

Don't recommend 6 MyLog tables. Instead have another field in table for the userID. Find some way to default this entry based on some sort of login procedure.