Resetting current record on a form

[jpl458]

I asked this before but lost the code because of Brain Fade. Have a form that has records displayed like data entry form, columnar. The is also a button the runs a query and the result is placed in a listbox.

The form is bound to table = Mastetbl3
The listbox = QryResultlb

When the results of the query are in the listbox, I want to be able to doubleclick on a row in the list box and have the data in the columnar part display the data from the row I selected using the ID field, which is a primary key

Before I blew it I had this running, but now using the following code:

 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

on the rs.FindFirst "ID = " & Me.QryResultlb line of code I get this error

What am i missing?

Thanks

 

[theDBguy]

I asked this before but lost the code because of Brain Fade. Have a form that has records displayed like data entry form, columnar. The is also a button the runs a query and the result is placed in a listbox.

The form is bound to table = Mastetbl3
The listbox = QryResultlb

When the results of the query are in the listbox, I want to be able to doubleclick on a row in the list box and have the data in the columnar part display the data from the row I selected using the ID field, which is a primary key

Before I blew it I had this running, but now using the following code:

 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

on the rs.FindFirst "ID = " & Me.QryResultlb line of code I get this error

View attachment 104015

What am i missing?

Thanks

Double check the bound column of your listbox and also try using the Nz() function. For example:

rs.FindFirst "ID=" & Nz(Me.QryResultlb,0)

Hope that helps...

 

[The_Doc_Man]

Got no idea what you are missing, but if you set a breakpoint you would be able to do a Debug.Print of Me.QryResultlb to see what is being returned by it. That might help you figure out what is being concatenated to "ID = " that leads to the error.

In theory you return the bound column of the selected record. By the way, since Me.RecordsetClone IS an open recordset and is actually meant for use in searches, you could immediately run a Me.RecordsetClone.FindFirst operation without going through the extra layer of a new recordset. That might also help simplify the code.

 

[jpl458]

Darn it, found another snake. I seem destined to look past the obvious. I didn't loose the code. I had changed the query doing something else and didn't include the index. Massive increase to my Dope factor.(Dope as in easily distracted, not drugs.)

 

[Pat Hartman]

I would never use the Find method to do this. I would change the form's RecordSource query to reference the listbox.

Where SomeID = Forms!myform!lstSomeID

The form will open empty. Then the user can select something from the listbox. In the click event of the listbox, just requery the form.

Me.Requery

 

[jpl458]

I would never use the Find method to do this. I would change the form's RecordSource query to reference the listbox.

Where SomeID = Forms!myform!lstSomeID

The form will open empty. Then the user can select something from the listbox. In the click event of the listbox, just requery the form.

Me.Requery

Sounds simple enough. Will give it a try.