Loss Of Focus After Refresh

[DomDom3]

Hi there,

I have a main form with 2 continuous sfrms. I can scroll through the records of one sfrm and the 2nd shows the details for each of those records.

A control in sfrm 2 has On Update code to save changes and refresh sfrm 1 (so a flag indicating a change is shown).

The problem is the refresh moves the focus to the first record of the continuous form, I would like the focus to remain at the record that triggered the refresh command.

Essentially I need to bookmark the record, so when refresh is done code can move focus to the bookmarked unique ID.

Can anyone help please?

Thanks

 

[rjoubert]

You could have a Global variable that stores the key for the current record. When the refresh is kicked off, store the current record's key value, then after the refresh, get the key value and load the appropriate record.

 

[MajP]

Private Sub Command10_Click()
Dim bk As String
bk = Me.Bookmark
Me.Requery
Me.Bookmark = bk
End Sub

replace me with the appropriate form/subform reference

 

[Zion7]

as either rjoubert or MajP said.
or, I found using
Me.recordset.Requery
will not affect the bookmark of the currenr records.

 

[TheAceMan1]

Wow! . . . someone tell me if I'm wrong! . . .

[blue][purple]Requerying a form invalidates any bookmarks set on records in the form.[/purple] However, clicking Refresh on the Records menu doesn't affect bookmarks.[/blue]

For the above reason I've always [blue]held the PK[/blue] of the record, and after requery [blue]set the current record to the new bookmark of that record as follows[/blue]:

[blue]   Dim rst As DAO.Recordset, hldPK As Integer
   
   hldPK = Me![purple][b]PrimaryKeyName[/b][/purple]
   Me.Requery
   Set rst = Me.RecordsetClone
   rst.FindFirst "[[purple][b]PrimaryKeyName[/b][/purple]] = " & hldPK
      
   If Not rst.NoMatch Then
      Me.Bookmark = rst.Bookmark
   End If

   Set rst = Nothing[/blue]

. . . or for shoter code:

[blue]   Dim hldPK As Integer
   
   hldPK = Me![purple][b]PrimaryKeyName[/b][/purple]
   Me.Requery
   Me.Recordset.FindFirst "[[purple][b]PrimaryKeyName[/b][/purple]] = " & hldPK[/blue]

Of course [blue]if the PK is a string[/blue] quotations have to be approriately placed.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

 

[Zion7]

Yo, AceMan, not sure if you're refering to my post or not but,
I found Me.Requery will loose the bookmark, whereas
Me.Recordset.Requery will not?
I've only tried it once, on a form with 3 subforms,
after editing the subs, and wanting the total to reflect right away, on the main.
and voila, it solved loosing my bookmark problem?
Have I corrected you?

 

[TheAceMan1]

How are ya Zion7 . . .

