How to retrieve specific items from a table, based on Listbox selection

[GEvans]

Hello all,

This is what I would like to do:

(The part that works
I have a small form with a button, listbox, and 2 textboxes inside it. When
I click on the button, the listbox populates with the "computer names" of all
users currently connected to the database (usually no more than 3 or 4 users).
This is all fine and good and works as advertised.

(The part that doesn't work
When I select one of the computer names in the listbox, I would like to
retrieve the associated users firstname and lastname from a linked table to
populate the 2 textboxes (the table also contains the users computer name and
I would use that as the "key" to grab the correct first and last name).

I'm sure there's a way in VB to retrieve and present the data from the linked
table, using a "After Update" event procedure on the listbox control to fire
it all off, but I can't figure out the "correct code" to save my life....

Any and all advice to a "Access Novice" as myself would be greatly
appreciated! Thanks!!

 

[Alex Dybenko]

something like this:
http://www.mvps.org/access/forms/frm0028.htm

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[GEvans]

Thanks! Haven't actually tried it yet, but I think that just might do the
trick! ;o)

something like this:
http://www.mvps.org/access/forms/frm0028.htm

Hello all,

[quoted text clipped - 25 lines]

Any and all advice to a "Access Novice" as myself would be greatly
appreciated! Thanks!!

 

[GEvans]

Actually that didn't work out, but in the course trying to make it work - I
inadvertently "stumbled" across this solution that works like a charm!! (Hey,
even a blind squirrel finds a nut sometimes!!!)

[source code for the Listbox AfterUpdate() event procedure below]

Private Sub List0_AfterUpdate()
Dim dbs As DAO.Database ' dbs dim'ed as 'Database'
Dim rst As DAO.Recordset ' rst dim'ed as 'Recordset'
Dim qdf As DAO.QueryDef ' qdf dim'ed as 'Query Definition'
Set dbs = CurrentDb ' set dbs to point to the 'Current
Database'
Set qdf = dbs.QueryDefs("qryGetUserName") ' set qdf to point to the
query 'qryGetUserName'
qdf.Parameters("PCName") = Me.List0 ' passes the value of the
listbox to the query

Set rst = qdf.OpenRecordset


Me.Text2 = rst("First Name") ' assigns the 'Text2' textbox the value
returned for 'First Name'
Me.Text4 = rst("Last name") ' assign the 'Text4' textbox the value
returned for 'Last name'

rst.Close ' close recordset

End Sub

It should be noted that the above query, 'qryGetUserName' is a query I
created that pulls data in from the linked table I mentioned in my original
post that contained the users "First Name" and "Last name" columns. the
results returned are based on the parameter "PCName"....

Hope this helps someone else having similar issues!!