Use list box to find record

[Jonathan Brown]

I have a database that keeps track of employees. It used to be in MS Access
but now I've recently upsized to SQL. I'm in the process of moving from
bound forms to unbound forms. In one of my forms I had a list box that
listed all of the employee names. I had it set up to where if I were to
select one of my employees from the list box the form would go to his/her
record.

I used this code on my list box before:

me.recordsetclone.findfirst "emp# = " & me![lstName]
me.bookmark = me.recordsetclone.bookmark

Now this obviously doesn't work. Could anyone help me figure out the code I
would need to use to open the recordset and then go to the record where
lstname = emp#. Couldn't I do something like the following:

lstName_AfterUpdate

dim cnn as new adodb.connection
dim rst as new adodb.recordset

'open connection
cnn.open "My Provider/Data source string"

'open recordset
rst.open "Select * from tblemployees"

rst.move "where emp# = lstname"

Then the rest of my code would populate the fields on my form with their
associated info. I really like the listbox used as an aid for record
navigation but it's much more complicated now with unbound forms with an sql
backend.

 

[Jonathan Brown]

Nevermind, I've figured it out. Here's the code I used for any interested
parties
-----------------------------------------------------------------------------------------------
Private Sub lstName_afterupdate()

Dim rst as New ADODB.Recordset
Dim cnn as New ADODB.Connection
Dim vartemp as Variant
Dim ctl as Control

'Open Connection
cnn.Open Me.xProvider & Me.xDataSource

'Open recordset
rst.Open "Select * From " & Me.controls("xRecordset"), cnn, adOpenStatic

'Set Recordcount
Me.tbxRecordCount = rst.RecordCount

'Find record in recordset that matches value in lstName
rst.Find "Emp# = " & Me![lstName]

'Iterate through controls on form to match fields in recordset
For Each ctl in Me
On Error Resume Next
Err = 0
vartemp = rst.Fields(ctl.Name).Name
If Err = 0 then
If ctl.enabled then
ctl.Value = rst.Fields(ctl.Name).Value
If ctl.tabindex = 0 then ctl.setfocus
end if
end if
next

end su