My question pertains to the part of my Access 2003 MDB application, where I
have a main form, plus a subform within it (Subform1), and another subform
(Subform2) which is in Subform1.

Subform2 is a continuous form. When the user selects a record in Subform2
(by clicking on the record selector), I want to have Subform1 switch to the
same record # as Subform2 was just moved to. (Subform1 is my detailed item
entry form, and Subform2 is an abbreviated listing of all the items entered.)

I attempted to do this by writing some simple code in the "On Current" event
in Subform2 to capture the record # into a variable, then use the GoToRecord
to move Subform1 active record to the same number. After reading the help
files and posts here, I've found that when using GoToRecord you can't
reference any other subform than the one you are in. (at least you can't
expect it to work) It does not seem that there is a conceivable way to make
the "GoToRecord" function work for what I need.

Am I correct about that, as it applies to my situation?

Are there any suggestions on ways to accomplish this?

Thanks in advance.

Assuming you have a primary key in the table that these subforms come from,
you could use the primary key value of the selected record to go to the same
record in the other subform.

The code would consist of something like this:

Dim frm As Form
If Not Me.NewRecord Then
Set frm = Me.Parent.[NameOfYourOtherSubformControlHere].Form
With frm.RecordsetClone
.FindFirst "[ID] = " & Me.ID
If Not .NoMatch Then
frm.Bookmark = .Bookmark
End If
End With
End If
Set frm = Nothing

This assumes that neither subform is dirty at the time, neither is filtered,
and it is okay not to move the other form if you are at a new record. After
it finds the record, Access is likely to scroll the other subform so that
the found record is the first one shown in the subform.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan Wieland" <Dan (E-Mail Removed)> wrote in message
news:E893BA31-9565-47A9-A4DD-(E-Mail Removed)...
> My question pertains to the part of my Access 2003 MDB application, where
> I
> have a main form, plus a subform within it (Subform1), and another subform
> (Subform2) which is in Subform1.
>
> Subform2 is a continuous form. When the user selects a record in Subform2
> (by clicking on the record selector), I want to have Subform1 switch to
> the
> same record # as Subform2 was just moved to. (Subform1 is my detailed
> item
> entry form, and Subform2 is an abbreviated listing of all the items
> entered.)
>
> I attempted to do this by writing some simple code in the "On Current"
> event
> in Subform2 to capture the record # into a variable, then use the
> GoToRecord
> to move Subform1 active record to the same number. After reading the help
> files and posts here, I've found that when using GoToRecord you can't
> reference any other subform than the one you are in. (at least you can't
> expect it to work) It does not seem that there is a conceivable way to
> make
> the "GoToRecord" function work for what I need.
>
> Am I correct about that, as it applies to my situation?
>
> Are there any suggestions on ways to accomplish this?
>
> Thanks in advance.

Thank you for your time and feedback. I have been studying your suggestion,
and learning about these methods and properties, but still can't seem to get
your code suggestion to work. I want to figure this out, but need a little
more education. It gets hung up on the line:
frm.Bookmark = .Bookmark
It gives me a runtime error 2465 and says it can't find field "|".

A little more background for you: fsubInvoiceItemsListing is where this
code resides. It's in the OnCurrent event. fsubInvoiceItemsListing is
embedded in fsubInvoiceItem. fsubInvoiceItem is embedded in frmInvoiceMain.

fsubInvoiceItemsListing is based on a query, and contains a field called
InvoiceItemNumber. fsubInvoiceItem is based on a table, and also contains
the same field InvoiceItemNumber. That field is the primary key for that
table. None of the forms are filtered.

What I'm trying to accomplish is this: When a record in
fsubInvoiceItemsListing is made current, I want fsubInvoiceItem to move to
that same record automatically.

Here's my code as of now:
Dim frm As Form
If Not Me.NewRecord Then
Set frm = Me.Parent.Form
With frm.RecordsetClone
.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
If Not .NoMatch Then
frm.Bookmark = .Bookmark
End If
End With
End If
Set frm = Nothing

I guess this really comes down to getting a better understanding of how the
"Bookmark", "RecordSetClone", and "Parent" properties work and how to use
them. The help files are not cutting it for me. Any online sites you might
recommend for more details on using properties like these?

Thanks!

"Allen Browne" wrote:

> Assuming you have a primary key in the table that these subforms come from,
> you could use the primary key value of the selected record to go to the same
> record in the other subform.
>
> The code would consist of something like this:
>
> Dim frm As Form
> If Not Me.NewRecord Then
> Set frm = Me.Parent.[NameOfYourOtherSubformControlHere].Form
> With frm.RecordsetClone
> .FindFirst "[ID] = " & Me.ID
> If Not .NoMatch Then
> frm.Bookmark = .Bookmark
> End If
> End With
> End If
> Set frm = Nothing
>
> This assumes that neither subform is dirty at the time, neither is filtered,
> and it is okay not to move the other form if you are at a new record. After
> it finds the record, Access is likely to scroll the other subform so that
> the found record is the first one shown in the subform.
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dan Wieland" <Dan (E-Mail Removed)> wrote in message
> news:E893BA31-9565-47A9-A4DD-(E-Mail Removed)...
> > My question pertains to the part of my Access 2003 MDB application, where
> > I
> > have a main form, plus a subform within it (Subform1), and another subform
> > (Subform2) which is in Subform1.
> >
> > Subform2 is a continuous form. When the user selects a record in Subform2
> > (by clicking on the record selector), I want to have Subform1 switch to
> > the
> > same record # as Subform2 was just moved to. (Subform1 is my detailed
> > item
> > entry form, and Subform2 is an abbreviated listing of all the items
> > entered.)
> >
> > I attempted to do this by writing some simple code in the "On Current"
> > event
> > in Subform2 to capture the record # into a variable, then use the
> > GoToRecord
> > to move Subform1 active record to the same number. After reading the help
> > files and posts here, I've found that when using GoToRecord you can't
> > reference any other subform than the one you are in. (at least you can't
> > expect it to work) It does not seem that there is a conceivable way to
> > make
> > the "GoToRecord" function work for what I need.
> >
> > Am I correct about that, as it applies to my situation?
> >
> > Are there any suggestions on ways to accomplish this?
> >
> > Thanks in advance.
>
>
>

Forms have a RecordsetClone, which can have a different "current" record
than the one in the form, so you can search it, check if there is a match,
and move to the found record. Setting the form's Bookmark to that of the
found recordset in the clone set makes that record current in the form.

If Access does not understand the Bookmark property of the recordset, it may
have the wrong kind of recordset. In Access 2000, Microsoft made the ADO
recordset the default instead of the DAO one. Fortunately, DAO is back again
in Access 2003.

Try being explicit about the kind of recordset you want:
Dim frm As Form
Dim rs As DAO.RecordsetClone
If Not Me.NewRecord Then
Set frm = Me.Parent.Form
Set rs = frm.RecordsetClone
rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
If Not rs.NoMatch Then
frm.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
Set frm = Nothing

