How to go to correct record in subform

[Tony Williams]

I have built a search form which has a list box which shows the results. On
clicking on one of the results it opens a form showing the record. The form
is built on a table tbldocument with a sub form based on tblinstitution.
There is a one to many relationship between the tables. The subform can hold
upto to 3 records for each matched record of the main form. The code on the
double click event is
Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtRefNbr]=" & Me.[List3]
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

Where txtrefnbr is the field that links the two tables. When the form opens
it displays the first record in the subform but it may be that the record I
want is the second record. Is there anyway to open the main form and have
the subform show a record other than the first?
Hope I've made sense here
Thanks
tony

 

[Bagger]

I have built a search form which has a list box which shows the results. On
clicking on one of the results it opens a form showing the record. The form
is built on a table tbldocument with a sub form based on tblinstitution.
There is a one to many relationship between the tables. The subform can hold
upto to 3 records for each matched record of the main form. The code on the
double click event is
Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtRefNbr]=" & Me.[List3]
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

Where txtrefnbr is the field that links the two tables. When the form opens
it displays the first record in the subform but it may be that the record I
want is the second record. Is there anyway to open the main form and have
the subform show a record other than the first?
Hope I've made sense here
Thanks
tony

 

[Bagger]

There are several ways you could do it, but the question is how do you
determine which record you want to display? If using a continuous form as
the subform to display all the related records won't be acceptable, then you
could always filter the subform to display a specific record, as long as you
know what record to display.

Sorry about the blank reply before this one. Didn't realize it erased my
text when it made me log back in.

 

[Marshall Barton]

I have built a search form which has a list box which shows the results. On
clicking on one of the results it opens a form showing the record. The form
is built on a table tbldocument with a sub form based on tblinstitution.
There is a one to many relationship between the tables. The subform can hold
upto to 3 records for each matched record of the main form. The code on the
double click event is
Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtRefNbr]=" & Me.[List3]
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

Where txtrefnbr is the field that links the two tables. When the form opens
it displays the first record in the subform but it may be that the record I
want is the second record. Is there anyway to open the main form and have
the subform show a record other than the first?

You need to tell frmMDi which record the subform should
display so it can navigate to that record. The easiest way
to do that is to use the OpenForm method's OpenArgs
argument:

DoCmd.OpenForm "frmMDi", _
WhereCondition:= MyWhereCondition, _
OpenArgs:= 2

Then the main form's Load event can use code something like
this air code outline:

With Me.subform.Form.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.subform.Form.AllowAdditions = (.RecordCount < 3)
If .RecordCount > Me.OpenArgs - 1 Then
.AbsolutePosition = Me.OpenArgs - 1
Else
.MoveFirst
End If
Me.Bookmark = .Bookmark
End With

 

[Tony Williams]

Thanks to you both. I'll try that code Marsh and come back to you if I can
with any problems.
Cheers
Tony

 

[Tony Williams]

Thanks Marsh, tried your code but get error message Not a Valid Bookmark and
on Debug this line is highlighted
Me.Bookmark = .Bookmark
Any ideas?
Thanks
Tony

I have built a search form which has a list box which shows the results.
On
clicking on one of the results it opens a form showing the record. The
form
is built on a table tbldocument with a sub form based on tblinstitution.
There is a one to many relationship between the tables. The subform can
hold
upto to 3 records for each matched record of the main form. The code on
the
double click event is
Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtRefNbr]=" & Me.[List3]
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

Where txtrefnbr is the field that links the two tables. When the form
opens
it displays the first record in the subform but it may be that the record
I
want is the second record. Is there anyway to open the main form and have
the subform show a record other than the first?

You need to tell frmMDi which record the subform should
display so it can navigate to that record. The easiest way
to do that is to use the OpenForm method's OpenArgs
argument:

DoCmd.OpenForm "frmMDi", _
WhereCondition:= MyWhereCondition, _
OpenArgs:= 2

Then the main form's Load event can use code something like
this air code outline:

With Me.subform.Form.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.subform.Form.AllowAdditions = (.RecordCount < 3)
If .RecordCount > Me.OpenArgs - 1 Then
.AbsolutePosition = Me.OpenArgs - 1
Else
.MoveFirst
End If
Me.Bookmark = .Bookmark
End With

 

[Marshall Barton]

