Display Out of Stock message in a form as a text. (1 Viewer)

Bandara

Member
Local time
Today, 22:57
Joined
Apr 17, 2020
Messages
60
Hi

I have a table with ProductID, ProductName, Quantity. I want to display a message in a form (Not a pop up message just a text in the form) If Quantity is 0 any product ""Product ID, ProductName" is Out of stock". If Quantity Less than 5 I want to display ""Product ID,ProductName" is Low Stock". There are many products in the table. I want display the message according to above requirement.

Example: ID001 Paint Brushes Out of stock, ID001 Paint Brushes Low Stock,

I am Not much expert in Access. If someone tells how to this step by step i appreciate it. Looking reply from you guys. Thank You
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
you can use a Query:

select ProductID, ProductName, Switch(Quantity =0, "Out of stock", Quantity < 5, "Low stock", True, "") As Remarks From Table1;

see query1:
 

Attachments

  • product_db.accdb
    408 KB · Views: 304

Bandara

Member
Local time
Today, 22:57
Joined
Apr 17, 2020
Messages
60
you can use a Query:

select ProductID, ProductName, Switch(Quantity =0, "Out of stock", Quantity < 5, "Low stock", True, "") As Remarks From Table1;

see query1:
Thank You for your reply. I am really Appreciate it. How can i add this to form to display as a text in the form. not a pop up?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
what have you tried so far?
you create a form using the query.
see form1 and form2.
 

Attachments

  • product_db.accdb
    544 KB · Views: 317

Bandara

Member
Local time
Today, 22:57
Joined
Apr 17, 2020
Messages
60
i
what have you tried so far?
you create a form using the query.
see form1 and form2.
it works thank you. one question. is there any way we can show all the out of stocks and low stock products in one form? I mean the form open it show all low stock and out of stock product lists

(example : I001 Paint Low Stock
I001 brushes Low Stock
I001 nuts out of stock Stock
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
i created another query, query2 which is somewhat similar to query1.
it has Criteria (see in design view).
now, i make a form (formNotOKStock, datasheet form) from this query.
 

Attachments

  • product_db.accdb
    616 KB · Views: 322

Bandara

Member
Local time
Today, 22:57
Joined
Apr 17, 2020
Messages
60
i created another query, query2 which is somewhat similar to query1.
it has Criteria (see in design view).
now, i make a form (formNotOKStock, datasheet form) from this query.
i created another query, query2 which is somewhat similar to query1.
it has Criteria (see in design view).
now, i make a form (formNotOKStock, datasheet form) from this query.
Check this form 1. is it possible to disaply the data one by one . now i have to scroll to see it
 

Attachments

  • product_db (1).accdb
    640 KB · Views: 282

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
see Query2.
form2 is based on this query.
open form1.
 

Attachments

  • product_db (1).accdb
    768 KB · Views: 263

Bandara

Member
Local time
Today, 22:57
Joined
Apr 17, 2020
Messages
60
see Query2.
form2 is based on this query.
open form1.
one last can we display this result as a message like attached image red message. now it visible like form
 

Attachments

  • 1641712278070.png
    1641712278070.png
    77.8 KB · Views: 269

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
open form2 in design view and click on Remark textbox.
go to Ribbon->Format->Conditional Formatting, to see the Formatting i made to Remark textbox.
close the form and Open form1 (or form2) to see.
 

Attachments

  • product_db (1).accdb
    768 KB · Views: 294

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
do you need it as a Reminder also?
 

Attachments

  • product_db (1).accdb
    792 KB · Views: 297

Bandara

Member
Local time
Today, 22:57
Joined
Apr 17, 2020
Messages
60
do you need it as a Reminder also?
Actually my idea is showing this message as texts. (like dashbaords) Can we put product id and quantity with remak out of stock. then i can hide other fields. And also is it possible to make reminders
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 01:27
Joined
May 7, 2009
Messages
19,169
you can embed form2 to your dashboard form (did you see sample Northwind from MS?).
you can't have it as Textbox since you don't know how many products have No/low stock.
 

Users who are viewing this thread

Top Bottom