Unlinked Form/Sub_Form Synchronisation

[gray]

Hi All

Access 2002/2007
WinXPPro


Here's an interesting one. I've got a leaping bookmark!

Outline
I'm syncing a mainForm with a subForm. Both forms are populated from the same table using:

RecordSource= "SELECT * FROM Addrs WHERE yardy-yar"
RecordSetType = Dynaset

mainForm is in single form view, subForm is in datasheet view. Navigation buttons on mainForm, none on subForm.

The forms are NOT linked because I want the datasheet to display all records. There are other subforms linked to mainform however.

Aim
When a user clicks on a record in the subForm I want the mainForm to scroll to the same record. When user moves mainForm using nav buttons I want the subForm to scroll to same record.
To do this, I do 'tests' in both forms' current events. These compare current record IDs and do a FindFirst as req'd.

Issue
The above sync works fine for a while, say 10 or 15 times... then it starts to go wobbly....e.g.

1. subForm/mainForm both at recordID 50.
2. User clicks on recordID 20 in the subForm
3. subForm current event fires, instructs 'sync' routine in mainForm to go to recordID 20
4. 'Sync' routine in mainForm tells mainForm bookmark to move to recordID 20 .....
At this point, despite the forms NOT being linked, the current event in the subForm fires again... Debug shows current event is no longer at recordID 20 or even recordID 50 but at the record prior to recordID 50???...
5. Focus gets passed back to the mainForm 'Sync' proc where it completes
6. Focus then returns to subForm current event (as one would expect but at the wrong record).

From this point onwards, I get an out-of-memory error from the 'sync' during the bookmark move.

Any ideas anyone?

Thanks

Code

Public Longs are declared in both Forms i.e. Rec_Id_To_Mve_To

MainForm Current Event -

Public Sub Form_Current() 
     If Me.Form!Unique_No <>  Me.Addrs_Dtls_SubForm.Form!Unique_No Then
          Me.Addrs_Dtls_SubForm.Form.Rec_Id_To_Mve_To = Me!Unique_No
          Call Me.Addrs_Dtls_SubForm.Form.Sync_To_Main_Form
     End If
Exit Sub

SubForm Current Event

Private Sub Form_Current()
    If Me.Parent.Form!Unique_No <> Me.Form!Unique_No Then
        Me.Parent.Rec_Id_To_Mve_To = Me!Unique_No
        Call Me.Parent.Sync_To_Addrs_Dtls_SubForm
    End If
End Sub

MainForm 'Sync' Proc

Public Sub Sync_To_Addrs_Dtls_SubForm()
    Dim rst As DAO.Recordset
 
    Set rst = Me.Form.RecordsetClone
    rst.FindFirst "[Unique_No]=" & Rec_Id_To_Mve_To
    If rst.NoMatch Then
        'Stay where we are
    Else
        Me.Form.Bookmark = rst.Bookmark
    End If
    rst.Close
    Set rst = Nothing
End Sub

SubForm 'Sync' Proc

Public Sub Sync_To_Main_Form()
    Dim rst As DAO.Recordset
 
    Set rst = Me.Form.RecordsetClone
    rst.FindFirst "[Unique_No]=" & Rec_Id_To_Mve_To
    If rst.NoMatch Then
        'Stay where we are
    Else
        Me.Form.Bookmark = rst.Bookmark
    End If
    rst.Close
    Set rst = Nothing
End Sub

I found this similar problem http://www.ms-windows.info/Link/article-35116.aspx but sadly no resolution...

 

[vbaInet]

gray,

Since you're using Access 2007, did you know about the new Split Form?

Here's the revised code.

Main Form:

Private Sub Form_Current()
    With Me.Addrs_Dtls_SubForm.Form
        If Me!Unique_No <> !Unique_No Then
            .OnCurrent = ""
            Call SyncAddresss(.Form, Me!Unique_No)
            .OnCurrent = "[Event Procedure]"
        End If
    End With
End Sub

Subform:

