Prevent double booking by unique serial and dates

[eddijensen]

Hey you all..!!

I am working on a little booking system (equipment rental) and I have figured out have to match double bookings by serial in column "B", but I want the form to find the serial and check for available dates.
Like this NOT A CODE (Check out the pictures and code if you don't understand)

Find match (Serial) = True then
IF End date (Second TexBox (2) in form) > Column "E" Start Date (and row where it did match the Serial) = True then
MsgBox "There is already a booking in this in selected period"

IF not true then
IF Start Date (First TextBox (1) in form) < Column "F" End Date (and row where it did match the Serial) = True then
MsgBox "There is already a booking in this in selected period"

Thanks in advance, let me know if you need more information.

BookForm.JPG

DataBase.JPG

This is what I have so far

Please Login or Register  to view this content.

And here is the full code if your are interested

Please Login or Register  to view this content.

[p24leclerc]

we need a workbook to work something for you. Can you attach one?

[eddijensen]

Hey p24leclerc.

Thank you for your time, I've have attached a sample of my workbook.

Booking_System_Sample.xlsm

[p24leclerc]

I did some modification to your Book macro. Please look at it and tell me if it looks like I'm on the right way.
The way you look for the serial (which is in column E by the way) you only find the first one. Is it that you can only have one occurence of a serial in this table. If not, then you ought to look for all occurences and test for the start and end date before booking.
As I can't read Danish, I'm not sure I'm doing the right thing but I think I'm not so far. Am I?

[eddijensen]

Can't see anything attached..
You are right with the Serial, it should look for more occurences, because the equipment could be booked more then one with different dates..

[eddijensen]

And yes the column I have change, I Just forgot to change it in the book form before uploading sorry..

[p24leclerc]

Oops! here is the file. It actually searches only the first occurence. Lets see first if I'm working in the right direction.
Regards

[eddijensen]

Hey p24leclerc.

Know I tried the code, it gives good logic, but I can't get it to work. So I tried specify the sheet but it did't work neither.
When I book something in the same period as another it just says "Your equipment is book" and nothing happens.

Maybe it has something to do with the date values, I can't figure out if it reading it right, because the dates in the table are converted to text by CDate when they are pasted in. So maybe there is a clash with dates, but I don't. But the Num is picking up the line of the Serial fine. Oh and if i book stuff after the end date it works as normal. I will try some looking around for date properties if it is the problem.

But thank I think it gives good sense though.

Please Login or Register  to view this content.

[p24leclerc]

Strange as it is working onmy computer.
Lets try this new workbook.
I changed the IF statement using DATESERIAL to compare dates which should not be sensitive to the system configuration.
It can always be a problem when working with dates.

[eddijensen]

That is cool. Okay it is working now when I book inside another booked period, but if I choose a date later then the end date it stills says "Equipment booked" and nothing is put in the table. Funny. Hey this is great now I have something to work with and I can see that it is possible.
Do you know if it is possible to put the date checking inside a variable? Like Check1 = If DateSerial(Year(Trim(txtSlutUdstyr.Value)), Month(Trim(txtSlutUdstyr.Value)), Day(Trim(txtSlutUdstyr.Value))) > DateSerial(Year.....

and then if
Check1 = True
MsgBox "Equipment booked"

Check1 = False
Continue to Check2 and so on

Because what I can see that I need to check the start date as well and then the code gets very big and hard to keep track on.

And one last question (Sorry for the noob stuff) To check for more occurrences for the serial I need to create a loop right? I have tried to work with a loop before, I "made" a search through a UserForm to collect data from a big database, but it did't really work and excel crashed from time to time. So if you are good with loops could you teach me how to do this?

I really appreciate the stuff you already have done, so thanks!

[p24leclerc]

Yes, we can put that in a variable. I'll look into this tomorrow.

Can you tell me what are all the tests you want to do with those dates?
What are the conditions to allow to book an item?

I'll also look into the loop issue tomorrow.
Regards

[p24leclerc]

Try the attached workbook.
I think I was able to set up the date's constraints to prevent double booking along with the loop to search the whole table.
Let me know how it works for you.

[eddijensen]

Wow.!!! You nailed it..!! Thank you so much... You've done it all right with the checking of dates and nice with the variables as well..

When I am done with this book I will send it to you..