Duplicate a recordsource from a Form to a List Box...

[Wrangler36]

I want to populate a listbox using the records displayed on a form, but I can't use the form's recordsource because after the form is opened using the recordsource, the user will make changes to certain fields that will change the contents of the recordsource if the form were to be re-queried.

After the form is opened and the user updates certain fields, I want to display a listbox with the same records displayed in the form, but including the updates to the fields.

For example: The form is opened with a criteria that displays all records where the 'Name' field is Null - and it opens with 5 records. After the form is displayed, the user updates the 'Name' field for two records. If the form is re-queried, it would then only display three records, because the 'Name' field in two of the records has been populated.

I want to be able to display the listbox with all five of the form's records while the form is still open, but display the records with the updates to the 'Name' field.

I hope this is not too confusing!

 

[MajP]

Private Sub Form_AfterUpdate()
  Me.YourListBoxName.Requery
End Sub

 

[Wrangler36]

MajP,

If the listbox has the same recordsource as the form and I requery the listbox after the form is updated, instead of displaying all five records, it will only display three records - because remember that the user has updated two of the 'Name' fields since the form has been opened.

What should the recordsource of the listbox be in order for your suggestion to work?

 

[MajP]

OK, I reread what you are asking. So you want to return the records with a null field, but as you update these records (which are no longer null) you want to show their updates. There is probably a much easier way, but I just could not think of one. So this is how I did it, hopefully someone has a more eloquent solution.

I define a public variable "strList" which is a list of the primary keys that I return from the initial query. So if I return the 5 records I make a list of ID like this

(11,3,7,8,22)

Then after I update the form I change the list box recordsource to something like

"select fields from tablename where ID in (11,3,7,8,22)"

Here is the code

Public strList As String

Private Sub Form_AfterUpdate()
  Me.List10.RowSource = "Select * from tblTasks where autoID in " & strList
End Sub

Private Sub Form_Load()
  Dim rs As DAO.Recordset
  Dim strID As String
  Set rs = Me.RecordsetClone
  strID = "( "
  Do While Not rs.EOF
    strID = strID & rs.Fields("autoID") & ", "
    rs.MoveNext
  Loop
  If Right(strID, 2) = ", " Then
    strID = Left(strID, Len(strID) - 2)
  End If
  strID = strID & " )"
  strList = strID
End Sub

This works fine, but I am sure there is a better solution.

 

[Wrangler36]

Thanks MajP. This will accomplish the job!