If the 2nd line gives you an error, and you are using A2000 or 2002, see:
http://allenbrowne.com/ser-38.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan Wieland" <(E-Mail Removed)> wrote in message
news:9126EAC4-06E4-45FF-91A8-(E-Mail Removed)...
> Thank you for your time and feedback. I have been studying your
> suggestion,
> and learning about these methods and properties, but still can't seem to
> get
> your code suggestion to work. I want to figure this out, but need a
> little
> more education. It gets hung up on the line:
> frm.Bookmark = .Bookmark
> It gives me a runtime error 2465 and says it can't find field "|".
>
> A little more background for you: fsubInvoiceItemsListing is where this
> code resides. It's in the OnCurrent event. fsubInvoiceItemsListing is
> embedded in fsubInvoiceItem. fsubInvoiceItem is embedded in
> frmInvoiceMain.
>
> fsubInvoiceItemsListing is based on a query, and contains a field called
> InvoiceItemNumber. fsubInvoiceItem is based on a table, and also contains
> the same field InvoiceItemNumber. That field is the primary key for that
> table. None of the forms are filtered.
>
> What I'm trying to accomplish is this: When a record in
> fsubInvoiceItemsListing is made current, I want fsubInvoiceItem to move to
> that same record automatically.
>
> Here's my code as of now:
> Dim frm As Form
> If Not Me.NewRecord Then
> Set frm = Me.Parent.Form
> With frm.RecordsetClone
> .FindFirst "InvoiceItemAutoNumber = " &
> Me.InvoiceItemAutoNumber
> If Not .NoMatch Then
> frm.Bookmark = .Bookmark
> End If
> End With
> End If
> Set frm = Nothing
>
> I guess this really comes down to getting a better understanding of how
> the
> "Bookmark", "RecordSetClone", and "Parent" properties work and how to use
> them. The help files are not cutting it for me. Any online sites you
> might
> recommend for more details on using properties like these?
>
> Thanks!
>
> "Allen Browne" wrote:
>
>> Assuming you have a primary key in the table that these subforms come
>> from,
>> you could use the primary key value of the selected record to go to the
>> same
>> record in the other subform.
>>
>> The code would consist of something like this:
>>
>> Dim frm As Form
>> If Not Me.NewRecord Then
>> Set frm = Me.Parent.[NameOfYourOtherSubformControlHere].Form
>> With frm.RecordsetClone
>> .FindFirst "[ID] = " & Me.ID
>> If Not .NoMatch Then
>> frm.Bookmark = .Bookmark
>> End If
>> End With
>> End If
>> Set frm = Nothing
>>
>> This assumes that neither subform is dirty at the time, neither is
>> filtered,
>> and it is okay not to move the other form if you are at a new record.
>> After
>> it finds the record, Access is likely to scroll the other subform so that
>> the found record is the first one shown in the subform.
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Dan Wieland" <Dan (E-Mail Removed)> wrote in message
>> news:E893BA31-9565-47A9-A4DD-(E-Mail Removed)...
>> > My question pertains to the part of my Access 2003 MDB application,
>> > where
>> > I
>> > have a main form, plus a subform within it (Subform1), and another
>> > subform
>> > (Subform2) which is in Subform1.
>> >
>> > Subform2 is a continuous form. When the user selects a record in
>> > Subform2
>> > (by clicking on the record selector), I want to have Subform1 switch to
>> > the
>> > same record # as Subform2 was just moved to. (Subform1 is my detailed
>> > item
>> > entry form, and Subform2 is an abbreviated listing of all the items
>> > entered.)
>> >
>> > I attempted to do this by writing some simple code in the "On Current"
>> > event
>> > in Subform2 to capture the record # into a variable, then use the
>> > GoToRecord
>> > to move Subform1 active record to the same number. After reading the
>> > help
>> > files and posts here, I've found that when using GoToRecord you can't
>> > reference any other subform than the one you are in. (at least you
>> > can't
>> > expect it to work) It does not seem that there is a conceivable way
>> > to
>> > make
>> > the "GoToRecord" function work for what I need.
>> >
>> > Am I correct about that, as it applies to my situation?
>> >
>> > Are there any suggestions on ways to accomplish this?
>> >
>> > Thanks in advance.

Thanks for the additional explanations. I think I understand the
RecordsetClone concept, and how it can be useful. Thanks!

I'm using A2003, and as far as I can determine, DAO. In the code window,
Tools-> References, I have the following checked, in this order:
- VBA
- Microsoft Access 11.0 for Object Library
- OLE Automation <--- this one wasn't listed in your table on your nice web
page.
- Microsoft DAO 3.6 Object Lib
- Microsoft ADO 2.5 Lib

As you guessed, the compiler didn't like the 2nd line:
> Dim rs As DAO.RecordsetClone
It gave me an error - "User defined type not defined"

If I changed it to "Dim rs As DAO.Recordset" it would compile fine.
However, then I'm back to the same run-time error '2465', can't find the
field "|" referenced in my expression. The debugger halts at this line
again:
frm.Bookmark = rs.Bookmark

Thanks for the explicit version. That helped me understand a little more of
the mechanics on these methods. Unfortunately still stuck. I tried a test
to get even more explicit as follows:

Dim frm As Form
Dim rs As DAO.Recordset
If Not Me.NewRecord Then
'Set frm = Me.Parent.Form
Set rs = Forms!frmInvoiceMain.fsubInvoiceItem.Form.RecordsetClone
rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
If Not rs.NoMatch Then
Forms!frmInvoiceMain.fsubInvoiceItem.Form.Bookmark = rs.Bookmark
End If
End If
Set rs = Nothing
Set frm = Nothing

Still same run-time error 2465. Perhaps it doesn't like the "= rs.Bookmark"
portion of that line? Any more suggestions for me? Thank you for your time
and help.

"Allen Browne" wrote:

> Forms have a RecordsetClone, which can have a different "current" record
> than the one in the form, so you can search it, check if there is a match,
> and move to the found record. Setting the form's Bookmark to that of the
> found recordset in the clone set makes that record current in the form.
>
> If Access does not understand the Bookmark property of the recordset, it may
> have the wrong kind of recordset. In Access 2000, Microsoft made the ADO
> recordset the default instead of the DAO one. Fortunately, DAO is back again
> in Access 2003.
>
> Try being explicit about the kind of recordset you want:
> Dim frm As Form
> Dim rs As DAO.RecordsetClone
> If Not Me.NewRecord Then
> Set frm = Me.Parent.Form
> Set rs = frm.RecordsetClone
> rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
> If Not rs.NoMatch Then
> frm.Bookmark = rs.Bookmark
> End If
> End If
> Set rs = Nothing
> Set frm = Nothing
>
> If the 2nd line gives you an error, and you are using A2000 or 2002, see:
> http://allenbrowne.com/ser-38.html
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dan Wieland" <(E-Mail Removed)> wrote in message
> news:9126EAC4-06E4-45FF-91A8-(E-Mail Removed)...
> > Thank you for your time and feedback. I have been studying your
> > suggestion,
> > and learning about these methods and properties, but still can't seem to
> > get
> > your code suggestion to work. I want to figure this out, but need a
> > little
> > more education. It gets hung up on the line:
> > frm.Bookmark = .Bookmark
> > It gives me a runtime error 2465 and says it can't find field "|".
> >
> > A little more background for you: fsubInvoiceItemsListing is where this
> > code resides. It's in the OnCurrent event. fsubInvoiceItemsListing is
> > embedded in fsubInvoiceItem. fsubInvoiceItem is embedded in
> > frmInvoiceMain.
> >
> > fsubInvoiceItemsListing is based on a query, and contains a field called
> > InvoiceItemNumber. fsubInvoiceItem is based on a table, and also contains
> > the same field InvoiceItemNumber. That field is the primary key for that
> > table. None of the forms are filtered.
> >
> > What I'm trying to accomplish is this: When a record in
> > fsubInvoiceItemsListing is made current, I want fsubInvoiceItem to move to
> > that same record automatically.
> >
> > Here's my code as of now:
> > Dim frm As Form
> > If Not Me.NewRecord Then
> > Set frm = Me.Parent.Form
> > With frm.RecordsetClone
> > .FindFirst "InvoiceItemAutoNumber = " &
> > Me.InvoiceItemAutoNumber
> > If Not .NoMatch Then
> > frm.Bookmark = .Bookmark
> > End If
> > End With
> > End If
> > Set frm = Nothing
> >
> > I guess this really comes down to getting a better understanding of how
> > the
> > "Bookmark", "RecordSetClone", and "Parent" properties work and how to use
> > them. The help files are not cutting it for me. Any online sites you
> > might
> > recommend for more details on using properties like these?
> >
> > Thanks!
> >
> > "Allen Browne" wrote:
> >
> >> Assuming you have a primary key in the table that these subforms come
> >> from,
> >> you could use the primary key value of the selected record to go to the
> >> same
> >> record in the other subform.
> >>
> >> The code would consist of something like this:
> >>
> >> Dim frm As Form
> >> If Not Me.NewRecord Then
> >> Set frm = Me.Parent.[NameOfYourOtherSubformControlHere].Form
> >> With frm.RecordsetClone
> >> .FindFirst "[ID] = " & Me.ID
> >> If Not .NoMatch Then
> >> frm.Bookmark = .Bookmark
> >> End If
> >> End With
> >> End If
> >> Set frm = Nothing
> >>
> >> This assumes that neither subform is dirty at the time, neither is
> >> filtered,
> >> and it is okay not to move the other form if you are at a new record.
> >> After
> >> it finds the record, Access is likely to scroll the other subform so that
> >> the found record is the first one shown in the subform.
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Dan Wieland" <Dan (E-Mail Removed)> wrote in message
> >> news:E893BA31-9565-47A9-A4DD-(E-Mail Removed)...
> >> > My question pertains to the part of my Access 2003 MDB application,
> >> > where
> >> > I
> >> > have a main form, plus a subform within it (Subform1), and another
> >> > subform
> >> > (Subform2) which is in Subform1.
> >> >
> >> > Subform2 is a continuous form. When the user selects a record in
> >> > Subform2
> >> > (by clicking on the record selector), I want to have Subform1 switch to
> >> > the
> >> > same record # as Subform2 was just moved to. (Subform1 is my detailed
> >> > item
> >> > entry form, and Subform2 is an abbreviated listing of all the items
> >> > entered.)
> >> >
> >> > I attempted to do this by writing some simple code in the "On Current"
> >> > event
> >> > in Subform2 to capture the record # into a variable, then use the
> >> > GoToRecord
> >> > to move Subform1 active record to the same number. After reading the
> >> > help
> >> > files and posts here, I've found that when using GoToRecord you can't
> >> > reference any other subform than the one you are in. (at least you
> >> > can't
> >> > expect it to work) It does not seem that there is a conceivable way
> >> > to
> >> > make
> >> > the "GoToRecord" function work for what I need.
> >> >
> >> > Am I correct about that, as it applies to my situation?
> >> >
> >> > Are there any suggestions on ways to accomplish this?
> >> >
> >> > Thanks in advance.
>
>
>

