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

[cwwaicw311]

Hi,



I have a form whereby i have created a listbox to show the row of record when the balance is equal to the limit i set in the query. The example should make this clearer...

I have a query for Item A where the minimun amount of balance could be in inventory is 100.

So in the form I create a listbox act like a alert column where if item A reach 100 the whole record where Item A reach 100 including ItemID, Date, Balance would be show in the listbox. Then when Item A have been replenish and more than 100 the record would disappear from the listbox since it above the limit.

Is this possible and How do i do this...??

[maximus]

Well as promised here is your mdb with minimum stock prompt.
Changes made:
1)Field Re-order level in inventory Table
2)Tag Field in Inventory_Tracking Table.

When you stock becomes = to < then re-order level on a particular transaction that transaction is taged and when you select the inventory in the main menu the transaction information is displayed. This will continue till your stock is less then or equal to re-order level. But as soon as the stock goes up and you select the inventory the taged trasaction is released.


I was following your post you wanted to know how to do a running sum. well i have used a query which does the same. now think about adding a danger stock level which should be lower then re-order level and once it reaches that level an user cannot make an more issue of that inventory.

let me know about your progress.

[cwwaicw311]

Hi,

Everything is working perfectly and smoothly. All the requirement from my supervisor is completed. However, he say that if the display column on the main menu form for the minimum balance could make it automatically update rather than update when user click on the selected item. Since, it work as an alert display to the user, so it is better if whenever they see the main menu page they could straight away see which item is currently needed re-ordering. Can it be done...??

Besides this, overall everything is working out perfectly. Thanks.

[maximus]

Glad could help you now a can I ask you a favour. Please mark this thread solved. and in future if you want to build any more of such databases please let me know. More complex and twisted the better.

[cwwaicw311]

Hi,

Sure I will mark this thread solved. But could you see the last problem of this database i post earlier. Regarding prompting the minimum balance on the main menu form automatically rather than by clicking and choosing an inventory.

Thank,

[maximus]

Sure checking now

[cwwaicw311]

hi,

Regarding on the same problem also, I tested out with a few record but i found out that when reach the limit of 100, the record prompt out on the main menu would be the record where the balance is 100. I think it would be better if something although reach minimum already but maybe user could still take out inventory one or two time, coz of delay on new - order or other scenario.

So maybe, you could make the textbox on the main menu to continue display the latest balance until new inventory is add - in.

thank

[maximus]

Yes it is just a promt that the user will receive the first time the stock reaches 100 or below after that the user can make issue and the prompt will not appear.

[maximus]

you have to set a danger level stock and if your stock reaches that level user cannot make issue

[cwwaicw311]

Sure, i will try setting the danger level stock to see how it works. So, how is the problem regarding automatically prompt the minimum balance n other stock at main menu form coming along.

Thank,

[maximus]

just a few more test then it will be ready.

[maximus]

As promised I have modified the database that the stock warning is set on the Inventory_Tracking_System_ Main_Menu form immediately when stock goes below or is equal to reorder level.
Check it out and let me know if you have any problems.

[maximus]

Now I have include a List Box that will show all inventory that is Below the Re-order level.

[cwwaicw311]

Hi,

Everything is working ok, but i found a slight problem on the listbox in the main menu form. The stock seem to be adding.

For example: I choose the first Item n the stock is 50 n display in the listbox since it below 100. Then, i choose the second item to test the listbox, the balance of the second Item should be 20 but it stock 70.

For Item A I receive 1000 and issue 950
For Item B I receive 1000 and issue 980

Could u try test it and see wat is the result of the stock...??
I also would like to ask wat does the coding on the form_close() function on the main form does. Coz whenever I wan to close the main form without any entry I would prompt out error saying Micorsoft Access cant find the form "Main menu form name"

Thank,

[cwwaicw311]

Hi,

I also find out that the main form oni insert 1 operator name even though i choose other operator. It keep inserting "B07810 Nor'Ain"