Refering to subform in Order to change its Recordset

[tfayer]

I have:
Parent For = frmContactList
Child Form = sfrmContactList
Unbound Form = frmLookUp

I want to change the recordset (originally based on qrycontactlist) from the unbound form

Im having problems set a varilabe, stSQL, equal to SQL statement the qrycontactlist was made of. I think this is because the SQL has "" in it. So when I put stSQL = "SELECT Trim([f_name]) & "" the origanl quotation used to make it a String may be interfering with SQL internal "".

Any thoughts?

Once I have that set and I choose a "WHERE" Clasue to add to I want to change the recordset of the subform sfrmContactList. I've tried:

Forms.frmContactList.sfrmStoredStudiesList.Form.RecordSource = strSQL

But dont know if I am referring to it correctly. I believe your supposed to . instead of ! when referring to a recordset. Is this true? How should I refer to it?

Thanks Todd

 

[DanJR]

Hi there,


Referencing:
------------

from the main form, you can refer to your subform's form using the syntax:

me.subform.form

in your case

me.sfrmContactList.form

Therefore, to change the RecordSource property of the subform:

me.sfrmContactList.form.RecordSource = strSQL

SQL String:
-----------
Yes, you're correct, Access is getting confused with the quotes. There are a few ways to 'un-confuse' MS Access...but here's one way:

[i'm a little confused on what your full sql statement looks like]

Use a single quote within the sql string

stSQL = "SELECT Trim([f_name]) FROM [the_table] WHERE [filter_field] = '" & strFilter & "'"

In order to see what your sql statement looks like, print your stSQL string to the immediate window eg

Debug.Print stSQL

then copy the sql statement from your immediate window and paste it into a query (in sql view) and see what it returns.

Hope this helps

Cheers
Dan