Displaying Recordset values in a Subform

[nilanjangm]

I have a subform in the main form. I have a recordset as well (Access 2007).

I want to display the records in the recordset in the subform. So I wrote this.

Set subfrmResourceProposedInProject.Form.Recordset = rst

(The recordset is fetching value correctly, I have checked that) However, this throws run-time error (

run-time error '2467': The expression you entered refers to an object that is closed or doesn't exist

)

I had another way which worked. The code was like this.

strQueryName = "SELECT * FROM tbl_Emp_Master;"
Set dbs = CurrentDb()
Set qdf = dbs.QueryDefs("qry_All_Emp_Detail")
qdf.SQL = strQueryName
    
subfrmCustomerExpense.SourceObject = "Query.qry_All_Emp_Detail"

In this case, I also had to define an Access query in the name of

qry_All_Emp_Det ail

, though I had to define that once again in the code here (don't know why!!).

Therefore, I wanted to know if there is any way to do it in the first way (using recordset object).

[ADezii]

I have no idea where, or even if, rst is Declared and Initialized. Try syntax similar to:

Dim MyDB As DAO.Database
Dim rst As DAO.Recordset
Dim strQueryName As String

strQueryName = "SELECT * FROM tbl_Emp_Master;"

Set MyDB = CurrentDb
Set rst = MyDB.OpenRecordset(strQueryName, dbOpenDynaset)

Set Me![subfrmCustomerExpense.].Form.Recordset = rst

[nilanjangm]

I had declared "rst" as

Dim rst As Recordset

along with

Dim dbs As Database
Dim qdf As QueryDef

Now, I have tried with your code. The line

Set Me![subfrmCustomerExpense.].Form.Recordset = rst

produced the error

Run-time error '2465': Microsoft Office Access can't find the field 'subfrmResource ProposedInProje ct.' reffered to in your expression.

. I removed the "." from the end of the 'subfrmResource ProposedInProje ct.' and tried again. This produced the same error as before

Quote:
run-time error '2467': The expression you entered refers to an object that is closed or doesn't exist

[nilanjangm]

I also checked whether the recordset holds the values using

Debug.Print rst.Fields(0) & ":"; rst.Fields(1) & ":" & rst.Fields(2) & ":" & rst.Fields(3)

after

Set rst = qdf.OpenRecordset()

This displayed the the values properly.

[ADezii]

Sounds like a Syntax Error on the actual SubForm Name. Remember also that you are referencing the Name of the SubForm Control and not the Name of the SubForm itself.