Creating a query dynamically

[Chris Kentebe]

Hello Y'all,
I have a form with multiple tables and the user wants to
be able to select 2 or 3 tables,select a few fields from
the different tables, perform a query and print the
result. Can anyone help me here ?
Thanks

 

[JohnR]

Chris,

Here is a start.

I have a form with two list boxes and two command
buttons. The first list box (lstTables) is populated
from a table listing all the available tables. (I didn't
populate it off the TableDef to avoid the system
tables). The user selects a tale in the list box and
clicks the first command button. That populates the
second list box is with a list of fields in the selected
table. The second Command Button uses the fields
selected in the second list box to create a query for the
user.

Here is the code for the Command Buttons:
Private Sub CmdSelectTable_Click()
Dim i As Integer
Dim strTable As String
For i = 0 To Me.lstTables.ListCount - 1
If Me.lstTables.Selected(i) Then
strTable = Me.lstTables.Column(0, i)
Exit For
End If
Next i
Me.lstFields.RowSource = strTable
Me.lstFields.Requery
End Sub

Private Sub CmdRunQuery_Click()
Dim db As Database
Dim qdf As DAO.QueryDef
Dim strQuery As String
Set db = CurrentDb
strQuery = "Select "
For i = 0 To Me.lstFields.ListCount - 1
If Me.lstFields.Selected(i) = True Then
strQuery = strQuery & Me.lstFields.Column(0, i)
& ", "
End If
Next i
strQuery = Left(strQuery, Len(strQuery) - 2)
strQuery = strQuery & " From [" & Me.lstFields.RowSource
& "]"
For Each qdf In db.QueryDefs
If qdf.name = "qryTemp" Then
db.QueryDefs.Delete qdf.name
Exit For
End If
Next qdf
Set qdf = db.CreateQueryDef("qryTemp", strQuery)
qdf.Close
DoCmd.OpenQuery "qryTemp"
Set db = Nothing
End Sub

You would have to add to this to create a multiple table
query.