Calendar / Inventory Scheduling - General Strategy
Posted on 2003-02-26
I'm building a database that is tracking inventory. This inventory is generally rented (opposed to sold, although they do a little of that) and I'd like it to track the availability of a particular piece of rental inventory at a particular point in time. So when a client calls requesting to rent 20 widgets on December 25th, I want the application to check to make sure that there are going to be 20 widgets available on that day.
I have a large part of the project done but I’m coming to this calendar part and I want to take the right direction with it. I’ve got some ideas about how to do this but without ever doing it before I’d like to ask some people who have. I know Access has a built Active X Calendar Control but I’m a little leery about diving into that without some assurance it is going to turn ugly on me (MicrosoftL).
Right now I have three general sections (about 25 tables):
1. A Customer Section – To add and edit Customers
2. An Inventory Section – To add and edit Inventory
3. A Transaction Section – To record rentals and sales
Each section is generally one tabbed form with all the details available. An added complexity is I’m using temp tables to essentially build “Save/Cancel” functionality into the application. In other words, the user edits data in a temp table and Saves it to the main table or cancels the changes (it’s removed from the temp folder and the data is reverted – moved from the main table back into the temp table).
The transaction section is where I want the calendar/availability functionality. There are four possible states for a transaction – Quote, Reservation, Invoice, Closed. I want the inventory requested to be unavailable to anyone else when the transaction becomes a reservation.
I'd also like it to have an additional graphical calendar interface that will allow the user to browse availability for particular inventory items. What I'm envisioning is a combo box at the top with all the inventory items (and maybe a text box to indicate the amount requested) and the calendar below. Where there is a lack of inventory I’m thinking the day could be red or something. It would also be nice if the user could double click on a particular day and get the detail by the hour or minute. A transaction records the rental return date and TIME so the information is there for this more detailed look.
I'm a bit of “big spender” on this question so I'd really like some good direction. I'm an intermediate developer, strong within Access and comfortable in VB. I like moonlit walks and... sorry. I hope I’ve given adequate detail in this short novel. I’m obviously available to fill in any detail.