Convert Value List to SQL?
I am using the following code to generate a Value List as the Row Source for a listbox. I am trying to find a way to be able to sort the listbox. All the methods that I have found so far involve changing the ORDER BY portion of an SQL statement and since I don't have an SQL statement, I can't use those methods to sort the listbox.
So. . . ,
- is there an easy way to convert my Value List to an SQL statement,
or
- is there an easy way to sort a listbox when the row source is a value list and not a SQL?
Thanks. here is the code:
DocumentList = "Document Name;Date;Time;"
DocumentName = Dir(OPTCPath & Right(CurrentYear, 2) & "\Correspondence\*.*")
Do While Len(DocumentName) > 0
If Left(DocumentName, 15) = CurrentRoll Then
DocumentDate = Format(ShowFileInfo(OPTCPa
DocumentTime = Format(ShowFileInfo(OPTCPa
DocumentName = Right(DocumentName, (Len(DocumentName) - 15))
DocumentList = DocumentList & DocumentName & ";" & DocumentDate & ";" & DocumentTime & ";"
End If
DocumentName = Dir
Loop
Me.lstDocList.RowSource = DocumentList
So. . . ,
- is there an easy way to convert my Value List to an SQL statement,
or
- is there an easy way to sort a listbox when the row source is a value list and not a SQL?
Thanks. here is the code:
DocumentList = "Document Name;Date;Time;"
DocumentName = Dir(OPTCPath & Right(CurrentYear, 2) & "\Correspondence\*.*")
Do While Len(DocumentName) > 0
If Left(DocumentName, 15) = CurrentRoll Then
DocumentDate = Format(ShowFileInfo(OPTCPa
DocumentTime = Format(ShowFileInfo(OPTCPa
DocumentName = Right(DocumentName, (Len(DocumentName) - 15))
DocumentList = DocumentList & DocumentName & ";" & DocumentDate & ";" & DocumentTime & ";"
End If
DocumentName = Dir
Loop
Me.lstDocList.RowSource = DocumentList
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Or you can read the entries into an array and do a sort (like an exchange sort) for whatever sort order you want, then build the value list from the sorted array.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Daniel,
Also as a side not, its not the best to use feild names called
Date
Time
Properties
Field (you cant anyway)
Now
Image (had probelms with ASP .Net and this one)
and any toher reserved words in access....
Try using something like dtFile or FileDate or something....
it may fall into the too hard basket now, but remember it for next time, it can cause pain.... generally you canget around it by using [date].
Dave
Also as a side not, its not the best to use feild names called
Date
Time
Properties
Field (you cant anyway)
Now
Image (had probelms with ASP .Net and this one)
and any toher reserved words in access....
Try using something like dtFile or FileDate or something....
it may fall into the too hard basket now, but remember it for next time, it can cause pain.... generally you canget around it by using [date].
Dave
ASKER
Thanks for the idea Flavo. I've tried to implement your suggestion, but I get the following error on the db.execute line:
'Object variable or With block variable not set.'
Any ideas?
I was going to try the other method of reading the variables into an array and then doing an exchange sort and building the list from the array, but I couldn't figure out how to do that with a two column listbox. I found a good example at Q_20975053, but it was only for one column and I couldn't figure out how to change it to handle two columns. Perhaps if someone could explain that? Here is the code from Q_20975053:
Dim FinishedSwapping As Boolean
Dim UpperBound As Long
Dim Temp As String
Dim strValue() As String
Dim i As Long
'Add each value in the list to a temporary array (for sorting)
For i = 0 To lstSerials.ListCount - 1
ReDim Preserve strValue(i)
strValue(i) = lstSerials.ItemData(i)
Next i
UpperBound = UBound(strValue())
'Sort array into ascending order
Do
FinishedSwapping = True
For i = 0 To UpperBound - 1
'If they are bigger then perform a swap
If strValue(i) > strValue(i + 1) Then
Temp = strValue(i + 1)
strValue(i + 1) = strValue(i)
strValue(i) = Temp
FinishedSwapping = False
End If
Next i
Loop While Not (FinishedSwapping)
'Clear list
lstSerials.RowSource = ""
lstSerials.Requery
'Add sorted items into list
For i = 0 To UpperBound
lstSerials.AddItem strValue(i)
Next i
'Object variable or With block variable not set.'
Any ideas?
I was going to try the other method of reading the variables into an array and then doing an exchange sort and building the list from the array, but I couldn't figure out how to do that with a two column listbox. I found a good example at Q_20975053, but it was only for one column and I couldn't figure out how to change it to handle two columns. Perhaps if someone could explain that? Here is the code from Q_20975053:
Dim FinishedSwapping As Boolean
Dim UpperBound As Long
Dim Temp As String
Dim strValue() As String
Dim i As Long
'Add each value in the list to a temporary array (for sorting)
For i = 0 To lstSerials.ListCount - 1
ReDim Preserve strValue(i)
strValue(i) = lstSerials.ItemData(i)
Next i
UpperBound = UBound(strValue())
'Sort array into ascending order
Do
FinishedSwapping = True
For i = 0 To UpperBound - 1
'If they are bigger then perform a swap
If strValue(i) > strValue(i + 1) Then
Temp = strValue(i + 1)
strValue(i + 1) = strValue(i)
strValue(i) = Temp
FinishedSwapping = False
End If
Next i
Loop While Not (FinishedSwapping)
'Clear list
lstSerials.RowSource = ""
lstSerials.Requery
'Add sorted items into list
For i = 0 To UpperBound
lstSerials.AddItem strValue(i)
Next i
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you naivad. Where exactly would I use the semicolon to separate the items in the list?
The RowSourceType needs to be "value list"
the RowSource can the be "1;red;#ff0000;2;green;#00
for a 3 column list/combo box.
the RowSource can the be "1;red;#ff0000;2;green;#00
for a 3 column list/combo box.
>> 'Object variable or With block variable not set.'
Need to add a refrence to Microsoft DAO Object Library.
To do this, in the VBA window Select Tools - Refrences and tick it from the list.
Dave
Need to add a refrence to Microsoft DAO Object Library.
To do this, in the VBA window Select Tools - Refrences and tick it from the list.
Dave