Thread: 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:

Private Sub Check36_AfterUpdate()
If Me.Check36.Value = -1 Then
Me.DelAddressLine1.Value = Me.InvAddressLine1
Me.DelAddressLine2.Value = Me.InvAddressLine2
Me.DelAddressLine3.Value = Me.InvAddressLine3
'Continue listing each of the controls you wish to have as the same.
End If

End Sub

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