The error suggests that Access cannot find the field named
InvoiceItemAutoNumber.

If you open the form in design view, and open the Field List (View menu), is
there a field with that name listed there?

If it is there, make sure that the Name AutoCorrect boxes are unchecked
under:
Tools | Options | General
and then compact. Explanation:
http://allenbrowne.com/bug-03.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan Wieland" <(E-Mail Removed)> wrote in message
news:C33B5048-AA13-4184-8A24-(E-Mail Removed)...
> Thanks for the additional explanations. I think I understand the
> RecordsetClone concept, and how it can be useful. Thanks!
>
> I'm using A2003, and as far as I can determine, DAO. In the code window,
> Tools-> References, I have the following checked, in this order:
> - VBA
> - Microsoft Access 11.0 for Object Library
> - OLE Automation <--- this one wasn't listed in your table on your nice
> web
> page.
> - Microsoft DAO 3.6 Object Lib
> - Microsoft ADO 2.5 Lib
>
> As you guessed, the compiler didn't like the 2nd line:
>> Dim rs As DAO.RecordsetClone
> It gave me an error - "User defined type not defined"
>
> If I changed it to "Dim rs As DAO.Recordset" it would compile fine.
> However, then I'm back to the same run-time error '2465', can't find the
> field "|" referenced in my expression. The debugger halts at this line
> again:
> frm.Bookmark = rs.Bookmark
>
> Thanks for the explicit version. That helped me understand a little more
> of
> the mechanics on these methods. Unfortunately still stuck. I tried a
> test
> to get even more explicit as follows:
>
> Dim frm As Form
> Dim rs As DAO.Recordset
> If Not Me.NewRecord Then
> 'Set frm = Me.Parent.Form
> Set rs = Forms!frmInvoiceMain.fsubInvoiceItem.Form.RecordsetClone
> rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
> If Not rs.NoMatch Then
> Forms!frmInvoiceMain.fsubInvoiceItem.Form.Bookmark = rs.Bookmark
> End If
> End If
> Set rs = Nothing
> Set frm = Nothing
>
> Still same run-time error 2465. Perhaps it doesn't like the "=
> rs.Bookmark"
> portion of that line? Any more suggestions for me? Thank you for your
> time
> and help.
>
> "Allen Browne" wrote:
>
>> Forms have a RecordsetClone, which can have a different "current" record
>> than the one in the form, so you can search it, check if there is a
>> match,
>> and move to the found record. Setting the form's Bookmark to that of the
>> found recordset in the clone set makes that record current in the form.
>>
>> If Access does not understand the Bookmark property of the recordset, it
>> may
>> have the wrong kind of recordset. In Access 2000, Microsoft made the ADO
>> recordset the default instead of the DAO one. Fortunately, DAO is back
>> again
>> in Access 2003.
>>
>> Try being explicit about the kind of recordset you want:
>> Dim frm As Form
>> Dim rs As DAO.RecordsetClone
>> If Not Me.NewRecord Then
>> Set frm = Me.Parent.Form
>> Set rs = frm.RecordsetClone
>> rs.FindFirst "InvoiceItemAutoNumber = " &
>> Me.InvoiceItemAutoNumber
>> If Not rs.NoMatch Then
>> frm.Bookmark = rs.Bookmark
>> End If
>> End If
>> Set rs = Nothing
>> Set frm = Nothing
>>
>> If the 2nd line gives you an error, and you are using A2000 or 2002, see:
>> http://allenbrowne.com/ser-38.html
>>
>> --
>> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> Tips for Access users - http://allenbrowne.com/tips.html
>> Reply to group, rather than allenbrowne at mvps dot org.
>>
>> "Dan Wieland" <(E-Mail Removed)> wrote in message
>> news:9126EAC4-06E4-45FF-91A8-(E-Mail Removed)...
>> > Thank you for your time and feedback. I have been studying your
>> > suggestion,
>> > and learning about these methods and properties, but still can't seem
>> > to
>> > get
>> > your code suggestion to work. I want to figure this out, but need a
>> > little
>> > more education. It gets hung up on the line:
>> > frm.Bookmark = .Bookmark
>> > It gives me a runtime error 2465 and says it can't find field "|".
>> >
>> > A little more background for you: fsubInvoiceItemsListing is where
>> > this
>> > code resides. It's in the OnCurrent event. fsubInvoiceItemsListing is
>> > embedded in fsubInvoiceItem. fsubInvoiceItem is embedded in
>> > frmInvoiceMain.
>> >
>> > fsubInvoiceItemsListing is based on a query, and contains a field
>> > called
>> > InvoiceItemNumber. fsubInvoiceItem is based on a table, and also
>> > contains
>> > the same field InvoiceItemNumber. That field is the primary key for
>> > that
>> > table. None of the forms are filtered.
>> >
>> > What I'm trying to accomplish is this: When a record in
>> > fsubInvoiceItemsListing is made current, I want fsubInvoiceItem to move
>> > to
>> > that same record automatically.
>> >
>> > Here's my code as of now:
>> > Dim frm As Form
>> > If Not Me.NewRecord Then
>> > Set frm = Me.Parent.Form
>> > With frm.RecordsetClone
>> > .FindFirst "InvoiceItemAutoNumber = " &
>> > Me.InvoiceItemAutoNumber
>> > If Not .NoMatch Then
>> > frm.Bookmark = .Bookmark
>> > End If
>> > End With
>> > End If
>> > Set frm = Nothing
>> >
>> > I guess this really comes down to getting a better understanding of how
>> > the
>> > "Bookmark", "RecordSetClone", and "Parent" properties work and how to
>> > use
>> > them. The help files are not cutting it for me. Any online sites you
>> > might
>> > recommend for more details on using properties like these?
>> >
>> > Thanks!
>> >
>> > "Allen Browne" wrote:
>> >
>> >> Assuming you have a primary key in the table that these subforms come
>> >> from,
>> >> you could use the primary key value of the selected record to go to
>> >> the
>> >> same
>> >> record in the other subform.
>> >>
>> >> The code would consist of something like this:
>> >>
>> >> Dim frm As Form
>> >> If Not Me.NewRecord Then
>> >> Set frm = Me.Parent.[NameOfYourOtherSubformControlHere].Form
>> >> With frm.RecordsetClone
>> >> .FindFirst "[ID] = " & Me.ID
>> >> If Not .NoMatch Then
>> >> frm.Bookmark = .Bookmark
>> >> End If
>> >> End With
>> >> End If
>> >> Set frm = Nothing
>> >>
>> >> This assumes that neither subform is dirty at the time, neither is
>> >> filtered,
>> >> and it is okay not to move the other form if you are at a new record.
>> >> After
>> >> it finds the record, Access is likely to scroll the other subform so
>> >> that
>> >> the found record is the first one shown in the subform.
>> >>
>> >> "Dan Wieland" <Dan (E-Mail Removed)> wrote in message
>> >> news:E893BA31-9565-47A9-A4DD-(E-Mail Removed)...
>> >> > My question pertains to the part of my Access 2003 MDB application,
>> >> > where
>> >> > I
>> >> > have a main form, plus a subform within it (Subform1), and another
>> >> > subform
>> >> > (Subform2) which is in Subform1.
>> >> >
>> >> > Subform2 is a continuous form. When the user selects a record in
>> >> > Subform2
>> >> > (by clicking on the record selector), I want to have Subform1 switch
>> >> > to
>> >> > the
>> >> > same record # as Subform2 was just moved to. (Subform1 is my
>> >> > detailed
>> >> > item
>> >> > entry form, and Subform2 is an abbreviated listing of all the items
>> >> > entered.)
>> >> >
>> >> > I attempted to do this by writing some simple code in the "On
>> >> > Current"
>> >> > event
>> >> > in Subform2 to capture the record # into a variable, then use the
>> >> > GoToRecord
>> >> > to move Subform1 active record to the same number. After reading
>> >> > the
>> >> > help
>> >> > files and posts here, I've found that when using GoToRecord you
>> >> > can't
>> >> > reference any other subform than the one you are in. (at least you
>> >> > can't
>> >> > expect it to work) It does not seem that there is a conceivable
>> >> > way
>> >> > to
>> >> > make
>> >> > the "GoToRecord" function work for what I need.
>> >> >
>> >> > Am I correct about that, as it applies to my situation?
>> >> >
>> >> > Are there any suggestions on ways to accomplish this?
>> >> >
>> >> > Thanks in advance.

