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?
SteveL13Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
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
John TsioumprisSoftware & Systems EngineerCommented:
Without some more info a guess
Me.ListBox.RowSource = "SELECT tblLoadsHeader.LoadRecordID, tblLoadsHeader.ContractNumber
FROM tblLoadsHeader
WHERE tblLoadsHeader.[ContractNumber] = " & Me.[ContractNumber]

Open in new window

SteveL13Author Commented:
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.
PatHartmanCommented:
You need to use two separate queries.  The current one that is used as the RowSource for NEW records and the one that John suggested for use with Existing records.  Just expand the code I gave you.

In the Current event of the form:

If Me.NewRecord = True Then
    Me.cboContractNumber.Locked = False
    Me.cboContractNumber.RowSource = "original query"
Else
    Me.cboContractNumber. Locked = True
    Me.cboContractNumber.RowSource = "John's suggested query"
End If

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.