Problem with a DB (Inventory System)

[elliot315]

I just created a db to manage an office supply. I created 3 tables (tblSupplies, tblEmployees and tblTransaction)
it is not for selling supplies it is just a control system to know who get things from the office (warehouse) and to know when to order more supplies. I related them like this from tblSupplies I created a field ProductID-PK and related to tblTransaction to a ProductID field (Data Type - Number) and from tblEmployees, EmployeeID-PK to tblTransaction to EmployeeID field (Data Type - Number) then created a form that displays the info of the employee and a subform displaying the transactions for that employee. The problem is that when I try to enter a transaction, Access displays a message saying "You cannot add or change a record because a related record is required in table 'Employee'".

This are my tables:
tblEmployee
EmplyeeID
Name
LastName
Title
Email
Extension
Tel
Department

tblSupplies
ProductID
ProductName
Description
SerialNumber
QuantityOnHand
ReorderLevel
Discontinued

tblTransactions
TransactionID
ProductID
EmployeeID
Description
Date
Received
Dispatched

Any suggestion? Thanks

 

[pbaldy]

The message implies you're trying to add a record to the transaction table using an employee ID that does not yet exist in the employee table. Are you?

 

[elliot315]

I have a form that contains the employee Info and a subform that has the transaction info... why it is not recognizing the employee id in the transaction... the EmployeeID I'm using as an example is 2 but the subform is using 0... why? I relate them the right way... it is supposed to see that id

 

[pbaldy]

Are the master/child links set between the form and subform?

 

[elliot315]

Here are the screenshots
(Spanish DB using an English System)

 

[elliot315]

Here is the DB

DB sample, take a look.

 

[pbaldy]

No, the master/child links. Look at the properties of the subform control, on the data tab.

 

[elliot315]

THANK YOU!!
now the second issue... I want to update the quantity on hand based on the transactions.. I tried Sum(received) - Sum(dispatched) from the transaction but it keeps saying !Error

 

[pbaldy]

Personally, I wouldn't try to store that value. Simply calculate it when you need it. Here's a good discussion on inventory:

http://allenbrowne.com/AppInventory.html

 

[elliot315]

I'm not storing it.. it is calculated but it is giving me the error message
thank you for your time

 

[pbaldy]

Well, you have this field name: "QuantityOnHand", which implies you are trying to store it. Where do you have that calculation?

 

[elliot315]

I removed it, now I have an unbound field in the products form that will be used to show the quantity on hand.. at first I thought about storing it, but as you said it is better to have a calculation for that... I tried the Dsum but Im getting a syntax error.. how do I read the received and dispatched fields from the Transactions subform so it gives me the real quantities for each article?... I need to specify the productID and then the formula sum(received) - sum(dispatched).... I'm a newbie in Access :S Which function is need it for that? DSUM ?

 

[pbaldy]

A DSum is one way to do it; here's a good reference on the syntax:

http://www.mvps.org/access/general/gen0018.htm

I might create a query that grouped on the ID field and summed up the quantities, giving me an inventory count by product. Then in queries you can simply join to that query to have a field that displays the current inventory.