We help IT Professionals succeed at work.

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

64 Views
Last Modified: 2019-02-12
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

CERTIFIED EXPERT
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

CERTIFIED EXPERT

Author

Commented:
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
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
CERTIFIED EXPERT

Author

Commented:
Thank you so much Gustav Brock , clearly and professionally you have sorted out this problem once and for all.

Many many thanks


Regards

Chris
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!