Using field value from tbl as variable and loop thru table??

[dschomburg]

Greetings,

I have a table with 1 field = "Report_Name". The table (surprisingly enough) contains the names of reports - which have been imported to various tmp tables.

I have a varible declared = SrcTable as string.

Here is my code thus far.

SrcTable = "PCIM9METTM" 'this is the report name

DoCmd.RunSQL "SELECT IIf(Left([Field2],3)='JOB','',IIf(Left([Field2],3)='PGM','',Trim([Field2]))) AS Test1, " & SrcTable & ".Field3, " & SrcTable & ".Field4, " & SrcTable & ".Field5, IIf(Left([Field7],1)='-',[Field6] & '-',[Field6]) AS test2 INTO tmp_Table_Type_1 FROM " & SrcTable & ";"

What I am struggling with is how to use each Report_Name field value from the "tbl_Report_Names" table via a loop I'm guessing to populate the value of the variable "SrcTable"

Does this make any sense? Is there an easier way, am I on the right track?

Any assistance is greatly apprciated.
Thanks,
Dave

 

[vbajock]

Your question actually covers a pretty broad topic. I recommend you get a book on Access coding like Programming Access 200x by Microsoft or Power Programming Access 200x
by Steve Barker, where x= your version of Access. The sections on ADO coding will show you how to set up a connection and how to do code loops etc

 

[dschomburg]

vbajock,

Thanks for the information. Perhaps my question was convoluted.

Here is my complete sub sans a global declaration for "SrcTable"...

Sub create_tables()
DoCmd.SetWarnings (False)

Dim Reportstable As Recordset
Set Reportstable = [tables]![tbl_Report_Names]

Do While Not Reportstable.EOF
SrcTable = Reportstable!Rpt_Nm

DoCmd.RunSQL "SELECT IIf(Left([Field2],3)='JOB','',IIf(Left([Field2],3)='PGM','',Trim([Field2]))) AS Test1, " & SrcTable & ".Field3, " & SrcTable & ".Field4, " & SrcTable & ".Field5, IIf(Left([Field7],1)='-',[Field6] & '-',[Field6]) AS test2 INTO tmp_Table_Type_1 FROM " & SrcTable & ";"

DoCmd.RunSQL "SELECT tmp_Table_Type_1.Test1, tmp_Table_Type_1.Field3, tmp_Table_Type_1.Field4, tmp_Table_Type_1.Field5, tmp_Table_Type_1.test2 INTO 'tmp_'" & SrcTable & " FROM tmp_Table_Type_1 WHERE (((tmp_Table_Type_1.Test1)<>' '));"

Reportstable.MoveNext
Loop

DoCmd.SetWarnings (True)

End Sub

It's actually the Dim and the set (where I'm trying to make the table="tbl_Report_Names" to be the recordset.

Thanks again.

 

[dschomburg]

DUH!
I was having a blonde moment.
Here's what I came up with

Sub create_tables()
DoCmd.SetWarnings (False)

Dim This As Database
Dim Reportstable As Recordset
Set This = CurrentDb
Set Reportstable = This.OpenRecordset("tbl_Report_Names")

Do While Not Reportstable.EOF
SrcTable = Reportstable!Rpt_Nm

DoCmd.RunSQL "SELECT IIf(Left([Field2],3)='JOB','',IIf(Left([Field2],3)='PGM','',Trim([Field2]))) AS Test1, " & SrcTable & ".Field3, " & SrcTable & ".Field4, " & SrcTable & ".Field5, IIf(Left([Field7],1)='-',[Field6] & '-',[Field6]) AS test2 INTO tmp_Table_Type_1 FROM " & SrcTable & ";"

DoCmd.RunSQL "SELECT tmp_Table_Type_1.Test1, tmp_Table_Type_1.Field3, tmp_Table_Type_1.Field4, tmp_Table_Type_1.Field5, tmp_Table_Type_1.test2 INTO tmp_" & SrcTable & " FROM tmp_Table_Type_1 WHERE (((tmp_Table_Type_1.Test1)<>' '));"

Reportstable.MoveNext
Loop

DoCmd.SetWarnings (True)

End Sub

 

[Zion7]

dschomberg, is it possible your field reference in the recordset, is syntaxed incorrectly also?
Reportstable(Rpt_Nm). which is DAO
Reportstable!Rpt_Nm is an ADO reference.

It appears Your recordset is a DAO connection.

Hope this helps.
Good luck!

 

[vbajock]

I tried to work with your code, but it is quite frankly, a basket case. Add some comments explaining what it is you are trying to do and maybe I can help you.