L
ltl
Guest
**Overall Problem**
I'm trying to create an inventory database that you can check items out to the people associated with the database but I can't figure out how to create the form to do this. Any help would be greatly appreciated.
**Background information**
First, here are the associated data structures:
>Tables<
People: LastName, FirstName, PersonID
Items: ItemID, Description
Inventory: ItemID, Size, NumInStock, InventoryID
Issued: PersonID, InventoryID, DateIssued, DateReturned
Each ItemID is unique to a particular category of item, however, it can have more than one size in the Inventory form. More than one item from inventory can be issued to the same person in the Issued form.
>Queries<
Inventory: Description, Size, InStock, ItemID, InventoryID
Issued: LastName, FirstName, Description, Size, DateIssued, DateReturned, InventoryID, PersonID
At the moment I have a form that allows you to select a person from a drop-down list and it will then show you the items from inventory (in a datasheet subform) that the person has been issued.
Currently that form looks like:
This is great to display what a person has been issued, but what I would really like is the ability to issue additional items to a person from this form. Currently if I try to create a new row in the datasheet, I get either the error that "Field cannot be updated" or "Cannot add record(s); join key of table 'Issued' not in recordset" depending if I try to add a new row by writing in Description or Size vs. DateIssued or DateReturned.
**Desired solution**
What I want is the ability to issue a new inventory item by first selecting the Item Description from a drop-down combo box, and then select from the allowable sized for that Item from a Size drop-down combo box. This should correspondingly update the Issued form with a PersonID and Inventory ID. It should also subtract one from Inventory.NumInStock for that item (and perhaps return an error if the num in stock is 0?). How do I go about doing this?
I would like the form to look either like:
and when you click issue these items it moves the items from the lower half of the window into the upper half because they've been issued. Alternatively, instead of having the ability to issue multiple items at once, it could just issue one at a time such as:
**How do I do this???**
Regardless, somehow I need to get the Item.ItemID from selecting the description, and using that ItemID present the appropriate sizes, then using those sizes, get the appropriate Inventory.InventoryID, and once you click the button, it gets the Person.PersonID to update the Issued form with the right values. All this is well over my head since I've never used Access before! Any help?
Thanks a million!
LtL
PS. In addition to subtracting one from the inventory count when adding records, I would also like to add one to the inventory count when DateReturned is set. I don't know how to do this either, but adding the record is a higher-priority.
I'm trying to create an inventory database that you can check items out to the people associated with the database but I can't figure out how to create the form to do this. Any help would be greatly appreciated.
**Background information**
First, here are the associated data structures:
>Tables<
People: LastName, FirstName, PersonID
Items: ItemID, Description
Inventory: ItemID, Size, NumInStock, InventoryID
Issued: PersonID, InventoryID, DateIssued, DateReturned
Each ItemID is unique to a particular category of item, however, it can have more than one size in the Inventory form. More than one item from inventory can be issued to the same person in the Issued form.
>Queries<
Inventory: Description, Size, InStock, ItemID, InventoryID
Issued: LastName, FirstName, Description, Size, DateIssued, DateReturned, InventoryID, PersonID
At the moment I have a form that allows you to select a person from a drop-down list and it will then show you the items from inventory (in a datasheet subform) that the person has been issued.
Currently that form looks like:
Code:
-------------------------------------------------------
Select Person {Combo Box}
LastName FirstName
{Text Box} {Text Box}
<Datasheet Subform>
Issued Items:
Description Size DateIssued DateReturned
{Text Box} {Text Box} {Text Box} {Text Box}
{Text Box} {Text Box} {Text Box} {Text Box}
...
-------------------------------------------------------This is great to display what a person has been issued, but what I would really like is the ability to issue additional items to a person from this form. Currently if I try to create a new row in the datasheet, I get either the error that "Field cannot be updated" or "Cannot add record(s); join key of table 'Issued' not in recordset" depending if I try to add a new row by writing in Description or Size vs. DateIssued or DateReturned.
**Desired solution**
What I want is the ability to issue a new inventory item by first selecting the Item Description from a drop-down combo box, and then select from the allowable sized for that Item from a Size drop-down combo box. This should correspondingly update the Issued form with a PersonID and Inventory ID. It should also subtract one from Inventory.NumInStock for that item (and perhaps return an error if the num in stock is 0?). How do I go about doing this?
I would like the form to look either like:
Code:
-------------------------------------------------------
Select Person {Combo Box}
LastName FirstName
{Text Box} {Text Box}
<Datasheet Subform>
Currently Issued Items:
Description Size DateIssued DateReturned
{Text Box} {Text Box} {Text Box} {Text Box}
{Text Box} {Text Box} {Text Box} {Text Box}
...
<Datasheet Subform>
Issue New Items:
Description Size DateIssued
{Combo Box} {Combo Box} {Text Box (default date of today)}
{Combo Box} {Combo Box} {Text Box (default date of today)}
...
[Issue These Items]{Button}
-------------------------------------------------------
Code:
-------------------------------------------------------
Select Person {Combo Box}
LastName FirstName
{Text Box} {Text Box}
<Datasheet Subform>
Currently Issued Items:
Description Size DateIssued DateReturned
{Text Box} {Text Box} {Text Box} {Text Box}
{Text Box} {Text Box} {Text Box} {Text Box}
...
Issue New Item:
Description Size DateIssued
{Combo Box} {Combo Box} {Text Box (default date of today)}
[Issue This Item]{Button}
-------------------------------------------------------**How do I do this???**
Regardless, somehow I need to get the Item.ItemID from selecting the description, and using that ItemID present the appropriate sizes, then using those sizes, get the appropriate Inventory.InventoryID, and once you click the button, it gets the Person.PersonID to update the Issued form with the right values. All this is well over my head since I've never used Access before! Any help?
Thanks a million!
LtL
PS. In addition to subtracting one from the inventory count when adding records, I would also like to add one to the inventory count when DateReturned is set. I don't know how to do this either, but adding the record is a higher-priority.
