To open a query based on a variable address

[mud2]

How to open a query based on a variable? I uas a function "Get_Table_Name" to return the name of the current table. How to use
Table_Name = Get_Table_Name ...and its various fields...as the base of a query. I suppose I could use SQL, with Insert into (variable)...Select (more variables!)....Oh How I dread trying to get the Chr(34)'s and chr(39)'s in all the right places!
Confused? I use a combobox to choose a field value, then search a table for this value. The table, the fields in the table, and the searched for value are all variables....!

[mud2]

I couldn't find the 'Post" for my question!

[mud2]

My original question was too simple:
I have several tables, each with DIFFERENT Column names. I want to query (select) this table with , naturally, a variable that might be in field1. I then want to put the results into a DIFFERENT table, whose field names must(?) agree with those selected. Is there some way to alias these names?
Whooosh!

[JimmyTheHand]

Here's one general way to use table and field names stored in variables.

Sub test()
Dim F1 As String, T1 As String
Dim MySQL As String
Dim RstSrc As Recordset, RstTgT As Recordset

F1 = "field3"
T1 = "table1"

MySQL = "SELECT field1, field2, " & F1 & ", field4 FROM " & T1 & " WHERE " & F1 & " = 'condition';"
Set RstSrc = CurrentDb.OpenRecordset(MySQL)
Set RstTgT = CurrentDb.OpenRecordset("table2")

Do Until RstSrc.EOF
RstTgT.AddNew
RstTgT!field1 = RstSrc!field1
RstTgT!field2 = RstSrc!field2
RstTgT.Fields(F1) = RstSrc.Fields(F1)
RstTgT!field4 = RstSrc!field4
RstTgT.Update
RstSrc.MoveNext
Loop
RstSrc.Close
RstTgT.Close
End Sub

When opening a recordset with SQL query, the most important thing is to create the SQL SELECT expression itself. It's a string. You can build it from any variables, even numerical ones, using the CStr function.

When adding records to a table, like in the example above (.AddNew) you don't need SQL SELECT to open the recordset. When updating records in a table, you might need it.

Please note the difference in syntax between when you use fixed field name and variable

RstTgT!field2 = RstSrc!field2 '<-- exclamation mark with fixed field name
RstTgT.Fields(F1) = RstSrc.Fields(F1) '<-- dot + Fields(variable) with variable
Also, in this example, the field names in the two tables are identical, but you can use non identical field names. E.g.
RstTgT.Fields(F1) = RstSrc.Fields(F2) I think the field data types should be the same, however.

HTH

Jimmy

[Norie]

Why not dynamically create the query?

[stanl]

Why not dynamically create the query?

I agree. In situations like this I use ADO's OpenSchema() to collect information to build queries, then the undocumented collect() method to transfer data w/out the overhead of the Fields Collection. .02 Stan