Listbox - Horizontal instead of Verticle Display

[Sorrells]

Hello again,

I have been searching the forums and it appears that I want to do something that is not often attempted or thought of.

I have a single field returned by an SQL query with up to 15 text items. I would like to have these items displayed from left to right and then descending with perhaps 4 or 5 to a row.

It appears that the ColumnCount property is only for when more than one field is returned and in fact this is the way I normally use it.

My problem is that I simple do not have enough real estate on the form to list these items vertically. Well my client is not interested in that either!

The only work-around I have come up seems like a pain in the rear. That would be to re-format the table(s) into many columns, use the ColumnCount property and then figure out how to trap whichever text in the list the user clicks on. (Like 2nd row, 4th column).

Any advice will be appreciated.

 

[ritchieroo]

The control that comes closest to the sort of thing you want is a ListView control. You'll need to have a licensed copy of the Microsoft ListView Control ActiveX component on your machine.

 

[Sorrells]

ritchieroo,

That looks like a Visual Basic control, not VBA. Am I right? Could they be integrated on an Access form? And would the Office XP Developer allow me to include the necessary files?

I found a neat site with lots of information about the ListView control at:

http://www.mvps.org/btmtz/listview/

My gut feeling is that this may be getting in deeper than I can swim!

My thought for today was creation of set labels on the form that I could program captions for depending on the text returned from a query. A couple problems could ensue, (1) is that I am using Ken Getz's resizer in this form so the labels might become disjointed at various monitor resolutions and secondly, the coding would be another journey into the unknown (but not as unknown as ListView, I think!).

If you are familiar with Visual Basic, I have a burning question as to if these two languages (VB & VBA) can be integrated in a predominately VBA application. With this form in particular, I was thinking that perhaps it would be easier in Visual Basic.

 

[ritchieroo]

You're right in saying that the ListView control is predominately used on the VB development platform, but it is just an ActiveX control like any other and you can use it in any VBA environment.

In practice some of these ActiveX components don't interact well with Access's design environment, but once in rutime they work as normal. This can make them a little trickier to work with in Access.

Personally, I don't draw any distinction between VB and VBA, without looking too deeply VBA is just a subset of VB. There are only a few functions that are missing from VBA, depending on what version of Access you are using.

Access is probably the best development package for creating windows database applications there is. You create good products in pure VB, but no where near as quickly or maintainably. And I wouldn't suggest it unless you were very experienced with VB.

Learning to use the ListView control is certainly an issue, Brad Martinez' site has some rather advanced examples involving all sorts of API stuff. Here's the MSDN link http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cmctl198/html/vbobjlistview.asp

I have had experience of display sizing issues with Access in the past and always develepoped to the smallest size. I haven't tried Ken Getz's resizer, but the ListView control would automatically handle any display sizing issues.

 

[IfButOnly]

I'm not sure if this is what you are after - but I did something similar recently.

Create a number of labels, name lbl1 - lbl15. Based on your SQL query, populate and display these labels as in the function SetUpLabels.

the On Click property from each of the labels, will call the function 'labelClick' - which basically can give you the row index by extracting the label number.

--------------------------------------------------------------
Function SetUpLabels()
' function sets up the labels dependant on the items returned from SQL statement

' SQL statement
' lstcnt = no of items returned

For I = 1 To lstcnt
Me("lbl" & I).Caption = Me.lstbox.ItemData(I)
Me("lbl" & I).Visible = True
Next I

End Function


Function labelClick()
' this function is called from each of the labels 'on Click' action.

Dim ctl As Control
Dim strctlName As String
Set ctl = Screen.ActiveControl
strctlName = ctl.Name ' the name of the label clicked
lblNum = Mid(strctlName, 4) ' the number of the label (1-15)

Select Case lblNum
' perform whatever actions required
' for each case.
End Select

End Function
----------------------------------------------------------------

hope it helps..PeteR.