Thread: Using a table to keep Query arguments

Hi

I am kind of a novice in Access, so hopefully this question makes sense.

I want to build a Query that will pull certain fields from two different tables. To make it easy for an end user to decide which fields from each table to take, I'd like to create a table that holds each Query argument in it (tbl_Query_Holder). Then to add or take away an result from the Query, the user can simply remove that field from the tbl_Query_Holder via a form. I set up the tbl_Query_Holder to have 3 fields: Index, TableName, and FieldName. The TableName is the name of the table the Query will be pulling from, and the FieldName is the name of the field in that table to grab.

My question is once I have tbl_Query_Holder populated, how do I generate a Query based on the info in it?

Is this even possible? If not, is there a another way to accomplish what I am seeking to do?

Ok, I think I've found some basic code I can modify to do what I am looking for. This is what I'm working from:

Code:

Public Sub CreateExportQuery()

Dim CriteriaID As String
Dim Record As String
Dim Sample As Recordset
Dim Test As String

CriteriaID = ""
Record = "SELECT * FROM tbl_Innovient_Input_Fields;"

'Set Sample = CurrentDb.OpenRecordset(Record, dbOpenDynaset)
'For a = 1 To Sample.RecordCount
'    Test = Sample!Source_Field_Name
'Next a

Test = "SELECT * FROM"

Set Sample = CurrentDb.OpenRecordset("tbl_Innovient_Input_Fields", dbOpenTable)
For a = 1 To Sample.RecordCount
    Test = Test & " " & Sample!Source_Table_Name & "!" & Sample!Source_Field_Name
    Sample.MoveNext
Next a

Debug.Print Test

End Sub

I have already done some modifying which might explain why some of the variables are unused.

I would still like to know anyone's input as to whether this is a smart way to set up this db.

Thanks!