G
GeraldM
G'day Everyone:
I am developing an access 2003 db which tracks tools issued from a tool
store to employees. I have run into a problem tring to work out how to update
data in more than one table as tools are issued and returned.
Tables - fields:
tblTool - ToolId, ToolDescription, ToolQty, AvailableQty
tblEmployee - EmpId, EmpName, ToolLimit, ToolsOnLoan
tblIssueReg - RegId, fToolId, fEmpId, IssuDate, RetDate, IssuQty
Each tool has a unique ID but there may have more than one of a particular
tool, eg 10 x 5mm drill, 5 x hacksaws etc.
Each employee has a unique employee ID and a preset limit to the number of
tools he may have at any one time (ToolLimit).
The issue register records issues and returns.
When a tool is issued to an employee:
1. a record is added to tblIssueReg
2. tblTool.QtyAvailable needs to reduce buy the qty issued (but not < zero).
3. tblEmployee.ToolsOnLoan needs to increase by the qty issued (but not >
ToolLimit).
When a tool is returned by an employee:
1. the appropriate record in tblIssueReg is ammended
2. tblTool.QtyAvailable needs to increas buy the qty returned.
3. tblEmployee.ToolsOnLoan needs to reduce by the qty returned.
all this will be controlled using a ToolIssue form.
So far so good - now the hard bit (for me anyway).
I cannot work out a simple way to update the quantity issued or returned in
both tblTool and tblEmployee. i have considered writing vba script to do this
but this appears to be overkill to me and may in fact not be the most
efficient. i suspect i can use an update query but cannot work out how to
enter the values into one via the ToolIssue form or to update both tables
-- Thanks in advance
PS if anyone has any suggestions you can e-mail me direct at:
[email protected]
I am developing an access 2003 db which tracks tools issued from a tool
store to employees. I have run into a problem tring to work out how to update
data in more than one table as tools are issued and returned.
Tables - fields:
tblTool - ToolId, ToolDescription, ToolQty, AvailableQty
tblEmployee - EmpId, EmpName, ToolLimit, ToolsOnLoan
tblIssueReg - RegId, fToolId, fEmpId, IssuDate, RetDate, IssuQty
Each tool has a unique ID but there may have more than one of a particular
tool, eg 10 x 5mm drill, 5 x hacksaws etc.
Each employee has a unique employee ID and a preset limit to the number of
tools he may have at any one time (ToolLimit).
The issue register records issues and returns.
When a tool is issued to an employee:
1. a record is added to tblIssueReg
2. tblTool.QtyAvailable needs to reduce buy the qty issued (but not < zero).
3. tblEmployee.ToolsOnLoan needs to increase by the qty issued (but not >
ToolLimit).
When a tool is returned by an employee:
1. the appropriate record in tblIssueReg is ammended
2. tblTool.QtyAvailable needs to increas buy the qty returned.
3. tblEmployee.ToolsOnLoan needs to reduce by the qty returned.
all this will be controlled using a ToolIssue form.
So far so good - now the hard bit (for me anyway).
I cannot work out a simple way to update the quantity issued or returned in
both tblTool and tblEmployee. i have considered writing vba script to do this
but this appears to be overkill to me and may in fact not be the most
efficient. i suspect i can use an update query but cannot work out how to
enter the values into one via the ToolIssue form or to update both tables
-- Thanks in advance
PS if anyone has any suggestions you can e-mail me direct at:
[email protected]