• Status: Solved
  • Priority: High
  • Security: Public
  • Views: 46
  • Last Modified:

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?
0
SteveL13
Asked:
SteveL13
  • 2
2 Solutions
 
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
0
 
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

0
 
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.
0
 
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now