I may be going about this the wrong way.. please correct me if I do.

I have an Access database that lists a bunch of equipment, users, and other related information.

Some of this equipment is borrowable... so we're making a little form that will tell us who has what item on what day. The calendaring (heh, is that a word?) and the queries to show what's in and what's out is done, however I am trying to restrict additions to the loaner table if an item is already loaned out.

Taking a page from MS SQL server, I have it in my head that this restriction should take place in the table instead of in the form. The logic being that a person could add the data arbitrarily to the table and bypass any sanity checks coded into the form.

However, it seems that the validation rules for a field in a table aren't complex enough to let me put in a query.

So.. does anyone have any suggested work-arounds?

The table structure is pretty straight-forward - the equipment table has a unique ID and some asset information, the users table has a unique ID and a full name.. the IDs are populated into a 3rd table, "loaned_items", as well as a "StartDate" and "EndDate".

Ideally, I want to run a query something like "SELECT COUNT(*) FROM loaned_items WHERE Equipment_ID = [Equipment_ID] AND (StartDate IS NOT BETWEEN [StartDate] AND [EndDate]) AND (EndDate IS NOT BETWEEN [StartDate] AND [EndDate])" and allow the entry if the value returned is 0. (dont quote me on that query...)

Any advise is greatly appreciated!

I would do that validation on the form, and then change all the table properties to Hidden. That may not work in some environments if the users are nosey.

Alternatively, you could open a hidden form when the database opens (acHidden) and then run an On Timer event every minute or so to look for dupes and notfy the user if there are multiple entries. You would also put that on the form.

You can also make the mdb into an mde, and I think disable shortcut keys (f11) to prevent them from opening the database window. Make sure your start up settings (Tools > Startup...) indicate to not open the database window on start up.

There are other security items available if you run Access Security, but I can't do that in my environment, so don't have many pointers there.

Did any of that help?



mmcdonal

I agree with mmcdonal (as always, he knows his stuff!)
Its always good practise to keep this kind of logic away from the data, the data store is exactly that, a store. The logic should always be done in your code, this makes maintenence a LOT easier! (change a DB after months/years of use is a NIGHTMARE!)

When loading the data from the store, perform the checks you require, if they fail alert the user that this cannot be done for whatever reason, ditch the data, dont do any updates and close the form if necessary.

If this ever requires changing, you just need to change this logic in the form.

Other than that, you could put a flag on each record saying that it is loaned out, and clear this when the item is checked back in, then you could just "SELECT ... WHERE [Item].[Loaned Out] = False" or something.. I would probably do something like that as it makes readability much better, as well as other things like reporting etc..

Hope this offers some inspiration.

Best Regards,
Rob