What was the test case when you got that error. In
eyeballing my air code, I don't hoe that can happen unless
there are no records. To deal with the case of no records,
try changing that line to:

If .RecordCount > 0 Then Me.Bookmark = .Bookmark
--
Marsh
MVP [MS Access]

Thanks Marsh, tried your code but get error message Not a Valid Bookmark and
on Debug this line is highlighted
Me.Bookmark = .Bookmark


"Marshall Barton"wrote

I have built a search form which has a list box which shows the results.
On
clicking on one of the results it opens a form showing the record. The
form
is built on a table tbldocument with a sub form based on tblinstitution.
There is a one to many relationship between the tables. The subform can
hold
upto to 3 records for each matched record of the main form. The code on
the
double click event is
Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtRefNbr]=" & Me.[List3]
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

Where txtrefnbr is the field that links the two tables. When the form
opens
it displays the first record in the subform but it may be that the record
I
want is the second record. Is there anyway to open the main form and have
the subform show a record other than the first?

You need to tell frmMDi which record the subform should
display so it can navigate to that record. The easiest way
to do that is to use the OpenForm method's OpenArgs
argument:

DoCmd.OpenForm "frmMDi", _
WhereCondition:= MyWhereCondition, _
OpenArgs:= 2

Then the main form's Load event can use code something like
this air code outline:

With Me.subform.Form.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.subform.Form.AllowAdditions = (.RecordCount < 3)
If .RecordCount > Me.OpenArgs - 1 Then
.AbsolutePosition = Me.OpenArgs - 1
Else
.MoveFirst
End If
Me.Bookmark = .Bookmark
End With

 

[Tony Williams]

Hi Marshall I got error 3159 If I debug and close the VBA window the form
will open at the right record for the main form but not the correct subform.
Thanks
Tony

What was the test case when you got that error. In
eyeballing my air code, I don't hoe that can happen unless
there are no records. To deal with the case of no records,
try changing that line to:

If .RecordCount > 0 Then Me.Bookmark = .Bookmark
--
Marsh
MVP [MS Access]

Thanks Marsh, tried your code but get error message Not a Valid Bookmark
and
on Debug this line is highlighted
Me.Bookmark = .Bookmark


"Marshall Barton"wrote

Tony Williams wrote:

I have built a search form which has a list box which shows the results.
On
clicking on one of the results it opens a form showing the record. The
form
is built on a table tbldocument with a sub form based on tblinstitution.
There is a one to many relationship between the tables. The subform can
hold
upto to 3 records for each matched record of the main form. The code on
the
double click event is
Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtRefNbr]=" & Me.[List3]
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

Where txtrefnbr is the field that links the two tables. When the form
opens
it displays the first record in the subform but it may be that the
record
I
want is the second record. Is there anyway to open the main form and
have
the subform show a record other than the first?


You need to tell frmMDi which record the subform should
display so it can navigate to that record. The easiest way
to do that is to use the OpenForm method's OpenArgs
argument:

DoCmd.OpenForm "frmMDi", _
WhereCondition:= MyWhereCondition, _
OpenArgs:= 2

Then the main form's Load event can use code something like
this air code outline:

With Me.subform.Form.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.subform.Form.AllowAdditions = (.RecordCount < 3)
If .RecordCount > Me.OpenArgs - 1 Then
.AbsolutePosition = Me.OpenArgs - 1
Else
.MoveFirst
End If
Me.Bookmark = .Bookmark
End With

 

[Tony Williams]

My List box showed the record I wanted, I clicked on it and got the Not a
valid bookmark error ( even with the additional wording) The form opens at
the correct main record but the subform in this case has two records and the
first one is showing not the second one which is the one I clicked on in the
list box.
Any help?
Thanks
Tony

Hi Marshall I got error 3159 If I debug and close the VBA window the form
will open at the right record for the main form but not the correct
subform.
Thanks
Tony

What was the test case when you got that error. In
eyeballing my air code, I don't hoe that can happen unless
there are no records. To deal with the case of no records,
try changing that line to:

If .RecordCount > 0 Then Me.Bookmark = .Bookmark
--
Marsh
MVP [MS Access]

Thanks Marsh, tried your code but get error message Not a Valid Bookmark
and
on Debug this line is highlighted
Me.Bookmark = .Bookmark


"Marshall Barton"wrote
Tony Williams wrote:

I have built a search form which has a list box which shows the
results.
On
clicking on one of the results it opens a form showing the record. The
form
is built on a table tbldocument with a sub form based on
tblinstitution.
There is a one to many relationship between the tables. The subform can
hold
upto to 3 records for each matched record of the main form. The code on
the
double click event is
Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtRefNbr]=" & Me.[List3]
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

Where txtrefnbr is the field that links the two tables. When the form
opens
it displays the first record in the subform but it may be that the
record
I
want is the second record. Is there anyway to open the main form and
have
the subform show a record other than the first?


You need to tell frmMDi which record the subform should
display so it can navigate to that record. The easiest way
to do that is to use the OpenForm method's OpenArgs
argument:

DoCmd.OpenForm "frmMDi", _
WhereCondition:= MyWhereCondition, _
OpenArgs:= 2

Then the main form's Load event can use code something like
this air code outline:

With Me.subform.Form.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.subform.Form.AllowAdditions = (.RecordCount < 3)
If .RecordCount > Me.OpenArgs - 1 Then
.AbsolutePosition = Me.OpenArgs - 1
Else
.MoveFirst
End If
Me.Bookmark = .Bookmark
End With

 

[Marshall Barton]

There Is something about what you're saying that doesn't
seem to add up. Are you selecting a main form or subform
record from the list box? Before, you said that the subform
record was identified by a nunber. If the list box
identifies the subform record, I will need to know how you
determine which subform record along with the list box
RowSource fields and which is the BoundColumn.

While you're posting that info, include a Copy/Paste of the
actual code that you are using.
--
Marsh
MVP [MS Access]

My List box showed the record I wanted, I clicked on it and got the Not a
valid bookmark error ( even with the additional wording) The form opens at
the correct main record but the subform in this case has two records and the
first one is showing not the second one which is the one I clicked on in the
list box.

Hi Marshall I got error 3159 If I debug and close the VBA window the form
will open at the right record for the main form but not the correct
subform.
Thanks
Tony

What was the test case when you got that error. In
eyeballing my air code, I don't hoe that can happen unless
there are no records. To deal with the case of no records,
try changing that line to:

If .RecordCount > 0 Then Me.Bookmark = .Bookmark


Tony Williams wrote:
Thanks Marsh, tried your code but get error message Not a Valid Bookmark
and
on Debug this line is highlighted
Me.Bookmark = .Bookmark


"Marshall Barton"wrote
Tony Williams wrote:

I have built a search form which has a list box which shows the
results.
On
clicking on one of the results it opens a form showing the record. The
form
is built on a table tbldocument with a sub form based on
tblinstitution.
There is a one to many relationship between the tables. The subform can
hold
upto to 3 records for each matched record of the main form. The code on
the
double click event is
Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtRefNbr]=" & Me.[List3]
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

Where txtrefnbr is the field that links the two tables. When the form
opens
it displays the first record in the subform but it may be that the
record
I
want is the second record. Is there anyway to open the main form and
have
the subform show a record other than the first?


You need to tell frmMDi which record the subform should
display so it can navigate to that record. The easiest way
to do that is to use the OpenForm method's OpenArgs
argument:

DoCmd.OpenForm "frmMDi", _
WhereCondition:= MyWhereCondition, _
OpenArgs:= 2

Then the main form's Load event can use code something like
this air code outline:

With Me.subform.Form.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.subform.Form.AllowAdditions = (.RecordCount < 3)
If .RecordCount > Me.OpenArgs - 1 Then
.AbsolutePosition = Me.OpenArgs - 1
Else
.MoveFirst
End If
Me.Bookmark = .Bookmark
End With

 

[Tony Williams]

Hi Marsh I think I may have solved my problem. I took a step back yesterday
and asked myself what I was trying to do. Originally I was opening a main
form (one I had created for the user to view all the records) based on one
table (tblDocument) with a subform based on another (tblInstitution1), the
tables had a one to many relationship, hence the possibility of having more
than one record in the subform, there could be more than one institution for
any document. However the records I was searching were on the subform so
I've created a new form with the main form based on tblInstitution1 and the
subform on tbldocument and now double clicking on my list box gives me the
record I want.

This is clearly a case of rushing headlong into something without thinking
it through and just going for the obvious which isn't always the correct
way.

