It is very similar to what you are already doing. Before you move the main
form record, you need to capture a unique field value from the current
record, then after you move the main form record, go back to the subform
record. For example purposes I will use a numeric data type and the names
will be made up, so use your own:
Dim lngCurrRec As Long
'Save the value for the current record
lngCurrRec = Me.txtUniqueField
'Move the main form record
With Me.Parent.RecordsetClone
.FindFirst "[RecordPointer = " & Me.RecordPointer
If Not .NoMatch Then
Me.Parent.Bookmark = .Bookmark
End If
End With
'Go Back to the previous current subform record
With Me.RecordsetClone
.FirdFirst "[UniqueField] = " & lngCurrRec
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With
(look familiar <g>)
--
Dave Hargis, Microsoft Access MVP
mcnews said:
I don't understand why it is going to the first row of your subform. Is
there some other code that may be causing that? I tested a form of mine that
has a double click for one of the subform controls and I don't get that
behavior.
When does it actually jump to the first row in the subform? After you
double click the first time, or as soon as you enter the subform? It is
normal for the first row in a subform to be the current form when you change
records in the main form, but not just moving from the main to the sub form.
If it is happening after the double click, it may be necessary to include
code to make it return to the current row in the subform after the main form
record change.
--
Dave Hargis, Microsoft Access MVP
:
I think goofy was a very polite word for this design.
Okay, so you want to move the main form's current record to match the
selected row in the subform.
I would try using the Double Click event of one of the controls on the
subform. I often use that to open a detail form for a row on a subform.
Since your code is in the subform's module, you address the main form as
Parent.
With Me.Parent.RecordsetClone
.FindFirst "[RecordPointer = " & Me.RecordPointer
If Not .NoMatch Then
Me.Parent.Bookmark = .Bookmark
End If
End With
--
Dave Hargis, Microsoft Access MVP
:
Okay, then to get your syntax correct, it should me
Me!NameOfSubFormControl.Form.RecordsetClone
NameOfSubFormControl is the name of the subform control on tab 3, not the
name of the form being used as a subform.
But, you really don't need to do that and no code is required.
A subform control has two properties that will do it for you. They are the
Link Master Field(s) and Link Child Field(s) properties. You put the name of
the field in the main form's recordset that relates to the to subform's
recordset in the Link Master Field(s) property and the name of the field in
subform's recordset that relates to the main form's recordset. That way,
each time you move to a different record in the main form, the records in the
subform that relate to the current record in the main form will be the only
records displayed.
--
Dave Hargis, Microsoft Access MVP
:
Can't figure out from your code what it is you are really trying to do.
One problem you have is your object referencing is incorrect and may be
confusing Access:
Forms!aMain.Form.RecordsetClone
If you are trying to address the RecordsetClone of your subform using this,
and aMain is the name of a subform control on the current form, then it sould
be:
Me!aMain.Form.RecordsetClone.
Can you describe what you are trying to achive? There may be a better way.
--
Dave Hargis, Microsoft Access MVP
:
2 questions:
why does the code below work (sorta) for my Form_Click() method but
not for my Form_Current() method?
Dim rstData As DAO.Recordset
Set rstData = Forms!aMain.Form.RecordsetClone
rstData.FindFirst "RecordPointer = " & RecordPointer
Forms!aMain.Form.Bookmark = rstData.Bookmark
Set rstData = Nothing
the onclick is invoked when a row is clicked. on first click the
record pointer moves to the first record so i have to click it again.
the oncurrent is invoked when the record navigator moves to the next
record. i then get a 'Method 'Bookmark' of object '_Form_aMain'
failed.
any clues?
the subform is in Datasheet view.
i inherited this app so there is some goofy stuff that i won't have
time to fix.
the parent form is called aMain the has a bound table called
specimens. it contains a tab control with 4 tabs. the 3rd tab has a
subform in datasheet view that is also bound to the specimen table. i
know this isn't good, but i can't change it. users need to be able to
edit specimens, but only certain fields. so i use the control on the
3rd tab that are bound to the specimen table for editing. so i am
using an autonumber field from the specimen row on the subform to keep
the record on the main form in sync.
i am sure this doesn't make sense, but i have to make it work as is.
as i said, the main form and subform are bound to the same table.
screwed up, i know, but that's what i got. i know about link fields.
they are doing what they're supposed to.
that's pretty much what i have.
i tried your code it the same thing happens - it does what you expect
then focus goes to the first row of the subform. the main form has
the right record and my edit/save does what it should, but have to
click the right row a second time to keep the focus where it needs to
be. better than it was before i tried to fix at least.
it happens each time i click on a different row.
i haven't come up with any code that works to get it back on the right
row....[hint hint]
