Multiple table data to save to a different table from a form

[steve1111]

Hi,

I have three tables: Vehicles; Vehicle Reallocated; and Vehicles Retired. I have a form that runs a query to find all the info in the Vehicles tbl that is not "Retired", not visible in the form. I then have the option to toggle to a Reallocated or Retired form. When i toggle to the reallocated form, i have the like fields in that table (ie Van #, Vin, Make etc) pulling the info from the hidden subform with the vehicle query, so i do not need to fill in repeat data. However, when i add a reallocated date and the new clinic that vehicle is for, i get the record ID for the vehicle reallocated table as expected, but when i save none of the data moved over from the query saves in the record?

Any ideas how to get all the data on the reallocated form to save?

thanks

 

[plog]

I didn't quite follow what you are trying to do, but I see what I think is a glaring error in your table structure. Do your 3 vehicle tables have the exact same structure, or as my grandfather says, pert near?

If so, you don't need 3 tables, you need 1 table, with an additonal field to hold the status of the vehicle. Doing this will make your life easier. When a vehicle gets retired/reallocated you don't move data, you simply update its status.

 

[steve1111]

I thought of that, however i know the vehicles and the Retired tables would only have one entry per van, so i made the PK the vin numbers on those tables. but the reallocated table needs to become more of a log, as in theory we could move the location of the vehicle to 100 different clinics during the life of the van.

my other concern was when we retire a van and by a new one, we letter the van the same number, so there could be multiple van 213 in the DB. We have a separate module that will read the latest odometer readings from the active 213 for upcoming maintenance reminders such as oil change. so i have to be careful to grab the active 213 and not the retired ones.

I was thinking an action query may need to be used, but i am not too familiar with those.

If i have one table with the holds all the details of the van, make number vin etc, do you have a suggestion how i would log the reallocation history?

thanks

 

[plog]

Yup, you have a bad structure. You need all your vehicles in one table, with a unique autonumber primary key. Then you need another table to designated where those vans are allocated.

If you can post your database I can help guide you to the right structure.

 

[steve1111]

I appreciate the help plog. I cannot post the DB due to security at work, but i think i would need a:

Vehicles*
[ID] - Autonumber
[Van #]
[etc details make, vin....]
[Reallocated] checkbox (this would just be to indicate there is a log in the
Reallocate table???
[Status] cbx active/retired


then a Reallocate table (this is where i get tripped up on the relationships)
[ID]
[From clinic]
[to clinic]
[date]


how do i link those two? also then would i perform an update query to update the clinic field of the tblVehicle from the latest date entry of the [to clinic] field of the tblReallocate?

sorry i know seeing the DB would be easier...

 

[plog]

Using that as a starting point I would change it to this:

Vehicles:
Vehicle_ID, Autonumber primary key
VanNumber, Text/Number depending on what data it holds
Make, Text
VIN, Text
Retired, Yes/No

Allocations
AllocationID, autonumber primary key
ID_Vehicle, number; foreign key to Vehicles.Vehicle_ID
AllocationDate, Date/Time
ID_Clinic, number; foreign key to Clinics.Clinic_ID

You don't need a field in Vehicles that says data is in Allocations, you simply run a query and see if there is data. You don't need a from and to field in Allocations because yo can look at the prior allocation to see where the vehicle came from.

 

[steve1111]

thanks so much for the tips plog