We help IT Professionals succeed at work.

Limit Combobox to only 1 instance in the records

pdvsa
pdvsa asked
on
Experts,

I have this form with cboBankType.
The form is Continous Forms with underlying table : tblBanks_Participating
ID = BankEnterID

I want to limit a users input on this cboBankType to only 1 occurence of a "Initial Issuing Bank" (the ID is =1 on this BankType)...meaning that there can only be 1 "Initial Issuing Bank".

When creating records, How can I limit a duplicate entry on cboBankType for only when the cboBankType ID is =1 which corresponds to  "Initial Issuing Bank"? (this means that the previous record has this banktype description already)...

cboBankType [BankType field name] has this as a row source:
SELECT tblBankType_dropbox.BankTypeID, tblBankType_dropbox.BankType FROM tblBankType_dropbox ORDER BY tblBankType_dropbox.BankTypeID;

Please ask if you have questions.  I am thinking that possibly there is some type of Validation rule you can setup or something pretty simple to limit to only one "Iniital Issuing Bank".
Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013

Commented:
Ise the after update event of your combo box and the current event of your form for this, to change the combo's rowsource accordingly:


If DCount ("*", "YourTable", "BankTypeID = " & 1) > 0 then
     me.cboBankType.Rowsource = "SELECT tblBankType_dropbox.BankTypeID, tblBankType_dropbox.BankType FROM tblBankType_dropbox  WHERE BankTypeID <> 1 ORDER BY tblBankType_dropbox.BankTypeID"
End IF
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2013
Commented:
Alternatively (IMO a better method), simply use data validation in either the before update event of the control or the before update event of the form:

If DCount ("*", "YourTable", "BankTypeID = " & 1) > 0 AND Me.cboBankType = 1 then 
     msgbox "You have already use Initial Issuing bank.  Please select something else"
     me.cboBankType.undo
     Cancel = true
End IF 

Open in new window

CERTIFIED EXPERT
Commented:
This is quite tricky.
The basic issue is that there can only be one rowsource for the drop down list (the same one for all visible records)
and if you remove the '1' value from the dropdown list then the value will disappear from the screen in the one record where it should appear (because it is no longer in the list).
The standard solution to this issue is to add a textbox to the form in addition to the combo, and display the current saved value in the textbox; with a bit of careful placement of controls you can create an illusion of  there being a single control.

But first can you say what happens after the '1' - can all other values in the combo appear more than once, or are they also limited to a single occurrence.
pdvsaProject finance

Author

Commented:
Mbizup:  will try that....

Peter:  
<But first can you say what happens after the '1' - can all other values in the combo appear more than once, or are they also limited to a single occurrence.
Short answer:  yes...others are not limited...as many as want.
pdvsaProject finance

Author

Commented:
I think I will try a different approach and see how that goes.   thank you for the information.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.