Stock database

[lizi]

Hi, I am currently creating a database to keep track of stock.

I am new to access/the whole database world and would like to ask for some help...

I am have a ProductInfo table. This includes 'p_productID, p_name....p_sellingprice, p_restocklevel and p_numberinstock' (among others) and customer/staff tables with things such as name, address etc.

I was hoping to do the following...

On a 'transaction' form, I wanted the customer and staff details on the form, with 'product info' as a subform. The member of staff then fills out the form and enters the products that the customer is buying.

Firstly...
I want to have a 'quantity' field on the transaction form so if the customers buying, say, two items, then '2' will be put into this field, then the 'subtotal' field will calculate a subtotal by doing 'sellingprice'*'quantity'.
I have tried to do this by using an unbound textbox(I know a problem i need help with later too!) however when I enter a quantity for the first item that they are buying, it changes the rest of the items to the same quantity value and will not let me change it.
How would I go about making the quantity individual for each product? And how do I 'bound' a textbox to a certain record?

Secondly...
I want it so that after an order quantity has been inputted, it updates the 'productInfo' table and decreases the stock level by the quantity ordered for that particular product.

I hope someone understands this
PLEASE help!

lizi

 

[pbaldy]

First is easy. It is the fact that the textbox is unbound that causes the behavior you describe. To bind it (presuming the form is bound to the table), set the control source property to the quantity field.

Second is trickier, and generally not recommended. Here's one discussion of the topic:

http://allenbrowne.com/AppInventory.html

 

[lizi]

Hey, thanks for your quick reply.

I have tried another method I found online - update queries. Prehaps you could help me with this?

I have made the query and it works, however when i click it, it updates all of the stock levels by the quantities from all forms/tables.
How can I make it so that it will only update the products in that certain transaction?
From what i've read I'm meant to put something along the lines of 'update the same product id as is equal to some other product id...' in the update to... field lol...sorry bad explanation I know.
I tried to include some screen shots of my relationships and stuff but it wont let me because I have under 10 posts lol.

In the query the following values are inserted:

Field: Number in stock
Table: ProductInfo
Update to: [Number in stock]-[QuantityOrdered]

I have the following tables in this query....

ProductInfo:
p_productid
p_name
p_sellingprice
p_restock level
p_Number in stock

Then a Transaction/Product link table:

p_productID
t_transID
QuantityOrdered

Then a Transaction table....
t_transID
t_time
t_date
c_custID
s_StaffID

In a transaction form there is then the fields from the transaction table
then on a subform there is the info from ProductInfo, and 'QuantityOrdered' field from Trans/Product link...This then gets multiplyed by the selling price to create a sub total.

On this form I was hoping to have a 'submit' button, this then....in my idea, would run the query to update the stock levels...but only for those products in that particular transaction, then close the transaction form and display a message, something along the lines of 'Transaction saved'

Any help with this would be GREAT, especially the only updating the products on the particular transaction and making the submit button do as outlined above

Thank you

 

[pbaldy]

Well, the direct answer to your question is that you need a criteria in your query. In SQL view it might look like this, the last line being the criteria part:

UPDATE ProductInfo
SET [Number in stock] = [Number in stock]-Forms!FormName.[QuantityOrdered]
WHERE p_productid = Forms!FormName.ControlName

where the two form references would be getting their respective values from the form you're entering data on (the quantity to subtract and the product to update).

That said, I would really caution against trying to keep that value updated (storing it at all, actually). This first part might be easy enough, but you have to catch anyplace and everyplace that a user might possible add, delete or edit a record. Say after you add this record, the user goes back and changes the quantity from 4 to 5. You've already subtracted 4, so you can't just use the quantity field again. You either have to unwind the first transaction and then subtract 5, or determine the amount of the change and either add or subtract as appropriate. It's a can of worms. I've written stock databases, and I just calculate amount on hand on the fly.

 

[lizi]

Thanks so much...what would 'ControlName' be?

I know its not ideal, I mean I dont even have a form where the products enters orders coming in haha...although i may develop one. but this is just for a small school project and im just playing around, getting to know database before a big project.

Thanks, lizi

 

[pbaldy]

That would be the name of the control on the form (textbox, combo, etc) that contains the appropriate value.