Create a history file

[Thomman]

Hi, Please help to solve this...
I am Currently doing one Access project. One form contains many records to enter like AssetId,AssetDescription,DriverId,StartDate,EndDat e,,etc.. I need to create a history file for Drivers. Whenever a driver is changed his vehicle to another vehicle, it must be recorded into a history file with the driverId, StartDate and EndDate.

Thomman

[TheShabz]

I sure hope all of that isnt on a single table. You should have one table that contains the assets, one that has driver information, and a junction table (many to many relationship) that will list the information (dates of assignment, etc) that links the drivers to the assets. That junction table will essentially be your history table.

Once the first two tables are set up behind the scenes, data entry will not have to enter in info regarding asset or driver. You can use comboboxes and let them select. once they are selected, the user enters the date, hits a button and you have your junction table updated. This both creates your history table, and minimizes data entry errors like typos or issuing a driver that doesnt exist to an asset.

[Thomman]

There are three tables.. 1. Assets 2. Drivers 3. DriversHistory. The DriverHistory file contains AssetID,DriverID,StartDate,EndDate...


Thomman

[TheShabz]

That looks correct. What is the relationship between those tables? It should be:
Assets 1----M DriversHistory M-----1 Drivers

So now you will have 2 comboboxes on your form. the first one will hold asset information from the Asset table. the second will hold driver information from the Drivers table.

Then you will have 2 text boxes. 1 for the user to enter the start date, and 1 for the user to enter the end date.

Then you will create a button that will run a query:
INSERT INTO DriverHistory (DriverID, AssetID, StartDate, EndDate)
Values (Forms!myForm!cmbDriver, Forms!myForm!cmbAsset, Forms!myForm!txtStart, Forms!myForm!txtEnd)

This will populate your DriverHistory table with the proper info. Now when you want to find records, you can query off of your DriverHistory table.

[Thomman]

Really Thanks to you. Agian some more help in this..

Instead of two text boxes, only one text box will be there. i.e StartDate.
There exists one driver or for a new entry the field is empty.

The policy is "the New driver's StartDate should be the EndDate of the the existing Driver..."

Then instead of creating a button to run the query, we need to create a trigger like this....
If the existing Driver (previous value in the DriverId) is changed with a new Driver, the form should display a message box and if we proceed then the query should be run.

Thomman

[TheShabz]

so end date would be left blank until a new driver is assigned. you would run a second INSERT INTO but this time your you would have a WHERE clause as well. I dont have access to Access (i hate that phrase) right now and i wont again until tuesday so I cant confirm the SQL below is valid (it should be) but the logic would be:

INSERT INTO DriversHistory (endDate)
VALUES (Forms!myForm!txtStart)
WHERE DriversHistory.Asset=Forms!myForm!cmbAsset AND DriversHistory.endDate is null

The above will take your start date and enter it into the end date of the record that has both your asset number and a blank end date (there should only be one record with a blank end date if your records are correct).

[Thomman]

When I used this I get Sytanx Error!!!

[Datagopherdan]

Parameters in queries are limited. You're getting the error because of the combo box reference. Also, it looks like you would want to do an update statement not an append. Try doing it in VBA like below. I'm assuming that your combo box is grabbing a numeric value, if not you would need to change "intAsset" to a string variable.

Dim dtStart As Date
Dim intAsset As Integer
If IsNull(Me.txtStart) Then
    MsgBox "You must enter a date"
        Exit Sub
End If
dtStart = Me.txtStart
intAsset = Me.cmbAssest
CurrentDb.Execute "UPDATE DriversHistory SET DriversHistory.endDate = " & dtStart & _
" WHERE Asset= " & intAsset & " AND endDate is null"

Dan
Access Development

[TheShabz]

Eh, like I said about my syntax, I dont have Access available to try it out when I'm at home. Hope it works out.

[maximus]

It is always essential to enclose Date with "#" when using SQl to Insert or update Table. Otherwise you may get undesirable results.

My suggestion will be

CurrentDb.Execute "UPDATE DriversHistory SET DriversHistory.endDate = #" & dtStart & _
"# WHERE Asset= " & intAsset & " AND endDate is null"

[Datagopherdan]

Thanks for adding that in, Maximus. You're absolutely right. You do always want to make sure you have the correct syntax for the type of variable such as "#" in this case.