It does seem like there is a problem with that field name, but I can't seem
to find it.
I added some debug.print statements to help see where the code gets, and
commented out the line where it always chokes.

Dim frm As Form
Dim rs As DAO.Recordset
If Not Me.NewRecord Then
Set frm = Me.Parent.Form
Set rs = frm.RecordsetClone
Debug.Print "findfirst"
rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
If Not rs.NoMatch Then
'Forms!frmInvoiceMain.fsubInvoiceItem.Form.Bookmark = rs.Bookmark
Debug.Print "hello world"
End If
End If
Set rs = Nothing
Set frm = Nothing

When I open the form, in the immediate window appears:
findfirst
hello world <-- it must have found a match at least once, right??
findfirst <-- why is it doing it again? Because there are mutliple
embedded frms?

Then it stops on the following line stating that the operation was cancelled
by the user, run-time error 3059:
rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber

If I hover over the "Me.InvoiceItemAutoNumber" it shows the correct value
for that field. I've tried changing "rs.FindFirst "InvoiceItemAutoNumber =
"... to a different field on this form, and the run-time error does not
occur. Like you said, there seems to be a problem with this
InvoiceItemAutoNumber field. Does it have to be indexed? Does it matter
that this form is based on a query?

I also tried your instructions regarding the Name Auto-Correct options.
(Mine were originally checked.) This did not seem to have any effect after I
unchecked and compacted. I also don't remember renaming this field, but
can't rule that out completely.

"Allen Browne" wrote:

