How limit list selections if record is not a new record

SteveL13
SteveL13 used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solutions Only
PatHartman
Distinguished Expert 2017

Commented:
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 Engineer
Commented:
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

SteveL13

Author

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.
Distinguished Expert 2017
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial