Changing Subform Recordsource Dynamically

[MikeEller]

Hello,

I have a main form with two subforms. The second subform is not visible until the user clicks on a row within the first subform. Upon the second subform becoming visible on the main form, I would like to set the second subform's recordsource via code based on what the user has selected in the first subform.

I have this...

thearg is global variable off the main form which holds the table name
this could be done with selRec as well...both equal the name of table that I
want to be the record source.

selRec = fldCrseTitle.Text
selRec = selRec & ".fldClassNum"
Forms!frmMain.ClassSchedule.Form.RecordSource = "SELECT '" & selRec & "' FROM '" & thearg & "';"

I get error message stating "syntax error in query. Incomplete query clause."

Any ideas?

Thanks,
Mike

 

[pbaldy]

You would not want single quotes around the field or table names.

 

[MikeEller]

OK,
When I remove the single quotes in the SQL string, I get the following error:

Syntax error (missing operator) in query expression 'Air Movement Control Officer.fldClassNum'.

This is what the selRec variable evaluates to based on users selection. So how can I format the sql string to work correctly?

 

[pbaldy]

Because of the inadvisable spaces, you'd need to surround the field name with brackets:

[field name]

 

[MikeEller]

OK,
Do I do that within the variable, i.e. build the variable string to include the brackets? Or just surround the variable name within the sql string with brackets?

I may look at a way to remove the spaces. It is all created dynamically...so it will not be easy.

Thanks,
Mike

 

[pbaldy]

If there will only be one table in the query, I would drop it from the SELECT clause. Then you could simply replace the single quotes in your original post with the appropriate brackets:

Forms!frmMain.ClassSchedule.Form.RecordSource = "SELECT [" & selRec & "] FROM [" & thearg & "];"

 

[MikeEller]

OK,
That worked.
Now I get an error stating that I canceled the previous operation.
I did not do that!

I get this doing a DLookup

ME

 

[pbaldy]

Check the syntax of the DLookup. I've seen that error thrown when it's wrong:

http://www.mvps.org/access/general/gen0018.htm