Wow. Got time on your hands or what!
How good are you at working with grid controls? That is the way I would go,
but using standard controls like ListBox and ComboBox here is a suggestion.
Put fields "SELECT" in a multi-select list box. Easy to loop through the
selected items in a list box to build the select part of the statement. By
the way, is this an inner, left, right join query or are these fields only in
one table? If they are in multiple tables you will have to put the table
name and field name in the in the listbox so when you build the select part
you can make sure that there are no "same" field names that would throw an
error. By the way... make sure you make all the listboxes Value List
The next part is tricky and can be accomplished different ways, that is why I
asked about grids because you can put a combobox in a grid field that they
could select from your < > = is like statement "WHERE" stuff
I would probably put another list box with the with fields to filter and a
combo box listing the various filter types (< > = etc ) and and a textbox for
their "value" and another listbox that will be filled with their selection
and value with a command button to hold the code and event of selecting a
WHERE. Have the filter list box hold only those fields that have not already
been selected as a WHERE filter. One thing you have to remember is will the
multi-field where be "AND" or "OR" connectors?
Have the person select a field, choose the filter type and put in the value,
press the command button and add it to the FilterListbox, storing those three
values (table and field name, fitler type < > =, and value. When the person
"adds" the filter have the code remove the field name from the Selection
Listbox. **You could allow them to make multiple selections on a single
field, but this will just add to the "fun" within your code. (Ex: Prod = 1
is a choice and then the also add Prod < 5 as a filter - personally I would
make each field selectable only once. See what I mean by AND or OR....
Prod=1 AND Prod<5 give you a completely different result than Prod=1 OR
Prod<5.
In the Build a Query command button you can just loop through the selected
items in the Field selection listbox and loop through all the records in the
FilterListBox creating the SELECT statement as you go along.
The table and join stuff should be the same for all their selections so
appending that onto the fields and then appending the where statement onto
that should not be a big problem
The form would have 3 listboxes and two command buttons. the code on the
form could look like this
Private Sub Command2_Click() 'button to have sql created
Dim sel
Dim sqlStr As String
Dim WhereStr As String
Dim tablenameJoin As String
tablenameJoin = "FROM Customers" 'or whatever your table join structure is
For Each sel In Me.List0.Selected
str = str & Me.List0.Value & ", "
Next
WhereStr = GetWhereStr(Me.List6)
str = Mid(str, 1, Len(str) - 2) & " " & tablenameJoin & " " & WhereStr & ";"
SqlListFields = SplitIt(str, 150)
End Sub
Private Sub Command5_Click() 'button to have the field to filter and the type
and value
Me.List6.AddItem Me.List3.Value & ";" & EqualsNotEqualLessThan & ";" &
InputValue 'you have to get the type an value from the form
End Sub
Private Sub Form_Load()
'fills listboxes with table name and fields
'if there is more than one table possible just loop through the table you
want
ListBoxFields "customers", Me.List0 ' customers is a fictious table name -
use your table names here
ListBoxFields "customers", Me.List3
End Sub
put these public functions in their own code module
Public Function SqlListFields(tablename As String) As String
Dim str As String
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
On Error GoTo HandleErr
str = ""
str = "SELECT "
rst.Open tablename, CurrentProject.Connection
For Each fld In rst.Fields
str = str & tablename & "." & fld.Name & ", "
Next
rst.Close
Set rst = Nothing
str = Mid(str, 1, Len(str) - 2) & " FROM " & tablename & ";"
SqlListFields = SplitIt(str, 150)
exithere:
Exit Function
HandleErr:
GoTo exithere
End Function
Public Function ListBoxFields(tablename As String, lBox As ListBox) As String
Dim str As String
Dim rst As New ADODB.Recordset
Dim fld As ADODB.Field
On Error GoTo HandleErr
str = ""
str = "SELECT "
rst.Open tablename, CurrentProject.Connection
For Each fld In rst.Fields
lBox.AddItem tablename & "." & fld.Name
Next
rst.Close
Set rst = Nothing
exithere:
Exit Function
HandleErr:
GoTo exithere
End Function
Public Function SplitIt(ByVal strx As String, Optional LineLength As Long =
100) As String
Dim SplitString As String
Dim splitvalue As Long
Do Until Len(strx) = 0
splitvalue = InStr(LineLength, strx, " ")
If splitvalue = 0 Then splitvalue = Len(strx)
SplitString = SplitString & """" & Replace(Left(strx, splitvalue), vbCrLf,
" ") & """" & IIf(Len(strx) = splitvalue, "", " & _" & vbCrLf)
strx = Mid(strx, splitvalue + 1)
Loop
Debug.Print SplitString
SplitIt = SplitString
End Function
Public Function GetWhereStr(whListbox As ListBox) As String
Dim whStr As String
Dim x As Long
Dim FilterStr As String
For x = 0 To whListbox.ListCount - 1
'put all your code here to break out the fields and types and values
depending on how you put them on the form
'Using Select Case statements would probably be best
'this assumes you are putting three values into the filter list box (table
and field, Type, value)
FilterStr = whListbox.Column(1, i) 'second column value
Select Case FilterStr
Case "Equals"
whStr = whStr & whListbox.Column(0, i) & "=" & "whListbox.Column
(2, i)"
Case "Less Than"
Case "More Than"
Case "Blah"
End Select
whStr = whStr & ", "
Next x
whStr = "WHERE " & Left(whStr, Len(whStr) - 2)
End Function
I included a couple extra code snippets above in the module. One builds a
sql from a table and the other is a spilt it function that will split long
lines into sections to your length spec.
This should get you wnat you basically want to do. You will have to put in
the various parts of the code. I would give the user an Option Group to pick
the TYPEs from ( <, >, =, <>, Like blah) and a text box to input the value to
have the filter on.
Hope this helps
