How to create SQL Statement from Combobox Selections?

[Mr Key]

Hi all!
I have 2-Combobox, one shows tablelist and another fieldlist of selected tables. the Second is activated only when the first is selected.
Currently it works fine.
I want to add a bit work around on the Combobox to let users Create their own SQL Statement so that it can be used to create a query.
How can this be achieved?

[NeoPa]

That's a bit ambitious for someone that needs to ask.

What have you got so far?

[Mr Key]

Thanks neo!

[Mr Key]

Well neopa!
Am in position of explaining much better!
I have a Combobox (cbxTables)which selects tables in database with a ROWSOURCE as follows

SELECT [MsysObjects].[Name] AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType FROM MsysObjects WHERE (([MsysObjects].[Connect]<>Null) And ([MSysObjects].[Flags]=11534336)); 

This Combobox list the linkedtables of exeltypes as expected!
From this Combo I have Created another Combo (cbxFld0) to display a fieldlist of a table Selected from the firstCombo.
See the attachement for more clarifications!
Questions!
1. How to set cbxFld0 to display AllRecords and not a Fieldlist?
2. How to add a wilcard on the VBA to have SQL with only one Record Selected on the Combobox (cbxFld0)?

[Mr Key]

Yeap!
With time I came up with solution on how to select a Column as follows:
Let the tableNamesList at cbxCombo1 and Recordslist on cbxCombo2, then the following code will create the list on cbxCombo2:

Private Sub cbxCombo1_AfterUpdate()
Dim strSQL As String
    strSQL = "Select " & Me!cbxCombo1
    strSQL = strSQL & " from MsysObjects"
    Me!cbxCombo2.RowSourceType = "Table/Query"
    Me!cbxCombo2.RowSource = cbxCombo1
    Me!cbxCombo2.Requery
End Sub

Question:
How to set this Two Combos Selection into SQL-Button of which ON-CLICK Event will triger the CreateSQL and hence a QUERY?

[NeoPa]

You may prefer to use [MSysObjects].[Connect]>''. It gives a clearer indication of what you're checking for. Technically Null isn't a value and can only properly be checked using the Is Null construct or the IsNull() function. Your code should work fine of course, as it is automatically converted to the empty string for you. Anyway, that was a small point.

For the purposes of the explanation and being clear about your question, you should assume that I cannot see attachments. In truth I have little interest in opening attachments from untrusted sources and the thread makes no sense if the question is hidden within one. Remember, when we're done, this may be used by others with the same problems you have, unless they can't understand the question because it's hidden in an attachment. If I need you to attach anything I'll let you know. It's very rare.

Mr Key:
1. How to set cbxFld0 to display AllRecords and not a Fieldlist?

What is AllRecords? Is it a name I should know? Is it an extra value entry "AllRecords", that should be included as well as all the individual field names? I presume Fieldlist is meant to be a field list. This needs to be clearer if I'm to help.

Mr Key:
How to add a wilcard on the VBA to have SQL with only one Record Selected on the Combobox (cbxFld0)?

What does this mean? I have no idea what you're asking here.

This is a problem. I asked for clarification of what you'd tried to ask in the first post. Instead, you have added two more questions it seems, and neither makes a great deal of sense (I'm unable to answer as the question isn't there properly). I hope you can understand why this is a problem. If you ignore what I say - how can I help you. I'm sure you weren't trying to ignore me, but you haven't paid too much attention to what I said either. Let's start at the start and see if we can make the first question clear, then we can see what we can do from there.

[NeoPa]

Mr Key:
How to set this Two Combos Selection into SQL-Button of which ON-CLICK Event will triger the CreateSQL and hence a QUERY?

It seems you have made a start, so I will respond to this. It also seems to be a clarification (of sorts) of your first post, so I think I have a better understanding of what you require.

It's not too simple, even to do, let alone explain. One thing I should explain before we start though, is that any SQL that is just a SELECT statement (display of data rather than action query) is not possible to run just as a SQL string. Query data only displays in a QueryDef object.

Are you trying to create SQL to display data, or to update data somehow?

If it is the former then you will need to play with QueryDefs. If the latter, then we can execute the SQL once created.

Why don't you give an example of the sort of SQL string you expect to see so that we know what we're dealing with to start with at least.

Some examples of playing with SQL strings in VBA code can be found at Cascaded Form Filtering and other articles linked to within there.

[Mr Key]

Well!
It might be difficult to explain what exactly am I looking for but I will try.
First of all I have taken your sugestionsinto considerations and working on it!
Thanks!
I have several tables to create a Parameter Query! Most of them will be linked-Tables from excel(.xl*), the only choice I have is to have SQL create a query depending on user selections from Comboboxes! and not just updating data!
The situation is as follows!
1. The first combobox called cbxTables has a list of tables of excel types and the Second Combobox named cbxFld0 consist of Recordslist of which its RECORDSOURCE set to select from cbxTables as follows:

Private Sub cbxTables_AfterUpdate()
Dim strSQL As String
    strSQL = "Select " & Me!cbxTables
    strSQL = strSQL & " from MsysObjects"
    Me!cbxFld0.RowSourceType = "Table/Query"
    Me!cbxFld0.RowSource = cbxTables
    Me!cbxFld0.Requery
End Sub

Or, Anything similar to that will be fine
This works fine! whatever the table selected from cbxTables will have the corresponding Records dispalyed on the cbxFld0.
These two combobox selections should be used to create SQL and hence a query.
For example two selections from both combos should be used to create the SQL e.g (SELECT * FROM cbxTables WHERE IDs=Forms!MyForm!cbxFld0)
Where IDs is example of FieldsName. Or the Like!
From that SQL I can try to create a Query.
As I have said I dont know exactly what am I looking for!
May we have another approach on this?!
Any hint will be appreciated.
Please help!!!

[Mr Key]

Well!
I have knew Idea on this!!
Almost done but I miss something that needs your help!
I set three comboboxes,
1. cbxTables which display-linked Tables
2. cbxFld0 which display a fieldlist of cbxTables
3. cbxCombo2 which display the Recordlist of cbxFld0

Where:
1.cbxTables ROWSOURCE set to

SELECT [MsysObjects].[Name] AS ObjectName, IIf([type]=1 Or [type]=6,"Table","Query") AS ObjectType FROM MsysObjects WHERE (([MsysObjects].[Connect]<>Null) And ([MSysObjects].[Flags]=11534336)); 

2. Afterupdate Event of cbxTables set to designate the ROWSOURCE of cbxFLD0 as follows:

Private Sub cbxTables_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
 '
   If Me.cbxTables.Tag = vbNullString Then Call fEnableNextInTab
   Me.cbxFld0.Requery
 
End Sub

3. On-Exit-Event set to Rowsource of cbxCombo2 as follows:

Private Sub cbxTables_Exit(Cancel As Integer)
Dim strSQL As String
    strSQL = "Select " & Me!cbxTables
    strSQL = strSQL & " from MsysObjects"
    Me!cbxCombo2.RowSourceType = "Table/Query"
    Me!cbxCombo2.RowSource = cbxTables
    Me!cbxCombo2.Requery
End Sub

They all works as expected!
to create SQL I have designed a text-SQL which can be used to create the the SQL, a ListBox called listSQL which can be used to display the resulted selections as a table and two Butons, one for building SQL and the other for Creating a query. In short the code is as follows:

Option Compare Database
Option Explicit
 
Private Type OPENFILENAME
  lStructSize As Long
  hwnd As Long
  hInstance As Long
  lpstrFilter As String
  lpstrCustomFilter As String
  nMaxCustFilter As Long
  nFilterIndex As Long
  lpstrFile As String
  nMaxFile As Long
  lpstrFileTitle As String
  nMaxFileTitle As Long
  lpstrInitialDir As String
  lpstrTitle As String
  Flags As Long
  nFileOffset As Integer
  nFileExtension As Integer
  lpstrDefExt As String
  lCustData As Long
  lpfnHook As Long
  lpTemplateName As String
End Type
 
Private Declare Function GetOpenFileName _
    Lib "comdlg32.dll" Alias "GetOpenFileNameA" _
    (pOpenfilename As OPENFILENAME) As Long
 
Private Declare Function GetSaveFileName Lib "comdlg32.dll" Alias "GetSaveFileNameA" (pOpenfilename As OPENFILENAME) As Long
 
Private Const OFN_FILEMUSTEXIST = &H1000
Private Const OFN_HIDEREADONLY = &H4
Private Const OFN_OVERWRITEPROMPT = &H2
Private Const OFN_PATHMUSTEXIST = &H800
Private Const OFN_SAVE = 0
Private Const OFN_OPEN = 1
 
 
 
Private Type CTLInf
  Name As String
  Enabled As Boolean
End Type
Private Declare Function apiSortStringArray Lib "msaccess.exe" _
   Alias "#81" _
   (astrStringArray() As String) _
   As Long
 
Private arrCtls() As CTLInf
Private mvarOriginalFields  As Variant
Private Const mconQ = """"
 
Private Sub cbxCombo2_AfterUpdate()
Me.cmdBuildSQL.Requery
Call sBuildSQL
End Sub
 
Private Sub cbxTables_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
 '
   If Me.cbxTables.Tag = vbNullString Then Call fEnableNextInTab
   Me.cbxFld0.Requery
 
End Sub
Function fEnableNextInTab()
'Enable and Setfocus to the next control
'in the form's TabIndex.
Dim ctlNew As Control, intTab As Integer
Dim ctlOld As Control, intNewTab As Integer
 
   On Error Resume Next
   'Since we're calling this function from AfterUpdate,
   'what's the current control's position in TabIndex
   Set ctlOld = Screen.ActiveControl
   'we want the next one
   intNewTab = ctlOld.TabIndex + 1
 
   For Each ctlNew In Me.Controls
      intTab = ctlNew.TabIndex
      If Not Err And (intTab = intNewTab) Then
         'if no error occurred and the tab index is same as
         'what we're looking for, then enable it
         With ctlNew
            'Store the control's name for later use
            'but exclude the listbox since the tag there
            'contains the number of fields in the object select
            If Not ctlOld.ControlType = acListBox Then _
               ctlOld.Tag = .Name
            Select Case .ControlType
               Case acListBox:
               Case acComboBox:
                  'If the control found is a combo, fill it's rowsource
                  Call sFillCombo
               Case Else:
 
            End Select
            .Enabled = True
            .Locked = False
            .BackColor = vbWhite
            .SetFocus
            Exit For
         End With
      End If
   Next
   Set ctlOld = Nothing
   Set ctlNew = Nothing
   'Build the SQL automatically only if the user specified so
   Call sBuildSQL
End Function
 
Private Sub sFillCombo()
'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.cbxFld0
   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
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 = 1
 
   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.cbxTables.Column(1) = "Table" Then
            Set tdf = db.TableDefs(Me.cbxTables.Column(0))
            Set fld = tdf.Fields(Me("cbxFld" & i))
         Else
            Set rsQdf = db.OpenRecordset( _
               "Select * from [" & Me.cbxTables.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.cbxTables & "] Where " & strWhere
 
   'If the user has modified the SQL directly, take what they've typed in
      '"save" it in the textbox
      Me.txtSQL = strSQL
 
   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.cmdCreateQDF.Enabled = True
         .RowSourceType = "Table/Query"
         .RowSource = Me.txtSQL
         .Enabled = True
         'display * fields
         .ColumnCount = CInt(Me.cbxTables.Tag)
         .ColumnHeads = True
         Else
        'Thanks for trying, better luck next time!!
         Me.cmdCreateQDF.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.cbxTables & 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.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
 
Private Sub cbxTables_Exit(Cancel As Integer)
Dim strSQL As String
    strSQL = "Select " & Me!cbxTables
    strSQL = strSQL & " from MsysObjects"
    Me!cbxCombo2.RowSourceType = "Table/Query"
    Me!cbxCombo2.RowSource = cbxTables
    Me!cbxCombo2.Requery
End Sub
 
Private Sub cmdBuildSQL_Click()
 'build the SQL with what we have
   Call sBuildSQL
End Sub
 
Private Sub cmdCreateQDF_Click()
On Error GoTo ErrHandler
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strName As String
   'first get a unique name for the querydef object
   strName = Application.Run("acwzmain.wlib_stUniquedocname", "Query1", acQuery)
   strName = InputBox("Please specify a query name", "Save As", strName)
   If Not strName = vbNullString Then
      'only create the querydef if user really wants to.
      Set db = CurrentDb
      Set qdf = db.CreateQueryDef(strName, Me.txtSQL)
      qdf.Close
   Else
      'ok, so they don't want to
      MsgBox "The save operation was cancelled." & vbCrLf & _
         "Please try again.", vbExclamation + vbOKOnly, "Cancelled"
   End If
ExitHere:
   On Error Resume Next
   qdf.Close
   Set qdf = Nothing
   db.QueryDefs.Refresh
   Set db = Nothing
   Exit Sub
ErrHandler:
   Resume ExitHere
End Sub
 
Private Sub txtSQL_AfterUpdate()
   'build the SQL with what we have
   Call sBuildSQL
End Sub
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) = .cbxTables.Column(0)
            strObjectType = .cbxTables.Column(1)
            j = -1
                 If strObjectType = "Table" Then
               Set tdf = sdb.TableDefs(sastrObjSource(0))
               Me.cbxTables.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.cbxTables.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 Function fSetDocObjectProperty(strObjectName As String, _
                            strObjectType As String, _
                            strPropertyName As String, _
                            varPropertyValue As Variant, _
                            Optional varPropertyType As Variant = dbText) _
                            As Boolean
'?fSetDocObjectProperty("Module33","Modules","DateLastUpdated",Now)
'
On Error GoTo ErrHandler
  Dim db As DAO.Database
  Dim doc As Document
  Dim ctr As Container
  Dim prop As Property
 
  Set db = CurrentDb
  Set ctr = db.Containers(strObjectType)
  Set doc = ctr.Documents(strObjectName)
 
  doc.Properties(strPropertyName).Value = varPropertyValue
  fSetDocObjectProperty = True
ExitHere:
  Set prop = Nothing
  Set doc = Nothing
  Set ctr = Nothing
  Set db = Nothing
  Exit Function
ErrHandler:
  Select Case Err.Number
    Case 3270:
      Set prop = doc.CreateProperty(strPropertyName, _
                            varPropertyType, varPropertyValue)
      doc.Properties.Append prop
      Resume Next
    Case Else:
      fSetDocObjectProperty = False
      Resume ExitHere
  End Select
  Resume ExitHere
End Function
 

They all works as expected!
Now I want to add the first three permanent records (Where ID<4)that has to be used to create SQL with user`s cbxCombo2 Selection.

[Mr Key]

I can send the sample of this db if you wish

[Mr Key]

Well welwell!
Finally its answered!
I have set a permanent txtbox property to WHERE clause of SQL and hence a user will select only a table and one record which will triger the SQL with four Records and hence a query!
Should you need any further details, I will!
Thanks all for your contributions

[NeoPa]

Well Mr Key.

Firstly, let me congratulate you on your work and success, then explain that I wasn't ignoring you - I was just away for four days (this happens sometimes I'm afraid). It seems that in my absence you have come to understand the situation better for yourself, without any help other than the suggestion to explain the situation as clearly as you can. It doesn't all make sense to me (so I may have been unable to be of much more help anyway - at least not without some further rereading and trying to understand better - but that doesn't mean the work was wasted, as it seems to have helped you in your thinking process and overall understanding (for which I am very glad for you).

Congratulations on coming up with the solution. Good for you! But sorry for not being there to help you through the process more easily.

[Mr Key]

Yeah!
It was a bit difficult to create a QueryDefs!!!
In your presence would have make it much easier than what I did.
I have learnt alot on the way through.
Thanks you all, for your positive challenges