Syncing Main and Subform

[gray]

Hi All

Access2002/Access2007
WinXPPro SP3

Here's an interesting one. I have an unlinked Main and Subform; Master/Child Links can't be used because they are wrapped in SQL transactions. To replicate those links, the mainForm Current event reloads the subform with a new SQL recordsource (SELECT * FROM myTable2 WHERE myTable1_ID =" & me!ID).

This works well when the subForm is in FormView. When the subForm is in DataSheetView, Access freezes after 2 or 3 changes of mainForm record are undertaken.

For the moment, my workaround is to make sure the subForm is in FormView at the beginning of the mainForm Current event (then switch it back if necc). It works and I can set the form paint off whilst I do so to stop flickering but it seems to me this is a clunky solution?

Current Event code...

Dim intSubFrmView as Integer

intSubFrmView = Me.mySubform.Form.CurrentView
If intSubFrmView <> 1 Then   'is in datasheetview so switch
       Me.mySubform.SetFocus
       DoCmd.RunCommand acCmdSubformFormView
End If
        
Call Me.mySubform.Form.Form_Open(0)  'SQL criteria is built in here
    
If intSubFrmView <> 1 Then   'had been in datasheet view
       Me.mySubform.SetFocus
       DoCmd.RunCommand acCmdSubformDatasheet
End If

I guess it's some sort of timing issue... it takes longer to build datasheet than formview?

Any suggestions please? Thanks

 

[apr pillai]

An example for synchronized Main Form and Sub-Form without linking on Master Field and Child field:

Synchronized floating popup form

 

[gray]

Hi Apr!

Thanks for the reply. Much appreciated. I had a quick look at the suggested solution but I think it's effectively what I am doing already (although I handle the construction of the SQL string in the subform-open event).

My problem only seems to occur when the subform is in datasheetview. Much as I hate to code in delays, I wonder if I might need a pause somewhere to give chance for the datasheet to populate. Or maybe some crafty use of the Timer event?

Thanks and Rgds