Dev Ashish Search ??

[lmv]

Is there a way to change the unbound combo boxes to sort ascending?
When the cbo requery happens?

Me.cbxFld0.Requery

Using Dev Ashish's search is great but the cbo boxes are random...
Figured out how to get the viewed result right but still can't figure out
how to sort the cbo.

Thanks!

 

[Wayne-I-M]

Open the form in design view and select the combo. Open the properties box
and the data column select Row Source then the build option (...). Create a
new query to base the combo on and sort it how you want.

Or

In the row source add an "ORDER BY"

Something like this
SELECT [TableName].[FieldName1], [TableName].[FieldName2],
[TableName].[FieldName3] FROM [TableName] ORDER BY [FieldName2];

Hope this helps

 

[lmv]

Thanks... but I can't the row source has fListFill in it
the list gets its value from a list box where you choose which table/query
etc you want and it fills the cbo from that it is a part of the whole search
program.

I thought maybe someone who was familiar with this search might have an idea
if there is a way to add the Order By somewhere in the code. I don't know how
to do it.
Thanks anyway.

Open the form in design view and select the combo. Open the properties box
and the data column select Row Source then the build option (...). Create a
new query to base the combo on and sort it how you want.

Or

In the row source add an "ORDER BY"

Something like this
SELECT [TableName].[FieldName1], [TableName].[FieldName2],
[TableName].[FieldName3] FROM [TableName] ORDER BY [FieldName2];

Hope this helps




--
Wayne
Manchester, England.

Is there a way to change the unbound combo boxes to sort ascending?
When the cbo requery happens?

Me.cbxFld0.Requery

Using Dev Ashish's search is great but the cbo boxes are random...
Figured out how to get the viewed result right but still can't figure out
how to sort the cbo.

Thanks!

 

[Dirk Goldgar]

Thanks... but I can't the row source has fListFill in it
the list gets its value from a list box where you choose which
table/query etc you want and it fills the cbo from that it is a part
of the whole search program.

I thought maybe someone who was familiar with this search might have
an idea if there is a way to add the Order By somewhere in the code.
I don't know how to do it.

Where'd you get the code from?

 

[lmv]

Not sure I understand the question... but
This is the code on the form ...

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

------------
Private Sub lstTables_AfterUpdate()
'Try and enable the next control only if the the Clear
'button has been clicked (ctl.Tag = vbNullString)
'Otherwise just requery the field's info
'
Call cmdClear_Click
If Me.lstTables.Tag = vbNullString Then Call fEnableNextInTab
Me.cbxFld0.Requery
End Sub

The unbound "where" cbo boxes have fListFill as the row source type.

Is that the question?

Thanks!

 

[Dirk Goldgar]

Not sure I understand the question... but
This is the code on the form ...

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

------------
Private Sub lstTables_AfterUpdate()
'Try and enable the next control only if the the Clear
'button has been clicked (ctl.Tag = vbNullString)
'Otherwise just requery the field's info
'
Call cmdClear_Click
If Me.lstTables.Tag = vbNullString Then Call fEnableNextInTab
Me.cbxFld0.Requery
End Sub

The unbound "where" cbo boxes have fListFill as the row source type.

Is that the question?

No, there's not enough background information there. I can only guess
at the nature of your form and the controls on it, and the code you
posted isn't all the code that's involved. Is this discussion thread a
follow-up to some other thread that explains in more detail what you're
doing?

I see that there's a function named "fListFill" by Dev Ashish posted on
the Access Web at http://www.mvps.org/access/forms/frm0049.htm, but that
function doesn't in itself say anything about the source of the data or
its sequence. I may be able to help you, but I need to know a lot more
about how you have your form and controls set up.

 

[lmv]

The source is ALL of the tables and query's in my mdb. You choose a table and
it poplulates the cbo. It is just pulling the info the way it is listed in
the table and there is no way to change the order... I guess.

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 DAO.Database
Dim I As Long
Dim J As Long
Dim tdf As TableDef
Dim rsQdf As DAO.Recordset
Dim fld As DAO.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

 

[Dirk Goldgar]

The source is ALL of the tables and query's in my mdb. You choose a
table and it poplulates the cbo. It is just pulling the info the way
it is listed in the table and there is no way to change the order...
I guess.

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 DAO.Database
Dim I As Long
Dim J As Long
Dim tdf As TableDef
Dim rsQdf As DAO.Recordset
Dim fld As DAO.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 [...]

I believe there's still something missing here. This is what I see from
the code you've posted:

+ When you click a row in lstTables, cbxFld0 (I guess) is filled with
the list of fields defined in that table or query. The list is sorted
alphabetically. That list is also preserved in the global array
"mvarOriginalFields".

+ The function sFillCombo would fill one of the numbered "cbxFldn" combo
boxes with the same list of fields, as far as I can tell, by building a
value list string from the array mvarOriginalFields and assigning it to
the combo's rowsource.

+ However, from your description, that doesn't sound like what you had
in mind, and ...

+ You haven't posted any code that actually calls sFillCombo, anyway.
So clearly there's something missing.

Are these remaining combo boxes really supposed to show field lists? Or
are they, as I'd expect from what you've said, supposed to show lists of
values from a particular field in a specified table? If they're
supposed to show values, I don't see how this code could possibly do it.
If the combos *are* showing you field values, but they're just not
sorted the way you want, please post the code that assigns the
rowsources of those combos. Then maybe we can come up with a fix.

 

[lmv]

Thanks for your help...

There is a list box with the following code.

SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or
[type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND
((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or (MsysObjects.Type)=6) AND
((MsysObjects.Flags)=2097152 Or (MsysObjects.Flags)=128 Or
(MsysObjects.Flags)=0 Or (MsysObjects.Flags)=16))
ORDER BY MsysObjects.Name;

after you choose from the list (it is alphabetical)

Private Sub lstTables_AfterUpdate()
'Try and enable the next control only if the the Clear
'button has been clicked (ctl.Tag = vbNullString)
'Otherwise just requery the field's info
'
Call cmdClear_Click
If Me.lstTables.Tag = vbNullString Then Call fEnableNextInTab
Me.cbxFld0.Requery
End Sub

The "where" cbo box fills...

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

then when you choose from where cbo(above) the "criteria" txtbox you can
type in .. then there is a result listbox.

Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'
On Error GoTo ErrHandler
Dim strSql As String
Dim strWhere As String
Dim strJoinType As String
Dim I As Integer
Dim J As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As TableDef
Dim qdf As DAO.QueryDef
Dim rsQdf As DAO.Recordset
Dim fld As DAO.Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSql = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these controls
For I = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & I).Enabled Then
'The Or/And set starts with the second combo/textbox set
'so if there's only one criteria specified, don't need to
'concatenate additional stuff.
If I > 0 Then
If Me("opgClauseType" & I) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If
'Get the a reference to the field in the table/Query as
'we'll need it for BuildCriteria later on
If Me.lstTables.Column(1) = "Table" Then
Set tdf = db.TableDefs(Me.lstTables.Column(0))
Set fld = tdf.Fields(Me("cbxFld" & I))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & Me.lstTables.Column(0) & "] Where 1=2",
dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & I))
End If

'Only build a criteria if something's typed in the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & I)) Then
strWhere = strWhere & strJoinType & Application.BuildCriteria( _
"[" & Me("cbxFld" & I) &
"]", _
fld.Type, Me("txtVal" & I) & "")
Else
strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & I) & "]
like '*'"
End If

End If
Next
'The final all important SQL statement
strSql = strSql & " from [" & Me.lstTables & "] Where " & strWhere

'If the user has modified the SQL directly, take what they've typed in
If Nz(Me.chkEditSQL, False) = False Then
'"save" it in the textbox
Me.txtSQL = strSql
End If

With Me.lstResult
Set rs = db.OpenRecordset(Me.txtSQL)
'assign the SQL to the lstResult only if
' (a) it's valid (Set rs will generate an error otherwise)
' (b) if the recordset actually returned any records.
If rs.RecordCount > 0 Then
Me.cmdCopySQL.Enabled = True
Me.cmdCreateQDF.Enabled = True
Me.cmdExport.Enabled = True
.RowSourceType = "Table/Query"
.RowSource = Me.txtSQL
.Enabled = True
'display * fields
.ColumnCount = CInt(Me.lstTables.Tag)
.ColumnHeads = True
Me.chkEditSQL.Enabled = True
Else
'Thanks for trying, better luck next time!!
Me.cmdCopySQL.Enabled = False
Me.cmdCreateQDF.Enabled = False
Me.cmdExport.Enabled = False
.ColumnCount = 1
.RowSourceType = "Value List"
.RowSource = "No records found."
End If
End With
ExitHere:
Set rsQdf = Nothing
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case err.Number
'we're trying to open a parameter query
Case 3061:
MsgBox "The " & mconQ & Me.lstTables & mconQ & " query you've
selected " _
& " is a Parameter Query." & vbCrLf & err.Description,
vbExclamation + vbOKOnly, _
"Missing parameters"
Case Else:
'Either invalid SQL or some other error
End Select
Me.cmdCopySQL.Enabled = False
Me.cmdCreateQDF.Enabled = False
With Me.lstResult
.RowSourceType = "Value List"
.RowSource = "Invalid SQL statement."
.ColumnHeads = False
.ColumnCount = 1
.Enabled = False
End With
Resume ExitHere
End Sub
------
then the fListFill code is next..

The source is ALL of the tables and query's in my mdb. You choose a
table and it poplulates the cbo. It is just pulling the info the way
it is listed in the table and there is no way to change the order...
I guess.

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 DAO.Database
Dim I As Long
Dim J As Long
Dim tdf As TableDef
Dim rsQdf As DAO.Recordset
Dim fld As DAO.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

------------
Private Sub lstTables_AfterUpdate()
'Try and enable the next control only if the the Clear
'button has been clicked (ctl.Tag = vbNullString)
'Otherwise just requery the field's info
'
Call cmdClear_Click
If Me.lstTables.Tag = vbNullString Then Call fEnableNextInTab
Me.cbxFld0.Requery
End Sub

I believe there's still something missing here. This is what I see from
the code you've posted:

+ When you click a row in lstTables, cbxFld0 (I guess) is filled with
the list of fields defined in that table or query. The list is sorted
alphabetically. That list is also preserved in the global array
"mvarOriginalFields".

+ The function sFillCombo would fill one of the numbered "cbxFldn" combo
boxes with the same list of fields, as far as I can tell, by building a
value list string from the array mvarOriginalFields and assigning it to
the combo's rowsource.

+ However, from your description, that doesn't sound like what you had
in mind, and ...

+ You haven't posted any code that actually calls sFillCombo, anyway.
So clearly there's something missing.

Are these remaining combo boxes really supposed to show field lists? Or
are they, as I'd expect from what you've said, supposed to show lists of
values from a particular field in a specified table? If they're
supposed to show values, I don't see how this code could possibly do it.
If the combos *are* showing you field values, but they're just not
sorted the way you want, please post the code that assigns the
rowsources of those combos. Then maybe we can come up with a fix.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

Thanks for your help...

There is a list box with the following code.

SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or
[type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND
((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or
(MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or
(MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or
(MsysObjects.Flags)=16))
ORDER BY MsysObjects.Name;

after you choose from the list (it is alphabetical)

Private Sub lstTables_AfterUpdate()
'Try and enable the next control only if the the Clear
'button has been clicked (ctl.Tag = vbNullString)
'Otherwise just requery the field's info
'
Call cmdClear_Click
If Me.lstTables.Tag = vbNullString Then Call fEnableNextInTab
Me.cbxFld0.Requery
End Sub

The "where" cbo box fills...

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

then when you choose from where cbo(above) the "criteria" txtbox you
can type in .. then there is a result listbox.

Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'
On Error GoTo ErrHandler
Dim strSql As String
Dim strWhere As String
Dim strJoinType As String
Dim I As Integer
Dim J As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As TableDef
Dim qdf As DAO.QueryDef
Dim rsQdf As DAO.Recordset
Dim fld As DAO.Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSql = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these controls
For I = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & I).Enabled Then
'The Or/And set starts with the second combo/textbox set
'so if there's only one criteria specified, don't need to
'concatenate additional stuff.
If I > 0 Then
If Me("opgClauseType" & I) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If
'Get the a reference to the field in the table/Query as
'we'll need it for BuildCriteria later on
If Me.lstTables.Column(1) = "Table" Then
Set tdf = db.TableDefs(Me.lstTables.Column(0))
Set fld = tdf.Fields(Me("cbxFld" & I))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & Me.lstTables.Column(0) & "] Where
1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & I))
End If

'Only build a criteria if something's typed in the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & I)) Then
strWhere = strWhere & strJoinType &

Application.BuildCriteria( _ "[" & Me("cbxFld" & I) & "]", _
fld.Type, Me("txtVal" &
I) & "") Else
strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & I)
& "] like '*'"
End If

End If
Next
'The final all important SQL statement
strSql = strSql & " from [" & Me.lstTables & "] Where " & strWhere

'If the user has modified the SQL directly, take what they've typed
in If Nz(Me.chkEditSQL, False) = False Then
'"save" it in the textbox
Me.txtSQL = strSql
End If

With Me.lstResult
Set rs = db.OpenRecordset(Me.txtSQL)
'assign the SQL to the lstResult only if
' (a) it's valid (Set rs will generate an error otherwise)
' (b) if the recordset actually returned any records.
If rs.RecordCount > 0 Then
Me.cmdCopySQL.Enabled = True
Me.cmdCreateQDF.Enabled = True
Me.cmdExport.Enabled = True
.RowSourceType = "Table/Query"
.RowSource = Me.txtSQL
.Enabled = True
'display * fields
.ColumnCount = CInt(Me.lstTables.Tag)
.ColumnHeads = True
Me.chkEditSQL.Enabled = True
Else
'Thanks for trying, better luck next time!!
Me.cmdCopySQL.Enabled = False
Me.cmdCreateQDF.Enabled = False
Me.cmdExport.Enabled = False
.ColumnCount = 1
.RowSourceType = "Value List"
.RowSource = "No records found."
End If
End With
ExitHere:
Set rsQdf = Nothing
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case err.Number
'we're trying to open a parameter query
Case 3061:
MsgBox "The " & mconQ & Me.lstTables & mconQ & " query you've
selected " _
& " is a Parameter Query." & vbCrLf & err.Description,
vbExclamation + vbOKOnly, _
"Missing parameters"
Case Else:
'Either invalid SQL or some other error
End Select
Me.cmdCopySQL.Enabled = False
Me.cmdCreateQDF.Enabled = False
With Me.lstResult
.RowSourceType = "Value List"
.RowSource = "Invalid SQL statement."
.ColumnHeads = False
.ColumnCount = 1
.Enabled = False
End With
Resume ExitHere
End Sub

Ah, now it begins to gel. So the object is to let the user choose a
table or query from a list box, specify some criteria to select records
from that table or query, and then display the results in another list
box. The criteria can be specified by up to five combo box/text box
pairs, named "cbxFldn" and "txtValn" respectively, with the suffix "n"
varying from 0 to 4. A matching numbered option group determines the
conjunction, AND or OR, that joins the criteria. I gather also that the
user can edit the SQL directly if he wants, but I'm going to ignore that
for the moment. If any of the above description is incorrect, let me
know.

This is still not all the relevant code, because I still see no place
where you call sFillCombo. I'm assuming you call that function
somewhere, for each of the combo boxes after the first.

So where exactly is the sort problem you're posting about turning up?
In your first post, you asked "Is there a way to change the unbound
combo boxes to sort ascending?" But as far as I can tell, all the field
lists in the combo boxes would be sorted alphabetically, because that's
the way the original array built by fListFill was sorted. So is that
really the sort issue that's troubling you, or is it the sort order of
the results, as displayed in lstResult? If the latter, you can
certainly amend it by specifying an ORDER BY clause to the SQL statement
that is built by sBuildSQL, but how is the function to know what order
you want to sort the results by?

 

[lmv]

So where exactly is the sort problem you're posting about turning up?

The "where" cbo boxes are not sorting.

But as far as I can tell, all the field
lists in the combo boxes would be sorted alphabetically, because that's
the way the original array built by fListFill was sorted. Which part of which code shows that?

For some reason it doesn't sort though... if you see it was build that way
then I don't understand it.

So is that really the sort issue that's troubling you, or is it the sort order of
the results, as displayed in lstResult?

No I already changed that in my search query that you choose from the table
/ query list that fills the cbo box. But no matter what you choose from that
list it still doesn't display the field choices in the cbobox sorted asc.
I finally found where I got the original code from...
http://www.hammerdata.com/Newsgrp/forms/frm0045.htm
download
http://www.hammerdata.com/Newsgrp/downloads/findrecord2k.zip

I had also wanted to limit which tables were viewable in the table list but
wasn't sure what to do to change that either.

Thanks for your help...

There is a list box with the following code.

SELECT MsysObjects.Name AS ObjectName, IIf([type]=1 Or
[type]=6,"Table","Query") AS ObjectType
FROM MsysObjects
WHERE (((Left$([Name],1))<>"~") AND ((Left$([Name],4))<>"Msys") AND
((MsysObjects.Type)=1 Or (MsysObjects.Type)=5 Or
(MsysObjects.Type)=6) AND ((MsysObjects.Flags)=2097152 Or
(MsysObjects.Flags)=128 Or (MsysObjects.Flags)=0 Or
(MsysObjects.Flags)=16))
ORDER BY MsysObjects.Name;

after you choose from the list (it is alphabetical)

Private Sub lstTables_AfterUpdate()
'Try and enable the next control only if the the Clear
'button has been clicked (ctl.Tag = vbNullString)
'Otherwise just requery the field's info
'
Call cmdClear_Click
If Me.lstTables.Tag = vbNullString Then Call fEnableNextInTab
Me.cbxFld0.Requery
End Sub

The "where" cbo box fills...

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

then when you choose from where cbo(above) the "criteria" txtbox you
can type in .. then there is a result listbox.

Sub sBuildSQL()
' Take what's currently selected on the form
' and create a dynamic SQL statement for the
' lstResults listbox.
'
On Error GoTo ErrHandler
Dim strSql As String
Dim strWhere As String
Dim strJoinType As String
Dim I As Integer
Dim J As Integer
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim tdf As TableDef
Dim qdf As DAO.QueryDef
Dim rsQdf As DAO.Recordset
Dim fld As DAO.Field
Const conMAXCONTROLS = 5

Set db = DBEngine(0)(0)
strSql = "Select * "
'Right now we have five combo/textbox sets
'so set up the master loop to go through these controls
For I = 0 To conMAXCONTROLS - 1
strJoinType = vbNullString
'there might be some unused sets, so don't bother
'going through the disabled controls
If Me("cbxFld" & I).Enabled Then
'The Or/And set starts with the second combo/textbox set
'so if there's only one criteria specified, don't need to
'concatenate additional stuff.
If I > 0 Then
If Me("opgClauseType" & I) = 1 Then
strJoinType = " OR "
Else
strJoinType = " AND "
End If
End If
'Get the a reference to the field in the table/Query as
'we'll need it for BuildCriteria later on
If Me.lstTables.Column(1) = "Table" Then
Set tdf = db.TableDefs(Me.lstTables.Column(0))
Set fld = tdf.Fields(Me("cbxFld" & I))
Else
Set rsQdf = db.OpenRecordset( _
"Select * from [" & Me.lstTables.Column(0) & "] Where
1=2", dbOpenSnapshot)
Set fld = rsQdf.Fields(Me("cbxFld" & I))
End If

'Only build a criteria if something's typed in the textbox
'otherwise assume all records
If Not IsNull(Me("txtVal" & I)) Then
strWhere = strWhere & strJoinType &

Application.BuildCriteria( _ "[" & Me("cbxFld" & I) & "]", _
fld.Type, Me("txtVal" &
I) & "") Else
strWhere = strWhere & strJoinType & "[" & Me("cbxFld" & I)
& "] like '*'"
End If

End If
Next
'The final all important SQL statement
strSql = strSql & " from [" & Me.lstTables & "] Where " & strWhere

'If the user has modified the SQL directly, take what they've typed
in If Nz(Me.chkEditSQL, False) = False Then
'"save" it in the textbox
Me.txtSQL = strSql
End If

With Me.lstResult
Set rs = db.OpenRecordset(Me.txtSQL)
'assign the SQL to the lstResult only if
' (a) it's valid (Set rs will generate an error otherwise)
' (b) if the recordset actually returned any records.
If rs.RecordCount > 0 Then
Me.cmdCopySQL.Enabled = True
Me.cmdCreateQDF.Enabled = True
Me.cmdExport.Enabled = True
.RowSourceType = "Table/Query"
.RowSource = Me.txtSQL
.Enabled = True
'display * fields
.ColumnCount = CInt(Me.lstTables.Tag)
.ColumnHeads = True
Me.chkEditSQL.Enabled = True
Else
'Thanks for trying, better luck next time!!
Me.cmdCopySQL.Enabled = False
Me.cmdCreateQDF.Enabled = False
Me.cmdExport.Enabled = False
.ColumnCount = 1
.RowSourceType = "Value List"
.RowSource = "No records found."
End If
End With
ExitHere:
Set rsQdf = Nothing
Set rs = Nothing
Set tdf = Nothing
Set db = Nothing
Exit Sub
ErrHandler:
Select Case err.Number
'we're trying to open a parameter query
Case 3061:
MsgBox "The " & mconQ & Me.lstTables & mconQ & " query you've
selected " _
& " is a Parameter Query." & vbCrLf & err.Description,
vbExclamation + vbOKOnly, _
"Missing parameters"
Case Else:
'Either invalid SQL or some other error
End Select
Me.cmdCopySQL.Enabled = False
Me.cmdCreateQDF.Enabled = False
With Me.lstResult
.RowSourceType = "Value List"
.RowSource = "Invalid SQL statement."
.ColumnHeads = False
.ColumnCount = 1
.Enabled = False
End With
Resume ExitHere
End Sub

Ah, now it begins to gel. So the object is to let the user choose a
table or query from a list box, specify some criteria to select records
from that table or query, and then display the results in another list
box. The criteria can be specified by up to five combo box/text box
pairs, named "cbxFldn" and "txtValn" respectively, with the suffix "n"
varying from 0 to 4. A matching numbered option group determines the
conjunction, AND or OR, that joins the criteria. I gather also that the
user can edit the SQL directly if he wants, but I'm going to ignore that
for the moment. If any of the above description is incorrect, let me
know.

This is still not all the relevant code, because I still see no place
where you call sFillCombo. I'm assuming you call that function
somewhere, for each of the combo boxes after the first.


--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

The "where" cbo boxes are not sorting.


For some reason it doesn't sort though... if you see it was build
that way then I don't understand it.


No I already changed that in my search query that you choose from the
table / query list that fills the cbo box. But no matter what you
choose from that list it still doesn't display the field choices in
the cbobox sorted asc.
I finally found where I got the original code from...
http://www.hammerdata.com/Newsgrp/forms/frm0045.htm
download
http://www.hammerdata.com/Newsgrp/downloads/findrecord2k.zip

Got it. I see it all now. That web page is actually copied -- with
permission or not -- from one of the pages at The Access Web
(www.mvps.org/access/). Because of that, and because Dev is an
extremely good programmer, I thought the function apiSortStringArray,
which he declares as being an exposed entry point in the Access program
itself:

Private Declare Function apiSortStringArray Lib "msaccess.exe" _
Alias "#81" _
(astrStringArray() As String) _
As Long

.... must work. But I know that a number of the entry points that worked
in Access 97 were removed or renumbered in later versions, and I see
that this function no longer works.

What you'll have to do is replace that function with a function of your
own. Here's one I have lying around -- not my own code, though I've
modified it slightly, and I don't remember where I got it:

'----- start of function code -----
Sub QSArray( _
arrIn() As String, _
ByVal intLowBound As Integer, _
ByVal intHighBound As Integer)

Dim intX As Integer
Dim intY As Integer
Dim varMidBound As Variant
Dim varTmp As Variant

If intHighBound > intLowBound Then
varMidBound = arrIn((intLowBound + intHighBound) \ 2)
intX = intLowBound
intY = intHighBound
Do While intX <= intY
If arrIn(intX) >= varMidBound And arrIn(intY) <= varMidBound Then
varTmp = arrIn(intX)
arrIn(intX) = arrIn(intY)
arrIn(intY) = varTmp
intX = intX + 1
intY = intY - 1
Else
If arrIn(intX) < varMidBound Then
intX = intX + 1
End If
If arrIn(intY) > varMidBound Then
intY = intY - 1
End If
End If
Loop
Call QSArray(arrIn(), intLowBound, intY)
Call QSArray(arrIn(), intX, intHighBound)
End If

End Sub
'----- end of function code -----

Copy and paste that code into a standard module, or into your form's
module if you think you'll only ever use it in that form. Then change
this line in the function fListFill:

Call apiSortStringArray(sastrFields)

to this:

QSArray sastrFields, _
LBound(sastrFields), UBound(sastrFields)

That ought to do it.

 

[lmv]

YAY!! That does it... This was a small annoyance which is wonderful to have
fixed.. now I realize what a large annoyance it really was!! thanks very
much!

Do you happen to know anything about hyperlink warnings...??? I have another
question posted...

http://www.microsoft.com/office/com...d5ef2c-1cbd-4a7c-9159-9a1af87b3a85&sloc=en-us