Selecting existing table name using form's combobox for a query

[ShanVel]

Folks,
can someone help me on this? I am not sure why the select query inside this procedure is not working. Here is the that routine:

Public Sub temp()
Dim strDocName As String
Dim strTableName As String
Dim strTbl As String
Dim aot As Access.AccessObject
Dim strSQL As String
Dim rpt As Report
Set rpt = CreateReport

strTbl = Forms!frmSearchBoilerGuar!cboTypeOfGuar

For Each aot In CurrentData.AllTables
If aot.Name = "strTbl" Then
strTableName = strTbl
End If

Next aot

strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, " & _
"strTablename.memGuranItem , strTableName.memLDs FROM tblProjts1 " & _
"FROM tblProjts1 LEFT JOIN strTableName ON" & _
"tblProjts1.intProjectId = strTableName.intProjectId"

rpt.RecordSource = strSQL
strDocName = "rpt"

DoCmd.OpenReport strDocName, acPreview
End Sub


Basically, I am trying to select a table name from the combobox and then use that table name for my query. Then I want to use that query as a recordsource for my report.

Any help is greatly apprecited.

Shan.

 

[boblarson]

Use this as the rowsource for your combobox:

SELECT MsysObjects.Name
FROM MsysObjects
WHERE (((Left$([Name],4))<>"Msys") AND ((MsysObjects.Type)=1))
ORDER BY MsysObjects.Name;

and then you can use this:

Dim strTableName As String
strSQL = "SELECT tblProjts1.chrProjectName, tblProjts1.chrBlrPropNum, " & _
strTablename & ".memGuranItem , " & strTableName & ".memLDs FROM tblProjts1 " & _
"FROM tblProjts1 LEFT JOIN & " strTableName & " ON" & _
"tblProjts1.intProjectId = " & strTableName & ".intProjectId"

As far as creating a report and using that as the recordsource I don't know for sure. But, while reading through your query, it begs the question "Why select a table and then create essentially the exact same query, with the exception of the table name?" That makes me think that you have many tables with the same structure but with different data.

Pardon me for asking, why would that be; what would be the advantage? I can only think of painful tribulation when dealing with something like that. Would you mind explaining more fully what you have and why you are doing this particular building of the query?

Again, sorry for all of the questions, but I'm sure someone else will ask if I don't.

 

[ShanVel]

Dear Boblarson,
First of all, let me thank you for taking time to look at my request.

Please see my response to your questions below in red :

As far as creating a report and using that as the recordsource I don't know for sure. But, while reading through your query, it begs the question "Why select a table and then create essentially the exact same query, with the exception of the table name?" That makes me think that you have many tables with the same structure but with different data.
Yes, that's correct. I have many tables with the same structure but with different data. That's a good question. The reason is if I show the table name in the combobox the user may not understand. So, I decided to have this CB's bound column set to table names and hide it; and have a 2nd column to display the more verbal describtion of the table that they can understand, unless if there is a way to show a better describtion of what they have to pick in the CB.
Pardon me for asking, why would that be; what would be the advantage? I can only think of painful tribulation when dealing with something like that. Would you mind explaining more fully what you have and why you are doing this particular building of the query?
Yes, sure. I am developing this database which is going to handle pretty much all text type information. There are like 60 tables and 60 forms and all of these tables are basically with same structure but different data, and all of them are memo datatype (I have to deal with more than 1000 charcters in some field).
Again, sorry for all of the questions, but I'm sure someone else will ask if I don't. Never mind, again, that's a good qtn