Stock Control Database

Hello,

I am creating a stock control database which needs to consists of item numbers, quantities, locations of storage and transactions type. I have created 3 tables:
tbl_CustomerContactList (lists all the customers and their addresses, etc.)
tbl_ItemsList (Lists all the items with their descriptions and it's affiliation to the customer)
tbl_TransactionsList (list of all transactions and also being it's history)

The problems I have occured are:

1. In table cust' contacts I have set up an input mask for the post code [>L?09 L00]. If I put the post code in the table itself it is OK with one little odd - it leaves a space in the place where you have ?. But when I go to do it through the form it only takes 1st letter and does not accept a number unless I put another letter in. Is there a way to fix this? The question mark was supposed to mean: a letter a-z may be entered.

2. In the same form which name is: frm_CustomerContactListInput I have got a check box which I want to use to copy the address if the same for the delivery as per inveice address.
Unfortunate;y I have no idea how to do it. I am going through the access bible but I am a bit slow at the moment - kids need some attention. Do you know what shall I do to make it happen - if you can push me in the right direction it would be great.

3. The same form - I was thinking if when open it can appear blank and maybe I could add clear/save/cancel buttons which would do as it says.

4. Last but not least - In a query qry_StockByPart I want to create a calculated field which would sum quantites conditioned by the transaction type in the Transaction Table. I have for types of transactions:
INV_IN
INV_OUT
COUNT_IN
COUNT_OUT
they are all in one field being chosen from the list. Or shall I create a different table to brake down the transactions, but I want later to create a form to input the transaction (which will be booking in/out procedure). So in that future form they will have to put in the Item number and then qty, location it goes to/from and the transaction type.

I think I covered everything I need to do with it at the moment.

If anybody has any idea what I shall do with it to make it all happen please share your knowledge.

Thanks for reading it and giving it a go.

Simon

For item 2. You need to put some code behind the checkbox. In the properties for the checkbox, in the Afterupdate event, put this code:

Please Login or Register  to view this content.

Not sure about item 1.

For item 4, you will need to create a running sum.

There are two options for this. Look at both and pick the one that you prefer and you think will work best for you.

http://www.datapigtechnologies.com/f...unningsum.html

http://www.mrexcel.com/forum/showthr...t=Running+Sums

In this last one scroll down to thread #8 for the SQL statement.


I am not sure I understand what you want in item 3. When you close a form or advance to a new record, Access automatically saves the record you have just worked on if your form is bound to a table as yours appears to be.

Alan