Private Sub Form_Current()
    With Me.Parent
        If Me!Unique_No <> !Unique_No Then
            .OnCurrent = ""
            Call SyncAddresss(.Form, Me!Unique_No)
            .OnCurrent = "[Event Procedure]"
        End If
    End With
End Sub

Put this in a Standard Module, not a Class Module and not the form's module:

Public Sub SyncAddresss(frm As Form, recID As Long)
    Dim rst As DAO.Recordset

    Set rst = frm.RecordsetClone

    rst.FindFirst "[Unique_No] = " & recID

    If Not rst.NoMatch Then
        frm.Bookmark = rst.Bookmark
    End If

    rst.Close
    Set rst = Nothing
End Sub

 

[gray]

HI

Thanks for the code VB1Net... I've implemented it but, although it's behaving more predictably, it's still not quite working. After a few selections, say 20 or so, it loses its way again. What seems to happen is:-

1. subForm datasheet is at rest at, say, ID 80
2. Click subForm datasheet record, say, ID 91
3. mainForm gets moved to ID91
4. subForm jumps back ID80, mainForm remains at ID 91

To re-sync:
5. click again on ID 91 on subForm, mainForm remains at ID 91.. so they are in sync again.

All very strange... It's odd that it works perfectly for the a few selections but then goes astray.... I wonder if it's an issue with recordsetclone resources... Or ids there some way of 'locking' the subForm bookmark when it is first clicked?

One good thing is I don't get the out-of-memory errors anymore ...
Thanks


P.S. The split forms look good ... thanks for the tip. For my current project though I think I need to stay with 2002 features...

 

[vbaInet]

Attach your db, tell me which form it is and I'll have a quick look.

 

[gray]

Hi

That'd be great (and very decent of you... Sure you've a ton of other things to do!)

I've had to butcher it a little but I think you'll be able to see what's happening.

Once opened and code enabled, click on the Addrs_Dtls_Form... this will open the form in question together with it's subForm which will be in datasheet view. If you click randomly in the datasheet records you'll notice after a few selects that the cursor in the subForm jumps around.

The NQ textbox in the header is bound to the Unique_No in the Addrs table and shows the record at which the mainForm sits.

The Unique_No Col (as it suggests) is also bound to the Addrs, Unique_no and shows where the subForm sits.

The code you gave me is in the SyncAddresss_Module. I hope I've placed that module correctly..

Thanks for your assistance...

 

[vbaInet]

I didn't experience the problems you explained. It's working quite well for me. You have

Here's a different approach (from LPurvis) that uses event sinking. See if that works for you:

http://www.databasedevelopment.co.uk/examples.htm

The link is captioned Forms in Sync

 

[gray]

Hi

All very bizzare. Curiously, I'm suddenly beset by Lock problems and I wonder if this and the syncing issue are related?

My main and subforms are not linked, both are dynaset, no-locks, both are initially set up with "Select " statements in their recordsources but when I try to reset my subform recordset to

SQLLine = "SELECT * FROM Addrs WHERE xyz

Set Edit_rstDAO = Edit_Db.OpenRecordset(SQLLine, dbOpenDynaset, adLockOptimistic)

I now get :-

"The table 'Addrs' is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically".

This was working just dandy a few days ago.

I've done a compact and repair a few times but still no joy. Where does access store it's locks? I'm guessing either in the .ldb file or one of the Msys tables.

Thanks for taking the time on this... and I'll have a nosy at the syncing link you provided....

cheers

 

[vbaInet]

I'm not sure why you're needing to manage locking but I will point out that you're using the ADO optimistic lock type when your recordset is a DAO recordset.

Set Edit_rstDAO = Edit_Db.OpenRecordset(SQLLine, dbOpenDynaset, adLockOptimistic)

It should have been dbOptimistic.

Also, you've got it in the Options parameter whereas it should have been in the LockEdit parameter as below:

