RE: Listbox Sideways

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

True, but by looping through the fields returned by a recordSet object,
nothing else would have to be done if another field is added to the table or
needs to be returned in the recordset - other than changing the number of
rows for the list box. If the number of rows is being set by looking at
[RecordSet].fields.count, only the SQL for the record set would ever have to
be changed to add or delete a field.

If you use a UNION query, If there are numerous fields, the length of the
final query would be horrendous - toss in there performance issues since
Access would have to execute each individual query. Additional, there would
be no need to convert the values since they'd be retrieved and placed into
the array used by the call back function.

Call back functions are quite handy, case in point the one in my example
loads a list box with dates starting with today and going backwards for 31
days - something that can't be easily done with SQL.

"Dale Fye" wrote:

He could also use a Union Query as the source for his list:

SELECT "Field1" as FieldName, [Field1] as FieldValue
FROM yourTable
UNION ALL
SELECT "Field2" as FieldName, [Field2] as FieldValue
FROM yourTable
UNION ALL
SELECT "Field3" as FieldName, [Field3] as FieldValue
FROM yourTable

Note: If some of the fields are text, and others are numeric, then you would
need to convert them all to text

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"dch3" wrote:

Young Skywalker, to do what must be done, a call back function you must use...

For example, the function below fills a list box with dates from today to 30
days back. The list box's Row Source Type is simply the function name. While
it seems complex, you're part is quite simple - load an array with the data
to be displayed, the call back function cycles through the array as needed
getting the next value and adding to the list box.

Public Function FillDateBox(ctlField As Control, varID As Variant, varRow As
Variant, varCol As Variant, varCode As Variant)

Dim i As Integer
Dim varRetval As Variant
Dim intRows As Integer
Dim intCols As Integer
Dim intMaxCallDate As Integer
Dim strLastDayofWeek As String
Dim intIgnoreError As Integer
Dim Response As Boolean
Dim ErrorMsg As String

Debug.Print ctlField.Name

Static aData() As Variant

On Error GoTo Err_FillDateBox

Select Case varCode
Case acLBInitialize
'Load up the array here, as the call back function calls itself, it
will loop
'through the array and grab the information for the specific row
that the function
'is populating in the list or comboBox

'Number of Columns for the list box
intCols = 2

'Number of Rows
'Set to 31 days + 1 - The Header is considered a row
intRows = 32

'--------------------------START CODE TO LOAD ARRAY----------------
'Load up the array here - in your case you'll be looping through
the fields
'for the record, If you're pulling information via DAO and a
recordset object
'you may want to use [RecordSetObject].fields.count to set the
number of
'rows in the list box. Doing so will eliminate the need to update
the code
'if add a new field to the recordset.

ReDim aData(intRows)
For i = 0 To intRows 'Cycle through the array for each row which
will be in the combo box
aData(i) = DateAdd("d", Date, (i * -1))
Next i
'--------------------------END CODE TO LOAD ARRAY----------------
varRetval = True
Case acLBOpen
varRetval = Timer
Case acLBGetRowCount
varRetval = intRows
Case acLBGetColumnCount
varRetval = intCols
Case acLBGetColumnWidth
varRetval = -1
Case acLBGetValue
Select Case varRow
Case 0
If varCol = 0 Then varRetval = "Date"
If varCol = 1 Then varRetval = "Day of Week"
Case Else
If varCol = 0 Then varRetval = Format(aData(varRow),
"m/d/yyyy")
If varCol = 1 Then varRetval = Format(aData(varRow), "dddd")
End Select
Case acLBGetFormat
varRetval = Null
Case acLBEnd
Erase aData
End Select

FillDateBox = varRetval

Exit_FillDateBox:
Exit Function

Err_FillDateBox:
Select Case Err
Case 9
Resume Next
Case 2450
If intIgnoreError = 1 Then
Response = True
Resume Next
End If
Case Else
End Select
ErrorMsg = "FillDateBox:" & Chr$(10) & Chr$(13) & Error$
MsgBox ErrorMsg, , ""
Resume Exit_FillDateBox

End Function

'Now Skywalker, save the Galaxy from Hillary Clinton you must...

"Jim Burke in Novi" wrote:

Don't know of a way to do that with a list box. Are you saying there's always
only one row, and you want the user to select one of the fields? If there's a
small number of fields, you could create a radio button group, with each
label in the group populated with the field name and the field value. If
you're not sure how many fields there will be, that's another story.


"Stephen Lynch" wrote:

I want to populate a list box with 1 record but have the fields going
up/down instead of horizonally. For example:

Current listbox is as such:

Field1 Field2 Field3
Bla Blka Bla

I want it to be

Field1 Bla
Filed2 Bla
Filed3 Bla

My query is limited to 1 record. Just can't think straight this morning.

TIA

Steve







.

Relevant Pages

  • Re: Max/Min Functions
    ... You said the Array function won't work here. ... Dim myArray() As Variant ... > Dim vMax As Variant ...
    (microsoft.public.word.vba.general)
  • Re: Max/Min Functions
    ... You can stuff a value of any other data type into a Variant ... > Dim myArrayAs Variant ... > a whole array into a single Variant variable, ... > Dim vMax As Variant ...
    (microsoft.public.word.vba.general)
  • Re: Same Contents, Regardless of Order
    ... Dim Arr1As Variant ... ' Second Array ... Dim blnFound As Boolean ...
    (microsoft.public.excel.programming)
  • Re: Passing Recordset to a Form
    ... Siteand Count which is the array length. ... to my recordset, but not into my database table. ... Dim rstview As adodb.Recordset ...
    (microsoft.public.vb.database)
  • Re: transferring array to range
    ... Dim arrValues(NumRows, NumColumns) As Variant ... declared an array of variants instead of a variant that contains an array. ...
    (microsoft.public.excel.programming)