Sorting fields dynamically displayed in a combo box in alpha order

G

Guest

Hi Folks

I am using an excellent search form designed by Dev Ashish & Terry Kreft and downloaded from the MS Access Web (http://www.mvps.org/access/resources/downloads.htm). There is one addition that I would like - that is - the form uses VBA to dynamically displays fields in a combo box but always in random order. Can someone suggest a method for displaying the fields in correct alphabetical order - this would be very useful

For information, I have looked at the code behind the form and these 2 routines are used to populate the rowsource of the combo boxes

Any help would be appreciated. Thank you

Ton

'VBA Code ---------------------------------------

Function fListFill(ctl As Control, varID As Variant, lngRow As Long,
lngCol As Long, intCode As Integer) As Varian
'The callback function for the first comb
' sFillCombo takes care of the rest of 'em
On Error GoTo ErrHandle
Static sastrObjSource() As Strin
Static sastrFields() As Strin
Static slngCount As Lon
Static sdb As Databas
Dim i As Lon
Dim j As Lon
Dim tdf As TableDe
Dim rsQdf As Recordse
Dim fld As Fiel
Dim varRet As Varian
Dim strObjectType As Strin
Dim varItem As Varian

Select Case intCod
Case acLBInitializ
If sdb Is Nothing Then Set sdb = CurrentD
With M
ReDim sastrObjSource(0
'Are we looking for a table or a quer
sastrObjSource(0) = .lstTables.Column(0
strObjectType = .lstTables.Column(1
j = -
If strObjectType = "Table" The
Set tdf = sdb.TableDefs(sastrObjSource(0)
Me.lstTables.Tag = tdf.Fields.Coun
'Get a list of all the field
For Each fld In tdf.Field
j = j +
ReDim Preserve sastrFields(j
sastrFields(j) = fld.Nam
Nex
j = UBound(sastrFields
Els
'Since the fieldnames can be changed, safest way is t
'open a recordset and go through it's Fields collectio
Set rsQdf = sdb.OpenRecordset(
"Select * from [" & sastrObjSource(0) & "] Where 1=2",
dbOpenSnapshot
Me.lstTables.Tag = rsQdf.Fields.Coun
For Each fld In rsQdf.Field
j = j +
ReDim Preserve sastrFields(j
sastrFields(j) = fld.Nam
Nex
j = UBound(sastrFields
End I
'sort the strin
Call apiSortStringArray(sastrFields
slngCount = UBound(sastrFields) +
'create a module level variant array for other combo
mvarOriginalFields = sastrField
End Wit
varRet = Tru

Case acLBOpe
varRet = Time

Case acLBGetRowCoun
varRet = slngCoun

Case acLBGetValu
varRet = sastrFields(lngRow

Case acLBEn
Set rsQdf = Nothin
Set tdf = Nothin
Set sdb = Nothin
Erase sastrField
Erase sastrObjSourc
End Selec
fListFill = varRe
ExitHere
Exit Functio
ErrHandler
Resume ExitHer
End Functio

Private Sub sFillCombo(intTargetIndex As Integer
'Fills the Rowsource for a comb

On Error GoTo ErrHandle
Dim i As Lon
Dim j As Intege
Dim strOut As Strin
Dim ctlTarget As Contro

'Which one to fill
Set ctlTarget = Me("cbxFld" & intTargetIndex
For i = LBound(mvarOriginalFields) To UBound(mvarOriginalFields
strOut = strOut & mvarOriginalFields(i) & ";
Nex
With ctlTarge
.RowSourceType = "Value List
.RowSource = strOu
End Wit
ExitHere
Set ctlTarget = Nothin
Exit Su
ErrHandler
Resume ExitHer
End Su
 
M

Melissa

Tony,

<< With ctlTarget
.RowSourceType = "Value List" >>

The code builds a value list using the following:

<< For i = LBound(mvarOriginalFields) To UBound(mvarOriginalFields)
strOut = strOut & mvarOriginalFields(i) & ";"
Next >>

So you need to add additional code at this point that builds a presorted list.

The alternative is to programatically put the values into a table and use a
query where the fields are sorted as the rowsource for the combobox.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com




Tony said:
Hi Folks,

I am using an excellent search form designed by Dev Ashish & Terry Kreft and
Click to expand...
downloaded from the MS Access Web
(http://www.mvps.org/access/resources/downloads.htm). There is one addition
that I would like - that is - the form uses VBA to dynamically displays fields
in a combo box but always in random order. Can someone suggest a method for
displaying the fields in correct alphabetical order - this would be very useful.
For information, I have looked at the code behind the form and these 2
Click to expand...
routines are used to populate the rowsource of the combo boxes.
Any help would be appreciated. Thank you.

Tony

'VBA Code ----------------------------------------

Function fListFill(ctl As Control, varID As Variant, lngRow As Long, _
lngCol As Long, intCode As Integer) As Variant
'The callback function for the first combo
' sFillCombo takes care of the rest of 'em.
On Error GoTo ErrHandler
Static sastrObjSource() As String
Static sastrFields() As String
Static slngCount As Long
Static sdb As Database
Dim i As Long
Dim j As Long
Dim tdf As TableDef
Dim rsQdf As Recordset
Dim fld As Field
Dim varRet As Variant
Dim strObjectType As String
Dim varItem As Variant

Select Case intCode
Case acLBInitialize
If sdb Is Nothing Then Set sdb = CurrentDb
With Me
ReDim sastrObjSource(0)
'Are we looking for a table or a query
sastrObjSource(0) = .lstTables.Column(0)
strObjectType = .lstTables.Column(1)
j = -1
If strObjectType = "Table" Then
Set tdf = sdb.TableDefs(sastrObjSource(0))
Me.lstTables.Tag = tdf.Fields.Count
'Get a list of all the fields
For Each fld In tdf.Fields
j = j + 1
ReDim Preserve sastrFields(j)
sastrFields(j) = fld.Name
Next
j = UBound(sastrFields)
Else
'Since the fieldnames can be changed, safest way is to
'open a recordset and go through it's Fields collection
Set rsQdf = sdb.OpenRecordset( _
"Select * from [" & sastrObjSource(0) & "] Where 1=2", _
dbOpenSnapshot)
Me.lstTables.Tag = rsQdf.Fields.Count
For Each fld In rsQdf.Fields
j = j + 1
ReDim Preserve sastrFields(j)
sastrFields(j) = fld.Name
Next
j = UBound(sastrFields)
End If
'sort the string
Call apiSortStringArray(sastrFields)
slngCount = UBound(sastrFields) + 1
'create a module level variant array for other combos
mvarOriginalFields = sastrFields
End With
varRet = True

Case acLBOpen
varRet = Timer

Case acLBGetRowCount
varRet = slngCount

Case acLBGetValue
varRet = sastrFields(lngRow)

Case acLBEnd
Set rsQdf = Nothing
Set tdf = Nothing
Set sdb = Nothing
Erase sastrFields
Erase sastrObjSource
End Select
fListFill = varRet
ExitHere:
Exit Function
ErrHandler:
Resume ExitHere
End Function

Private Sub sFillCombo(intTargetIndex As Integer)
'Fills the Rowsource for a combo
'
On Error GoTo ErrHandler
Dim i As Long
Dim j As Integer
Dim strOut As String
Dim ctlTarget As Control

'Which one to fill?
Set ctlTarget = Me("cbxFld" & intTargetIndex)
For i = LBound(mvarOriginalFields) To UBound(mvarOriginalFields)
strOut = strOut & mvarOriginalFields(i) & ";"
Next
With ctlTarget
.RowSourceType = "Value List"
.RowSource = strOut
End With
ExitHere:
Set ctlTarget = Nothing
Exit Sub
ErrHandler:
Resume ExitHere
End Sub
Click to expand...
 
Top