Display Message Box based on Combo Box Selection

Question

I have a form with several subforms. Currently I have two combo boxes. One that filters out the subforms by supplier, and the other selects a price level which determines which subform, price level, is visible. I have that part working fine. However, now I also want to display a message box based on the price level selection.

I have another table that contains special customer pricing. Is it possible for example, when the combo box price level is selected it checks to see if there are customers at that price level with special pricing and display a message box with the customers who have special pricing.

If there are two customers: "Customer Name" and "Customer Name" have special pricing.

Here is my table relations

enter image description here

Here is my current code for the price level combo box

Option Compare Database

Sub ShowSubform()

'Save unsaved changes to currently open subform
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

'Display appropriate subform based on cboPriceLevel chosen
If cboPriceLevel = "J6" Then
    J6_All_subform.Visible = True
    J7_All_subform.Visible = False
    J8_All_subform.Visible = False


    ElseIf cboPriceLevel = "J7" Then
    J6_All_subform.Visible = False
    J7_All_subform.Visible = True
    J8_All_subform.Visible = False

    Else
    J6_All_subform.Visible = False
    J7_All_subform.Visible = False
    J8_All_subform.Visible = True

End If

End Sub

Private Sub Form_Current()

'Call subroutine to display appropriate subform based on template type
ShowSubform

End Sub

Private Sub cboPriceLevel_AfterUpdate()

'Call subroutine to display appropriate subform based on template type
ShowSubform

End Sub

Thanks for the help. Let me know if I don't make sense and you need a better explanation.

Yes, but you didn't give enough details to provide a specific answer. You'd need to add your code to the combo box's Got Focus event if you want it to trigger when it's selected. IF you feel like giving more details, basically pretend you aren't familiar with your project read what you said and see if you understand. For instance, how would I know if a customer was at a price level with special pricing? — Daniel
I would not use the got focus event for a message box. it is going to make tabbing through a form very tedious indeed. — Fionnuala
I was thinking it may be possible to add something to my existing if statement after update for cboPriceLevel — Lars Hovden
if you want to alert someone to the pricing, I would recommend using a text box that displays nothing for J8 and some bright font for a J7 or J6 customer - especially if you position it just after the customer name, as they will be looking in that area already — SeanC

SendETHToThisAddress SendETHToThisAddress · Accepted Answer · 2018-04-07T02:31:27

You can create an On_Click event (or on lost focus or whichever you like) that will trigger when a price is selected. For instance, if you have a table named "Customers" that contains the columns "name" and "specialPricing" then you can run a SQL query to find who has special pricing and display that data to the user. The algorithm would go something like this:

after user selection is made, get price from combo box
use stored SQL query and add the price value into the string: SELECT c.[name] FROM Customers AS c WHERE c.[specialPricing] = " & comboBoxPrice.value & ";"
store the return values from the query in a recordset.
if the RS is empty, display MsgBox to user "no customers with special pricing at this price level" else: loop through the recordset to create a string that contains customers with special pricing and MsgBox this list/string to the user.

Display Message Box based on Combo Box Selection

1 Answers