Get a record in a table from the data in a listbox

[jpl458]

I have a listbox that contains the results of a query. The left most column in the listbox is the ID (PrimaryKey) of the returned records from the query. Also on the form is a column of text boxes that display the current record. I want to be able to double click a row in the list box and have that members ID become the current record. I have tried this and it doesn't work.

  If IsNull(Me.QryResultlb) Then Exit Sub
    DoCmd.GoToRecord acDataForm, "CallMaster", acGoTo, , , "ID = " & Me.QryResultlb, acFormEdit, acDialog
    Me.QryResultlb.Requery
    Me.QryResultlb = Null

CallMaster is the table
QryResultlb is the listbox
ID is the name of the primary key in the table, and leftmost column in the listbox

I get a Compile Error "Wrong number of arguments or invalid property assignment"

Thanks

 

[moke123]

try using .findfirst and .bookmark

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone


rs.FindFirst "ID = " & Me.QryResultlb


If Not rs.NoMatch Then
     Me.Bookmark = rs.Bookmark
End If

 

[jpl458]

try using .findfirst and .bookmark

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone


rs.FindFirst "ID = " & Me.QryResultlb


If Not rs.NoMatch Then
     Me.Bookmark = rs.Bookmark
End If

Thanks again. Now I have to spend a day or 2 to figure out how your code works/

 

[moke123]

I learn better with examples so look at this.

 

[The_Doc_Man]

Now I have to spend a day or 2 to figure out how your code works

Quick overview:

On a simple bound form you have two recordsets - the main recordset that drives the bound controls, and a clone of the main recordset that looks at the same records as does the main, but acts as a second channel to the same source.

If you use me.recordsetclone to do a search, you can then pick up the location of the thing you found (assuming you found what you wanted). Then you take that location, which is the Me.RecordsetClone.Bookmark, and load that to Me.Recordset.Bookmark - which moves the form to the record you found. But if you didn't find anything, you didn't do anything to the primary recordset or the form bound to it.

You don't use a direct search of Me.Recordset because moving the primary recordset on a bound form has a few side effects including auto-save of any dirty records, and there is some overhead on the primary recordset due its being bound to something. Moving the recordset clone does not have those negative side-effects.