Set Edit_rstDAO = Edit_Db.OpenRecordset(SQLLine, dbOpenDynaset, [COLOR=Red], [/COLOR][COLOR=Blue][B]dbOptimistic[/B][/COLOR])

 

[gray]

Hi

Ahh.. so that's what's happening... I'd tried using it as the 4th parameter and it failed... no wonder! DAO vs ADO.... I guess the clue I missed was adLockOptimistic

Excellent advice!... thanks for clearing that up....

 

[gray]

Hi VBA1Net

I have tried LPurvis' technique too but I still have the problem... I have noticed something further however..

I didn't experience the problems you explained

I notice that the issue only occurs when clicking inside a control within a row... so it works fine when clicking on the datasheet row-edge but leaps about all over when clicking in, say, the Address Name textbox...

A debug reveals the following..

a) the subform is at rest at, say, row 30,
b) the Name textbox in the subform is clicked at, say, row 25
c) the subform current event fires and is correctly at row25
d) the subform event finishes and is re-fired immediately but is now back at row30?..

When the edge row-selector is similarly clicked the subform current event fires , finishes but then rather then fire again, the Main form event fires...

I've disabled all events in that textbox but still the issue persists...

Any further ideas?

Thanks

 

[vbaInet]

I had tried all variations and never had the problem.

Perhaps you should Compile your code (i.e. Debug > Compile) and run it. Also, run it on a different machine as well.

 

[gray]

Hi

Well it was worth a go... compiled OK but still same problem. I've even commented all the subform events out and de-rowsource'd/de-controlsource'd the varous comboboxes (advice I saw on a similar thread).

One thing.. I'm not alone as I found someone else (in addition to the above) describing the exact same thing..

http://www.eggheadcafe.com/software/aspnet/35978182/using-a-datasheet-subform-as-record-selector.aspx

Sadly, again, no resolution shown...

I haven't tried on another machine yet... I'll save that delight until tomorrow...

Thanks again for your time...

 

[gray]

Hi All

I seem to have fixed this... having run out of ideas I decided to work back through the various sync options from the beginning...

I reverted to the traditional...

mainForm

Private Sub Form_Current()
     If Me.My_SubForm.Form!Unique_No <> Me.Form!Unique_No Then
        Me.My_SubForm.Form.Recordset.Bookmark = Me.Recordset.Bookmark
    End If
End sub

subForm

Private Sub Form_Current()
    If Me.Parent.Form!Unique_No <> Me.Form!Unique_No Then
        Me.Parent.Recordset.Bookmark = Me.Recordset.Bookmark
    End If 
End sub

To my amazement this has solved the problem. I say amazed because this where I started days ago!... I can only assume that some of the events/code that I've been gradually pruning to isolate the issue has been successful... I'm going to start re-introducing those... if I can find any obvious candidates I'll update this post.

Thanks (esp to VBA1Net for time and patience )

 

[spikepl]

I have been following your trials and tribulations from the sideline, and am rather stunned by your latest message. According to the docs, one cannot use a bookmark across different recordsets, even if based on the same data: http://msdn.microsoft.com/en-us/library/bb221142(v=office.12).aspx Of course, documentation has been found wrong many a time, so if it works I guess it works.

I am looking forward to hearing the final "It works "

 

[gray]

Arrrrgh!!

Well I shall read up on that because it seems I spoke too soon!!!

What's actually happened is that the number of selections working normally has increased from about 2 or 3 up to about the low 30s... then the subForm bookmark starts jumping all over the place again... Once the mainForm has been moved to the selected record the subForm cursor bounces back to where it was (anything upto about 5 selections previously)....

Surely, this has to be some memory or other resource issue..??

 

[spikepl]

I played around with your DB yesterday. I commented out the OnCurrent on your main form, and commented out the call to SyncAddress on your subform. Everything then worked fine. Then I commented the Call SynAddress back in and then the trouble started. Clicking on the record selector is fine. But clicking on one of the fields, after a while, requires two clicks. The first one selects the record where the last record-selector click was, and the next the actual record I am clicking on. And so on. After a while the thing falls in love with some other record it wants to go back to. All this is weird.

 

