title: setting the recordsource of a subform control using VBA
body: <p>Boring background, I'm trying to how to implement VBA correctly with Access - on the side - to hopefully improve a very primitive Access "application" at my place of employment.  Management is of the mindset, if "it" works (no matter how poorly) why try to improve "it"?</p>
  
  <p>All of that being said, a form is being used to display a list of tables to be opened for editing.  The main form contains the selection drop-down box and the sub-form which originally is linked to a table.  I'm trying to open the selected table in the subform so the user and delete records that do not need to be processed (which is working, thanks to stackoverflow.com).</p>
  
  <p>Anyway, the User selects the table and then the sql statement is created.</p>
  
  <blockquote>
    <p>SELECT [PLS_Elkins_072513].[Structure Number], [PLS_Elkins_072513].[Structure Comment    ], [PLS_Elkins_072513].[Structure Comment 2], [PLS_Elkins_072513].[Structure Comment 3], [PLS_Elkins_072513].[Structure Comment 4], [PLS_Elkins_072513].[Structure Comment 5], [PLS_Elkins_072513].[Structure Comment 6], [PLS_Elkins_072513].[Structure  Comment 7], [PLS_Elkins_072513].[Structure Comment 8], [PLS_Elkins_072513].[Structure Comment 9], [PLS_Elkins_072513].[Structure Comment 10], [PLS_Elkins_072513].[Structure Comment 11], [PLS_Elkins_072513].[Structure Comment 12], [PLS_Elkins_072513].[Structure Comment 13], [PLS_Elkins_072513].[Structure Comment 14], [PLS_Elkins_072513].[Structure Comment 15], [PLS_Elkins_072513].[Structure Comment 16], [PLS_Elkins_072513].[Structure Comment 17], [PLS_Elkins_072513].[Structure Comment 18], [PLS_Elkins_072513].[Structure Comment 19], [PLS_Elkins_072513].[Structure Comment 20], [PLS_Elkins_072513].[Structure Comment 21], [PLS_Elkins_072513].[Structure Comment 22], [PLS_Elkins_072513].[Structure Comment 23], [PLS_Elkins_072513].[Structure Comment 24], [PLS_Elkins_072513].[Structure Comment 25], [PLS_Elkins_072513].[Structure Comment 26], [PLS_Elkins_072513].[Structure Comment 27], [PLS_Elkins_072513].[Structure Comment 28], [PLS_Elkins_072513].[Structure Comment 29], [PLS_Elkins_072513].[Structure Comment 30], [PLS_Elkins_072513].[Structure Comment 31], [PLS_Elkins_072513].[Structure Comment 32], [PLS_Elkins_072513].[Structure Comment 33], [PLS_Elkins_072513].[Structure Comment 34], [PLS_Elkins_072513].[Structure Comment 35], [PLS_Elkins_072513].[Structure Comment 36], [PLS_Elkins_072513].[Structure Comment 37], [PLS_Elkins_072513].[Structure Comment 38], [PLS_Elkins_072513].[Structure Comment 39], [PLS_Elkins_072513].[Structure Comment 40], [PLS_Elkins_072513].[Structure Comment 41], [PLS_Elkins_072513].[Structure Comment 42], [PLS_Elkins_072513].[Structure Comment 43], [PLS_Elkins_072513].[Structure Comment 44], [PLS_Elkins_072513].[Structure Comment 45], [PLS_Elkins_072513].[Structure Comment 46], [PLS_Elkins_072513].[Structure Comment 47], [PLS_Elkins_072513].[Structure Comment 48], [PLS_Elkins_072513].[Structure Comment 49], [PLS_Elkins_072513].[Structure Comment 50] FROM PLS_Elkins_072513;</p>
  </blockquote>
  
  <p>And the following code is  used to set the recordset</p>
  
  <pre><code>Me.sfrmTempTable.Form.RecordSource = strSQL
  </code></pre>
  
  <p>Next I get a dialog box asking me to enter the parameter value for the 51 fields...I hit "enter" 51 times....</p>
  
  <p>"<a href="http://i138.photobucket.com/albums/q245/yosso22/Work%20Pictures/accessvba/enter_parameter_value_zps0f75f99d.png" rel="nofollow">http://i138.photobucket.com/albums/q245/yosso22/Work%20Pictures/accessvba/enter_parameter_value_zps0f75f99d.png</a>"</p>
  
  <p>VBA Code...</p>
  
  <pre><code>Private Sub EditStakingTable_Click()
  
  Dim strSQL As String
  Dim SelectTable As String
  
  On Error GoTo Err_Handler
  
  SelectTable = Me.InputTable.Value
  
  ' Check for a blank table name
  If SelectTable = "" Then
      MsgBox "No Table Selected!!!", vbInformation, "Please select a table for editing."
      Exit Sub
  End If
  
  ' Process - 6/13/13
  ' DoCmd.SetWarnings False ' Turn off warnings
  
  strSQL = "SELECT [" &amp; SelectTable &amp; "].[Structure Number], [" &amp; SelectTable &amp; "].    [Structure Comment 1], [" &amp; SelectTable &amp; "].[Structure Comment 2], [" &amp; SelectTable &amp; "].    [Structure Comment 3], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 4], [" &amp; SelectTable &amp; "].    [Structure Comment 5], [" &amp; SelectTable &amp; "].[Structure Comment 6], [" &amp; SelectTable &amp; "].    [Structure  Comment 7], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 8], [" &amp; SelectTable &amp; "].    [Structure Comment 9], [" &amp; SelectTable &amp; "].[Structure Comment 10], [" &amp; SelectTable &amp; "].    [Structure Comment 11], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 12], [" &amp; SelectTable &amp; "].    [Structure Comment 13], [" &amp; SelectTable &amp; "].[Structure Comment 14], [" &amp; SelectTable &amp;     "].[Structure Comment 15], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 16], [" &amp; SelectTable &amp; "].[Structure Comment 17], [" &amp; SelectTable &amp; "].[Structure Comment 18], [" &amp; SelectTable &amp; "].[Structure Comment 19], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 20], [" &amp; SelectTable &amp; "].[Structure Comment 21], [" &amp; SelectTable &amp; "].[Structure Comment 22], [" &amp; SelectTable &amp; "].[Structure Comment 23], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 24], [" &amp; SelectTable &amp; "].[Structure Comment 25], [" &amp; SelectTable &amp; "].[Structure Comment 26], [" &amp; SelectTable &amp; "].[Structure Comment 27], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 28], [" &amp; SelectTable &amp; "].[Structure Comment 29], [" &amp; SelectTable &amp; "].[Structure Comment 30], [" &amp; SelectTable &amp; "].[Structure Comment 31], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 32], [" &amp; SelectTable &amp; "].[Structure Comment 33], [" &amp; SelectTable &amp; "].[Structure Comment 34], [" &amp; SelectTable &amp; "].[Structure Comment 35], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 36], [" &amp; SelectTable &amp; "].[Structure Comment 37], [" &amp; SelectTable &amp; "].[Structure Comment 38], [" &amp; SelectTable &amp; "].[Structure Comment 39], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 40], [" &amp; SelectTable &amp; "].[Structure Comment 41], [" &amp; SelectTable &amp; "].[Structure Comment 42], [" &amp; SelectTable &amp; "].[Structure Comment 43], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 44], [" &amp; SelectTable &amp; "].[Structure Comment 45], [" &amp; SelectTable &amp; "].[Structure Comment 46], [" &amp; SelectTable &amp; "].[Structure Comment 47], ["
  strSQL = strSQL &amp; SelectTable &amp; "].[Structure Comment 48], [" &amp; SelectTable &amp; "].[Structure Comment 49], [" &amp; SelectTable &amp; "].[Structure Comment 50] FROM " &amp; SelectTable &amp; ";"
  
  Debug.Print strSQL
  
  Stop
  
  ' 8 / 09 / 13
  
  Me.sfrmTempTable.Form.RecordSource = strSQL
  
  ' After tblStakingEdit has been opened, edited and closed
  ' copy (or rename) the tblStakingEdit table back to
  ' the originally named table
  
  'DoCmd.SetWarnings True ' All done, turn on warnings back on.
  
  Exit_Here:
  
  Exit Sub
  
  Err_Handler:
       If Err.Number = 2101 Then
       'Me.frmtesting.Visible = True
       'ignore or message
       Else
          MsgBox Err.Description
       End  If
  
       Resume Exit_Here
  
  End Sub
  </code></pre>
  
  <p>After the code runs, I'm back at the original screen, but all of the fields in subform are displaying "#Name?". </p>
  
  <p>Screenshot url of final product
  "<a href="http://i138.photobucket.com/albums/q245/yosso22/Work%20Pictures/accessvba/final_screenshot_error_zpsa8ecb3a8.png" rel="nofollow">http://i138.photobucket.com/albums/q245/yosso22/Work%20Pictures/accessvba/final_screenshot_error_zpsa8ecb3a8.png</a>"</p>
  
  <p>There has to be something I'm overlooking, as I'm learning on the "fly" so to speak.  I did purchase a MVP book "Access Solution" which has a plethora of great examples, but now I'm at a loss and am appealing to the stackoverflow.com collective for assistance. :-)</p>