> The error suggests that Access cannot find the field named
> InvoiceItemAutoNumber.
>
> If you open the form in design view, and open the Field List (View menu), is
> there a field with that name listed there?
>
> If it is there, make sure that the Name AutoCorrect boxes are unchecked
> under:
> Tools | Options | General
> and then compact. Explanation:
> http://allenbrowne.com/bug-03.html
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dan Wieland" <(E-Mail Removed)> wrote in message
> news:C33B5048-AA13-4184-8A24-(E-Mail Removed)...
> > Thanks for the additional explanations. I think I understand the
> > RecordsetClone concept, and how it can be useful. Thanks!
> >
> > I'm using A2003, and as far as I can determine, DAO. In the code window,
> > Tools-> References, I have the following checked, in this order:
> > - VBA
> > - Microsoft Access 11.0 for Object Library
> > - OLE Automation <--- this one wasn't listed in your table on your nice
> > web
> > page.
> > - Microsoft DAO 3.6 Object Lib
> > - Microsoft ADO 2.5 Lib
> >
> > As you guessed, the compiler didn't like the 2nd line:
> >> Dim rs As DAO.RecordsetClone
> > It gave me an error - "User defined type not defined"
> >
> > If I changed it to "Dim rs As DAO.Recordset" it would compile fine.
> > However, then I'm back to the same run-time error '2465', can't find the
> > field "|" referenced in my expression. The debugger halts at this line
> > again:
> > frm.Bookmark = rs.Bookmark
> >
> > Thanks for the explicit version. That helped me understand a little more
> > of
> > the mechanics on these methods. Unfortunately still stuck. I tried a
> > test
> > to get even more explicit as follows:
> >
> > Dim frm As Form
> > Dim rs As DAO.Recordset
> > If Not Me.NewRecord Then
> > 'Set frm = Me.Parent.Form
> > Set rs = Forms!frmInvoiceMain.fsubInvoiceItem.Form.RecordsetClone
> > rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
> > If Not rs.NoMatch Then
> > Forms!frmInvoiceMain.fsubInvoiceItem.Form.Bookmark = rs.Bookmark
> > End If
> > End If
> > Set rs = Nothing
> > Set frm = Nothing
> >
> > Still same run-time error 2465. Perhaps it doesn't like the "=
> > rs.Bookmark"
> > portion of that line? Any more suggestions for me? Thank you for your
> > time
> > and help.
> >
> > "Allen Browne" wrote:
> >
> >> Forms have a RecordsetClone, which can have a different "current" record
> >> than the one in the form, so you can search it, check if there is a
> >> match,
> >> and move to the found record. Setting the form's Bookmark to that of the
> >> found recordset in the clone set makes that record current in the form.
> >>
> >> If Access does not understand the Bookmark property of the recordset, it
> >> may
> >> have the wrong kind of recordset. In Access 2000, Microsoft made the ADO
> >> recordset the default instead of the DAO one. Fortunately, DAO is back
> >> again
> >> in Access 2003.
> >>
> >> Try being explicit about the kind of recordset you want:
> >> Dim frm As Form
> >> Dim rs As DAO.RecordsetClone
> >> If Not Me.NewRecord Then
> >> Set frm = Me.Parent.Form
> >> Set rs = frm.RecordsetClone
> >> rs.FindFirst "InvoiceItemAutoNumber = " &
> >> Me.InvoiceItemAutoNumber
> >> If Not rs.NoMatch Then
> >> frm.Bookmark = rs.Bookmark
> >> End If
> >> End If
> >> Set rs = Nothing
> >> Set frm = Nothing
> >>
> >> If the 2nd line gives you an error, and you are using A2000 or 2002, see:
> >> http://allenbrowne.com/ser-38.html
> >>
> >> --
> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> Reply to group, rather than allenbrowne at mvps dot org.
> >>
> >> "Dan Wieland" <(E-Mail Removed)> wrote in message
> >> news:9126EAC4-06E4-45FF-91A8-(E-Mail Removed)...
> >> > Thank you for your time and feedback. I have been studying your
> >> > suggestion,
> >> > and learning about these methods and properties, but still can't seem
> >> > to
> >> > get
> >> > your code suggestion to work. I want to figure this out, but need a
> >> > little
> >> > more education. It gets hung up on the line:
> >> > frm.Bookmark = .Bookmark
> >> > It gives me a runtime error 2465 and says it can't find field "|".
> >> >
> >> > A little more background for you: fsubInvoiceItemsListing is where
> >> > this
> >> > code resides. It's in the OnCurrent event. fsubInvoiceItemsListing is
> >> > embedded in fsubInvoiceItem. fsubInvoiceItem is embedded in
> >> > frmInvoiceMain.
> >> >
> >> > fsubInvoiceItemsListing is based on a query, and contains a field
> >> > called
> >> > InvoiceItemNumber. fsubInvoiceItem is based on a table, and also
> >> > contains
> >> > the same field InvoiceItemNumber. That field is the primary key for
> >> > that
> >> > table. None of the forms are filtered.
> >> >
> >> > What I'm trying to accomplish is this: When a record in
> >> > fsubInvoiceItemsListing is made current, I want fsubInvoiceItem to move
> >> > to
> >> > that same record automatically.
> >> >
> >> > Here's my code as of now:
> >> > Dim frm As Form
> >> > If Not Me.NewRecord Then
> >> > Set frm = Me.Parent.Form
> >> > With frm.RecordsetClone
> >> > .FindFirst "InvoiceItemAutoNumber = " &
> >> > Me.InvoiceItemAutoNumber
> >> > If Not .NoMatch Then
> >> > frm.Bookmark = .Bookmark
> >> > End If
> >> > End With
> >> > End If
> >> > Set frm = Nothing
> >> >
> >> > I guess this really comes down to getting a better understanding of how
> >> > the
> >> > "Bookmark", "RecordSetClone", and "Parent" properties work and how to
> >> > use
> >> > them. The help files are not cutting it for me. Any online sites you
> >> > might
> >> > recommend for more details on using properties like these?
> >> >
> >> > Thanks!
> >> >
> >> > "Allen Browne" wrote:
> >> >
> >> >> Assuming you have a primary key in the table that these subforms come
> >> >> from,
> >> >> you could use the primary key value of the selected record to go to
> >> >> the
> >> >> same
> >> >> record in the other subform.
> >> >>
> >> >> The code would consist of something like this:
> >> >>
> >> >> Dim frm As Form
> >> >> If Not Me.NewRecord Then
> >> >> Set frm = Me.Parent.[NameOfYourOtherSubformControlHere].Form
> >> >> With frm.RecordsetClone
> >> >> .FindFirst "[ID] = " & Me.ID
> >> >> If Not .NoMatch Then
> >> >> frm.Bookmark = .Bookmark
> >> >> End If
> >> >> End With
> >> >> End If
> >> >> Set frm = Nothing
> >> >>
> >> >> This assumes that neither subform is dirty at the time, neither is
> >> >> filtered,
> >> >> and it is okay not to move the other form if you are at a new record.
> >> >> After
> >> >> it finds the record, Access is likely to scroll the other subform so
> >> >> that
> >> >> the found record is the first one shown in the subform.
> >> >>
> >> >> "Dan Wieland" <Dan (E-Mail Removed)> wrote in message
> >> >> news:E893BA31-9565-47A9-A4DD-(E-Mail Removed)...
> >> >> > My question pertains to the part of my Access 2003 MDB application,
> >> >> > where
> >> >> > I
> >> >> > have a main form, plus a subform within it (Subform1), and another
> >> >> > subform
> >> >> > (Subform2) which is in Subform1.
> >> >> >
> >> >> > Subform2 is a continuous form. When the user selects a record in
> >> >> > Subform2
> >> >> > (by clicking on the record selector), I want to have Subform1 switch
> >> >> > to
> >> >> > the
> >> >> > same record # as Subform2 was just moved to. (Subform1 is my
> >> >> > detailed
> >> >> > item
> >> >> > entry form, and Subform2 is an abbreviated listing of all the items
> >> >> > entered.)
> >> >> >
> >> >> > I attempted to do this by writing some simple code in the "On
> >> >> > Current"
> >> >> > event
> >> >> > in Subform2 to capture the record # into a variable, then use the
> >> >> > GoToRecord
> >> >> > to move Subform1 active record to the same number. After reading
> >> >> > the
> >> >> > help
> >> >> > files and posts here, I've found that when using GoToRecord you
> >> >> > can't
> >> >> > reference any other subform than the one you are in. (at least you
> >> >> > can't
> >> >> > expect it to work) It does not seem that there is a conceivable
> >> >> > way
> >> >> > to
> >> >> > make
> >> >> > the "GoToRecord" function work for what I need.
> >> >> >
> >> >> > Am I correct about that, as it applies to my situation?
> >> >> >
> >> >> > Are there any suggestions on ways to accomplish this?
> >> >> >
> >> >> > Thanks in advance.
>
>
>

The field does not have to be indexed. Try this:

Earlier in the code:
Set frm = Me.Parent

