Check availability

[limpep]

Hi

I need a little help for my assignment. I am doing a program for a car rent company (made up one).

What my problem is when a customer reserves a vehicle and another customer also wants to that same vehicle they should click on check availaility button to see if that vehicle is available or not.

way it should work is once one customer has reserved a vehicle with a specific start date and end date the button checks the vehicle, start date and end date and give a message to the customer tell them if the vechicle is available or not.

I have started it off but i am stuck in creating this function.


If any one can help i will be very thankful


Thanks Ergun

[Simon Lloyd]

To be fair to you I haven't checked your workbook out as it is YOUR assignment and is meant for you to confirm to your tutor that you have understood and can manipulate the course content, but you would probably want to use a VlookUp in VBA to lookup the list of cars and check the OFFSET(s) for a date in the start date column and then for a date in the End date column, then if dates are found give a message box something like MsgBox "The Vehicle " & Activecell.value & " Is Booked Between " & Activecell.OffSet(0,1).value & " And " &Activecell.OffSet(0,2).VAlue & " Please Check Again!"Regards,
SImon

[limpep]

hmmm i see i'll try that.

[limpep]

For some reason i cant get it to work although all i did was to copy what you wrote.


Private Sub cmd_checkA_Click()

MsgBox "The Vehicle " & ActiveCell.Value & " Is Booked Between " & ActiveCell.Offset(0, 1).Value & " And " & ActiveCell.Offset(0, 2).Value & " Please Check Again!"
End Sub

[Simon Lloyd]

Firstly the line i provided was just a prompt to help you in the right direction, secondly that line will not work unless you tell it what it has to work with i.e which sheet, you will then have to (using the code you have developed) select a cell (this being the activecell) and then it will provide you with the information.

I have attached a small example doing exactly what you want.

Regards,
SImon

[limpep]

This is to advance coding for me i am still at the basice level of programming. would it be possible to give me a better understaning of what your programming is doing.

Here is what i understand for your programming

This part formats combobox2 in to a date format


Private Sub ComboBox2_Change()
ComboBox2.Value = Format(ComboBox2.Value, "dd/mm/yy")
End Sub

No clue what is going on here


Dim MyCell
Dim Rng As Range
Application.ScreenUpdating = False

I think this part sets the range


Set Rng = Sheets("Sheet1").Range("A2:A8")

No clue what is going on here too


For Each MyCell In Rng
If MyCell.Value = ComboBox1.Value Then
MyCell.Select
End If
Next

No clue what is going on here also


With Sheets("Sheet1")
If ActiveCell.Offset(0, 1).Value = ComboBox2.Value Or ActiveCell.Offset(0, 2).Value = ComboBox2.Value Then
MsgBox "The Vehicle " & ActiveCell.Value & " Is Booked Between " & ActiveCell.Offset(0, 1).Value & " And " & ActiveCell.Offset(0, 2).Value & " Please Check Again!"
Else: MsgBox "Car is available!", vbOKOnly, "Availabilty Confirmed"

End If
End With
Unload Me
Application.ScreenUpdating = True
End Sub


Thanks

[Simon Lloyd]

I have just checked out your ASSIGNMENT!, clearly not an assignment, there is code in there beyond the simple look at a cell and display message box, which of course means that you are capable of solving your own question!....Therefore i have removed my attachment!

Apologies to those of you who viewed it and wished to use it (not that there was anything special in it!).

Come clean and get the help you need!

regards,
Simon

[limpep]

So what your saying is i am lieing. I can prove that i am not lieing to just loot at the criteria sheet i attached (only allowed a 1 attachment).





Plus the codings i have in my assignment are given to my be the teacher.

[limpep]

http://i42.photobucket.com/albums/e326/limpep/SWScan00002-1.jpg

http://i42.photobucket.com/albums/e326/limpep/SWScan00007.jpg

http://i42.photobucket.com/albums/e326/limpep/SWScan00004.jpg

http://i42.photobucket.com/albums/e326/limpep/SWScan00005.jpg

http://i42.photobucket.com/albums/e326/limpep/SWScan00006.jpg

Still need proof

[mdmackillop]

