Previous Record (1 Viewer)

[ansentry]

I am using Access 97 for this database (I also have Access 2000)

I have attached a small database sample that I am having a problem with.

When you open the database you will see a form called Vehicles with a subform attached.

The purpose of the database is for vehicle use, the vehicle has an outdate driver and indate.

What I want to happen is a message box to appear with a message etc (this I can do) if the user tries to book out a vehicle that does not have a datein in the previous record.

What I want to know how to do is code it so that it checks the "previous" indate to see if it is empty or null.

In other words the vehicle cannot be booked out if it has not returned.

Thank you in advance.

 

[Nouba]

you should get the available vehicles with a query like

SELECT
  VehicleID
, RegistrationNumber
, Make
, Model
FROM tbl_Vehicles
WHERE VehicleID Not In (
  SELECT
    VehicleID
  FROM tbl_Usage
  WHERE InDate Is Null)

BTW: I would use the DriverID as a foreign key in tbl_Usage instead of the driver's name. If you ever need data from tbl_Driver like firstname or lastname, I would strongly recommend putting the first and lastname into separate fields.

 

[ansentry]

Nouba,

Thank you for your reply, yes that gives me a list of the available vehicles.

So what I will try and do is put code behind OutDate so that before update it checks to see if the current vehicleid is on the "list" or not.

Any ideas?

 

[Nouba]

I would restrict the selection for the vehicle by setting up a main form having the criteria from the postet query. You could accomplish this by either using the form's Filter property or assigning the query to the form's RecordSource property. In a subform joined via the VihicleID you couldn't have an unavailable vehicle. By changing the criteria in the main form to the opposite you can fill out your Indate field in the subform for received vehicles.

 

[ansentry]

Nouba,

I have go it working, I setup a query with your SQL and called it Qry_AvailVehicles.

In OutDateBeforeUpdate I put the following code, I have just finished it and only test it 3 or 4 times but appears to work.

It does a dlookup of your query and compares it to the current vehicleid if the vehicleid is not in your query then it is no go.

So thank you very much I will not keep testing, and put some other code in (like you indate must be equal to or greater than out date etc.)

Also have taken you suggestion re the driver and will make that change.

You may not be interested but I will explain what the database is for. It will be used by a small motor dealer franchise (VW) it is for the staff vehicles that the staff drive overnight, and holiday. We have a lot of fixed speed cameras, red light cameras and toll motor ways. This is so that the dealership can track who was driving the vehicle and when.

THANK YOU VERY MUCH,






Private Sub OutDate_BeforeUpdate(Cancel As Integer)

Dim strMsg As String, strTitle As String
Dim intStyle As Integer

If Not Me.VehicleID = DLookup("[VehicleID]", "Qry_AvailVehicles") Then

strMsg = "This Vehicle has not been returned @ 1.This entry will now be cleared @ 2. Select another vehicle"
strTitle = "Vehicle Allocation Error"
intStyle = vbOKOnly
MsgBox strMsg, intStyle, strTitle
Cancel = True
Me.Undo

Else
Exit Sub
End If


End Sub

 

[ansentry]

Nouba,

My way failed, so I did it your way and it works fine..


Thank you for your help and suggestions.