Help with Access Database + VB

I have a list of names from a database shown in a list box.When a name is clicked and the ok button is pushed the username is assigned to astring variable.But...
How do I get the number associated with the name clicked. I have the field "Name" where the username is extracted from but I also want to extract the next field which is "Lesson Number". So if I click a name which is on lesson 5 how do I take that 5 from the database to the program?

Hope that made sense :] If not look below for pic explanation:

http://picserver.student.utwente.nl/view_image.php/V4551F8A2Z

I have a list of names from a database shown in a list box.When a name is clicked and the ok button is pushed the username is assigned to astring variable.But...
How do I get the number associated with the name clicked. I have the field "Name" where the username is extracted from but I also want to extract the next field which is "Lesson Number". So if I click a name which is on lesson 5 how do I take that 5 from the database to the program?

Hope that made sense :] If not look below for pic explanation:

http://picserver.student.utwente.nl/view_image.php/V4551F8A2Z

is your listbox bind to a table or recordset?

I have a list of names from a database shown in a list box.When a name is clicked and the ok button is pushed the username is assigned to astring variable.But...
How do I get the number associated with the name clicked. I have the field "Name" where the username is extracted from but I also want to extract the next field which is "Lesson Number". So if I click a name which is on lesson 5 how do I take that 5 from the database to the program?

Hope that made sense :] If not look below for pic explanation:

http://picserver.student.utwente.nl/view_image.php/V4551F8A2Z

is your listbox bind to a table or recordset?

A table I think. This is the code for filling up my listbox

Private Sub Form_Load()
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=g:\db1.mdb;Persist Security Info=False"

Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
conn.Open connect
rec.Open "Usernames", conn, 3, 3
rec.MoveFirst

Do Until rec.EOF
lstUsernames.AddItem rec.Fields("Name")
rec.MoveNext
Loop

End Sub

I have a list of names from a database shown in a list box.When a name is clicked and the ok button is pushed the username is assigned to astring variable.But...
How do I get the number associated with the name clicked. I have the field "Name" where the username is extracted from but I also want to extract the next field which is "Lesson Number". So if I click a name which is on lesson 5 how do I take that 5 from the database to the program?

Hope that made sense :] If not look below for pic explanation:

http://picserver.student.utwente.nl/view_image.php/V4551F8A2Z

is your listbox bind to a table or recordset?

A table I think. This is the code for filling up my listbox

Private Sub Form_Load()
connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=g:\db1.mdb;Persist Security Info=False"

Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
conn.Open connect
rec.Open "Usernames", conn, 3, 3
rec.MoveFirst

Do Until rec.EOF
lstUsernames.AddItem rec.Fields("Name")
rec.MoveNext
Loop

End Sub


use a OLEDB Listbox (DataBound List Control) it is much easier to use that populating it at runtime.

but if you don't like it you will need to have a select statement to get the lesson number.

Don't suppose you could give me an example I've been bashing away at this for ages and still haven't got very far....

Don't suppose you could give me an example I've been bashing away at this for ages and still haven't got very far....

on the click event of the listbox

Dim RS As New ADODB.Recordset
Dim sql As String
sql = "SELECT [Lesson NUmber] FROM Usernames WHERE [Name] ='" & lstUser.Text &"'"
RS.CursorLocation = adUseClient
RS.Open sql,Conn, the last parameter should be adCmdText not adCmdTable
if not RS.EOF Then
text1.text = RS.Fields("[Lesson Number]")
end if
RS.Close
set RS = NOthing

Don't suppose you could give me an example I've been bashing away at this for ages and still haven't got very far....

on the click event of the listbox

Dim RS As New ADODB.Recordset
Dim sql As String
sql = "SELECT [Lesson NUmber] FROM Usernames WHERE [Name] ='" & lstUser.Text &"'"
RS.CursorLocation = adUseClient
RS.Open sql,Conn, the last parameter should be adCmdText not adCmdTable
if not RS.EOF Then
text1.text = RS.Fields("[Lesson Number]")
end if
RS.Close
set RS = NOthing

Thanks a lot I'll give it a whirl tommorow I'm sleepy now...

Private Sub Form_Load()
Dim i as integer

connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=g:\db1.mdb;Persist Security Info=False"

Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
conn.Open connect
rec.Open "Usernames", conn, 3, 3
rec.MoveFirst
i = 0
Do Until rec.EOF
lstUsernames.AddItem rec.Fields("Name")
lstUsernames.ItemData(i) = rec.Fields("LessonNumber")
rec.MoveNext
i = i + 1
Loop

End Sub

When the list box is clicked, get the name and then to get the number:

LessonNumber = lstUsernames.ItemData(lstUsernames.ListIndex)

Private Sub Form_Load()
Dim i as integer

connect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=g:\db1.mdb;Persist Security Info=False"

Set conn = New ADODB.Connection
Set rec = New ADODB.Recordset
conn.Open connect
rec.Open "Usernames", conn, 3, 3
rec.MoveFirst
i = 0
Do Until rec.EOF
lstUsernames.AddItem rec.Fields("Name")
lstUsernames.ItemData(i) = rec.Fields("LessonNumber")
rec.MoveNext
i = i + 1
Loop

End Sub

When the list box is clicked, get the name and then to get the number:

LessonNumber = lstUsernames.ItemData(lstUsernames.ListIndex)

Worked like a charm :D Thanx a lot.