Order Processing System

[fatmbk]

I am currently building an order processing system using MS Access 2000. I have hit a problem - I need to be able to change the data in one field of the open record when the user goes onto the next record. i can't work out how to do it. i would appriciate it if someone could point me in the right direction.

To put it a little more in to context:

I have created a table for stock called tblStock. within this table there is a field called StockStatus. This is either Available or Sold (text field). There is another table called tblOrder. I have designed a form that enables the user to enter order details and then select the stock for that order via a sub form. this sub form contains all the list of stock that has its StockStatus set to Available (using a filter via query). i then need to be able to somehow set the StockStatus to Sold when the user selects that piece of stock to be added to the order. In other words i need the stock that has been sold not to be listed in the list of stock once it has been sold - without deleting it from the stock table.

Hope someone out there understands!

Thanks in advnce for any help.

Fatmbk

 

[KenReay]

Hi

one option:

Execute an update query to update the relevant column(stockStatus) of the stock table, and requery the sub form

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[fatmbk]

Thanks - that makes sense now - one question:

How do you requery?

Thanks.

 

[KenReay]

Hi

To requery the main form

Me.Requery

To requery the subform

Me.YourSubformcontrolname.requery

using your control names of course

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[MichaelRed]

You details seem to indicate a situation which is poor database design. Having the stock status change on usage would appear to indicate that ALL items in stock are individually listed (e.g. 23 HP Laser Printers listed individually via serial number?). Although not completly outlandish in general, this would generally indicate a data set which is not properly normalized. While it is simple in a small data base (a few hundered items) in most retail situations it would become rather cumbersome.




MichaelRed
mlred@verizon.net

 

[fatmbk]

Thanks for all your input. I do agree with the last post - perhaps it isn't properly normalised. The database is for a computer business that usually has maybe 200 - 300 items of stock. As i think about it now, maybe i should have used something which had the stock details in and then had a quantity rather than listing each individual item. Has anyone ever made a similar database to this? It is mainly for tracking stock and orders. If anyone has any tips i would be grateful.

thanks for the help!

 

[MichaelRed]

The 'tutorial' "NorthWind Traders" is a retail operation db and should provide you with an abundance of examples of how these are generally organized.





MichaelRed
mlred@verizon.net