Resource Management system

[Bobby Digital]

Hi

First off, hello - I've been lurking around these boards for a little while now and picked up some invaluable tips. I've finally gotten round to registering and thought I would present my situation and see what you guys reckon my options are.

First off a little background info...

I work for an Audio Visual Department in a University and we look after alot AV equipment in AV teaching rooms, offer technician support and run an equipment loan service of about which incorporates around 800 items.

I started 'dabbling' with MSAccess around 7yrs ago and as a personal project built a small loan database to keep track of where our equipment currently is. Over the years this grew and grew and now the database pretty much looks after the weekly workschedules of around 10 people, the equipment loan service, the equipment/support we do in teaching rooms and recently incorporated a disability equiipment loan service for disabled staff/students.

I've never really felt that comfortable having so many operations hinge on what was primarily a learning tool for me to familiarise myself with Access all those years ago. I have been toying with the idea of re-writing it at some point and because lately it has been looking a little unstable I feel that time may be sooner rather than later.

This is where my dilemma comes in....

I'm self taught with Access and my VB/VBA knowledge is pretty much non-existent (apart from a couple of bits i've 'borrowed' and manipulated) and I've decided that if I'm going to re-do this I need to do a 'proper' planning job if it's to be done properly.

I'm sure I can replicate pretty much most of what I currently have but make it alot more structured and solid just using the Access programme itself (although I am aware that using VB is a better option but I don't really have time to sit and learn it at the moment as this is really just a sideline to my main AV job).

My biggest problem to overcome is that I've never managed to incorporate a 'pre-booking' system for equipment bookings which would incorporate things like double booking clashes etc. as I don't think my current knowledge level is sufficient to do so.

Basically the operation works a little like this...

1) A booking comes in to the secretary and she pre-books the equipment via a diary/excel sheet and dummy entry into the database for our reference. This can often be multiple items for one client. There may also be a request in for lecture room 'services' (ie a tech to attend and help setup insitu equipment) in a lecture room.


2) On the date that the equipment goes out we book the requested items out to the customer using the database. Or undertake the request to attend a lecture room.

3) When the customer returns the equipment we book the items back into stock on the database. Or add details of what services were used when we attended the lecture room.

It's number 1 where I have the issue. I want the secretary to be able to select items that will be in stock between the dates specified for the term of the loan and bearing in mind there could well be many pre-bookings in for the same items well in advance.

Do you think this sort of thing could be possible without me having to learn VB at a very hasty speed?

Sorry for the long first post and thanks in advance for any suggestions. Btw - I have tried looking around the forums for similar problems/solutions but can't really find any of any use or that I understand fully.

Cheers

(ive enclosed a pic of what i envisage the relationships to look something like)

 

[sam77755]

Hi there
I think basing your booking form on a select query that only select Items available at the requested time would be the answer to your question.
I dont know your table structure but in this case you should have a BookingDateFrom, BookingDateTo field and a BookingTimeFrom, BookingTimeTo fields.
then ask the query to select items that do not have a value in those fields... got the picture? this way you will not need the code but you get the job done.

 

[sam77755]

I dont know your table structure but in this case you should have a BookingDateFrom, BookingDateTo field and a BookingTimeFrom, BookingTimeTo fields.
then ask the query to select items that do not have a value similar to what you are requesting in those fields ( you can do this by comparing the value in unbound text boxes on your form)... this way you will not need the code but you get the job done.

 

[The_Doc_Man]

It's number 1 where I have the issue. I want the secretary to be able to select items that will be in stock between the dates specified for the term of the loan and bearing in mind there could well be many pre-bookings in for the same items well in advance.

This should not be a massive problem. First, if you haven't seen it before, read the help files on the Between ... And operator.

Now, you need a bookings table. (You probably already have one, but let me be just a little bit pedantic here...) In it, you need some obvious things.

tblBookings
BookStart - date/time of beginning of booking. (If you only book whole days, this could be just a date)
BookEnd - date/time of end of booking. (See comments for BookStart)
BookObj - the thing being booked
BookBy - the person (on your staff) making the booking
BookWhen - the date/time when the booking was entered
BookTo - the person for whom the booking is being performed
BookWhere - the place where the object goes.
etc.

