Change form recordset remotely from another form

[llyal]

Change form recordset remotely from another form problem

I want to change the recordset on a form remotely from another form and would like to do this in DAO. How do i do it? I am not familiar with what is involved with DAO in Access- the following code does not work. I get an 'item not found in collection error".


Code:

Dim strSQL As String
Dim rsDAO As RecordSet

strSQL = ...

Set rsDAO = Forms!frmLibraryBooks.RecordsetClone(strSQL)

Set rsDAO = Nothing
DoCmd.Close acForm, "frmLibraryBooksSubSearch", acSaveNo


Thank you!

Llyal

 

[cpuSmoker]

Change recordsource remotely

For what purpose? To change the forms recordsource property permanantly?

If you want to change a remote forms record source from another form you shou be able to do it like so:

Private Sub cmdChangeForm_Click()
Dim Sql as String

sql = "SELECT * FROM tblTable WHERE (((tblTable.Field)='HELLO');"

docmd.openform "frmFormToOpen"

forms!frmFormToOpen.RecordSource = sql
End Sub

However, this is ONLY GOING TO WORK IF THE NEW RECORDSOURCE HAS THE SAME FIELDS AND DEFINITIONS THAT THE ORIGINAL DOES!

IE: The original form is linked to table1 and you are trying to link table2 to it. Table2 has different field names and default controls to it. If you are trying to get it to display a specific record, may I suggest the FILTER property?

Hope this helps.

 

[modest]

I think he wants to change just the recordset of a form, not necessarily a table.

 

[cpuSmoker]

Ahh, yes

Yes i believe you are right, but I am reading his question as he wants to permanantly change the recordset associated with a form. And since all the fields on a given form are linked to that specific table, unless the new recordset is just a specific query based on data held in that table...

You see my point.

 

[MarkK]

The RecrdsetClone property of a form IS a recordset, and your attempt to pass it the parameter strSQL causes the error.
In later versions of Access a form has a recordset property. To make the recordset that underlies FormA the same as that underlying FormB use...
Set FormA.Recordset = FormB.Recordset
To do this from FormB you might use...
Set Forms!FormA.Recordset = Me.Recordset

 

[RoyVidar]

Have a look at the posted date (or rather year) of the initial question

 

[modest]

Roy,

we can't answer everyone's question right away
Besides.. the thread did go unanswered so we should either answer it so others will know how, or delete the thread altogether?

Modest