Modifying a function

[sjs94704]

I am creating a car rental database. This function is in the OnClick event of the command button to close a car rental contract. Its purpose is to change the status of the car selected from available to rented.



My desired result is to change this routine so that if the user pulls up an existing contract and changes cars for some reason, that the routine is sure to mark the old car as 1 for available and the new car selected as 2 rented.

How should I change the code?


Public Function RentCar()

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * from CarProfileT WHERE CarProfileID=" & Forms!CarRentalContractF!CarProfileCombo)
rs.Edit
rs!StatusID = 2 ' Rented
rs.Update
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing


End Function

[RuralGuy]

Have you considered using DateRented and DateReturned as an indicator of "available"?

[sjs94704]

Well, that won't work because I will use this same routine for if the car is out for repair, so DateRented and DateReturned are not the only choices.

[RuralGuy]

It would still indicate "available" or not regardless of why it is out.

[RuralGuy]

Have you considered what data your function will need in order to achieve the results you desire? How is it going to get that information?

[orange]

Have you created your tables and relationships?
Here is a free, existing model that may give you some ideas
http://www.databaseanswers.org/data_...hire/index.htm

And here are the business facts that underlie the model
http://www.databaseanswers.org/data_...ness_rules.htm

Good luck with your project