Hi all,
im trying to create a model to manage parts from one location to another, two locations are either on a vehicle or in stores, im currntly trying to manage it like this
TblEventPartsLocation
EventTrackableParts_ID
EventTracker_IDFK
Vehicle_IDFK
Part_IDFK
PartLocation_IDFK
TimeStamp
TblPart
Part_ID
SerialNumber
Quantiy
TimeStamp
TblVehicle
Vehicle_ID
VehicleName
TimeStamp
TblPartLocation
PartLocation_ID
PartLocation
TimeStamp
TblPart.Part_ID is linked to TblEventTrackablePart.Part_IDFK
TblVehicle.Vehicle_ID is linked to Tbl.EventTrackablePart.Vehicle_IDFK
TblPartLocation.PartLocation_ID is linked to Tbl.EventTrackablePart.PartLocation_IDFK
So the idea is a part is either on a vehicle or in a part location in this case its stores, i can do this by adding new events to the TblEventTrackablePart Table, but what im struggling to do is return the most recent entry, be it either on a vehicle or in a part location (Stores).
i have two forms that manage the location of the part, Form add part which adds the part to a vehicle, and a remove part which removes the part from a vehicle and adds it back to stores, in both cases i use an append query to add a new entry in to the table and i use DoCmd.GoTorecord,, AcLast to access the relevant record, this works fine, but what im struggling at is on the add and remove form i hava a combo box with a query, on the add part form, the combo box should return all parts that are assigned to stores, and this is where im struggling, i can return the latest entry for that part, so if all the parts are assigned to vehicles then the query should return nothing, but i cant get it to do that, hence the question, of whether this is the correct model.
Im not sure if this is the best way to manage the part location, if any body has a better idea to manage them i would love to hear.
im trying to create a model to manage parts from one location to another, two locations are either on a vehicle or in stores, im currntly trying to manage it like this
TblEventPartsLocation
EventTrackableParts_ID
EventTracker_IDFK
Vehicle_IDFK
Part_IDFK
PartLocation_IDFK
TimeStamp
TblPart
Part_ID
SerialNumber
Quantiy
TimeStamp
TblVehicle
Vehicle_ID
VehicleName
TimeStamp
TblPartLocation
PartLocation_ID
PartLocation
TimeStamp
TblPart.Part_ID is linked to TblEventTrackablePart.Part_IDFK
TblVehicle.Vehicle_ID is linked to Tbl.EventTrackablePart.Vehicle_IDFK
TblPartLocation.PartLocation_ID is linked to Tbl.EventTrackablePart.PartLocation_IDFK
So the idea is a part is either on a vehicle or in a part location in this case its stores, i can do this by adding new events to the TblEventTrackablePart Table, but what im struggling to do is return the most recent entry, be it either on a vehicle or in a part location (Stores).
i have two forms that manage the location of the part, Form add part which adds the part to a vehicle, and a remove part which removes the part from a vehicle and adds it back to stores, in both cases i use an append query to add a new entry in to the table and i use DoCmd.GoTorecord,, AcLast to access the relevant record, this works fine, but what im struggling at is on the add and remove form i hava a combo box with a query, on the add part form, the combo box should return all parts that are assigned to stores, and this is where im struggling, i can return the latest entry for that part, so if all the parts are assigned to vehicles then the query should return nothing, but i cant get it to do that, hence the question, of whether this is the correct model.
Im not sure if this is the best way to manage the part location, if any body has a better idea to manage them i would love to hear.
Last edited: