Get a record in a table from the data in a listbox (1 Viewer)

[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.

 

[Pat Hartman]

On the other hand, if your BE is ODBC or you might need to upsize in the future, you would NEVER use local filtering since it defeats the purpose of upsizing to SQL Server et al.

If I have an unbound combo, list, or textbox (or combination thereof) that is used for filtering, I reference those controls in my RecordSource query.

Select ...
From ...
Where Somefield = Forms!myform!myListbox Or SomeOtherField = Forms!myform!mycombo;

Using this method, the form always opens empty, to a new record. You choose an item from the combo/list or type it in the listbox and in the AfterUpdate event of the control, requery the form:
Me.Requery

If you have only ONE search field, do the requery in the control's AfterUpdate event, but if you have more than one, add a button and do the requery in the click event of the button.

Since most of my apps end up being bound to some server-side RDBMS, I just use this method all the time. It works regardless of your BE type whereas, the old Access style of local form filters works ONLY when you are using Jet or ACE tables. Because I design with good client/server technichiques from day 1, I can convert most apps from Jet/ACE to SQL Server in an afternoon. It only takes that long because it takes a long time to test everything. The actual conversion is rarely more than a half hour unless I run into some data conversion issue.