Select a table name from a combo box and use it in a query

[ShanVel]

This must be an easy one, but I am having trouble.

Can someone help me how to populate a combo box with table names (I have like 50 tables in my database) and then have user pick an item from the como box list and use that item (table name) in a select query? The combo box contains two columns, the bound column is the actual table name and the second column is the more descriptive name for table name so that user can understand.

After picking an item (i.e selecting a table name, say Table_A) from the list, I want to use that table name (Table_A) in a select query. I am having trouble with syntax, I would appreciate if someone can help me please.

Shan.

[Moniker]

To get the complete list of tables in your DB, use the TableDef collection in the TableDefs object.

However, it sounds like you've already built a table with the actual table names in column zero and the more descriptive name in column one, and you're displaying column one in your drop-down combo. Just make column zero the bound column, and the combo box will default to that value.

You said you were having problems with syntax, but you didn't post the example of what you are stuck on, so there's nothing to go on.

[rborob]

With regards to the syntax, you should try creating a query through access, building some criteria, changing the type then looking at the SQL. Most of the time it comes from habit and experience. i used to just build all my queries. After a while i found myself typing them all freehand.

[Mile-O]

Provided you have been sensible in giving your tables an indentifying prefix then you can open a new query, switch to SQL View, and paste the following SQL:

SELECT MSysObjects.Name
FROM MSysObjects
WHERE (((MSysObjects.Type)=1) AND ((MSysObjects.Name) Like "tbl*"));

In the above example, I use tbl to prefix my tables.

EDIT: Also, 50 tables sounds like a hell of a lot, especially given that you want to use the table name in a SELECT query. It sounds like a number of these tables, if not all, are identical in structure and would therefore suggest that your database is suffering from a lack of normalisation.

[ShanVel]

Moniker, Rborob and SJ:

Thank you for taking time to look into my request. It's greatly appreciated.

Moniker, you are correct. I have built a table with the actual table names in column one and the more descriptive name in column two. Is the column index starts with 0 or 1? I have column widths 0";4" but bound column as 1.

Also here's the SQL I have placed under the RecordSource property of combo box:
SELECT tblBlrGuarTableNames.GuaranteeTableNames, tblBlrGuarTableNames.chrDescrGuaranteeName
FROM tblBlrGuarTableNames;

SJ - You are correct. This database is definitely suffering from a lack of normalization. This is not like an order entry/tracking database, but to store similar data for more than 50 items (guarantees) with 12 fields (all memo type with each field to accommodate more than 1300 chrs in some cases). I couldn't come up with a better design, at least for now.

Here's the routine I am having trouble with:
Public Sub cmdSearch_Click()
Dim strDocName As String
Dim strTableName As String
Dim rpt As Report
Set rpt = CreateReport

strTableName = Forms!frmSearchBoilerGuar!cboTypeOfGuar

strSQL = "SELECT P.chrProjectName, P.chrBlrPropNum, " & _
"T.memGuranItem , T.memLDs FROM tblProjts1 as P, strTablename as T" & _
" WHERE" & _
"P.intProjectId = T.intProjectId"

rpt.RecordSource = strSQL
strDocName = "rpt"

DoCmd.OpenReport strDocName, acPreview
End Sub
If the above info is not clear I can provide more, just let me know.

As you notice, I am trying to build this SQL and run a report via VBA code. I am not sure the report part is going to work or not, but I want to deal with one issue at a time. Any help/suggestion is greatly appreciated. I am not an access pro, but have some background on programming.

Shan.

[rborob]

why are you trying to build the SQL statement in code? why not just have the report bound to the SQL?

[ShanVel]

rborob,
Thanks for the response. The report is going to be created on the fly, it may include certain criteria that user can pick (which I plan to do later once this code works) before run the click button. I don't have any pre-created report where I can insert an SQL under report's RecordSource property, because the report may vary based on what user picks to include with their report. Hope this may clarify you question. If not please let me know or if you have any better suggestions.

Thanks again/Shan.

[rborob]

are the criteria combo boxes? if so you can build it based on what isnt null (what youve selected) so it is constantly up to date

start off with a string

strSql = "SELECT whatever WHERE whatever (put something which is always true here e.g. ID of table Is Not Null"...dont put a ";"

then

If IsNull(Me.combo_one) = False Then
strSql = strSql & " AND (Criteria for this combo)"
End If

If IsNull(Me.combo_two) = False Then
strSql = strSql & " AND (criteria for this one)"
End If

then at the bottom.........

Me.combobox.RowSource = strSql
Me.combobox.Requery

[ShanVel]

rborob,

Actually, the criteria is in checkBox and OptionButtons.

I have attached the GDB_1.zip file which has this search form. If see the from then it will be more clear.

Thanks/Shan.

[rborob]

Have a function Called SQLCreator or something. then in that have your SQL being built as i mentioned above. Have all the criteria (i cant tell what your criteria is really since your field names arent that good and you have no primary keys) in here regardless of whether one may be selected or not. Then after each check box press or each combo box selection call this function to get the updated SQL. have a play round and see how far you get

[ShanVel]

rborob,
Thanks. The tables tblProjts1 has "projectId" as a primary key and all other tables (tblEffcy, tblAuxPwrConsmptn, etc, I didn't include these tables on my last posting, but I have now without any data) which I select from the combo box has got their own primary key and "projectId" as a foreign key.

My main problem is how to select a table name from the combo box and assign it to a variable and then use that in a select query as described in my initial postings.

If you or anyone can give some tips then I can try this weekend. I will try your suggestion mentioned above and see how far I can go.

Thanks again.

[rborob]

havent had a proper look but ive added a bit to your "temp" Sub to show you how i would do it. Also changed the rowsource of your combo box to return the table names. have a look at the code. This shows how to build the SQL statement based on user options. (also changed the field names to Yes/No since they correspond to check boxes but thats up to you|)

[ShanVel]

rborob,
Sorry, I couldn't get back to you about how I made up. Unfortunately, I ran into some debugging problems. I tried with some breakpoints in my code and all the sudden it didn't stop at the breakpoints. Finally I got that figured and your suggestion of building SQL did work. Now I can select the table name from combo box and use it in a select a query, it is pulling the data as I expected.

One another question, I saw this (following) code in your response for option check boxes and I didn't understand little bit. Can you give some explanation? I know you are trying to combine the SQL that was built with combo box selection, but the syntax seems little hard for me.


If Me.Check57 = -1 Then
strSQL = strSQL & " AND ((" & strTbl & ".memPred)= Yes)"
End If

Thanks for your help.