Records in Listboxes

[deesheeha]

Im having a problem for the last 2 days which is annoying me! I have a listbox which reads in records from a table. i want the listbox to work that when you double click on a record in the listbox it opens a form specific to the record chosen.

 

[rjoubert]

Use the DblClick event of your listbox.

 

[deesheeha]

yes im doing that.. here's my code:

Private Sub list46_dblclick()
MsgBox List46.Text
End Sub

 

[kjv1611]

Hmm.. Maybe something like this?

Private Sub List0_DblClick(Cancel As Integer)
  Dim strSQL As String
  Dim strSelected 'For the currently selected item
  strSelected = List0.Selected
  strSQL = "SELECT RecordID FROM tblMyTable WHERE RecordName = '" & strSelected & "'"
  Forms!MyNewForm.RowSource = strSQL
End Sub

Have you tried anything along those lines?

 

[rjoubert]

You would actually need to use List46.Value to get the selected value, assuming you have your Bound Column set to the field you want to query against.

 

[kjv1611]

In a list box, you may be better off using .selected as apposed to [bp.value[/b] as used with text boxes and such.

 

[kjv1611]

typo correction:
In a list box, you may be better off using .selected as apposed to .value as used with text boxes and such.

 

[rjoubert]

From the Access Help...

You can use the Selected property in Visual Basic to determine if an item in a list box is selected. Read/write Long.

expression.Selected(lRow)
expression Required. An expression that returns one of the objects in the Applies To list.

lRow Required Long. The item in the list box. The first item is represented by a zero (0), the second by a one (1), and so on.

I don't think that's what we're looking for here.

 

[rjoubert]

I tested a listbox using the .Value and it returns the Bound Column value of the selected row.

 

[deesheeha]

hmmmm, i tried that and its giving me an error on .selected!!! doesnt make sense!

 

[rjoubert]

Try .Value

 

[deesheeha]

.value seems to return the SQL string

 

[kjv1611]

Oops, sorry about that - goof on my part.

You could probably use somewhat of the code example that I listed above, but change .Selected to .Value.... give that a try...

Private Sub List0_DblClick(Cancel As Integer)
  Dim strSQL As String
  Dim strSelected 'For the currently selected item
  strSelected = List0[b].Value[/b]
  strSQL = "SELECT RecordID FROM tblMyTable WHERE RecordName = '" & strSelected & "'"
  Forms!MyNewForm.RowSource = strSQL
End Sub

This is assuming you are not using a multi-select list box, which I would imagine is the case, usually, when using the double-click event.. Otherwise, you'd need to loop through .ItemsSelected

 

[deesheeha]

thanks for your help. but value just returns a integer. what im looking for is to double click on a value in the listbox (which is just a list of records from a table) and be able to recognise which record ive selected. for example it pulls a record holding a userid and a username from a user table and stores them in the list. then when i click one of these values in the listbox ill be able to tell which user i selected. E.G. i click on a user and a msg box would pop up displaying that users userid and username

 

[rjoubert]

What is the Bound Column of your listbox? This is what the integer represents.

 

[rjoubert]

If you don't have anything set in your Bound Column, then it's likely just giving you the row number. Set your Bound Column to 1, if the first column is the User ID.

 

[deesheeha]

Ah, I see!! Thats what i did wrong. its working now! Hurray. cheers!