OK, if you really want to avoid too much VBA, there is another way to skin this cat. (MMMMMEEEEEEOOOOOOWWWWWRRRRRRR!)

Create a form bound to a table with the identical structure as tblBookings, but call it tblTentative. Anything you would have looked up (like authorized bookers or bookees ... or bookies?), look up on this form the same exact way. If you would have used a drop-down, use a drop-down. If there was an auto-lookup, use an auto-lookup. If you have other validation rules, apply the same rules.

Enter the tentative booking in the tblTentative table. Using the form button wizards, create a button that saves the current record. (It is one of the preset items in the form button wizard.)

Now... here's the key... Create a SELECT query. It might be a little tricky, because it involves two tables (tblBookings and tblTentative) but it won't be THAT tricky. Make it a JOIN of tblBookings and tblTentative on the ID code of the booking object. I.e. the thing you want to book. Or, if you have a generic code for all objects of a similar class, use that instead - much better if you have that kind of code. Look up "Cascading Combo Boxes" by searching this forum. Put the class of object as the first box and the specific object ID/serial number as the second box in the cascade sequence. That way, if you have to go back and try a second object of the same class (see later), it is easy to do.

In this query, you want to find all those established bookings for which one of these cases applies:

1. The tentative booking you just entered has start/end times that bracket the accepted booking start time for the selected object.
2. The tentative booking you just entered has start/end times that bracket the accepted booking end time for the selected object.
3. The tentative booking you just entered has a start time bracketed by an accepted booking's start/end times for the selected object.
4. The tentative booking you just entered has an end time bracketed by an accepted booking's start/end times for the selected object.

There are other ways to do this, but this covers all possible cases including the formal subset cases - where one long booking overlaps both ends of a shorter booking.

That query looks kinda nasty, but it is really just four BETWEEN ... AND operators on four rows of the Criteria portion of the query design grid. These are automagically OR'd together for you. The JOIN across the Object ID does most of the rest of the filtration for you. And the cascading combo box stuff would do the filtration required to find similar objects.

Write a simple-minded report based on this query. You could even use the report wizard to build it. All you want is a list of conflicting bookings.

OK, now create another button on your tentative booking form. There is a wizard preset that lets you open this report. If no records match, you can make the booking. If any records show up, you can see that the item is already booked. When you run the report, you either find a booking in the way or you don't. In either case, close the report and act on the result.

So case 1: Item booked - edit the tentative booking to select another object with similar function and try again. Save the same record (in its updated state) & re-run the query.

Case 2: Item booked and no similar object exists OR last similar object has been tested and still no joy - (get the button wizard to create another button on the tentative booking form to...) Delete the current tblTentative record. Have the secretary relay "No Joy" to the requesting person.

Case 3: The item can be booked. Ahead of time, create two more queries. One appends all records of tblTentative to tblBookings. (This is why I suggested you clone the table - all fields will match up so it is a truly trivial query.) The other query erases all records from tblTentative. (As long as only one person at a time can make a booking, this works OK.)

Create a macro to run these two queries in the order: append first, erase second. Ask the wizard to make one more button on the tentative booking form. In it, ask the wizard to run the named macro. OK, so now you can make all bookings using this button. Just turn a tentative booking into a real one.

NOW... for completeness, make a button on the tblBookings (non-tentative) form so you can UNBOOK an object if the person coming in second has - higher priority / political clout / is better looking (to your secretary) - than the person who made the first booking. That would require you to find the conflicting booking on the form using tblBookings as its recordsource. Once you make that record current, the button built by the wizard can do its thing and erase the record. (But that is a matter of your business rules as to whether / how you might do that mechanically.)

There, no VBA. Except for that set of VBA code the wizard built for you. And by the way, it wouldn't hurt you to read the code so generated. You might pick up a few ideas from it. Learning experiences come where you find them, and that is one place to find them.

 

[Bobby Digital]

WOW!

Thanks for such an in depth response Doc-Man. When I get a good couple of hours to sit and digest that lot I'll see what I can come up with.

Thanks again guys