Setting Form Recordset Problem

[Gray]

Hi



I have a main form with a subform. The subform is unlinked. When I open the main form I build an SQL statement using various params and apply it to both the main and subform.. I won't bore you to death with why but I am using pre-Acc2007 but trying to replicate a Acc2007split-form!

When a filter is applied to the subform, I reset the main form recordset as below. I've added some msgboxes in there to help diagnosis:-

Dim rst As DAO.Recordset
 
MsgBox Me.My_SubForm.Form.RecordsetClone.RecordCount
 
Set Me.Form.Recordset = My_SubForm.Form.RecordsetClone
MsgBox Me.Form.RecordsetClone.RecordCount

But the recordset is not copied correctly... the recordset count is always 6 and when cycling thru' the field names using:-

 
me.form.recordsetclone.Fields(nn).Name

None of the ![column_names] are in the collection with the field names being "001","002","003" etc instead Any idea why please???

Thanks

P.S.
As an experiment, I set mainform recordset=subform recordsetclone in the form_open event, the form(s) open and behave perfectly.

[Gray]

I've got a little further with this...

When ApplyFilter is called in the subForm, I set a TimerInterval and call the subForm timer. In the Timer I set

Forms!My_Main_Form_Name.Form.RecordSet = Forms!My_Main_Form_Name.My_Sub_Form_NameForm.RecordSetClone

This seems to set the Recordset Ok (the recordcount is correct and the column names are available) it then cycles aound all the various current events (incl main)... but.... when the timer ends, the main form current event fires once more and the recordset "is nothing"... derrr.... the Access pixies have run away with it .. does anyone know where they live so I can fetch it back??

Any ideas anyone please?

[Gray]

OK I think I've found a solution by varying my approach.Trap any changes in ApplyFilter events.. use code to make those changes in the mainform and then set the subform recordset = mainform recordset.

Beware... I'm using Acc2007 but I'm not sure that earlier versions will allow this? I don't have a copy to hand so can't test it.

[orange]

Aren't you talking about Forms!My_Main_Form_Name.Form.RecordSource?
Not RecordSet???

[Gray]

Hi

I'm using the form's recordset property rather than recordsource.. Form's have a DAO recordset by default.. I do quite a bit of manipulation of the mainform recordsource so it's a handy way of keeping the mainform and subforms identical without having to re-code the subform recordsource too...

The downside of the approach above is that the subform loses it 'visible' filters... that is to say, if one right-clicks a control, the 'Clear filter from xxx' is not avialable. I have a custom button to manage my filters so it's not a particular issue for me.

Thanks

[orange]

I don't know if this is useful Gray, but here's a link. I don't have acc2007.

http://office.microsoft.com/en-us/ac...001232787.aspx

[Gray]

Thanks Orange... I think my issue is now resolved... interesting bit in the link you gave me....

Calling the Requery method of a form's recordset (for example, Forms(0).Recordset.Requery) can cause the form to become unbound. To refresh the data in a form bound to a recordset, set the RecordSource property of the form to itself (Forms(0).RecordSource = Forms(0).RecordSource).

Worth bearing in mind.....
rgds

[orange]

Glad you got the issue resolved, and posting the"fix" may benefit others.