Listbox Sideways

[Stephen Lynch]

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

 

[Jim Burke in Novi]

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.

 

[dch3]

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...

 

[Stephen Lynch]

Thanks, I try the code. I can always use a single record form, but I have
been getting tricky with changing the rowsource by select items in another
list box.

Thanks again.

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...

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.

 

[dch3]

If that's the case then just modify the bit that loads up the array to
reference the other control when you select the record. You'll then need to
add a statement in the other select box's _AfterUpdate event to requery the
sideway listbox.

Thanks, I try the code. I can always use a single record form, but I have
been getting tricky with changing the rowsource by select items in another
list box.

Thanks again.

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...

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.


:

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

 

[Dale Fye]

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.

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...

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.

 

[dch3]

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.

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.

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...

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.


:

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

 

[Dale Fye]

For a large number of fields, what you recommend looks great. For a small
number, I think this method would work just fine.

Actually, for the list with dates, I use a specialize query I wrote a couple
of years ago. Works great with dates, sequential numbering schemes, or
anytime you have sequential values. I put tbl_Numbers and qry_Numbers in
almost every database I build.

1. First, create a table (tbl_Numbers) with a single field (int_Value).
Populate it with the values 0 through 9.

2. Create a query (qry_Numbers). You can expand this as needed, but you'll
get the idea:

SELECT Hundreds.int_Value * 100
+ Tens.int_Value * 10
+ Ones.int_Value as Int_Value
FROM tbl_Numbers as Hundreds,
tbl_Numbers as Tens,
tbl_Numbers as Ones

3. Now for your list with dates during the last 30 days, just do:

SELECT DateAdd("d", -int_Value, Date()) as SomeDate
FROM qry_Numbers
WHERE int_Value < 31

--
Dale

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

email address is invalid
Please reply to newsgroup only.

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.

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.

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...

:

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.


:

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

 

[dch3]

Using a Call Back function eliminates all of that additional work. If you've
never utilized one, I would recommend it as its a great learning opportunity.

For a large number of fields, what you recommend looks great. For a small
number, I think this method would work just fine.

Actually, for the list with dates, I use a specialize query I wrote a couple
of years ago. Works great with dates, sequential numbering schemes, or
anytime you have sequential values. I put tbl_Numbers and qry_Numbers in
almost every database I build.

1. First, create a table (tbl_Numbers) with a single field (int_Value).
Populate it with the values 0 through 9.

2. Create a query (qry_Numbers). You can expand this as needed, but you'll
get the idea:

SELECT Hundreds.int_Value * 100
+ Tens.int_Value * 10
+ Ones.int_Value as Int_Value
FROM tbl_Numbers as Hundreds,
tbl_Numbers as Tens,
tbl_Numbers as Ones

3. Now for your list with dates during the last 30 days, just do:

SELECT DateAdd("d", -int_Value, Date()) as SomeDate
FROM qry_Numbers
WHERE int_Value < 31

--
Dale

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

email address is invalid
Please reply to newsgroup only.

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.

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.



:

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...

:

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.


:

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

 

[Dale Fye]

must be missing something. I had never heard them called "call back"
functions. I always referred to them as "recursive functions".

I agree that they have their place (navigating any tree structure in a
database or file system) but I'm not sure why you would call one in this
particular case.

Dale

Using a Call Back function eliminates all of that additional work. If
you've
never utilized one, I would recommend it as its a great learning
opportunity.

For a large number of fields, what you recommend looks great. For a
small
number, I think this method would work just fine.

Actually, for the list with dates, I use a specialize query I wrote a
couple
of years ago. Works great with dates, sequential numbering schemes, or
anytime you have sequential values. I put tbl_Numbers and qry_Numbers in
almost every database I build.

1. First, create a table (tbl_Numbers) with a single field (int_Value).
Populate it with the values 0 through 9.

2. Create a query (qry_Numbers). You can expand this as needed, but
you'll
get the idea:

SELECT Hundreds.int_Value * 100
+ Tens.int_Value * 10
+ Ones.int_Value as Int_Value
FROM tbl_Numbers as Hundreds,
tbl_Numbers as Tens,
tbl_Numbers as Ones

3. Now for your list with dates during the last 30 days, just do:

SELECT DateAdd("d", -int_Value, Date()) as SomeDate
FROM qry_Numbers
WHERE int_Value < 31

--
Dale

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

email address is invalid
Please reply to newsgroup only.

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.

:

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.



:

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...

:

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.


:

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

 

[Stuart McCall]

must be missing something. I had never heard them called "call back"
functions. I always referred to them as "recursive functions".

<snip>

Me neither. I tend to call them ListFill functions.