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

Bandara

Member
Local time
Today, 18:56
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
Today, 21:26
Joined
May 7, 2009
Messages
16,077
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: 166

Bandara

Member
Local time
Today, 18:56
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
Today, 21:26
Joined
May 7, 2009
Messages
16,077
what have you tried so far?
you create a form using the query.
see form1 and form2.
 

Attachments

  • product_db.accdb
    544 KB · Views: 168

Bandara

Member
Local time
Today, 18:56
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
Today, 21:26
Joined
May 7, 2009
Messages
16,077
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: 171

Bandara

Member
Local time
Today, 18:56
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: 155

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:26
Joined
May 7, 2009
Messages
16,077
see Query2.
form2 is based on this query.
open form1.
 

Attachments

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

Bandara

Member
Local time
Today, 18:56
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: 147

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:26
Joined
May 7, 2009
Messages
16,077
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: 162

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 21:26
Joined
May 7, 2009
Messages
16,077
do you need it as a Reminder also?
 

Attachments

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

Bandara

Member
Local time
Today, 18:56
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
Today, 21:26
Joined
May 7, 2009
Messages
16,077
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