Then:
frm.Bookmark = rs.Bookmark

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan Wieland" <(E-Mail Removed)> wrote in message
news:B2328BD2-35F8-49BC-9BFD-(E-Mail Removed)...
> It does seem like there is a problem with that field name, but I can't
> seem
> to find it.
> I added some debug.print statements to help see where the code gets, and
> commented out the line where it always chokes.
>
> Dim frm As Form
> Dim rs As DAO.Recordset
> If Not Me.NewRecord Then
> Set frm = Me.Parent.Form
> Set rs = frm.RecordsetClone
> Debug.Print "findfirst"
> rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
> If Not rs.NoMatch Then
> 'Forms!frmInvoiceMain.fsubInvoiceItem.Form.Bookmark =
> rs.Bookmark
> Debug.Print "hello world"
> End If
> End If
> Set rs = Nothing
> Set frm = Nothing
>
> When I open the form, in the immediate window appears:
> findfirst
> hello world <-- it must have found a match at least once, right??
> findfirst <-- why is it doing it again? Because there are mutliple
> embedded frms?
>
> Then it stops on the following line stating that the operation was
> cancelled
> by the user, run-time error 3059:
> rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
>
> If I hover over the "Me.InvoiceItemAutoNumber" it shows the correct value
> for that field. I've tried changing "rs.FindFirst "InvoiceItemAutoNumber
> =
> "... to a different field on this form, and the run-time error does not
> occur. Like you said, there seems to be a problem with this
> InvoiceItemAutoNumber field. Does it have to be indexed? Does it matter
> that this form is based on a query?
>
> I also tried your instructions regarding the Name Auto-Correct options.
> (Mine were originally checked.) This did not seem to have any effect
> after I
> unchecked and compacted. I also don't remember renaming this field, but
> can't rule that out completely.
>
> "Allen Browne" wrote:
>
>> The error suggests that Access cannot find the field named
>> InvoiceItemAutoNumber.
>>
>> If you open the form in design view, and open the Field List (View menu),
>> is
>> there a field with that name listed there?
>>
>> If it is there, make sure that the Name AutoCorrect boxes are unchecked
>> under:
>> Tools | Options | General
>> and then compact. Explanation:
>> http://allenbrowne.com/bug-03.html
>>
>> "Dan Wieland" <(E-Mail Removed)> wrote in message
>> news:C33B5048-AA13-4184-8A24-(E-Mail Removed)...
>> > Thanks for the additional explanations. I think I understand the
>> > RecordsetClone concept, and how it can be useful. Thanks!
>> >
>> > I'm using A2003, and as far as I can determine, DAO. In the code
>> > window,
>> > Tools-> References, I have the following checked, in this order:
>> > - VBA
>> > - Microsoft Access 11.0 for Object Library
>> > - OLE Automation <--- this one wasn't listed in your table on your
>> > nice
>> > web
>> > page.
>> > - Microsoft DAO 3.6 Object Lib
>> > - Microsoft ADO 2.5 Lib
>> >
>> > As you guessed, the compiler didn't like the 2nd line:
>> >> Dim rs As DAO.RecordsetClone
>> > It gave me an error - "User defined type not defined"
>> >
>> > If I changed it to "Dim rs As DAO.Recordset" it would compile fine.
>> > However, then I'm back to the same run-time error '2465', can't find
>> > the
>> > field "|" referenced in my expression. The debugger halts at this
>> > line
>> > again:
>> > frm.Bookmark = rs.Bookmark
>> >
>> > Thanks for the explicit version. That helped me understand a little
>> > more
>> > of
>> > the mechanics on these methods. Unfortunately still stuck. I tried a
>> > test
>> > to get even more explicit as follows:
>> >
>> > Dim frm As Form
>> > Dim rs As DAO.Recordset
>> > If Not Me.NewRecord Then
>> > 'Set frm = Me.Parent.Form
>> > Set rs =
>> > Forms!frmInvoiceMain.fsubInvoiceItem.Form.RecordsetClone
>> > rs.FindFirst "InvoiceItemAutoNumber = " &
>> > Me.InvoiceItemAutoNumber
>> > If Not rs.NoMatch Then
>> > Forms!frmInvoiceMain.fsubInvoiceItem.Form.Bookmark =
>> > rs.Bookmark
>> > End If
>> > End If
>> > Set rs = Nothing
>> > Set frm = Nothing
>> >
>> > Still same run-time error 2465. Perhaps it doesn't like the "=
>> > rs.Bookmark"
>> > portion of that line? Any more suggestions for me? Thank you for your
>> > time
>> > and help.
>> >
>> > "Allen Browne" wrote:
>> >
>> >> Forms have a RecordsetClone, which can have a different "current"
>> >> record
>> >> than the one in the form, so you can search it, check if there is a
>> >> match,
>> >> and move to the found record. Setting the form's Bookmark to that of
>> >> the
>> >> found recordset in the clone set makes that record current in the
>> >> form.
>> >>
>> >> If Access does not understand the Bookmark property of the recordset,
>> >> it
>> >> may
>> >> have the wrong kind of recordset. In Access 2000, Microsoft made the
>> >> ADO
>> >> recordset the default instead of the DAO one. Fortunately, DAO is back
>> >> again
>> >> in Access 2003.
>> >>
>> >> Try being explicit about the kind of recordset you want:
>> >> Dim frm As Form
>> >> Dim rs As DAO.RecordsetClone
>> >> If Not Me.NewRecord Then
>> >> Set frm = Me.Parent.Form
>> >> Set rs = frm.RecordsetClone
>> >> rs.FindFirst "InvoiceItemAutoNumber = " &
>> >> Me.InvoiceItemAutoNumber
>> >> If Not rs.NoMatch Then
>> >> frm.Bookmark = rs.Bookmark
>> >> End If
>> >> End If
>> >> Set rs = Nothing
>> >> Set frm = Nothing
>> >>
>> >> If the 2nd line gives you an error, and you are using A2000 or 2002,
>> >> see:
>> >> http://allenbrowne.com/ser-38.html
>> >>
>> >> --
>> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
>> >> Tips for Access users - http://allenbrowne.com/tips.html
>> >> Reply to group, rather than allenbrowne at mvps dot org.
>> >>
>> >> "Dan Wieland" <(E-Mail Removed)> wrote in message
>> >> news:9126EAC4-06E4-45FF-91A8-(E-Mail Removed)...
>> >> > Thank you for your time and feedback. I have been studying your
>> >> > suggestion,
>> >> > and learning about these methods and properties, but still can't
>> >> > seem
>> >> > to
>> >> > get
>> >> > your code suggestion to work. I want to figure this out, but need a
>> >> > little
>> >> > more education. It gets hung up on the line:
>> >> > frm.Bookmark = .Bookmark
>> >> > It gives me a runtime error 2465 and says it can't find field "|".
>> >> >
>> >> > A little more background for you: fsubInvoiceItemsListing is where
>> >> > this
>> >> > code resides. It's in the OnCurrent event. fsubInvoiceItemsListing
>> >> > is
>> >> > embedded in fsubInvoiceItem. fsubInvoiceItem is embedded in
>> >> > frmInvoiceMain.
>> >> >
>> >> > fsubInvoiceItemsListing is based on a query, and contains a field
>> >> > called
>> >> > InvoiceItemNumber. fsubInvoiceItem is based on a table, and also
>> >> > contains
>> >> > the same field InvoiceItemNumber. That field is the primary key for
>> >> > that
>> >> > table. None of the forms are filtered.
>> >> >
>> >> > What I'm trying to accomplish is this: When a record in
>> >> > fsubInvoiceItemsListing is made current, I want fsubInvoiceItem to
>> >> > move
>> >> > to
>> >> > that same record automatically.
>> >> >
>> >> > Here's my code as of now:
>> >> > Dim frm As Form
>> >> > If Not Me.NewRecord Then
>> >> > Set frm = Me.Parent.Form
>> >> > With frm.RecordsetClone
>> >> > .FindFirst "InvoiceItemAutoNumber = " &
>> >> > Me.InvoiceItemAutoNumber
>> >> > If Not .NoMatch Then
>> >> > frm.Bookmark = .Bookmark
>> >> > End If
>> >> > End With
>> >> > End If
>> >> > Set frm = Nothing
>> >> >
>> >> > I guess this really comes down to getting a better understanding of
>> >> > how
>> >> > the
>> >> > "Bookmark", "RecordSetClone", and "Parent" properties work and how
>> >> > to
>> >> > use
>> >> > them. The help files are not cutting it for me. Any online sites
>> >> > you
>> >> > might
>> >> > recommend for more details on using properties like these?
>> >> >
>> >> > Thanks!
>> >> >
>> >> > "Allen Browne" wrote:
>> >> >
>> >> >> Assuming you have a primary key in the table that these subforms
>> >> >> come
>> >> >> from,
>> >> >> you could use the primary key value of the selected record to go to
>> >> >> the
>> >> >> same
>> >> >> record in the other subform.
>> >> >>
>> >> >> The code would consist of something like this:
>> >> >>
>> >> >> Dim frm As Form
>> >> >> If Not Me.NewRecord Then
>> >> >> Set frm =
>> >> >> Me.Parent.[NameOfYourOtherSubformControlHere].Form
>> >> >> With frm.RecordsetClone
>> >> >> .FindFirst "[ID] = " & Me.ID
>> >> >> If Not .NoMatch Then
>> >> >> frm.Bookmark = .Bookmark
>> >> >> End If
>> >> >> End With
>> >> >> End If
>> >> >> Set frm = Nothing
>> >> >>
>> >> >> This assumes that neither subform is dirty at the time, neither is
>> >> >> filtered,
>> >> >> and it is okay not to move the other form if you are at a new
>> >> >> record.
>> >> >> After
>> >> >> it finds the record, Access is likely to scroll the other subform
>> >> >> so
>> >> >> that
>> >> >> the found record is the first one shown in the subform.
>> >> >>
>> >> >> "Dan Wieland" <Dan (E-Mail Removed)> wrote in
>> >> >> message
>> >> >> news:E893BA31-9565-47A9-A4DD-(E-Mail Removed)...
>> >> >> > My question pertains to the part of my Access 2003 MDB
>> >> >> > application,
>> >> >> > where
>> >> >> > I
>> >> >> > have a main form, plus a subform within it (Subform1), and
>> >> >> > another
>> >> >> > subform
>> >> >> > (Subform2) which is in Subform1.
>> >> >> >
>> >> >> > Subform2 is a continuous form. When the user selects a record in
>> >> >> > Subform2
>> >> >> > (by clicking on the record selector), I want to have Subform1
>> >> >> > switch
>> >> >> > to
>> >> >> > the
>> >> >> > same record # as Subform2 was just moved to. (Subform1 is my
>> >> >> > detailed
>> >> >> > item
>> >> >> > entry form, and Subform2 is an abbreviated listing of all the
>> >> >> > items
>> >> >> > entered.)
>> >> >> >
>> >> >> > I attempted to do this by writing some simple code in the "On
>> >> >> > Current"
>> >> >> > event
>> >> >> > in Subform2 to capture the record # into a variable, then use the
>> >> >> > GoToRecord
>> >> >> > to move Subform1 active record to the same number. After reading
>> >> >> > the
>> >> >> > help
>> >> >> > files and posts here, I've found that when using GoToRecord you
>> >> >> > can't
>> >> >> > reference any other subform than the one you are in. (at least
>> >> >> > you
>> >> >> > can't
>> >> >> > expect it to work) It does not seem that there is a conceivable
>> >> >> > way
>> >> >> > to
>> >> >> > make
>> >> >> > the "GoToRecord" function work for what I need.
>> >> >> >
>> >> >> > Am I correct about that, as it applies to my situation?
>> >> >> >
>> >> >> > Are there any suggestions on ways to accomplish this?
>> >> >> >
>> >> >> > Thanks in advance.

