Car Rental System Documentation

Project In Database Management System CAR RENTAL SYSTEM DOCUMENTATION

Submitted By: Cabunot, Joanna Marie Lubrico, Janen Magallanes, Mary Cris Nahudan, Eiman

Submitted To: Ms. Marinella T. Tose

Car Rental System Analysis

A Car company rents a car. Car (Plate_no, Car_Name, Color, Speed, and rental) is identified by its Car_Id rents by a customer.

Customer

(Lastname,

Firstname,

Birthdate,

Telephone_no, Cellphone_no and Address) is identified by a Cus_Id processed the transaction by the employee. Employee (Lastname,

Firstname,

Address,

Position,

Telephone_no,

Cellphone_no and Birthdate) is identified by its Emp_Id.

Whenever a customer rents a car date_rented, amount, deposit and the no_of_days are kept. In returning the car the date_returned,

date_rented

transaction are recorded.

and

the

who

processed

the

OVERVIEW OF THE FUNCTIONALITIES This System will facilitate the functioning of a "typical" Rental Car store. Each type of car should have a different rental fee per day. Rental fee depends on number of day, brand and how fast the car runs. The system should have the following functionalities: Rent: The system equipped to answer Customer's inquiries about the availability and rental fee of various "types" of cars for certain dates in the future. When the customer makes a decision about the "Type" of car and the Dates, the system should be able to "Reserve" or "Earmark" the requested type of car for requested dates. The customer should be given a "Confirmation Number". Pick Up: The system process a Car Pick Up. Customer walks in and supplies either the confirmation number, or name. The system should pull up all the reservation information about this customer. The customer is then asked to supply a drivers’ license.

Return: The system process a return. The system should record the date, time and processed by Depending on these parameters, the system calculate the final rental amount.

