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

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)

Microsoft AccessVBA

Last Comment

Gustav Brock

8/22/2022 - Mon

Gustav Brock

2/12/2019

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

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc

2/12/2019

ASKER

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

ASKER CERTIFIED SOLUTION

Gustav Brock

2/12/2019

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial

GET A PERSONALIZED SOLUTION

Ask your own question & get feedback from real experts

Find out why thousands trust the EE community with their toughest problems.

Hankwembo Christopher,FCCA,FZICA,CIA,MAAT,B.A.Sc