Hi Ergun
Under the rules of the forum (http://www.vbaexpress.com/forum/faq.php?faq=psting_faq_item#faq_hom_faq_item) we cannot due your work for you. If you're having problems, post your attempted solution and we may be able to point you in the right direction.

[Bob Phillips]

?26 per hour. Whilst it is low rate, do we get that for any help we provide :devil2:

[Bob Phillips]

Does anybody think as I do that this is a pretty complex problem to give a guy who by his own admission is a programming tyro?

[mdmackillop]

Agreed. There's a lot of complexity here which would take a fair time to comprehend, prior to generating any new code. Definitely not a beginner level exercise.

[Simon Lloyd]

Thanks for the support Bob, Malcom.....when i took a look thats exactly what i thought.....as you are aware i have been on the recieving end of help from both of you, the help has been for "school boy" and more complex code.......but i like others are willing to try ourselves and supply our attempted solution..with you guys being quite expert in what you do you instantly spot who is capable of what from their posts, questions and answers.

I really enjoy helping where i can but of course i don't want to be taken for a mug!

Again my apologies if it seems i am being harsh......i'm working nights so short fused at the mo!

Regards,
Simon

P.S i cant understand why a teacher would supply so much code and "Practicallyfinished" workbook and ask the student to "Dive" in where he has no idea how he got there!

[limpep]

Oh well since i am not going to get any more help here thanks for those of you who have helped and those of who havent helped.

All i asked was for some one to do that little part for me i didnt ask any one to do my work but all i got was grife.

[mdmackillop]

Hi Ergun
Sorry if you feel that way, but if you won't post what you have attempted, I don't see how we can assist.

[limpep]

Well here is what i have tried to do



Sub Reserve_button_Click()
StartDate = Worksheets("Enquiries").Range("D12").Value
EndDate = Worksheets("Enquiries").Range("D14").Value
ReserveTable = Worksheets("Reservation").Range("H" & Row).Value
MakeReservation StartDate, EndDate, ReserveTable
Worksheets("Reservation").Range("H" & Row).Value = ReserveTable
End Sub
Sub cmd_checkA_Click()
StartDate = Worksheets("Enquiries").Range("D12").Value
EndDate = Worksheets("Enquiries").Range("D14").Value
ReserveTable = Worksheets("Reservation").Range("H" & Row).Value
If CheckAvailability(StartDate, EndDate, ReserveTable) Then
MsgBox "Vehicle is available!!"
Else
MsgBox "Sorry, vehicle is not available!!"
End If
End Sub

[Simon Lloyd]

Ergun sorry if it sounded harsh, but you have to admit if you don't understand a FOR NEXT statement or an IF THEN END IF then you are probably overstretching yourself doing a BTEC in this.

We are all willing to help with specific glitches and code needs.

Regards,
SImon

[limpep]

It's not that i don't understand it, it's that i am too lazy and i have to admit it i was hoping for some one to do the availability check for me i didn't ask for any one to do my assignment just to that one part. I was hoping i can get away with it.

I looked at your coding Simon and i just couldnt be asked with it because of my lazyness, Sorry about that.

But if any one still wants to do that part for me i will be very thankful.

[Charlize]

First of all, you have to ask yourself. Do I want to understand it and do I want to learn. If you start and ask your teacher some things that you don't understand, he or she will probably help you out in the right direction (that's why we pay taxes, to teach you some things. In this case, to think in a logical way). Anyway I would probably try the following :
- Look if everything is filled in by doing a checkup on the cells that needs to be filled in.
- if not, give a warning (msgbox)
- You could even check if the rental period is in the future. Check also that start date < end date.
- if everything is filled in, check if that car is filled in in the rental range (use application.worksheetfunction.vlookup or find method).
- if that car is found, check on the dates (dim your variables as date)
(use offset to get the dates of a found car that is to be rented)
- if the new period isn't in the booked periods, then do the booking
Charlize

[Simon Lloyd]

Ergun,the code i supplied even though very simple i.e not using Application.WorkSheetFunction.VLookUp or the FIND method can be applied to your situation, if you use the FIND method (this can be recorded) select the found cell and then you can use the rest of the code i have given you as it looks at the offsets, of course you will have to change the offsets to suit your worksheet but therein you have the tools to create the desired effect!

Regards,
SImon

[Charlize]

Well Ergun, just want to know if you've tried something to find a solution to your problem.

[limpep]

Well here is updata version i have taken it into the beta stage
http://limpep.pwp.blueyonder.co.uk/excelprogram.html

One problem i have with it that even do the vehicle has already be reserved by another customer it still reservse that vehicle

RTable = Worksheets("Vehicles").Range("B" & Row).Value

If RTable = "R" Then
MsgBox ("Sorry this car is not available"), vbOKOnly, "Error"
End

It should check to see if there is any R's then give a error message say vehicle is no available.

Thanks

[Bob Phillips]

RTable = Worksheets("Vehicles").Range("B" & Row).Value

If Application.CountIf(RTable, "R") > 0 Then
MsgBox ("Sorry this car is not available"), vbOKOnly, "Error"
End

[limpep]

RTable = Worksheets("Vehicles").Range("B" & Row).Value

If Application.CountIf(RTable, "R") > 0 Then
MsgBox ("Sorry this car is not available"), vbOKOnly, "Error"
End


Nope this didnt work, still reserves the vehicle even though there is R in the table.


This is how i tried it.

Sub Reserve_button_Click()

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' This is a availability check function ''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
NOOB = Worksheets("Enquiries").Range("A7").Value
Row = 1
Do
Row = Row + 1
LAME = Worksheets("Vehicles").Range("A" & Row).Value
Loop Until (LAME = NOOB) Or (Row = 55)
If Row = 55 Then
MsgBox ("error!")
Else


StartDate = Worksheets("Enquiries").Range("D12").Value
EndDate = Worksheets("Enquiries").Range("D14").Value
ReserveTable = Worksheets("Vehicles").Range("B" & Row).Value
MakeReservation StartDate, EndDate, ReserveTable
Worksheets("Vehicles").Range("B" & Row).Value = ReserveTable
End If
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' This part of the code sets a verable name ''
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''


Title = ComboBox_Title
With Sheets("Enquiries")
FirstName = Worksheets("Enquiries").Range("D21").Value
LastName = Worksheets("Enquiries").Range("D23").Value
Address1 = Worksheets("Enquiries").Range("H20").Value
Address2 = Worksheets("Enquiries").Range("H22").Value
City = Worksheets("Enquiries").Range("H24").Value
PostCode = Worksheets("Enquiries").Range("H26").Value
StartDate = Worksheets("Enquiries").Range("D12").Value
EndDate = Worksheets("Enquiries").Range("D14").Value
VehicleType = Worksheets("Enquiries").Range("A7").Value
RandomN = RNumber
DailyCost = DailyCharge.Text
End With

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'' This part of the code is for checking validation ''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
RTable = Worksheets("Vehicles").Range("B" & Row).Value

If Application.CountIf(RTable, "R") > 0 Then
MsgBox ("Sorry this car is not available"), vbOKOnly, "Error"
End
Else

If CheckBox1 = Empty Then
MsgBox ("Please agree with the terms and condition"), vbOKOnly, "Terms and Condition"
End

Else



If FirstName = "" Then
MsgBox "Please put in a First name", vbInformation
End
ElseIf LastName = "" Then
MsgBox "Please put in a Last Name", vbInformation
End
ElseIf Address1 = "" Then
MsgBox " Please put in vaild adree e.g. 17 walk down street", vbInformation
End
ElseIf City = "" Then
MsgBox "Please enter a vaild city", vbInformation
End
ElseIf PostCode = "" Then
MsgBox "Please enter a vaild postcode e.g. P7 7AB", vbInformation
End
ElseIf StartDate = "" Then
MsgBox "Please enter the date you wish to rent the vehicle e.g. 12/02/2007", vbInformation
End
ElseIf EndDate = "" Then
MsgBox "Please enter the date you wish to return the vehicle e.g. 12/02/2007", vbInformation
End
End If
End If
End If

