Msgbox from Query

[chefdaveross]

How would i set up a msgbox from a query that states the name of an item and criteria.

Example: Item Chicken Soup is over target food cost

 

[pl456]

Not usre how you are displaying you results but i would put an IIF expr in the query.


Or buy a different soup (like tomato)

 

[rainman89]

Are you just running a query or is there something that is running the query? Ie a form or something of that nature?

 

[chefdaveross]

i have item pricing that i put into a table.
items make the recipe.
recipe has a cost. I have a query that shows my cost vs selling price.
There is also a target cost/selling price percentage.
I would like to have a msgbox alert me when my costs are above my target percentage.

Such as:
Item Beef Tacos are above target food cost

would it be possible to do this if the query is not open?
thanks!

 

[rainman89]

i have item pricing that i put into a table.
items make the recipe.
recipe has a cost. I have a query that shows my cost vs selling price.
There is also a target cost/selling price percentage.
I would like to have a msgbox alert me when my costs are above my target percentage.

Such as:
Item Beef Tacos are above target food cost

would it be possible to do this if the query is not open?
thanks!

No i do not believe so.

How are you inputting the information into the table? Are you doing it directly?

 

[chefdaveross]

yes, directly

maybe there is another way to do this?

 

[SOS]

yes, directly

maybe there is another way to do this?

USE FORMS for input. They have events and can bring up Message Boxes, etc. based on things. Tables and queries do not have that ability.

 

[rainman89]

also, you never want to give someone direct access to the tables... it can get messy

 

[chefdaveross]

gotcha,
its actually through a form that the tables get updated.
the form is where i add pricing records

to clarify somewhat.

I have a form that adds the price of my item. From there, the recipe price gets updated.
But seeing that I am working on an item form, and my conditional response is on another form, or query
I was wondering if I can have a msgbox alert me on the form I am on that is triggered from another form or query

Or do i have to program all the conditions on the item form msgbox

 

[rainman89]

In the after update event of your price box you can have it check and see what the value of the recipe is. Is the recipe value being stored or is it strictly being calculated in a query.. hope you are going to say query!

What you can do is have it check the value after you input the price, or if you click save, however you are updating the records.. then have it return the message box

You could also put a field on the form that will show you the actual price and use conditional formatting or something like that that will change the color of the text, etc...

 

[chefdaveross]

Yes, were getting closer!
Yes the recipe values are stored in a query.

qryFoodCostAlert qryFoodCostAlert RecipeID RecipeName SumOfTotalItemCost SellingPrice FoodCost DishButton Target_FoodCost 1 skatewing 2.59 19 13.63% Yes 0.27 7 Tuna Tartare 5.57 16 34.81% Yes 0.26

But, as far a field in the form that shows me recipe prices,
I have a separate form for recipe data.
So I am updating an item, such as butter, then all my recipes with butter get updated, if that price of butter sends a recipe or multiple recipes over the target percentage, then I would like a msgbox of recipes and id's to pop up.
Possible?
Looking for a direction in what VBA i need to write

thanks!

 

[pl456]

It might be something like this on a command button's click event, say a button on the form that says save (here the button is called Command14) the part you are interested in is the if to end if. The rest is the code that saves and handles any errors.
It is basically looking for a field on the form called foodcost and comparing it to one called targetfoodcost, you may not have these fields on your form or they may be called something different.

    Private Sub Command14_Click()
  On Error GoTo Err_Command14_Click
   
  If  Me![foodcost] > Me![targetfoodcost] Then
      MsgBox "Item is over target food cost ", vbOKOnly, "Warning"
  End If
   
      DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
   
  Exit_Command14_Click:
      Exit Sub

  Err_Command14_Click:
      MsgBox Err.Description
      Resume Exit_Command14_Click
      
  End Sub

 

[rainman89]

What i would probably do is have a check recipes button that runs the query and then will return a form with all recipes that have a price that is too high...

If you did messageboxes. You would need to loop through and have it return a message box for each one... how many could that be? It could get annoying!

but that is just how I would do it....

 

[chefdaveross]

That is a great idea,
perhaps you mean report instead of form?

 

[rainman89]

That is a great idea,
perhaps you mean report instead of form?

Either way would work. I would use a form. I would only use a report if I wanted to print it out...