Form field: replace Value List w/table?

[Sonny Jim]

I have a form with a field that uses a Value List to pick a person's name rather than a linked table of names. Now that the list has grown very long I want to use a table instead of the Value List but the form will not show the old records where the list was used.

What do I need to do to use this newly created "name" table as the source to fill my form field while maintaing the ability to still see the names formerly derived from the Value List as I browse through previously entered records using this form?

 

[Peter Reid]

Add the 'names formerly derived from the Value List' to your new source.

If you don't want these old names selected for new records, you could use vba to change the source based on whether the form is in create mode or not (or if the record is new/old)

 

[Sonny Jim]

Thank you Peter. That is the key to my problem.

 

[Peter Reid]

I would add a new field to your new Names table called inactive and add the old names to the table with the inactive field checked

On the form, add code on the current event to change the source of the combo box, something like

Private Sub Form_Current()
  If Me.NewRecord = True Then
    Me.NameID.RowSource = "SELECT NameID, FullName FROM tbl_Names WHERE Not(Inactive);"
  Else
    Me.NameID.RowSource = "SELECT NameID, FullName FROM tbl_Names;"
  End If
End Sub

You can then mark currently deemed active names as inactive at a later date without further code change

 

[Sonny Jim]

That is a real good idea, thank you very much for your insight Peter!