[spikepl]

It may have something to do with focus - note how differently the lines are highlighted, depending on which mood the bl... thing is in.

 

[gray]

Hi All

How's this grab ya' ?

Somehow I get that uneasy feeling about my logic but tested it with 100+ selections (yawn!) and it seems OK?

Tests:
1. Navigated both mainForm and subForm to first and last records
2. Navigated using both/mixed mainForm and subForm navigation buttons
2. Navigated using both/mixed mainForm navigation buttons and datasheet selections
3. Single and Double-Clicked on many of the datasheet columns
4. Called the search form up on the mainForm and used that to find/scroll to chosen records

All stays in sync...

One thinig that does not work correctly is when using the search form on the subForm... it works perfectly ... until the seach form is closed whereupon the subForm current event fires and scrolls back to it's previous row...

Here's what I did:-

Declare Curr_BkMrk in subForm header

mainForm

    If Me.My_subForm.Form!Unique_No <> Me.Form!Unique_No Then
        With Me.My_subForm.Form.RecordsetClone
            .FindFirst "[Unique_No] = " & Me!Unique_No
            If Not .NoMatch Then
                Me.My_subForm.Form.Bookmark = .Bookmark
            End If
        End With
    End If

SubForm

    If Not IsEmpty(Curr_BkMrk) Then
        If Not IsNull(Curr_BkMrk) Then
            Me.Form.Bookmark = Curr_BkMrk
            Exit Sub
        End If
    End If
    If Me.Parent.Form!Unique_No <> Me.Form!Unique_No Then
        With Me.Parent.Form.RecordsetClone
            .FindFirst "[Unique_No] = " & Me!Unique_No
 
            If Not .NoMatch Then
                Curr_BkMrk = Me.Form.Bookmark
                Me.Parent.Form.Recordset.Bookmark = .Bookmark
                Curr_BkMrk = Null
            End If
        End With
    End If

I know it seems 'expensive' cos' the subFrom event seems to get called in a 3 or 4 to 1 ratio with the mainForm .... but I'm desperate now....

Thanks All

 

[vbaInet]

Eventually I noticed the problem you were experiencing when I was using a laptop at home.

See attached.

Nice going with your approach. You really did keep at it

 

[gray]

Brilliant! Sublime even !! ...

Far better than my approach! Quicker and less flicker (rhyme unintended!)... Really do appreciate the time you've spent on this... Think I wold have been there til' 3011 !!

For anyone else experiencing this issue, VBA1Net used a timer which, I guess, stops the subForm from an immediate re-fire....

Rather then trawl thru' my code here are the salient parts..keyField is called Unique_No (I use the more usual 'ID', for something else)

mainForm Current

Private Sub Form_Current()
    With Me.My_SubForm.Form
        If Me!Unique_No <> !Unique_No Then
            .OnCurrent = ""
            Call SyncAddress(.Form, Me!Unique_No)
            .OnCurrent = "[Event Procedure]"
        End If
    End With
End Sub

mainForm Timer

Private Sub Form_Timer()
    Call SyncAddress(Me, Me.My_SubForm.Form.Unique_No)
 
    Me.TimerInterval = 0
End Sub

subFom Current

Private Sub Form_Current()
   With Me.Parent
        If Me.Unique_No <> !Unique_No Then
            .OnCurrent = ""
            .TimerInterval = 100
            .OnCurrent = "[Event Procedure]"
        End If
    End With
End Sub

Sync proc - standard module

Public Sub SyncAddress(frm As Form, recID As Long)
    Dim rst As DAO.Recordset
    Set rst = frm.RecordsetClone
    rst.FindFirst "[Unique_No] = " & recID
    If Not rst.NoMatch Then
        frm.Bookmark = rst.Bookmark
    End If
    rst.Close
    Set rst = Nothing
End Sub

Thanks to SpikePL for looking at this too.... I know only too well how much time this takes...