Setting focus on subform row

[Gordon]

I have a main form (frmVocalArrangements) with 2 subforms. This main
form lists recording artists (fldVocalistID), subform1 the music
titles ((fldMusicID) for them and subform 2 the disc recordings on
which are found those titles. For a particular artist selected, I
then select a music title , then click a button to enter details of a
new disc recording in a second form (the first form remains open).
When I close that second form. I requery the the first form and return
to it with the original recording artist selected. That part all
works fine but I want it also to set the focus on the orignal music
title but this part fails.

Here is the code I am using on the close event of the second form:

intVocalistID = Me.fldVocalistID
intMusicID = Me!fldMusicID
'Requery the original "master" form
Forms!frmVocalArrangements.Requery

'Do Recordsetclone processing
Set frm = Forms.frmVocalArrangements
With frm.RecordsetClone
.FindFirst ("[fldVocalistID]=" & intVocalistID)

If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
intMusicID = Me.fldMusicID
Set frm = Forms.frmVocalArrangements.frmVocalArrangementsSubform1.Form
With frm.RecordsetClone
.FindFirst ("[fldMusicID]=" & intMusicID)
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
CloseForm Me

Can I use 2 bookmarks in this way or do I need another approach?

Thanks for any help

Gordon

 

[Jeanette Cunningham]

Hi Gordon,
one thing to try, is to move the code from the close event of the form to
the unload event of the form.
The close event is too late to do things with data such as getting a value
for intMusicID.

You may need to save the value for intMusicID in a module level variable so
you can use it in the unload event.
Try it and see.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Gordon]

Hi Gordon,
one thing to try, is to move the code from the close event of the form to
the unload event of the form.
The close event is too late to do things with data such as getting a value
for intMusicID.

You may need to save the value for intMusicID in a module level variable so
you can use it in the unload event.
Try it and see.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a main form (frmVocalArrangements) with 2 subforms.  This main
form lists recording artists (fldVocalistID), subform1 the music
titles ((fldMusicID) for them and subform 2 the disc recordings on
which are found those titles.  For a particular artist selected, I
then select a music title , then click a button to enter details of a
new disc recording in a second form (the first form remains open).
When I close that second form. I requery the the first form and return
to it with the original recording artist selected.  That part all
works fine but I want it also to set the focus on the orignal music
title but this part fails.

Here is the code I am using on the close event of the second form:

   intVocalistID = Me.fldVocalistID
   intMusicID = Me!fldMusicID
   'Requery the original "master" form
   Forms!frmVocalArrangements.Requery

   'Do Recordsetclone processing
   Set frm = Forms.frmVocalArrangements
   With frm.RecordsetClone
   .FindFirst ("[fldVocalistID]=" & intVocalistID)

If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
intMusicID = Me.fldMusicID
Set frm = Forms.frmVocalArrangements.frmVocalArrangementsSubform1.Form
With frm.RecordsetClone
.FindFirst ("[fldMusicID]=" & intMusicID)
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
 Set frm = Nothing
CloseForm Me

Can I use 2 bookmarks in this way or do I need another approach?

Thanks for any help

Gordon- Hide quoted text -

- Show quoted text -

Hi Jeanette,

Thanks for your post. My delay in responding does not indicate a lack
of interest - far from it. I have been busy trying to make the
proposed solution work. I understand the point about the close event
being too late to action anything. I tried using the unload event as
you suggested in a very simple 2 form example and it worked but trying
it in my real database just won't work. Something else in my code
must be interfering with it. I will however persevere even more and
if I can't crack it I will re-post.

Thanks again

Gordon

 

[Jeanette Cunningham]

Maybe worth trying to choose the subform set focus from the main form .
Put code on the main form that will recognise when you have added a new
record from that other form.
When the main form has moved to that new record, activate some code on the
main form that will set focus to the particular row of the subform.
So instead of

Set frm = Nothing

intMusicID = Me.fldMusicID
Set frm = Forms.frmVocalArrangements.frmVocalArrangementsSubform1.Form
With frm.RecordsetClone
.FindFirst ("[fldMusicID]=" & intMusicID)
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
CloseForm Me

code the main form to do the work of moving to the matching record on the
subform.
You would need to save the value of intMusicID somewhere on the main form,
then use something like the untested air code below

Set frm = Me.frmVocalArrangementsSubform1.Form
With frm.RecordsetClone
.FindFirst ("[fldMusicID]=" & intMusicID)
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing

Also, I'm not sure if the subform is linked to the main form using link
master and child fields or not - this could make a difference to the code
you need.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Hi Gordon,
one thing to try, is to move the code from the close event of the form to
the unload event of the form.
The close event is too late to do things with data such as getting a value
for intMusicID.

You may need to save the value for intMusicID in a module level variable
so
you can use it in the unload event.
Try it and see.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

I have a main form (frmVocalArrangements) with 2 subforms. This main
form lists recording artists (fldVocalistID), subform1 the music
titles ((fldMusicID) for them and subform 2 the disc recordings on
which are found those titles. For a particular artist selected, I
then select a music title , then click a button to enter details of a
new disc recording in a second form (the first form remains open).
When I close that second form. I requery the the first form and return
to it with the original recording artist selected. That part all
works fine but I want it also to set the focus on the orignal music
title but this part fails.

Here is the code I am using on the close event of the second form:

intVocalistID = Me.fldVocalistID
intMusicID = Me!fldMusicID
'Requery the original "master" form
Forms!frmVocalArrangements.Requery

'Do Recordsetclone processing
Set frm = Forms.frmVocalArrangements
With frm.RecordsetClone
.FindFirst ("[fldVocalistID]=" & intVocalistID)

If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
intMusicID = Me.fldMusicID
Set frm = Forms.frmVocalArrangements.frmVocalArrangementsSubform1.Form
With frm.RecordsetClone
.FindFirst ("[fldMusicID]=" & intMusicID)
If Not .NoMatch Then frm.Bookmark = .Bookmark
End With
Set frm = Nothing
CloseForm Me

Can I use 2 bookmarks in this way or do I need another approach?

Thanks for any help

Gordon- Hide quoted text -

- Show quoted text -

Hi Jeanette,

Thanks for your post. My delay in responding does not indicate a lack
of interest - far from it. I have been busy trying to make the
proposed solution work. I understand the point about the close event
being too late to action anything. I tried using the unload event as
you suggested in a very simple 2 form example and it worked but trying
it in my real database just won't work. Something else in my code
must be interfering with it. I will however persevere even more and
if I can't crack it I will re-post.

Thanks again

Gordon