Query to only show a value in a form from a query

[cwwaicw311]

I have solve the problem regarding the entry of operatorID, but the 2 problem above still have not solved yet...

[maximus]

Study the code on the OnClose Event

If intStock - intReorderLevel <= 0 Then

intTransactionIDTaged = DLookup("[TransactionID]", "Inventory_Tracking", "[IventoryID]=" & intInventoryID & " And [Tag]=" & True)


Now there can be two cases when you Stock can be Zero
1) When You are starting with Zero Stock.Stock=0 Re-Order Level=100 so result = -100)
2) When Your Stock becomes equal to or less then the reorder level


when difference between stock and re-order level becomes Zero or less then zero Access starts searching for a taged entry. In the second case it will find one because this has happened due to normal issue and there is a tag entry in the Inventory_Tracking Table. So while closing it finds out the taged entry. and post the details onto the main form.

How to correct this problem:

To introduce a contition that if the stock is zero the form should close without any error. already taken care of that now for your second problem.


There is another bug which is in the tagged stock calculation. as in the calculation of tagged stock in the running sum Inventory clause is not put in and thus it is calculating the total stock.

Now Previously:
Inventory A Receive 1000 Issue 920
Inventory B Receive 2000 Issue 1960

A stock showing 80
B stock Showing 120 (80+40) as total of both the stock is 120

But Now
A stock showing 80
B stock Showing 40


The case of Operator being the same Combo8 to be replace by Combo14 in the SQl string

[maximus]

did you check out the the mdb I attached. Is the problems solved.

[cwwaicw311]

Hi,

There are no problem, and everything is running smoothly. But I am trying to display the latest stock in the main menu listbox so that eventhough is below the limit but user can still see how much balance of stock is there...

If I still cant solve the problem then I would ask for ur help.

Thank,

[maximus]

Sure you are welcome to ask for help. glad that database is running smoothly. One thing that I would personally like to see in your database is that the user cannot issue any more inventory if the stock reaches a certain level that should be below you reorder level.

[cwwaicw311]

Hi,

I still cant manage to DLookup the smaller balance to display on the main menu listbox. Could you help me...??

I also trying to open report from another combo box in the main menu form which value is like the 1st combo box which open the form. So when i choose a item it will open a report about the chosen item only. I have created 12 query for it since i have 12 different item. But, is there another way of doing it with only 1 query...??

Thank,

[maximus]

zip you current data base let me make the modifications. Let me get you straight you want the users to see the actual stock levels fro all inventory on the menu form. and help with reports right.

[cwwaicw311]

Hi,

I have made some modification and adjustment, so overall the name of all column n design would be different.

[maximus]

Well you made quite some changes. You still have kept some erdundant codes I see. I have deactivated them. Remove them as they will cause errors. These codes are not required any more as I see you have removed the text boxes from the main menu form.

'strInventoryType = IIf(IsNull(DLookup("[InventoryType]", "Inventory Table", "[InventoryID]=" & Me.MMInventoryType)), "Stock Nil", DLookup("[InventoryType]", "Inventory Table", "[InventoryID]=" & Me.MMInventoryType))
'strOperatorName = IIf(IsNull(DLookup("[Operator]", "Operator Table", "[OperatorID]=" & DLookup("[OperatorID]", "Inventory_Tracking", "[TransactionID]=" & intLookupTagTransactionID))), "Nil", DLookup("[Operator]", "Operator Table", "[OperatorID]=" & DLookup("[OperatorID]", "Inventory_Tracking", "[TransactionID]=" & intLookupTagTransactionID)))
'intStockAsTaged = IIf(IsNull(DLookup("[Stock]", "Inventory_Tracking Query", "[MyTransID]=" & intLookupTagTransactionID)), 0, DLookup("[Stock]", "Inventory_Tracking Query", "[MyTransID]=" & intLookupTagTransactionID))
'dtTransDate = IIf(IsNull(DLookup("[Date_Time]", "Inventory_Tracking", "[TransactionID]=" & intLookupTagTransactionID)), " ", DLookup("[Date_Time]", "Inventory_Tracking", "[TransactionID]=" & intLookupTagTransactionID))
These codes are on the AfterUpdate Event of the Combo box of the main menu form.

[cwwaicw311]

Hi,

Could you help solve the problem occur in this database. I have use DLookup to look up the value from a query into a report. Somehow the report is duplicating the record and it oni showing the same record but not the new record I enter.
The report is open by a second combo box i create in the main menu form.

Thank.

[maximus]

well you didnot send me this file did you any ways solution will follow very shortly.

[maximus]

here is a modified mdb file I am sending check if this solves your problem. I have just now changed the attachment arranged the report by the order of Transaction ID makes more sense that way.

[maximus]

is your problem solved.

[cwwaicw311]

Hi,

It have solved my problem. I see that the balance in the listbox is still the same. Hope you could help solve that problem also.

Thank.