Originally Posted by
belliott4488
I had been wondering how to enforce the requirement that the current location equals the new location from the most recent transfer (for a given item).
Create an UDF (User Defined Function), which returns latest value of asked table field (i.e. location for this task) for given filter condition. E.g. using an query like
Code:
SELECT TOP 1 LocationID FROM Transfers WHERE ItemID = SomeItemID AND TransferDate <= Date() ORDER BY TransferDate DESC
And then you add an unbound control into your Items form, which uses this UDF to return the current location for active item.
The reason I did it this way is that it's not uncommon (given the nature of this club) for club members to pass gear among themselves and forget to tell anyone. So, by the time I hear that it's going to someone new, I'm learning that it has been with someone else I hadn't known about. I guess the better solution would be to insist that the people involved provide me with the best reconstruction they can of the item's history, and then I'll add as many transfers as they can remember.
When you discover, that some item's current location is wrong (because the item was relocated without informing you), then additionally having the wrong previous location of this item too doesn't help you in any way! You simply have to register missed location(s) of this item, based on info you get from employee.
So, no assignment to a Category - I get that from the Subcategory, correct? I'm going to need help learning how to create a form that will let me select the Category first and the Subcategory from the related list.
Or you do like I did in my app - in unbound main form you have a single combo, which selects a subcategory, but displays both category and subcategory (like "Computer: Desktop"). The query used for this is ordered by this text, so selecting the right one will be simple and fast. (Remember - when you start typing into combo, in combo's search list you are moved to first entry beginning with entered string!)