Name: Cabunot, Joana Marie Z. Address: San Roque, Z.C Birthday: November 5, 1989 Work Assigned: System Analyst (is responsible for researching, planning, coordinating and recommending software and system choices to meet an organization's business requirements)

Contribution: bond papers, folder, venue, food, and latop Name: Lubrico, Janen A. Address: San Roque, Z.C Birthday: July 9, 1990 Work Assigned: Programmer (who writes the code of this system.) Contribution: food, fare rate, and water Name: Magallanes, Mary Cris G. Address: Baliwasan Z.C Birthday: December 25, 1990 Work Assigned: Error Corrector and detector (has great practical importance in maintaining data (information) integrity across noisy channels and less-than-reliable storage media.) Contribution: food ,venue, and fare rate Name: Nahudan, Eiman S. Address: San Roque, Z.C Birthday: March 18, 1989 Work Assigned: System Designer (who designs the UI of the system.) Contribution: printing, free internet, food, and venue

CAR RENTAL SYSTEM ERD N

Deposit RENTS N Date_rented Date_rent Date_returned No_of_days Processed_By Amount Car_Id * Car_Id* Cus_Id* Cus_Id* N N

N Cus_Id Lastname RETURN

N

Firstname Birthdate Telephone_No Address

Car_Id Plate_no Car_name Brand Color Speed Rental

ISSUED BY

Car_Id* Emp_Id

Emp_Id 1

Lastname Firstname Address Position

Birthdate Telephon e_no Cellphone _no

CAR RENTAL SYSTEM TABLES cartable Car_Id

Plate_no 1 JKL 900 2 KLI 567 3 AAZ 123 4 ZXC 435 5 QQQ 123

Brand CRV RANGER HONDA CIVIC TOYOTA

Colo Speed r RED 230 mph BLUE 300 mph GREY 300 mph

BLAC 250mph K adventure blue 550mph

Rental

Status

200 300 300

1 1 1

500

1

1000

1

customertable Cus_ Lastna Id me 1 Magallan es 2 Bacus 3 Columbr es 4 De asis 5 Ablao

Firstna Address me Marycris caumpang

Ricky Rheanet te Shiella Gian Carlo 6 Gubante Ferr s Jane

584641976

Cellphone_ no 4545485

RQEREQR XFGVSZDV

56624 65378

776 5464563

10/6/2008 9/30/2008

sdjfsadkjfsd SDFASDFDS

992 9919191

0918 0919

10/6/2008 3/18/1989

jdafsda

9912345

0905123456

9/29/2008

Telephone_no

Birthdate 12/25/2008

employeetable Emp_ Firstnam Addre Telephone cellphone Lastname Id e ss _no _no 1 LUBRICO JANEN FDGH 756765765 5475456 DF 2 TWIEUTIWR TIRUTIR TWTW 4545 4545 UT T 3 DACAY SHEENAM TABUK 993 90512345 AE 4 PANDAY HAZEL CHCO 993 90765432 21

Birthdat Date_hir MyPositi e ed on 10/14/20 10/6/200 Cashier 08 8 9/30/200 12:00:00 cashier 8 AM 10/6/200 12:00:00 manager 8 AM 10/22/20 12:00:00 cashier 08 AM

renttable Cus_ Date_Ren No_of_da Car_Id Id ted ys 1 2 10/15/200 2 8

Amount

Deposit

1500

Status

1500 2

returntable Lastna Firstna Plate_ Carna Date_Rented Date_Returned Process_by me me No me Magallan Marycris QQQ CRV 10/05/2008 10/07/2008 Janen es 123

CAR RENTAL SYSTEM USER INTERFACE

FORM USER AUTHENTICATION

FORM CARRENTAL

If you click Customer Profile, Customer Table will appear.

If you click new command Customer Entity form will appear.

Whenever you did not fill up the required information, this dialog box will appear.

When a record is successfully saved, this dialog box will appear.

When you click Edit command, this form will appear.

When a record is successfully updated, this dialog box will appear.

When we click the Search command, this form will appear.

A message box will appear if you want to continue to delete a record.

When a record is successfully deleted, this dialog box will appear.

If you click Employee Profile, Employee Table will appear.

If you click new command Employee Entity form will appear.

Whenever you did not fill up the required information, this dialog box will appear.

When a record successfully saved, this dialog box will appear.

is

When you click Edit command, this form will appear.

When a record is successfully updated, this dialog box will appear.

When we click the Search command, this form will appear.

A message box will appear if you want to continue to delete a record.

When a record is successfully deleted, this dialog box will appear.

This form will appear whenever there’s a transaction made or when you click the transaction menu bar.

Whenever there’s a new transaction’s made, this form will appear.

Whenever you did not fill up the required information, this dialog box will appear.

When you click the transaction details at the menu bar, this form will appear.

CAR RENTAL SYSTEM CODES FRMCARRENTAL Private Sub car_rent_Click() frmrenttbl.Show End Sub Private Sub logoutsub_Click() Unload Me frmlogin.Show End Sub Private Sub mnucustomerprofile_Click() frmcustbl.Show End Sub Private Sub mnuemployeeprofile_Click() frmemptbl.Show End Sub Private Sub mnusearch_Click() 'frmsearch.Show End Sub Private Sub mnutransaction_Click() 'frmrent.Show End Sub Private Sub quitsub_Click() End End Sub Private Sub retcars_Click() frmreturntbl.Show End Sub FRMCUSTBL Private Sub Command1_Click() cust_opt = 1 frmnewcust.Show (1) End Sub Private Sub Command2_Click() Dim did did = DataGrid1.Columns(0).Text If Not did = "" Then custid = did cust_opt = 2 frmnewcust.cmd_save.Caption = "Update" frmnewcust.Show (1) End If

End Sub Private Sub Command3_Click() Dim pr Call connectme conn.Open pr = InputBox("Please Enter Any Key to Search!" & Chr(10) & Chr(13) & "CUS ID/LASTNAME/FIRSTNAME", "Searching...") If pr = "" Then ElseIf Val(pr) >= 1 Then query = "SELECT * FROM customertable WHERE Cus_Id=" & pr rs.Open query, conn Set DataGrid1.DataSource = rs ElseIf pr = "All" Or pr = "all" Then query = "SELECT * FROM customertable ORDER BY Lastname" rs.Open query, conn Set DataGrid1.DataSource = rs Else query = "SELECT * FROM customertable WHERE Lastname LIKE '" & pr & "%' or Firstname LIKE '" & pr & "%' ORDER BY Lastname" rs.Open query, conn Set DataGrid1.DataSource = rs End If End Sub Private Sub Command4_Click() Dim confirm Dim did did = DataGrid1.Columns(0).Text If Not did = "" Then confirm = MsgBox("This will delete the selected Record!..proceed anyway!", vbOKCancel + vbExclamation, "Warning!") If confirm = vbOK Then Call connectme conn.Open conn.Execute "DELETE FROM customertable WHERE Cus_Id=" & did MsgBox "The Record was successfully Deleted!", vbInformation conn.Close Call myquery End If Else MsgBox "There are no records to delete!", vbCritical End If

End Sub Private Sub Command5_Click() Unload Me End Sub Private Sub Form_Load() Call myquery ''************ USER LEVEL If userlevel = 3 Then Command1.Enabled Command2.Enabled Command4.Enabled Else Command1.Enabled Command2.Enabled Command4.Enabled End If

= False = False = False = True = True = True

End Sub Public Sub myquery() Call connectme conn.Open query = "SELECT * FROM customertable ORDER BY Cus_Id desc" rs.Open query, conn Set DataGrid1.DataSource = rs If Not rs.EOF Then rs.MoveLast reccnt.Caption = rs.RecordCount Else reccnt.Caption = 0 End If Call datgrid DataGrid1.MarqueeStyle = 3 End Sub Public Sub datgrid() DataGrid1.Font.Size = 11 DataGrid1.Font.Name = "Arial" DataGrid1.HeadFont.Size = 11 DataGrid1.HeadFont.Bold = True DataGrid1.HeadFont.Name = "Arial" DataGrid1.Columns(0).Caption = "Cust ID" DataGrid1.Columns(0).Width = 1000 DataGrid1.Columns(1).Width = 3200 DataGrid1.Columns(2).Width = 3200 DataGrid1.Columns(3).Caption = "Telephone No." DataGrid1.Columns(3).Width = 1700

DataGrid1.Columns(4).Caption = "CellPhone No." DataGrid1.Columns(4).Width = 1600 End Sub FRMEMPTBL Private Sub Command1_Click() emp_opt = 1 frmnewemp.Show (1) End Sub Private Sub Command2_Click() Dim did did = DataGrid1.Columns(0).Text If Not did = "" Then empid = did emp_opt = 2 frmnewemp.cmd_save.Caption = "Update" frmnewemp.Show (1) End If End Sub Private Sub Command3_Click() Dim pr Call connectme conn.Open pr = InputBox("Please Enter Any Key to Search!" & Chr(10) & Chr(13) & "EMP ID/LASTNAME/FIRSTNAME", "Searching...") If pr = "" Then ElseIf Val(pr) >= 1 Then query = "SELECT * FROM employeetable WHERE Emp_Id=" & pr rs.Open query, conn Set DataGrid1.DataSource = rs ElseIf pr = "All" Or pr = "all" Then query = "SELECT * FROM employeetable ORDER BY Lastname" rs.Open query, conn Set DataGrid1.DataSource = rs Else query = "SELECT * FROM employeetable WHERE Lastname LIKE '" & pr & "%' or Firstname LIKE '" & pr & "%' ORDER BY Lastname" rs.Open query, conn Set DataGrid1.DataSource = rs End If

End Sub Private Sub Command4_Click() Dim confirm Dim did did = DataGrid1.Columns(0).Text If Not did = "" Then confirm = MsgBox("This will delete the selected Record!..proceed anyway!", vbOKCancel + vbExclamation, "Warning!") If confirm = vbOK Then Call connectme conn.Open conn.Execute "DELETE FROM employeetable WHERE Emp_id=" & did MsgBox "The Record was successfully Deleted!", vbInformation conn.Close Call myquery End If Else MsgBox "There are no records to delete!", vbCritical End If End Sub Private Sub Command5_Click() Unload Me End Sub Private Sub Form_Load() Call myquery ''************ USER LEVEL If userlevel = 1 Then Command1.Enabled = True Command2.Enabled = True Command4.Enabled = True Else Command1.Enabled = False Command2.Enabled = False Command4.Enabled = False End If End Sub Public Sub myquery() Call connectme conn.Open query = "SELECT * FROM employeetable ORDER BY Emp_Id desc"

rs.Open query, conn Set DataGrid1.DataSource = rs If Not rs.EOF Then rs.MoveLast reccnt.Caption = rs.RecordCount Else reccnt.Caption = 0 End If Call datgrid DataGrid1.MarqueeStyle = 3 End Sub Public Sub datgrid() DataGrid1.Font.Size = 11 DataGrid1.Font.Name = "Arial" DataGrid1.HeadFont.Size = 11 DataGrid1.HeadFont.Bold = True DataGrid1.HeadFont.Name = "Arial" DataGrid1.Columns(0).Caption = "Emp ID" DataGrid1.Columns(0).Width = 1000 DataGrid1.Columns(4).Caption = "Telephone No." DataGrid1.Columns(4).Width = 1700 DataGrid1.Columns(5).Caption = "CellPhone No." DataGrid1.Columns(5).Width = 1600 DataGrid1.Columns(7).Caption = "Date Hired" DataGrid1.Columns(7).Width = 1600 End Sub

FRMLOGIN Private Sub Command1_Click() Call connectme conn.Open ssql = "SELECT * FROM myusers WHERE myusername='" & txt_user.Text & "' AND mypassword='" & txt_pass.Text & "'" rs.Open ssql, conn If Not rs.EOF Then With rs userlevel = !user_level myuser = txt_user.Text frmcarrental.Enabled = True Unload Me End With Else

MsgBox "Invalid username and password !", vbCritical, "KRIN" txt_user.SetFocus frmcarrental.Enabled = False End If conn.Close End Sub Private Sub Command2_Click() End End Sub Private Sub Form_Load() frmcarrental.Show End Sub FRMNEWCUST Private Sub cmd_cancel_Click() Unload Me End Sub Private Sub clearfields() txt_Lastname.Text = "" txt_Firstname.Text = "" txt_Address.Text = "" txt_tp.Text = "" txt_cp.Text = "" txt_Position.Text = "" dtdatehired.Value = Now End Sub Private Sub cmd_save_Click() If txt_Lastname.Text = "" Then MsgBox "Please enter Customer Name", vbQuestion, "KRIN" txt_Lastname.SetFocus ElseIf txt_Address.Text = "" Then MsgBox "Please enter Address", vbQuestion, "KRIN" txt_Address.SetFocus Else If cust_opt = 1 Then Call addrec MsgBox "Record was successfully saved!", vbInformation, "KRIN" Unload Me ElseIf cust_opt = 2 Then Call editrec MsgBox "Record was successfully updated!", vbInformation, "KRIN"

End If Call frmcustbl.myquery End If End Sub Private Sub Form_Load() Dim rs1 As ADODB.Recordset Dim rs2 As ADODB.Recordset Set rs1 = New ADODB.Recordset Set rs2 = New ADODB.Recordset Call connectme If cust_opt = 2 Then conn.Open ssqlsub = "SELECT * FROM customertable WHERE Cus_Id=" & custid rs2.Open ssqlsub, conn If Not rs2.EOF Then With rs2 txt_Lastname.Text = !lastname txt_Firstname.Text = !firstname dtbirthdate.Value = !Birthdate txt_tp.Text = !Telephone_no txt_cp.Text = !Cellphone_no txt_Address.Text = !Address End With End If conn.Close End If End Sub Public Sub addrec() '******** save record Call connectme conn.Open conn.Execute "INSERT INTO customertable(Lastname,Birthdate,Telephone_no,Cellphone_no,Address) VALUES('" & txt_Lastname & "','" & txt_Firstname & "','" & dtbirthdate.Value & "','" & txt_tp.Text & "','" & txt_cp.Text & "','" & txt_Address.Text & "')" End Sub Public Sub editrec() '******** update record Call connectme conn.Open

conn.Execute "UPDATE customertable set Firstname='" & txt_Firstname.Text & "', Lastname='" & txt_Lastname.Text & "',Birthdate='" & dtbirthdate.Value & "',Telephone_no='" & txt_tp.Text & "', Cellphone_no='" & txt_cp.Text & "', Address='" & txt_Address & "' WHERE Cus_Id=" & custid End Sub FRMNEWEMP Private Sub cmd_cancel_Click() Unload Me End Sub Private Sub clearfields() txt_Lastname.Text = "" txt_Firstname.Text = "" txt_Address.Text = "" txt_tp.Text = "" txt_cp.Text = "" txt_Position.Text = "" dtdatehired.Value = Now End Sub Private Sub cmd_save_Click() If txt_Lastname.Text = "" Then MsgBox "Please enter Lastname", vbQuestion, "KRIN" txt_Lastname.SetFocus ElseIf txt_Firstname.Text = "" Then MsgBox "Please enter Firstname", vbQuestion, "KRIN" txt_Firstname.SetFocus ElseIf txt_Address.Text = "" Then MsgBox "Please enter Address", vbQuestion, "KRIN" txt_Address.SetFocus Else If emp_opt = 1 Then Call addrec MsgBox "Record was successfully saved!", vbInformation, "KRIN" Unload Me ElseIf emp_opt = 2 Then Call editrec MsgBox "Record was successfully updated!", vbInformation, "KRIN" End If Call frmemptbl.myquery End If End Sub Private Sub Form_Load()

Dim rs1 As ADODB.Recordset Dim rs2 As ADODB.Recordset Set rs1 = New ADODB.Recordset Set rs2 = New ADODB.Recordset Call connectme If emp_opt = 2 Then conn.Open ssqlsub = "SELECT * FROM employeetable WHERE Emp_Id=" & empid rs2.Open ssqlsub, conn If Not rs2.EOF Then With rs2 txt_Lastname.Text = !lastname txt_Firstname.Text = !firstname txt_Position.Text = !MyPosition dtbirthdate.Value = !Birthdate dtdatehired.Value = !Date_hired txt_tp.Text = !Telephone_no txt_cp.Text = !Cellphone_no txt_Address.Text = !Address End With End If conn.Close End If End Sub Public Sub addrec() '******** save record Call connectme conn.Open conn.Execute "INSERT INTO employeetable(Lastname,Firstname,MyPosition,Birthdate,Date_hired,Telephone_no,Cellphon e_no,Address) VALUES('" & txt_Lastname & "','" & txt_Firstname & "', '" & txt_Position.Text & "', '" & dtbirthdate.Value & "','" & dtdatehired.Value & "','" & txt_tp.Text & "','" & txt_cp.Text & "','" & txt_Address.Text & "')" End Sub Public Sub editrec() '******** update record Call connectme conn.Open conn.Execute "UPDATE employeetable set Firstname='" & txt_Firstname.Text & "', Lastname='" & txt_Lastname.Text & "',Birthdate='" & dtbirthdate.Value & "',Date_hired='" & dtdatehired.Value & "',Telephone_no='" & txt_tp.Text & "', Cellphone_no='" & txt_cp.Text &

"', Address='" & txt_Address & "', MyPosition='" & txt_Position.Text & "' WHERE Emp_Id=" & empid End Sub FRMNEWRENT Private Sub cmd_cancel_Click() Unload Me End Sub Private Sub clearfields() txt_Lastname.Text = "" txt_Firstname.Text = "" txt_Address.Text = "" txt_tp.Text = "" txt_cp.Text = "" txt_Position.Text = "" dtdatehired.Value = Now End Sub Private Sub cmd_save_Click() If txt_custid.Text = "" Then MsgBox "Please enter Custmer Information", vbQuestion, "KRIN" customer.SetFocus ElseIf txt_carid.Text = "" Then MsgBox "Please enter Car Information", vbQuestion, "KRIN" txt_plateno.SetFocus ElseIf txt_days.Text = "" Then MsgBox "Please enter No. of days!", vbQuestion, "KRIN" txt_days.SetFocus ElseIf txt_deposit.Text = "" Then MsgBox "Please enter Amount Deposit/Paid!", vbQuestion, "KRIN" txt_deposit.SetFocus Else If rent_opt = 1 Then Call addrec MsgBox "Record was successfully saved!", vbInformation, "KRIN" Unload Me ElseIf rent_opt = 2 Then Call editrec MsgBox "Record was successfully updated!", vbInformation, "KRIN" End If Call frmrenttbl.myquery End If End Sub Private Sub customer_KeyPress(KeyAscii As Integer) If KeyAscii = 13 Then If Not customer.Text = "" Then

ssqlsub = "SELECT * FROM customertable WHERE Customer_Name='" & Trim(customer.Text) & "'" rs.Open ssqlsub, conn If Not rs.EOF Then With rs txt_custid.Text = !Cus_Id txt_cp.Text = !Cellphone_no txt_tp.Text = !Telephone_no txt_Address.Text = !Address End With End If rs.Close End If End If End Sub Private Sub Form_Load() Dim cus, car Dim rs1 As ADODB.Recordset Dim rs2 As ADODB.Recordset Dim rs3 As ADODB.Recordset Set rs1 = New ADODB.Recordset Set rs2 = New ADODB.Recordset Set rs3 = New ADODB.Recordset Call connectme conn.Open If rent_opt = 2 Then ssqlsub = " SELECT * FROM renttable WHERE Rent_Id=" & rentid rs2.Open ssqlsub, conn If Not rs2.EOF Then With rs2 cus = !Cus_Id car = !Car_Id ssqlcus = "SELECT * FROM customertable WHERE Cus_Id=" & cus rs.Open ssqlcus, conn With rs txt_custid.Text = cus lastname.Text = !lastname txt_cp.Text = !Cellphone_no txt_tp.Text = !Telephone_no txt_Address.Text = !Address End With ssqlcar = "SELECT * FROM cartable WHERE Car_Id=" & car rs3.Open ssqlcar, conn With rs3 txt_carid = car txt_plateno.Text = !Plate_no

txt_carname.Text = !Brand txt_color.Text = !Color txt_rental.Text = !Rental End With txt_days.Text = !No_of_days txt_due.Text = !Amount txt_deposit.Text = !Deposit End With End If End If ssql2 = "SELECT lastname FROM customertable" rs1.Open ssql2, conn Set customer.RowSource = rs1 customer.ListField = rs1.Fields("lastname").Name End Sub Public Sub addrec() '******** save record Call connectme conn.Open conn.Execute "INSERT INTO renttable(Cus_Id,Car_Id,Date_Rented,No_of_days,Amount,Deposit,Status) VALUES('" & txt_custid & "','" & txt_carid & "', '" & daterent.Value & "', '" & txt_days.Text & "','" & txt_due.Text & "','" & txt_deposit.Text & "','1')" conn.Execute "Update cartable set Status='2' WHERE Car_Id=" & txt_carid End Sub Public Sub editrec() '******** update record Call connectme conn.Open conn.Execute "UPDATE renttable set Cus_Id='" & txt_custid.Text & "', Car_id='" & txt_carid.Text & "',No_of_days='" & txt_days.Text & "', Amount='" & txt_due.Text & "',Deposit='" & txt_deposit.Text & "', Date_Rented='" & daterent.Value & "' WHERE Rent_Id=" & rentid End Sub Private Sub txt_days_Change() If Not txt_days.Text = "" Then txt_due.Text = Val(txt_rental.Text) * Val(txt_days.Text) txt_due.Text = Format(txt_due.Text, "#########.00") End If End Sub Private Sub txt_plateno_KeyPress(KeyAscii As Integer) If KeyAscii = 13 Then If Not txt_plateno.Text = "" Then

ssqlsub = "SELECT * FROM cartable WHERE Plate_no='" & Trim(txt_plateno.Text) & "' AND Status='1'" rs.Open ssqlsub, conn If Not rs.EOF Then With rs txt_carid.Text = !Car_Id txt_carname.Text = !Brand txt_color.Text = !Color txt_rental.Text = !Rental End With Else MsgBox "The Car is not Available at the moment!", vbCritical Call clearme End If rs.Close End If End If End Sub Public Sub clearme() txt_carid.Text = "" txt_carname.Text = "" txt_color.Text = "" txt_rental.Text = "" End Sub FRMRENTBL Private Sub Command6_Click() frmlistcar.Show End Sub Private Sub Command1_Click() rent_opt = 2 frmnewrent.Show (1) End Sub Private Sub Command2_Click() Dim did did = DataGrid1.Columns(0).Text If Not did = "" Then rentid = did rent_opt = 1 frmnewrent.cmd_save.Caption = "Update" frmnewrent.Show (1) End If End Sub Private Sub Command3_Click() Dim pr Call connectme

conn.Open pr = InputBox("Please Enter Any Key to Search!" & Chr(10) & Chr(13) & "PLATE NO./CUSTOMER ", "Searching...") If pr = "" Then ElseIf pr = "All" Or pr = "all" Then Call myquery Else rs.Open query, conn Set DataGrid1.DataSource = rs Call datgrid End If End Sub Private Sub Command4_Click() Dim confirm Dim did did = DataGrid1.Columns(0).Text lastname = DataGrid1.Columns(1).Text firstname = DataGrid1.Columns(2).Text plateno = DataGrid1.Columns(3).Text carname = DataGrid1.Columns(4).Text daterent = DataGrid1.Columns(5).Text dateret = Format(Now, "mm/dd/yyyy") If Not did = "" Then confirm = MsgBox("This will RETURN the Car Now?..proceed anyway!", vbOKCancel + vbExclamation, "Warning!") If confirm = vbOK Then Call connectme conn.Open conn.Execute "Update cartable set Status='1' WHERE Plate_no='" & plateno & "'" conn.Execute "Update renttable set Status='2' WHERE Rent_Id=" & did conn.Execute "Insert into returntable(Rent_Id,lastname,firstname,Plate_No,Carname,Date_Rented,Date_Returned,Proc ess_by) VALUES ('" & did & "','" & lastname & "','" & firstname & "','" & plateno & "','" & carname & "','" & daterent & "','" & dateret & "','" & myuser & "')" MsgBox "The Return Transaction was successfully saved!", vbInformation conn.Close Call myquery End If Else MsgBox "There are no records to delete!", vbCritical End If End Sub Private Sub Command5_Click() Unload Me End Sub Private Sub Form_Load()

Call myquery ''************ USER LEVEL If userlevel = 3 Then Command1.Enabled Command2.Enabled Command4.Enabled Else Command1.Enabled Command2.Enabled Command4.Enabled End If

= False = False = False = True = True = True

End Sub Public Sub myquery() Call connectme conn.Open query = "SELECT c.Rent_ID,a.lastname,a.firstname,b.Plate_No,b.Brand,c.Date_Rented,c.No_of_days FROM customertable a, cartable b, renttable c WHERE a.Cus_Id = c.Cus_Id AND b.Car_Id = c.Car_Id AND c.Status='1' ORDER BY c.Date_Rented DESC" rs.Open query, conn Set DataGrid1.DataSource = rs If Not rs.EOF Then rs.MoveLast reccnt.Caption = rs.RecordCount Else reccnt.Caption = 0 End If Call datgrid DataGrid1.MarqueeStyle = 3 End Sub Public Sub datgrid() DataGrid1.Font.Size = 11 DataGrid1.Font.Name = "Arial" DataGrid1.HeadFont.Size = 11 DataGrid1.HeadFont.Bold = True DataGrid1.HeadFont.Name = "Arial" DataGrid1.Columns(0).Caption = "Rent ID" DataGrid1.Columns(0).Width = 1000 DataGrid1.Columns(1).Caption = "Lastname" DataGrid1.Columns(1).Width = 2000 DataGrid1.Columns(2).Caption = "Firstname" DataGrid1.Columns(2).Width = 2000 DataGrid1.Columns(3).Caption = "Plate NO." DataGrid1.Columns(3).Width = 1200

DataGrid1.Columns(4).Caption = "Car Name" DataGrid1.Columns(4).Width = 2400 DataGrid1.Columns(5).Caption = "Date Rented" DataGrid1.Columns(5).Width = 1200 DataGrid1.Columns(6).Caption = "No.Days" DataGrid1.Columns(6).Width = 1200 End Sub FRMRETURNTBL Private Sub Command1_Click() Call connectme conn.Open query = "SELECT * FROM returntable WHERE Date_Returned='" & dtsearch.Value & "' ORDER BY lastname" rs.Open query, conn Set DataGrid1.DataSource = rs Call datgrid End Sub Private Sub Command3_Click() Dim pr Call connectme conn.Open pr = InputBox("Please Enter Any Key to Search!" & Chr(10) & Chr(13) & "LASTNAME/FIRSTNAME", "Searching...") If pr = "" Then ElseIf Val(pr) >= 1 Then ElseIf pr = "All" Or pr = "all" Then query = "SELECT * FROM returntable ORDER BY Date_Returned DESC" rs.Open query, conn Set DataGrid1.DataSource = rs Else query = "SELECT * FROM returntable WHERE Lastname LIKE '" & pr & "%' or Firstname LIKE '" & pr & "%'" rs.Open query, conn Set DataGrid1.DataSource = rs End If End Sub Private Sub Command5_Click() Unload Me End Sub

Private Sub Form_Load() Call myquery End Sub Public Sub myquery() Call connectme conn.Open query = "SELECT * FROM returntable ORDER BY Date_Returned desc" rs.Open query, conn Set DataGrid1.DataSource = rs If Not rs.EOF Then rs.MoveLast reccnt.Caption = rs.RecordCount Else reccnt.Caption = 0 End If Call datgrid DataGrid1.MarqueeStyle = 3 End Sub Public Sub datgrid() DataGrid1.Font.Size = 11 DataGrid1.Font.Name = "Arial" DataGrid1.HeadFont.Size = 11 DataGrid1.HeadFont.Bold = True DataGrid1.HeadFont.Name = "Arial" DataGrid1.Columns(0).Caption = "Ret. ID" DataGrid1.Columns(0).Width = 1000 DataGrid1.Columns(1).Width = 0 DataGrid1.Columns(2).Width = 3200 End Sub FORM NEW CAR Private Sub cmd_cancel_Click() Unload Me End Sub Private Sub clearfields() txt_plate_no.Text = "" txt_brand.Text = "" txt_color.Text = "" txt_speed.Text = ""

txt_rental.Text = "" txt_status.Text = "" End Sub Private Sub cmd_save_Click() If txt_plate_no.Text = "" Then MsgBox "Please enter Plate no", vbQuestion, "KRIN" txt_plate_no.SetFocus ElseIf txt_brand.Text = "" Then MsgBox "Please enter Brand", vbQuestion, "KRIN" txt_brand.SetFocus Else If car_opt = 1 Then Call addrec MsgBox "Record was successfully saved!", vbInformation, "KRIN" Unload Me ElseIf car_opt = 2 Then Call editrec MsgBox "Record was successfully updated!", vbInformation, "KRIN" Unload Me End If Call frmcartbl.myquery End If End Sub Private Sub Form_Load() Dim rs1 As ADODB.Recordset Dim rs2 As ADODB.Recordset Set rs1 = New ADODB.Recordset Set rs2 = New ADODB.Recordset Call connectme If car_opt = 2 Then conn.Open ssqlsub = "SELECT * FROM cartable WHERE Car_Id=" & carid rs2.Open ssqlsub, conn If Not rs2.EOF Then With rs2 txt_plate_no.Text = !Plate_no txt_brand.Text = !Brand txt_color.Text = !Color txt_speed.Text = !Speed txt_rental.Text = !Rental txt_status.Text = !status

End With End If conn.Close End If End Sub Public Sub addrec() '******** save record Call connectme conn.Open conn.Execute "INSERT INTO cartable(Plate_no, Brand, Color, Speed, Rental, Status) VALUES('" & txt_plate_no & "','" & txt_brand & "','" & txt_color & "','" & txt_speed.Text & "','" & txt_rental.Text & "','" & txt_status.Text & "')" End Sub Public Sub editrec() '******** update record Call connectme conn.Open conn.Execute "UPDATE cartable set Plate_no='" & txt_plate_no.Text & "',Brand='" & txt_brand.Text & "',Color='" & txt_color.Text & "', Speed='" & txt_speed.Text & "', Rental='" & txt_rental & "', Status='" & txt_status & "' WHERE Car_Id=" & carid End Sub Class Module Global variable Public userlevel Public myuser Public rent_opt Public rentid Public emp_opt Public empid Public cust_opt Public custid Public car_opt Public carid Option Explicit Public conn As ADODB.Connection Public rs As ADODB.Recordset Public Function connectme() Set conn = New ADODB.Connection Set rs = New ADODB.Recordset conn.CursorLocation = adUseClient conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=databasecar.mdb;Persist Security Info=False"

'conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=;Data Source=databasecar.mdb; Persist Security Info=False" End Function