asked on

How limit list selections if record is not a new record

On a form I have a single select listbox. The SQL behind the listbox is:

SELECT tblLoadsHeader.LoadRecordID, tblLoadsHeader.ContractNumber
FROM tblLoadsHeader
ORDER BY tblLoadsHeader.[ContractNumber];

Which display all ContractNumbers in tblLoadsHeader.

This is fine for a new record. But If I'm viewing an existing record I only want to see the ContractNumber in the listbox that was selected when the record was created. Note: The contractnumber IS being stored in the table that is the record source for the form.

How can I make the listbox only show that record?

PatHartman

You would have to use different queries depending on whether this was a new record or an existing one but that isn't how you should be controlling the ContractNumber. You should lock the control except when you are on a new record and that will prevent anyone from changing it once it has been entered.

In the Current event of the form:

If Me.NewRecord = True Then
Me.cboContractNumber.Locked = False
Else
Me.cboContractNumber. Locked = True
End If

This will still allow you to change the contract number until you leave the record. If you want to lock the combo as soon as the record is saved, then in the AfterUpdate event of the form, add:

Me.cboContractNumber.Locked = True

SOLUTION

John Tsioumpris

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SteveL13

ASKER

Pat, I appreciate your suggestion and will lock it if is not a new record. But I still want to have it display whatever was selected when the record was created.

John, your suggestion may work but I need to have it as VBA code. I don't know how to convert it.

ASKER CERTIFIED SOLUTION

PatHartman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial