Multiselect Listbox

[ZOR]

I have a query which produces 2 column data for a listbox.

SELECT AthleteNames.Athlete, Mid([Athlete],InStrRev([Athlete]," ")+1) AS Expr1
FROM AthleteNames
WHERE (((AthleteNames.Athlete) Like [FORMS]![Memos].[UU].[CAPTION] & "*"))
ORDER BY AthleteNames.Athlete;

If the list is made as a multiselect, how can I just give the user choice between the left or right column, and not both or mixed row. Can it be done? Thanks

 

[ByteMyzer]

The Multiselect property of a listbox refers to Rows, not Columns. What is it that you are trying to accomplish?

 

[TheAceMan1]

Howdy ZOR . . .

Yes it can be done. However it'll take two SQL statements. Consider the following routine:

[blue]Public Sub SetRowSrc(flg As Boolean)
   [green]'flg = True = [b]Athlete[/b]
   'flg = False = [b]Expr1[/b][/green]
   
   Dim SQL As String
   
   If flg Then
      SQL = "SELECT Athlete " & _
            "FROM AthleteNames " & _
            "WHERE ([Athlete] Like [Forms]![Memos].[UU].[Caption] & '*') " & _
            "ORDER BY [Athlete];"
   Else
      SQL = "Mid([Athlete],InStrRev([Athlete],' ')+1) AS Expr1 " & _
            "FROM AthleteNames " & _
            "WHERE ([Athlete] Like [Forms]![Memos].[UU].[Caption] & '*') " & _
            "ORDER BY Mid([Athlete],InStrRev([Athlete],' ')+1;"
   End If
   
   Me![purple][b][b][i]ListboxName[/i][/b][/purple].RowSource = SQL

End Sub[/blue]

How you use the routine is up to you.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997
Also faq181-2886

 

[MajP]

If I understand correctly, you want to be able to pick either the complete name or last name only.

You would need like a grid control, where you can highlight any cell.


The best workarounds I can think of:

1) Use a subform instead of a listbox and format it to look like a listbox. Add click events to capture the clicked selection.

2) Use a union query to return all of the data into a single column:

Butcher
Frank Butcher
Fuller
Robert Fuller
John Fuller

 

[ZOR]

Many thanks all. I am going down the road of a subform (continuos form) dressed up as a Listbox. Is there any way to replace bound fields with command buttons having their captions filled with what the fields were. I have two fields side by side, Fullname & Surname. Is there a way to do it and identify which button is pressed. Otherwise I will have to make the textboxes look like buttons which is not so good. Thanks again.

 

[ZOR]

I have now used text boxes with labels on top. I set the label background to transparent, however if I click on a label I cannot get the underlying text box value out. It only works on the first row. Any ideas, thanks

 

[MajP]

You will have to explain much clearer, and post some code.

 

[ZOR]

Very little I can add. I have a query behind the form. 3 Text boxes are bound to the fields in the query. 3 Lables are put over the text boxes with a basic onclick event.

Thanks

Query for continuos form
SELECT StrConv([Athlete],3) AS Expr1, StrConv(Mid([Athlete],InStr([Athlete]," ")+1),3) AS Expr2
FROM AthleteNames
WHERE (((AthleteNames.Athlete) Like [FORMS]![Mainform1]![Subform1].[UU].[CAPTION] & "*"))
ORDER BY AthleteNames.Athlete;


Label click events

Private Sub Label5_Click()
MsgBox Me.Text1
End Sub
Private Sub Label6_Click()
MsgBox Me.Text2
End Sub

Private Sub Label8_Click()
MsgBox Me.Text3
End Sub

 

[MajP]

What is the purpose of transparent labels over text boxes?

 

[ZOR]

To make them more like buttons. Label borders are raised. The text boxes underneath are disabled and locked. It just provides a better user interface than a cursor appearing over a textbox for a selection of an item. Regards

 

[ZOR]

I have aborted the idea and going for 2 seperate listboxes, much easier, no complications. Regards