Stuck need suggestions

[ncopeland]

Hi

I have a problem that is driving me up the walls. I have a form that allows a user to receipt material or issue material. Table as follows

Receipt No Stock Code Movement Qty

0001 Test123 I 200
0002 Test123 R 500
0003 Test123 I 150

This bit of the form and entry works fine. I want to create another form that the user will select the part number from the part file. With a drop down box the user will select part Test123. A box will then display a quantity total of 150. Total in stock. I is an Issue so it is subtracted. R is a receipt and so added on.

I have struggled with this form. Not sure how to deal with it.

KR


NC.

 

[Remou]

Have you considered DlookUp to retrieve the most recent quantity on file? Alternatively, "InStock" could be held in the products table and updated for each movement of stock.

 

[Pampers]

You can base your combobox on a totals query, where you use Max-function to display the last stock amount to the user.

But if you want to calculate the amount in stock every time something in issued or received, is another matter. You need a running sum query for that...

Pampers
Keeping it simple can be complicated

 

[ncopeland]

Hi

I think the way that has been explained is to use a recipts/Issues table that records issues or receipts. This table has been created and transactions are recorded to this. I have created a query that gets all receipts. I have created a query that gets all issues. The issue query calculates by mutiplying the stock issued by -1 to get a negtive value. The receipts query is multiplied by 1 so giving a positive vale. The two queries are then unioned to get the queries into one query. What I now want to do with this query is on the from to select the part number and the calculated value is displayed.

Any ideas.

KR

NC.

 

[Pampers]

Yes I understand, but what calculated value do you want to be displayed? If I take your first example, you (I)ssued 350 items and (R)eceived 500, meaning the balace is on 150 (+). To calculate the balance, you need something like a running sum calculation/query which does the following...


(Inition Stock = 0)
Issued 200, Balance: -200
Received 500, Balance: 300 (-200+500)
Issued 150, Balance: 150 (300-150)

Or...
you can make a totals query, 'only' showing the total sum of the stock per item (and not the whole history) in you combox.





Pampers
Keeping it simple can be complicated

 

[ncopeland]

Hi

I have created a total query that looks at the union query and creates a line for each part with total available in stock. The user will select using a combo box by selecting from the part table a part number. I want the value that is in that query that relates to that part number to appear on the form.


KR.

NC.

 

[Pampers]

oh, that is what is you mean.
you can refer to combox-values, like:

Me.MyCombox.Column(2)
This will refer to the third column in your combo, since column number 1 is refered to as 0 (Column(0). You can put the code in the after_update or so event.

Pampers
Keeping it simple can be complicated