''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''
'' This part of the code copys data from one place to another ''
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' '''''


Row = 2

Do

Row = Row + 1

Reservation_1 = Worksheets("Reservation").Range("A" & Row).Value

Loop Until IsEmpty(Reservation_1)

Worksheets("Reservation").Range("B" & Row).Value = FirstName
Worksheets("Reservation").Range("C" & Row).Value = LastName
Worksheets("Reservation").Range("D" & Row).Value = Address1
Worksheets("Reservation").Range("E" & Row).Value = Address2
Worksheets("Reservation").Range("F" & Row).Value = City
Worksheets("Reservation").Range("G" & Row).Value = PostCode
Worksheets("Reservation").Range("A" & Row).Value = Title
Worksheets("Reservation").Range("I" & Row).Value = StartDate
Worksheets("Reservation").Range("J" & Row).Value = EndDate
Worksheets("Reservation").Range("K" & Row).Value = VehicleType
Worksheets("Reservation").Range("L" & Row).Value = RandomN
Worksheets("Reservation").Range("M" & Row).Value = DailyCost

End Sub

[Charlize]

I don't understand why you use a reservation column on the vehicles tab. You have a reservation tab for the vehicles where you book a car to be rented. If the name of the car is in that list and you do a check on the start and end date (and later you built in a check on return date not empty to process the line or just go to the next one in the list) you can define if a car is available or not. Since the carnames are unique, you can use that to know if a car is in the list (column K).


'You have defined your variable as date ?
StartDate = Worksheets("Enquiries").Range("D12").Value
EndDate = Worksheets("Enquiries").Range("D14").Value
'you must define a range to hold the whole table. K3:K20
'and use the reservation tab for the range and not vehicles
Set ReserveTable = Worksheets("Reservation").Range("K3:K20")
'ReserveTable = Worksheets("Vehicles").Range("B" & Row).Value
'I've also used a boolean to know if it's True that a car can be booked
'after the cars in the list are being checked.
'started the loop with bookable = True
? MakeReservation StartDate, EndDate, ReserveTable
? Worksheets("Vehicles").Range("B" & Row).Value = ReserveTable
'When you've defined a range to look in, you can use
For Each car In Reservetable
'Then you've got to check if empty with vbnullstring (or there will be an error). When the value is empty you can book in else clause.
'if not empty you've got to proceed to check every carname in the reservetable by using your start and enddates

Hope it will help you a bit.

When you are standing on codewords For Each - If - you can press F1 and you'll get some more background info that could be of some use.

Charlize

[limpep]

The reason i used a reservation table on the vehicle was to make it easyer for the user to see if the that vehicle has been reserved. Also the reason i have a return sheet is for the invoice. It calculates the mileage and return data to give the customer a price.

Sorry you have completely lost me in your coding.

[Charlize]

The reason i used a reservation table on the vehicle was to make it easyer for the user to see if the that vehicle has been reserved. Also the reason i have a return sheet is for the invoice. It calculates the mileage and return data to give the customer a price.

Sorry you have completely lost me in your coding.Well, If you do a check on the reservationsheet you already know that a car is available or not. A car can be booked several times but in another period.

start end Car
13/02/07 15/02/07 Ford
15/03/07 17/03/07 Ford
17/02/07 10/03/07 Ford

Now let's say you want to book Ford between 10/02/07 - 12/02/07 . Will this be bookable ?
1. Check first the car in the reservetable. Going through every car in the list until bookable (my true or false variable) is true. If bookable is not false and last entry is reached because the row K? is empty you can do the booking (that's the easy one). I mean that the name of the car must be different then vbnullstring (a constant of vba).
2. use the start and enddate to compare the periods. 10/02 is less then 13/02 and 12/02/07 is less then 13/02/07. Hey, bookable is true but not last car in list so go on until end of list and bookable must be true to book. If bookable becomes false during the checkup of the list, exit the loop and display message that car isn't bookable in the desired period.

That was my idea. The R in the reservation table on vehicles tab is understandable but why putting an extra R in that table if you can know the periods with a buttonclick. If you want to see the periods of the car you can always make an extra button to show the booked periods.

Charlize

ps.: What do you already have learnt about looping ?
For Next - For Each x In xrange - While Wend - ...
Conditions to check
If Then Else - Select Case ... End Select - ...

What about ranges where you can search, add, delete ?

[Simon Lloyd]

Sorry you have completely lost me in your coding.Ergun Charlize has only supplied your coding back to you with minor changes and plenty of helpful comments, where you just dropped Xld's line in you are not understanding that it is only a line that checks and then displays a message box it does nothing more than that!, you have to replace the message box with the action you would then like to perform.....what Charlize says about checking a list and then the offset is exactly the code i gave you....it's te simplest most accurate way to check your availabilty!

Regards,
Simon

[limpep]

Ok guys i am nearly finished with this project.

http://limpep.pwp.blueyonder.co.uk/excelprogram.html

I got most of the stuff working and all i got left to do is the invoice page

:D

[Charlize]

Looking good.

For the invoice part you could use the doubleclick event on the reservation tab. Since all the info is present on the line that you've doubleclicked, you could copy that info to the invoice tab and mark the entry as invoiced when you print the invoice.

Have you done this by yourself or did you get some guidance (help) for the coding ?

Charlize

[Charlize]

Have you used 365 f's in the reservationtable on the vehicles tab and then change te according day of the year (end - start) from F in R ?

What if you manually remove a booked car ?

Charlize