I'm sorry to have taken your time Marsh but thanks for sticking with me.

A lesson learned on my part, I hope!!
Cheers
Tony

There Is something about what you're saying that doesn't
seem to add up. Are you selecting a main form or subform
record from the list box? Before, you said that the subform
record was identified by a nunber. If the list box
identifies the subform record, I will need to know how you
determine which subform record along with the list box
RowSource fields and which is the BoundColumn.

While you're posting that info, include a Copy/Paste of the
actual code that you are using.
--
Marsh
MVP [MS Access]

My List box showed the record I wanted, I clicked on it and got the Not a
valid bookmark error ( even with the additional wording) The form opens at
the correct main record but the subform in this case has two records and
the
first one is showing not the second one which is the one I clicked on in
the
list box.

Hi Marshall I got error 3159 If I debug and close the VBA window the
form
will open at the right record for the main form but not the correct
subform.
Thanks
Tony
What was the test case when you got that error. In
eyeballing my air code, I don't hoe that can happen unless
there are no records. To deal with the case of no records,
try changing that line to:

If .RecordCount > 0 Then Me.Bookmark = .Bookmark


Tony Williams wrote:
Thanks Marsh, tried your code but get error message Not a Valid
Bookmark
and
on Debug this line is highlighted
Me.Bookmark = .Bookmark


"Marshall Barton"wrote
Tony Williams wrote:

I have built a search form which has a list box which shows the
results.
On
clicking on one of the results it opens a form showing the record.
The
form
is built on a table tbldocument with a sub form based on
tblinstitution.
There is a one to many relationship between the tables. The subform
can
hold
upto to 3 records for each matched record of the main form. The code
on
the
double click event is
Private Sub List3_DblClick(Cancel As Integer)
Dim MyWhereCondition As String
MyWhereCondition = "[txtRefNbr]=" & Me.[List3]
DoCmd.OpenForm "frmMDi", , , MyWhereCondition
End Sub

Where txtrefnbr is the field that links the two tables. When the form
opens
it displays the first record in the subform but it may be that the
record
I
want is the second record. Is there anyway to open the main form and
have
the subform show a record other than the first?


You need to tell frmMDi which record the subform should
display so it can navigate to that record. The easiest way
to do that is to use the OpenForm method's OpenArgs
argument:

DoCmd.OpenForm "frmMDi", _
WhereCondition:= MyWhereCondition, _
OpenArgs:= 2

Then the main form's Load event can use code something like
this air code outline:

With Me.subform.Form.RecordsetClone
If .RecordCount > 0 Then .MoveLast
Me.subform.Form.AllowAdditions = (.RecordCount < 3)
If .RecordCount > Me.OpenArgs - 1 Then
.AbsolutePosition = Me.OpenArgs - 1
Else
.MoveFirst
End If
Me.Bookmark = .Bookmark
End With

 

[Marshall Barton]

Hi Marsh I think I may have solved my problem. I took a step back yesterday
and asked myself what I was trying to do. Originally I was opening a main
form (one I had created for the user to view all the records) based on one
table (tblDocument) with a subform based on another (tblInstitution1), the
tables had a one to many relationship, hence the possibility of having more
than one record in the subform, there could be more than one institution for
any document. However the records I was searching were on the subform so
I've created a new form with the main form based on tblInstitution1 and the
subform on tbldocument and now double clicking on my list box gives me the
record I want.

This is clearly a case of rushing headlong into something without thinking
it through and just going for the obvious which isn't always the correct
way.

I'm sorry to have taken your time Marsh but thanks for sticking with me.

A lesson learned on my part, I hope!!

No wonder I wasn't getting a grip on your situation ;-)

This is a lesson well worth the cost of this experience.
Every time you are tempted to charge ahead, stop and think
about the general rule:
--------------------------------------------------------------------------------
The cost of making a change increases by an order of
magnitude for each level you that you have to go back to
make a correction.
--------------------------------------------------------------------------------

Levels are along the lines of:
Application Specifications
Detailed Specifications
Overall Design
Detailed Design
Coding
Unit Testing
Application Testing
After Release Maintenance

With database applications, determining your table structure
is such a major step that it probably deserves a level of
it's own between Overall and Detailed Design. Certainly,
you can not produce a detailed design of any queries, forms,
reports or data manipulating VBA procedures without knowing
what entity each table represents.