Changing the active record on a form, via code on a different form

[Dan Wieland]

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.

 

[Allen Browne]

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]

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!

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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!

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Dan Wieland]

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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!

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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!

:

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.

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.

 

[Dan Wieland]

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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!

:

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.

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.

 

[Allen Browne]

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

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.

:

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.

Reply to group, rather than allenbrowne at mvps dot org.

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!

:

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.

message
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.

 

[Dan Wieland]

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.

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.

Reply to group, rather than allenbrowne at mvps dot org.

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.

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

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.

:

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.

Reply to group, rather than allenbrowne at mvps dot org.

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!

:

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.

message
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.)

 

[Allen Browne]

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.