No . . . it was meant for [blue]MajP[/blue] (I'm sure its just an oversight . . . we all do it!)

I didn't mention the [blue]Recordset Requery Method[/blue] because you already covered that . . .

See Ya! . . . . . .

 

[Zion7]

Got Ya!

...personally, I would've been very surprised,
if I did, correct you (LOL)!

 

[MajP]

Oops. Does not happen too often, but I was wrong.

 

[TheAceMan1]

So what happed to [blue]DomDom3![/blue]

See Ya! . . . . . .

 

[DomDom3]

Sorry guys, I was unexpectedly out of the office for the last few days. I greatly appreciate your responses, I'm working through them now.

 

[DomDom3]

I'm convinced I've made this more complicated that it need be.

So far I've used this code but get a run-time 438 Object does not support this property on method. This appears against the last line.

Dim hldPK As Integer
hldPK = Me![Unique ID]
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
Forms!frminstallationoutlook.sfrmDataEntrySummaryView.Form.Requery
Forms!frminstallationoutlook.sfrmDataEntrySummaryView.Form.Recordset.FindFirst "[Unique ID] = " & hldPK

hldPK holds the correct bookmarked value, but [Unique ID] holds the ID of the first record in the recordset. The code will not match the 2.

Can you see why?

Thanks

 

[TheAceMan1]

DomDom3 . . .
[ol][li]Are the [blue]RecordSources[/blue] of subforms 1 & 2 based on the same table?[/li]
[li]How are you synchronizing sub2 to sub1 (I expect query criteria)?[/li]
[li]What version Access?[/li][/ol]

[blue]hldPK holds the correct bookmarked value, but [Unique ID] holds the ID of the first record in the recordset. The code will not match the 2.[/blue]

No! No! . . . I gave two methods, one via [blue]BookMark[/blue] the other via [blue]PrimaryKey[/blue]. Your using the [blue]PrimaryKey[/blue] method.

Is [blue][Unique ID][/blue] the primarykey of the underlying table for subform1?

See Ya! . . . . . .

 

[DomDom3]

AceMan thanks for sticking with me,

1. The record sources of subforms 1 & 2 are different.
2. Sfrm 2 is linked parent child with mainform.
sfrm1 is linked to mainform through code which aligns the 2 together. This code is triggered by "OnEnter" on the Unique ID in sfrm1.
3. Access 2002

I've replicated the similar form set up in a new form but without the code aligning the mainform and sfrm1 and the sub that Majp provided at the top of this thread works, so it must be something to do with my forms - and/or form aligning code.

 

[DomDom3]

Right...
I'm beginning to see some light...

The code is in OnUpdate in sfrm2
When I requery sfrm1 from sfrm2, the focus stays with sfrm2.

I want to requery sfrm1 which I can do, but then set the focus on sfrm1 which I can do, but the clincher is to then set the focus at the record with the same [Unique ID] as the record in sfrm where the code was run from.

So essentially my problem is after requerying and setting focus just getting to the bookmarked ID.
Can you help please (before I pull my hair out!!)

 

[TheAceMan1]

DomDom3 . . .

Tried to get to you earlier today but emergencies popped up at work . . . Anyway to continue . . .

I'm fighting with a few ambiguities here that prevents me from choosing the right path to follow. Consider the following:

I asked the question:

[blue]1. Are the RecordSources of subforms 1 & 2 [purple]based on the same table?[/purple][/blue]

. . . and you answered:

[blue]1. The record sources of subforms 1 & 2 [purple]are different[/purple].[/blue]

This [purple]doesn't tell me if the record sources are based on the same table or not[/purple] . . . just that their different. [blue]Resolution form you on this is important![/blue]

If the subforms are based on different tables, [blue]why do you find it necesary to requery sub1[/blue] (it shouldn't be necessary if tables are different)?

Now the major ambiguity is this . . . in your post origination you say:

[blue]I can scroll through the records of one sfrm and [purple]the 2nd shows the details for each of those records[/purple].[/blue]

[blue]This is indicitive of sub2 being synchronized with sub1![/blue] . . . Then in your next to last post (in answer to my questions) you say:

[blue][purple]2. Sfrm 2 is linked parent child with mainform[/purple].[/blue]

[blue]Can you see the ambiguity?[/blue]

See Ya! . . . . . .

 

[Zion7]

...maybe, just this may suffice?

DoCmd.DoMenuItem acFormBar, acRecordsMenu, _
5, , acMenuVer70
Me.Parent!sfrmDataEntrySummaryView.Form.Recordset.Requery
or

Me.Parent!sfrmDataEntrySummaryView.Form.recalc

 

[DomDom3]

Thanks very much for the responses, particularly TheAceMan1 for sticking with me. I think I was stuck in the problem so deeply that I couldn't see the wood from the trees.

I must have got to the eye of the storm this morning and had a moment of clarity and things fell into place.

As I began to suspect the problem was which subforms to reference when and how. The solution now seems oh so simple.

In case anybody should understand my ravings and come across the same problem here goes...

The code is AfterUpdate on a control in sfrm2...

'Save update
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
'Bookmark cuurent record
bk = Forms!Mainform!sfrm1.form.bookmark
'refresh summary form to show updates
Forms!Mainform!sfrm1.form.Refresh
'move summary from active record to bookmark
Forms!Mainform!sfrm1.form.Bookmark = bk
'Set Focus to bookmarked record
Forms!Forms!Mainform!sfrm1.SetFocus