How to limit selection of items from a combo box to once only

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc
Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc used Ask the Experts™
on
I have limited the list of selection from a combobox as you can see from the picture below, now all I want is that the two accounts showing in the combobox MUST NOT BE SELECTED TWICE  , in short I'm saying they must be selected once per transaction NOT TWICE.

Is the a way to use VBA to achieve that?

The combo box name is called ACCOUNTID , this combo box has only two accounts namely ( Stocks  and Work in progress)

productions-vat.png
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You could run this code in the BeforeUpdate event of the combobox:

Dim rs As DAO.Recordset

Set rs = Me.RecordsetClone

rs.FindFirst "AccountID = '" & Me!AccountID.Value & "'"
Cancel = Not rs.NoMatch

If Cancel = True Then
    MsgBox "This Account has already been selected. Please use another."
End If

Open in new window

So how do silot the account code? the two account codes are "77" & "94"which represent the primary keys, so how do i slot in the code????????

im rs As DAO.Recordset

Set rs = Me.RecordsetClone

rs.FindFirst "AccountID = '77" & Me!AccountID.Value & "'"
Cancel = Not rs.NoMatch

If Cancel = True Then
    MsgBox "This Account has already been selected. Please use another."
End If
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
If the combobox is bound to another column than the displayed, try this:

rs.FindFirst "AccountID = '" & Me!AccountID.Column(n) & "'"
' or, for numbers:
rs.FindFirst "AccountID = " & Me!AccountID.Column(n) & ""

Open in new window

where n is the column id (from 0).
Thank you so much Gustav Brock , clearly and professionally you have sorted out this problem once and for all.

Many many thanks


Regards

Chris
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial