Dynamically insert Table in Query...is it possible?

[NRK]

I have a Make Table query that I am trying to run. It works fine when I define the table. Here is my code:
DoCmd.SetWarnings False
DoCmd.RunSQL ("SELECT tcID INTO tbl_cust FROM 1_tbltcMaster WHERE PrintRpt = True&quot
DoCmd.SetWarnings True

But, what I really need is the ability to define what table the data is inserted in to (INTO portion of code). Ideally, when the user clicks 'Save', a pop-up form prompts them for the table. They select it from a drop-down box and the Make Table query is performed using the specified table.

I have tried defining the table as a string and having the string be in the code (INTO strTable FROM...) but it doesn't seem to work...

Any ideas would be greatly appreciated.

 

[KyleS]

Hi, try this:
DoCmd.SetWarnings False
DoCmd.RunSQL ("SELECT tcID INTO " & Forms!YourFormName!YourComboBoxName & " FROM 1_tbltcMaster WHERE PrintRpt = True&quot
DoCmd.SetWarnings True

Hope this helps... Kyle :

 

[NRK]

Kyle...that was it! Thank you so much.

 

[NRK]

Kyle...apologize for the simplistic follow-up but I am trying to run your code through another SQL set, but I get errors.

Here is the code:
strSQL = "UPDATE 1_tbltcMaster INNER JOIN " & Forms!frm_CustFilter_Print!cboCustomer & "
ON [1_tbltcMaster].tcID = " & Forms!frm_CustFilter_Print!cboCustomer & ".tcID
SET [1_tbltcMaster].RptList = True
WHERE ((([1_tbltcMaster].tcID)=" & Forms!Netrak_CustFilter_Print!cboCustomer & ".[tcID]));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


Do you see anything wrong? I believe the issue is that I am referring to data within the combo box as a table with specifics (table.tcID).

The combo box looks up a group of tables (built on a query), so is there any way to have the combo box value be inserted into this query correctly?

If you need any further information, let me know...

 

[KyleS]

You know, having never tried this I can't tell you for sure whether it should work or not, however, the theiry is sound...

So try putting brackets "[" before and after the field reference like this:

strSQL = "UPDATE 1_tbltcMaster INNER JOIN " & Forms!frm_CustFilter_Print!cboCustomer & "
ON [1_tbltcMaster].tcID = [" & Forms!frm_CustFilter_Print!cboCustomer & "].tcID
SET [1_tbltcMaster].RptList = True
WHERE ((([1_tbltcMaster].tcID)= [" & Forms!Netrak_CustFilter_Print!cboCustomer & "].[tcID]));"
DoCmd.SetWarnings False
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True Kyle :

 

[NRK]

Didn't work unfortunately...I went into the Query Builder and built what I wanted

Actual SQL for working query:
UPDATE 1_tbltcMaster INNER JOIN cust_name
ON [1_tbltcMaster].tcID = cust_name.tcID
SET [1_tbltcMaster].RptList = True
WHERE ((([1_tbltcMaster].tcID)=[cust_name].[tcID]));

I compared it to what you sent and the only difference I saw was your inclusion of brackets on the "ON" line.

Completely stuck...tried variations of yours and mine; know that the concept is sound (via running it in a Query); know that your concept is sound (as it worked on the other form).

 

[KyleS]

Hey do me a favor and take mine and put it in a msgbox (or dbug window if you prefer) and take a look at what Access is trying to run

msgbox strsql

OK, let me know if you see anything wierd... Kyle :

 

[NRK]

Kyle - got it!

It was a silly mistake - transposed a character...your code was perfect.

Thanks so much for your help!

 

[KyleS]

Happy to Help Kyle :