setting the recordsource of a subform control using VBA

Question

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"?

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).

Anyway, the User selects the table and then the sql statement is created.

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;

And the following code is used to set the recordset

Me.sfrmTempTable.Form.RecordSource = strSQL

Next I get a dialog box asking me to enter the parameter value for the 51 fields...I hit "enter" 51 times....

"http://i138.photobucket.com/albums/q245/yosso22/Work%20Pictures/accessvba/enter_parameter_value_zps0f75f99d.png"

VBA 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 [" & SelectTable & "].[Structure Number], [" & SelectTable & "].    [Structure Comment 1], [" & SelectTable & "].[Structure Comment 2], [" & SelectTable & "].    [Structure Comment 3], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 4], [" & SelectTable & "].    [Structure Comment 5], [" & SelectTable & "].[Structure Comment 6], [" & SelectTable & "].    [Structure  Comment 7], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 8], [" & SelectTable & "].    [Structure Comment 9], [" & SelectTable & "].[Structure Comment 10], [" & SelectTable & "].    [Structure Comment 11], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 12], [" & SelectTable & "].    [Structure Comment 13], [" & SelectTable & "].[Structure Comment 14], [" & SelectTable &     "].[Structure Comment 15], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 16], [" & SelectTable & "].[Structure Comment 17], [" & SelectTable & "].[Structure Comment 18], [" & SelectTable & "].[Structure Comment 19], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 20], [" & SelectTable & "].[Structure Comment 21], [" & SelectTable & "].[Structure Comment 22], [" & SelectTable & "].[Structure Comment 23], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 24], [" & SelectTable & "].[Structure Comment 25], [" & SelectTable & "].[Structure Comment 26], [" & SelectTable & "].[Structure Comment 27], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 28], [" & SelectTable & "].[Structure Comment 29], [" & SelectTable & "].[Structure Comment 30], [" & SelectTable & "].[Structure Comment 31], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 32], [" & SelectTable & "].[Structure Comment 33], [" & SelectTable & "].[Structure Comment 34], [" & SelectTable & "].[Structure Comment 35], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 36], [" & SelectTable & "].[Structure Comment 37], [" & SelectTable & "].[Structure Comment 38], [" & SelectTable & "].[Structure Comment 39], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 40], [" & SelectTable & "].[Structure Comment 41], [" & SelectTable & "].[Structure Comment 42], [" & SelectTable & "].[Structure Comment 43], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 44], [" & SelectTable & "].[Structure Comment 45], [" & SelectTable & "].[Structure Comment 46], [" & SelectTable & "].[Structure Comment 47], ["
strSQL = strSQL & SelectTable & "].[Structure Comment 48], [" & SelectTable & "].[Structure Comment 49], [" & SelectTable & "].[Structure Comment 50] FROM " & SelectTable & ";"

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

After the code runs, I'm back at the original screen, but all of the fields in subform are displaying "#Name?".

Screenshot url of final product "http://i138.photobucket.com/albums/q245/yosso22/Work%20Pictures/accessvba/final_screenshot_error_zpsa8ecb3a8.png"

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. :-)

Usually that means something is misspelled or not being queried properly. Have you tried just entering one parameter and to see if that works first? And you may also want to add a requery after you set the recordsource of your subform. — Grant
– Grant, Commented Aug 9, 2013 at 14:29
Tried the requery, it simply requested the field values a second time. I did trim the query down to just 4 fields. :-) There are multiple tables all with the same field structure that originate from a design/analysis program. I'm using a ODBC link to send the data from the program to access, perhaps it's some sort of problem with how the tables are created? Anyway, I'll check the spelling and experiment with single fields for testing purposes. — yosso
– yosso, Commented Aug 9, 2013 at 16:05
You could also design a query table with your ODBC linked table(s) and pull from that design just to make sure it's working too. — Grant
– Grant, Commented Aug 9, 2013 at 16:33
That worked, it allowed me to discover that the field names have not one, but two spaces in their field names. PLS-CADD and it's arbitrary field naming is going to be the death of me yet. — yosso
– yosso, Commented Aug 9, 2013 at 16:54

HansUp · Accepted Answer · 2013-08-09 14:42:56Z

1

Next I get a dialog box asking me to enter the parameter value for the 51 fields...I hit "enter" 51 times....

The SELECT clause lists 51 fields. If none of those fields exist in PLS_Elkins_072513, the db engine will assume the (unfound) field names are actually parameters and ask you to supply values for them ... 51 times. Then the subform fields can't find their Control Source in the form's recordset, so Access displays #Name? instead.

Your VBA code includes ...

Debug.Print strSQL

So copy that output from the Immediate window, create a new query in the query designer, switch to SQL View, and paste in the copied text. That effort should help you figure out how you need to change the query so Access will accept it without asking for parameters.

In a bigger picture view, as I understand the strategy you described, it can only work if all the possible table selections include the same 51 fields. Multiple tables with the same structure is generally regarded as poor design.

While you're sorting all this out, you can give the users an alternative to allow them to select a table and view/edit it.

DoCmd.OpenTable Me.InputTable.Value, acViewNormal

That does not give all the power of a custom form, but it's an easy interim kludge while you fix the original form.

edited Aug 9, 2013 at 14:42

answered Aug 9, 2013 at 14:30

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

yosso Over a year ago

I left out some background info, the tables all originate from a analysis/design program. Thusly, they always have the same field names. We are processing the tables in order to extract all of the assemblies or components. I shall try out the VBA code at lunch - looks to be a more than valid solution for my problem.

yosso Over a year ago

Discovered that the field names have two blank spaces, not just one. After adding the spaces to the field names the form updates as desired. Thanks for giving me the idea to examine the queries in the query designer - that did the trick!

HansUp Over a year ago

That makes perfect sense. And you're welcome. When building your SQL statement, consider walking the TableDef.Fields collection of the selected table to retrieve each field's .Name property. The code would then be resilient in case the field names in the table dumps from the other program ever change. And even without them ever changing, you would avoid developer error hard-coding the field names. :-)

HansUp Over a year ago

Incidentally, you do not need to explicitly requery the subform after changing its record source property. That happens automatically when you change the record source.

yosso Over a year ago

Excellent, removing the code and thanks for advice on retrieving the field names via VBA.

Collectives™ on Stack Overflow

setting the recordsource of a subform control using VBA

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related