Combo box entry

  • Thread starter Thread starter AimeeK via AccessMonster.com
  • Start date Start date
A

AimeeK via AccessMonster.com

Hello, I have a form with a combo box that is bound to a query. The query
contains these fields: Product Code, Description, and Status. The combo box
is bound to the Product Code field, in which a user will select a Product
Code, and the text box next to the combo box will automatically populate with
the Product Code's Description. This works wonderfully. However, if a user
selects a Product Code that is a Status 5, I need a message box to come up
saying something like "Item not allowed". (Status 5 items are discontinued
items in my company.) In words, how can I tell the combo box to check the
item's status field to see if it's Status 5? The combo box is named Text260
(switched from originally being a text box), and the query is called
qryProductandDescr. I know I probably will have to place code in some event
somewhere. Can anybody help? Thanks.
 
Hello, I have a form with a combo box that is bound to a query. The query
contains these fields: Product Code, Description, and Status. The combo box
is bound to the Product Code field, in which a user will select a Product
Code, and the text box next to the combo box will automatically populate with
the Product Code's Description. This works wonderfully. However, if a user
selects a Product Code that is a Status 5, I need a message box to come up
saying something like "Item not allowed". (Status 5 items are discontinued
items in my company.) In words, how can I tell the combo box to check the
item's status field to see if it's Status 5? The combo box is named Text260
(switched from originally being a text box), and the query is called
qryProductandDescr. I know I probably will have to place code in some event
somewhere. Can anybody help? Thanks.
Click to expand...

Code the Combo Box BeforeUpdate event:

If Me![Text260].Column(2) = 5 then
MsgBox "Item is discontinued... Re-enter."
Cancel = true
End If

Note: Column(2) is the 3rd column. Change as needed.

I would suggest you change text260 to cboProductCode (or something
which suggests that it is a combo box, not a text control).
 
AimeeK via AccessMonster.com said:
Nevermind, I figured out a way to work around this.
Click to expand...

It's always nice if you tell how you worked around it, for the benefit of
others who are reading your thread, or Googling sometime in the future for
an answer to a similar question.

My question back to you would be, why don't you use criteria in the Query
that is Row Source for your Combo Box, and not even include the
discontinued/not-allowed items? That's certainly one way to fix it, and much
more efficient that allowing the user to choose what appears to be a valid
item, but then slapping their wrist with a MsgBox when they do.

Larry Linson
Microsoft Access MVP
 
Back
Top