I've discovered what appears to be the problem. This code executed on the
"On Current" event of the inner most subform. The code was designed to
change the current record of it's parent form, which must have caused it's
own record to change as a result. It seemed to behave in a recursive manner,
and this tight loop of constantly changing current record caused the code to
get lost.

The solution was to put this exact same code in the "On Click" event of the
inner most form. It works exactly as I had hoped to achieve. Now when I
click on a record selector of the inner most form, it's parent form changes
it's current record accordingly. No more recursive changing of current
record.

I have learned a great deal through this experience, and want to thank you
for your support and guidance along the way. It is really nice to have a
forum like this to use. MVP's like you really make it all work. Thank you
for doing it.

"Allen Browne" wrote:

> The field does not have to be indexed. Try this:
>
> Earlier in the code:
> Set frm = Me.Parent
>
> Then:
> frm.Bookmark = rs.Bookmark
>
> --
> Allen Browne - Microsoft MVP. Perth, Western Australia.
> Tips for Access users - http://allenbrowne.com/tips.html
> Reply to group, rather than allenbrowne at mvps dot org.
>
> "Dan Wieland" <(E-Mail Removed)> wrote in message
> news:B2328BD2-35F8-49BC-9BFD-(E-Mail Removed)...
> > It does seem like there is a problem with that field name, but I can't
> > seem
> > to find it.
> > I added some debug.print statements to help see where the code gets, and
> > commented out the line where it always chokes.
> >
> > Dim frm As Form
> > Dim rs As DAO.Recordset
> > If Not Me.NewRecord Then
> > Set frm = Me.Parent.Form
> > Set rs = frm.RecordsetClone
> > Debug.Print "findfirst"
> > rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
> > If Not rs.NoMatch Then
> > 'Forms!frmInvoiceMain.fsubInvoiceItem.Form.Bookmark =
> > rs.Bookmark
> > Debug.Print "hello world"
> > End If
> > End If
> > Set rs = Nothing
> > Set frm = Nothing
> >
> > When I open the form, in the immediate window appears:
> > findfirst
> > hello world <-- it must have found a match at least once, right??
> > findfirst <-- why is it doing it again? Because there are mutliple
> > embedded frms?
> >
> > Then it stops on the following line stating that the operation was
> > cancelled
> > by the user, run-time error 3059:
> > rs.FindFirst "InvoiceItemAutoNumber = " & Me.InvoiceItemAutoNumber
> >
> > If I hover over the "Me.InvoiceItemAutoNumber" it shows the correct value
> > for that field. I've tried changing "rs.FindFirst "InvoiceItemAutoNumber
> > =
> > "... to a different field on this form, and the run-time error does not
> > occur. Like you said, there seems to be a problem with this
> > InvoiceItemAutoNumber field. Does it have to be indexed? Does it matter
> > that this form is based on a query?
> >
> > I also tried your instructions regarding the Name Auto-Correct options.
> > (Mine were originally checked.) This did not seem to have any effect
> > after I
> > unchecked and compacted. I also don't remember renaming this field, but
> > can't rule that out completely.
> >
> > "Allen Browne" wrote:
> >
> >> The error suggests that Access cannot find the field named
> >> InvoiceItemAutoNumber.
> >>
> >> If you open the form in design view, and open the Field List (View menu),
> >> is
> >> there a field with that name listed there?
> >>
> >> If it is there, make sure that the Name AutoCorrect boxes are unchecked
> >> under:
> >> Tools | Options | General
> >> and then compact. Explanation:
> >> http://allenbrowne.com/bug-03.html
> >>
> >> "Dan Wieland" <(E-Mail Removed)> wrote in message
> >> news:C33B5048-AA13-4184-8A24-(E-Mail Removed)...
> >> > Thanks for the additional explanations. I think I understand the
> >> > RecordsetClone concept, and how it can be useful. Thanks!
> >> >
> >> > I'm using A2003, and as far as I can determine, DAO. In the code
> >> > window,
> >> > Tools-> References, I have the following checked, in this order:
> >> > - VBA
> >> > - Microsoft Access 11.0 for Object Library
> >> > - OLE Automation <--- this one wasn't listed in your table on your
> >> > nice
> >> > web
> >> > page.
> >> > - Microsoft DAO 3.6 Object Lib
> >> > - Microsoft ADO 2.5 Lib
> >> >
> >> > As you guessed, the compiler didn't like the 2nd line:
> >> >> Dim rs As DAO.RecordsetClone
> >> > It gave me an error - "User defined type not defined"
> >> >
> >> > If I changed it to "Dim rs As DAO.Recordset" it would compile fine.
> >> > However, then I'm back to the same run-time error '2465', can't find
> >> > the
> >> > field "|" referenced in my expression. The debugger halts at this
> >> > line
> >> > again:
> >> > frm.Bookmark = rs.Bookmark
> >> >
> >> > Thanks for the explicit version. That helped me understand a little
> >> > more
> >> > of
> >> > the mechanics on these methods. Unfortunately still stuck. I tried a
> >> > test
> >> > to get even more explicit as follows:
> >> >
> >> > Dim frm As Form
> >> > Dim rs As DAO.Recordset
> >> > If Not Me.NewRecord Then
> >> > 'Set frm = Me.Parent.Form
> >> > Set rs =
> >> > Forms!frmInvoiceMain.fsubInvoiceItem.Form.RecordsetClone
> >> > rs.FindFirst "InvoiceItemAutoNumber = " &
> >> > Me.InvoiceItemAutoNumber
> >> > If Not rs.NoMatch Then
> >> > Forms!frmInvoiceMain.fsubInvoiceItem.Form.Bookmark =
> >> > rs.Bookmark
> >> > End If
> >> > End If
> >> > Set rs = Nothing
> >> > Set frm = Nothing
> >> >
> >> > Still same run-time error 2465. Perhaps it doesn't like the "=
> >> > rs.Bookmark"
> >> > portion of that line? Any more suggestions for me? Thank you for your
> >> > time
> >> > and help.
> >> >
> >> > "Allen Browne" wrote:
> >> >
> >> >> Forms have a RecordsetClone, which can have a different "current"
> >> >> record
> >> >> than the one in the form, so you can search it, check if there is a
> >> >> match,
> >> >> and move to the found record. Setting the form's Bookmark to that of
> >> >> the
> >> >> found recordset in the clone set makes that record current in the
> >> >> form.
> >> >>
> >> >> If Access does not understand the Bookmark property of the recordset,
> >> >> it
> >> >> may
> >> >> have the wrong kind of recordset. In Access 2000, Microsoft made the
> >> >> ADO
> >> >> recordset the default instead of the DAO one. Fortunately, DAO is back
> >> >> again
> >> >> in Access 2003.
> >> >>
> >> >> Try being explicit about the kind of recordset you want:
> >> >> Dim frm As Form
> >> >> Dim rs As DAO.RecordsetClone
> >> >> If Not Me.NewRecord Then
> >> >> Set frm = Me.Parent.Form
> >> >> Set rs = frm.RecordsetClone
> >> >> rs.FindFirst "InvoiceItemAutoNumber = " &
> >> >> Me.InvoiceItemAutoNumber
> >> >> If Not rs.NoMatch Then
> >> >> frm.Bookmark = rs.Bookmark
> >> >> End If
> >> >> End If
> >> >> Set rs = Nothing
> >> >> Set frm = Nothing
> >> >>
> >> >> If the 2nd line gives you an error, and you are using A2000 or 2002,
> >> >> see:
> >> >> http://allenbrowne.com/ser-38.html
> >> >>
> >> >> --
> >> >> Allen Browne - Microsoft MVP. Perth, Western Australia.
> >> >> Tips for Access users - http://allenbrowne.com/tips.html
> >> >> Reply to group, rather than allenbrowne at mvps dot org.
> >> >>
> >> >> "Dan Wieland" <(E-Mail Removed)> wrote in message
> >> >> news:9126EAC4-06E4-45FF-91A8-(E-Mail Removed)...
> >> >> > Thank you for your time and feedback. I have been studying your
> >> >> > suggestion,
> >> >> > and learning about these methods and properties, but still can't
> >> >> > seem
> >> >> > to
> >> >> > get
> >> >> > your code suggestion to work. I want to figure this out, but need a
> >> >> > little
> >> >> > more education. It gets hung up on the line:
> >> >> > frm.Bookmark = .Bookmark
> >> >> > It gives me a runtime error 2465 and says it can't find field "|".
> >> >> >
> >> >> > A little more background for you: fsubInvoiceItemsListing is where
> >> >> > this
> >> >> > code resides. It's in the OnCurrent event. fsubInvoiceItemsListing
> >> >> > is
> >> >> > embedded in fsubInvoiceItem. fsubInvoiceItem is embedded in
> >> >> > frmInvoiceMain.
> >> >> >
> >> >> > fsubInvoiceItemsListing is based on a query, and contains a field
> >> >> > called
> >> >> > InvoiceItemNumber. fsubInvoiceItem is based on a table, and also
> >> >> > contains
> >> >> > the same field InvoiceItemNumber. That field is the primary key for
> >> >> > that
> >> >> > table. None of the forms are filtered.
> >> >> >
> >> >> > What I'm trying to accomplish is this: When a record in
> >> >> > fsubInvoiceItemsListing is made current, I want fsubInvoiceItem to
> >> >> > move
> >> >> > to
> >> >> > that same record automatically.
> >> >> >
> >> >> > Here's my code as of now:
> >> >> > Dim frm As Form
> >> >> > If Not Me.NewRecord Then
> >> >> > Set frm = Me.Parent.Form
> >> >> > With frm.RecordsetClone
> >> >> > .FindFirst "InvoiceItemAutoNumber = " &
> >> >> > Me.InvoiceItemAutoNumber
> >> >> > If Not .NoMatch Then
> >> >> > frm.Bookmark = .Bookmark
> >> >> > End If
> >> >> > End With
> >> >> > End If
> >> >> > Set frm = Nothing
> >> >> >
> >> >> > I guess this really comes down to getting a better understanding of
> >> >> > how
> >> >> > the
> >> >> > "Bookmark", "RecordSetClone", and "Parent" properties work and how
> >> >> > to
> >> >> > use
> >> >> > them. The help files are not cutting it for me. Any online sites
> >> >> > you
> >> >> > might
> >> >> > recommend for more details on using properties like these?
> >> >> >
> >> >> > Thanks!
> >> >> >
> >> >> > "Allen Browne" wrote:
> >> >> >
> >> >> >> Assuming you have a primary key in the table that these subforms
> >> >> >> come
> >> >> >> from,
> >> >> >> you could use the primary key value of the selected record to go to
> >> >> >> the
> >> >> >> same
> >> >> >> record in the other subform.
> >> >> >>
> >> >> >> The code would consist of something like this:
> >> >> >>
> >> >> >> Dim frm As Form
> >> >> >> If Not Me.NewRecord Then
> >> >> >> Set frm =
> >> >> >> Me.Parent.[NameOfYourOtherSubformControlHere].Form
> >> >> >> With frm.RecordsetClone
> >> >> >> .FindFirst "[ID] = " & Me.ID
> >> >> >> If Not .NoMatch Then
> >> >> >> frm.Bookmark = .Bookmark
> >> >> >> End If
> >> >> >> End With
> >> >> >> End If
> >> >> >> Set frm = Nothing
> >> >> >>
> >> >> >> This assumes that neither subform is dirty at the time, neither is
> >> >> >> filtered,
> >> >> >> and it is okay not to move the other form if you are at a new
> >> >> >> record.
> >> >> >> After
> >> >> >> it finds the record, Access is likely to scroll the other subform
> >> >> >> so
> >> >> >> that
> >> >> >> the found record is the first one shown in the subform.
> >> >> >>
> >> >> >> "Dan Wieland" <Dan (E-Mail Removed)> wrote in
> >> >> >> message
> >> >> >> news:E893BA31-9565-47A9-A4DD-(E-Mail Removed)...
> >> >> >> > My question pertains to the part of my Access 2003 MDB
> >> >> >> > application,
> >> >> >> > where
> >> >> >> > I
> >> >> >> > have a main form, plus a subform within it (Subform1), and
> >> >> >> > another
> >> >> >> > subform
> >> >> >> > (Subform2) which is in Subform1.
> >> >> >> >
> >> >> >> > Subform2 is a continuous form. When the user selects a record in
> >> >> >> > Subform2
> >> >> >> > (by clicking on the record selector), I want to have Subform1
> >> >> >> > switch
> >> >> >> > to
> >> >> >> > the
> >> >> >> > same record # as Subform2 was just moved to. (Subform1 is my
> >> >> >> > detailed
> >> >> >> > item
> >> >> >> > entry form, and Subform2 is an abbreviated listing of all the
> >> >> >> > items
> >> >> >> > entered.)

Great, Dan. You have it solved.

Yes, if the field/control(s) named in LinkMasterFields changes, the subform
records are reloaded, so it is very easy to trigger an endless loop doing
that.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Dan Wieland" <(E-Mail Removed)> wrote in message
news:00CF8EDE-8527-47AB-9890-(E-Mail Removed)...
> I've discovered what appears to be the problem. This code executed on the
> "On Current" event of the inner most subform. The code was designed to
> change the current record of it's parent form, which must have caused it's
> own record to change as a result. It seemed to behave in a recursive
> manner,
> and this tight loop of constantly changing current record caused the code
> to
> get lost.
>
> The solution was to put this exact same code in the "On Click" event of
> the
> inner most form. It works exactly as I had hoped to achieve. Now when I
> click on a record selector of the inner most form, it's parent form
> changes
> it's current record accordingly. No more recursive changing of current
> record.
>
> I have learned a great deal through this experience, and want to thank you
> for your support and guidance along the way. It is really nice to have a
> forum like this to use. MVP's like you really make it all work. Thank
> you
> for doing it.