Combobox Display Problem

[Guest]

This appears to be my final frontier on the current project. All other
threads relating to it are now closed.

My data which displays in the Items Combobox of the Group Subform is,
finally, the right data. The problem is that when I click on an item in the
expanded combobox the pointer immediately jumps back tot he first displayed
record.

The Combobox has the controlling table of the subform as its control source,
and displays a single field from that table. I have checked and there are no
dangling Events that could be causing this.

Anyone got any ideas where I should be looking to stop this?

 

[Ken Snell \(MVP\)]

You're saying that the combobox moves to the first value in the dropdown
list whenever you select a value in the dropdown list? Is the combo box
bound to a field? Is the combo box locked? Where in the subform is the combo
box located -- form header? form detail? somewhere else?

 

[Guest]

«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which is Item, the
text field containing the Item Names, the Combobox comes up empty. Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header? form detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After Update for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

... but it didn't help.
--
Dave
Temping with Staffmark
in Rock Hill, SC

You're saying that the combobox moves to the first value in the dropdown
list whenever you select a value in the dropdown list? Is the combo box
bound to a field? Is the combo box locked? Where in the subform is the combo
box located -- form header? form detail? somewhere else?

 

[Guest]

One other piece of information -- when I open the sub form on its own, not as
part of the main form, I do not get this behavior. Clicking on any row
"sticks" just fine.
--
Dave
Temping with Staffmark
in Rock Hill, SC

You're saying that the combobox moves to the first value in the dropdown
list whenever you select a value in the dropdown list? Is the combo box
bound to a field? Is the combo box locked? Where in the subform is the combo
box located -- form header? form detail? somewhere else?

 

[Ken Snell \(MVP\)]

I'm not completely sure I understand what you're seeing, but if you're
running the code (which is slightly erroneous if you want to move only if a
record is found) you show in the AfterUpdate event of the combo box, then
the subform will "move" to the first record that matches the value found in
the current record's Item field control (is that the name of the combobox?).

Do you want the combo box to be used just for selecting a value for the
Item_ID field in the current record? Or do you want to use the combo box as
a "navigation" device to move the subform to a desired record?

FYI -- Corrected code to move to a record (note use of NoMatch property, not
EOF property):

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

--

Ken Snell
<MS ACCESS MVP>

«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which is Item,
the
text field containing the Item Names, the Combobox comes up empty. Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header? form
detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After Update for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

.. but it didn't help.

 

[Guest]

«Or do you want to use the combo box as a "navigation" device to move the
subform to a desired record?»

That's the plan.

I tried inserting your code both in the After_Update of the subform and of
the Item combobox, with a Stop immediately before the rs.MoMatch line so that
I could check what I was getting in that Item value -- surprise (to me
anyway) when I click on a row below the top one the code doesn't stop.
Hmmmmm. At least I know now what I am NOT doing right <g>.

Needless to say -- help still needed! (had a lovely time reading a novel
Friday <g>)
--
Dave
Temping with Staffmark
in Rock Hill, SC

I'm not completely sure I understand what you're seeing, but if you're
running the code (which is slightly erroneous if you want to move only if a
record is found) you show in the AfterUpdate event of the combo box, then
the subform will "move" to the first record that matches the value found in
the current record's Item field control (is that the name of the combobox?).

Do you want the combo box to be used just for selecting a value for the
Item_ID field in the current record? Or do you want to use the combo box as
a "navigation" device to move the subform to a desired record?

FYI -- Corrected code to move to a record (note use of NoMatch property, not
EOF property):

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

--

Ken Snell
<MS ACCESS MVP>

«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which is Item,
the
text field containing the Item Names, the Combobox comes up empty. Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header? form
detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After Update for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

.. but it didn't help.
--
Dave
Temping with Staffmark
in Rock Hill, SC

You're saying that the combobox moves to the first value in the dropdown
list whenever you select a value in the dropdown list? Is the combo box
bound to a field? Is the combo box locked? Where in the subform is the
combo
box located -- form header? form detail? somewhere else?
--

Ken Snell
<MS ACCESS MVP>



This appears to be my final frontier on the current project. All other
threads relating to it are now closed.

My data which displays in the Items Combobox of the Group Subform is,
finally, the right data. The problem is that when I click on an item in
the
expanded combobox the pointer immediately jumps back tot he first
displayed
record.

The Combobox has the controlling table of the subform as its control
source,
and displays a single field from that table. I have checked and there
are
no
dangling Events that could be causing this.

Anyone got any ideas where I should be looking to stop this?

 

[Guest]

That's "rs.NoMatch" line -- (Alzheimers again <g>)
--
Dave
Temping with Staffmark
in Rock Hill, SC

«Or do you want to use the combo box as a "navigation" device to move the
subform to a desired record?»

That's the plan.

I tried inserting your code both in the After_Update of the subform and of
the Item combobox, with a Stop immediately before the rs.MoMatch line so that
I could check what I was getting in that Item value -- surprise (to me
anyway) when I click on a row below the top one the code doesn't stop.
Hmmmmm. At least I know now what I am NOT doing right <g>.

Needless to say -- help still needed! (had a lovely time reading a novel
Friday <g>)
--
Dave
Temping with Staffmark
in Rock Hill, SC

I'm not completely sure I understand what you're seeing, but if you're
running the code (which is slightly erroneous if you want to move only if a
record is found) you show in the AfterUpdate event of the combo box, then
the subform will "move" to the first record that matches the value found in
the current record's Item field control (is that the name of the combobox?).

Do you want the combo box to be used just for selecting a value for the
Item_ID field in the current record? Or do you want to use the combo box as
a "navigation" device to move the subform to a desired record?

FYI -- Corrected code to move to a record (note use of NoMatch property, not
EOF property):

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

--

Ken Snell
<MS ACCESS MVP>

«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which is Item,
the
text field containing the Item Names, the Combobox comes up empty. Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header? form
detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After Update for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

.. but it didn't help.
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

You're saying that the combobox moves to the first value in the dropdown
list whenever you select a value in the dropdown list? Is the combo box
bound to a field? Is the combo box locked? Where in the subform is the
combo
box located -- form header? form detail? somewhere else?
--

Ken Snell
<MS ACCESS MVP>



This appears to be my final frontier on the current project. All other
threads relating to it are now closed.

My data which displays in the Items Combobox of the Group Subform is,
finally, the right data. The problem is that when I click on an item in
the
expanded combobox the pointer immediately jumps back tot he first
displayed
record.

The Combobox has the controlling table of the subform as its control
source,
and displays a single field from that table. I have checked and there
are
no
dangling Events that could be causing this.

Anyone got any ideas where I should be looking to stop this?

 

[Guest]

Oooookay --- slight bit of tweaking at this end. First, I have studied the
Access Naming Conventions, and have renamed all my fields and Controls to
"Industry Standards". Hopefully this will make what's going on a little less
obscure for you.

Next I have made the control Source for the fsubGroup Subform into a query,
qrtQuery1:

SELECT tblItem.idsItem_ID, tblItem.chrItem, tblItem.hlkReference_Address,
tblItem.idrCompany_ID FROM tblItem;

The Control Source for cboItem on fsubGroup is chrItem, and the Row Source
is qryQuery1, Bound To Column 1.

The fsubGroup Click Event code now contains the code you gave me:

Private Sub Form_Click()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
'Stop
rs.FindFirst "[idsItem_ID] = " & Str(Nz(Me![idsItem_ID], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

.. and the Stop, when uncommented shows that it is looking for the right
hing.

However when in the main form I am still getting the same behavior -- click
on a row below the top one, and the pointer immediately bounces back up to
the top.

--
Dave
Temping with Staffmark
in Rock Hill, SC

I'm not completely sure I understand what you're seeing, but if you're
running the code (which is slightly erroneous if you want to move only if a
record is found) you show in the AfterUpdate event of the combo box, then
the subform will "move" to the first record that matches the value found in
the current record's Item field control (is that the name of the combobox?).

Do you want the combo box to be used just for selecting a value for the
Item_ID field in the current record? Or do you want to use the combo box as
a "navigation" device to move the subform to a desired record?

FYI -- Corrected code to move to a record (note use of NoMatch property, not
EOF property):

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

--

Ken Snell
<MS ACCESS MVP>

«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which is Item,
the
text field containing the Item Names, the Combobox comes up empty. Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header? form
detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After Update for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

.. but it didn't help.
--
Dave
Temping with Staffmark
in Rock Hill, SC

You're saying that the combobox moves to the first value in the dropdown
list whenever you select a value in the dropdown list? Is the combo box
bound to a field? Is the combo box locked? Where in the subform is the
combo
box located -- form header? form detail? somewhere else?
--

Ken Snell
<MS ACCESS MVP>



This appears to be my final frontier on the current project. All other
threads relating to it are now closed.

My data which displays in the Items Combobox of the Group Subform is,
finally, the right data. The problem is that when I click on an item in
the
expanded combobox the pointer immediately jumps back tot he first
displayed
record.

The Combobox has the controlling table of the subform as its control
source,
and displays a single field from that table. I have checked and there
are
no
dangling Events that could be causing this.

Anyone got any ideas where I should be looking to stop this?

 

[Ken Snell \(MVP\)]

I just noticed that you had used Recordset.Clone in your code, and I copied
it without noticing. This is a better approach, using RecordsetClone:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub

If you want to use the combo box for navigation, then it needs to be in the
Header section of the form (subform), not the Detail section. And it should
not be bound to any field.

In a subsequent post, you mentioned the following:

"
Next I have made the control Source for the fsubGroup Subform into a query,
qrtQuery1:

SELECT tblItem.idsItem_ID, tblItem.chrItem, tblItem.hlkReference_Address,
tblItem.idrCompany_ID FROM tblItem;

The Control Source for cboItem on fsubGroup is chrItem, and the Row Source
is qryQuery1, Bound To Column 1.

The fsubGroup Click Event code now contains the code you gave me:
"


A subform control does not have a control source; it has a SourceObject
property. The form that is the SourceObject has a RecordSource property. I
assume that this latter term is what you meant?

I think that you can solve your problem if you put the combo box in the
header section and leave it unbound (its control source should be empty).
--

Ken Snell
<MS ACCESS MVP>

«Or do you want to use the combo box as a "navigation" device to move the
subform to a desired record?»

That's the plan.

I tried inserting your code both in the After_Update of the subform and of
the Item combobox, with a Stop immediately before the rs.MoMatch line so
that
I could check what I was getting in that Item value -- surprise (to me
anyway) when I click on a row below the top one the code doesn't stop.
Hmmmmm. At least I know now what I am NOT doing right <g>.

Needless to say -- help still needed! (had a lovely time reading a novel
Friday <g>)
--
Dave
Temping with Staffmark
in Rock Hill, SC

I'm not completely sure I understand what you're seeing, but if you're
running the code (which is slightly erroneous if you want to move only if
a
record is found) you show in the AfterUpdate event of the combo box, then
the subform will "move" to the first record that matches the value found
in
the current record's Item field control (is that the name of the
combobox?).

Do you want the combo box to be used just for selecting a value for the
Item_ID field in the current record? Or do you want to use the combo box
as
a "navigation" device to move the subform to a desired record?

FYI -- Corrected code to move to a record (note use of NoMatch property,
not
EOF property):

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

--

Ken Snell
<MS ACCESS MVP>

«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which is
Item,
the
text field containing the Item Names, the Combobox comes up empty. Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header? form
detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After Update
for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

.. but it didn't help.
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

You're saying that the combobox moves to the first value in the
dropdown
list whenever you select a value in the dropdown list? Is the combo
box
bound to a field? Is the combo box locked? Where in the subform is the
combo
box located -- form header? form detail? somewhere else?
--

Ken Snell
<MS ACCESS MVP>



This appears to be my final frontier on the current project. All
other
threads relating to it are now closed.

My data which displays in the Items Combobox of the Group Subform
is,
finally, the right data. The problem is that when I click on an item
in
the
expanded combobox the pointer immediately jumps back tot he first
displayed
record.

The Combobox has the controlling table of the subform as its control
source,
and displays a single field from that table. I have checked and
there
are
no
dangling Events that could be causing this.

Anyone got any ideas where I should be looking to stop this?

 

[Guest]

It has all been hugely helpful to me. However moving the comboboxes up into
the Header resulted in them not appearing on the main form/sub form anywhere.

In the end I decided to start over with the Wizard, and then hack in with
some of the toys I had built in the previous mess. Took about an hour and I
have the whole thing working. However, in the process I have learned a whole
lot more than I ever would have done had I not passed along the road less
travelled!

Thanks again for ALL your help!!!
--
Dave
Temping with Staffmark
in Rock Hill, SC

I just noticed that you had used Recordset.Clone in your code, and I copied
it without noticing. This is a better approach, using RecordsetClone:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub

If you want to use the combo box for navigation, then it needs to be in the
Header section of the form (subform), not the Detail section. And it should
not be bound to any field.

In a subsequent post, you mentioned the following:

"
Next I have made the control Source for the fsubGroup Subform into a query,
qrtQuery1:

SELECT tblItem.idsItem_ID, tblItem.chrItem, tblItem.hlkReference_Address,
tblItem.idrCompany_ID FROM tblItem;

The Control Source for cboItem on fsubGroup is chrItem, and the Row Source
is qryQuery1, Bound To Column 1.

The fsubGroup Click Event code now contains the code you gave me:
"


A subform control does not have a control source; it has a SourceObject
property. The form that is the SourceObject has a RecordSource property. I
assume that this latter term is what you meant?

I think that you can solve your problem if you put the combo box in the
header section and leave it unbound (its control source should be empty).
--

Ken Snell
<MS ACCESS MVP>

«Or do you want to use the combo box as a "navigation" device to move the
subform to a desired record?»

That's the plan.

I tried inserting your code both in the After_Update of the subform and of
the Item combobox, with a Stop immediately before the rs.MoMatch line so
that
I could check what I was getting in that Item value -- surprise (to me
anyway) when I click on a row below the top one the code doesn't stop.
Hmmmmm. At least I know now what I am NOT doing right <g>.

Needless to say -- help still needed! (had a lovely time reading a novel
Friday <g>)
--
Dave
Temping with Staffmark
in Rock Hill, SC

I'm not completely sure I understand what you're seeing, but if you're
running the code (which is slightly erroneous if you want to move only if
a
record is found) you show in the AfterUpdate event of the combo box, then
the subform will "move" to the first record that matches the value found
in
the current record's Item field control (is that the name of the
combobox?).

Do you want the combo box to be used just for selecting a value for the
Item_ID field in the current record? Or do you want to use the combo box
as
a "navigation" device to move the subform to a desired record?

FYI -- Corrected code to move to a record (note use of NoMatch property,
not
EOF property):

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

--

Ken Snell
<MS ACCESS MVP>


«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which is
Item,
the
text field containing the Item Names, the Combobox comes up empty. Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header? form
detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After Update
for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

.. but it didn't help.
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

You're saying that the combobox moves to the first value in the
dropdown
list whenever you select a value in the dropdown list? Is the combo
box
bound to a field? Is the combo box locked? Where in the subform is the
combo
box located -- form header? form detail? somewhere else?
--

Ken Snell
<MS ACCESS MVP>



This appears to be my final frontier on the current project. All
other
threads relating to it are now closed.

My data which displays in the Items Combobox of the Group Subform
is,
finally, the right data. The problem is that when I click on an item
in
the
expanded combobox the pointer immediately jumps back tot he first
displayed
record.

The Combobox has the controlling table of the subform as its control
source,
and displays a single field from that table. I have checked and
there
are
no
dangling Events that could be causing this.

Anyone got any ideas where I should be looking to stop this?

 

[Ken Snell \(MVP\)]

Did you put the combo box in the Form Header, not the Page Header? Have to
put it in the Form Header.
--

Ken Snell
<MS ACCESS MVP>

It has all been hugely helpful to me. However moving the comboboxes up
into
the Header resulted in them not appearing on the main form/sub form
anywhere.

In the end I decided to start over with the Wizard, and then hack in with
some of the toys I had built in the previous mess. Took about an hour and
I
have the whole thing working. However, in the process I have learned a
whole
lot more than I ever would have done had I not passed along the road less
travelled!

Thanks again for ALL your help!!!
--
Dave
Temping with Staffmark
in Rock Hill, SC

I just noticed that you had used Recordset.Clone in your code, and I
copied
it without noticing. This is a better approach, using RecordsetClone:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub

If you want to use the combo box for navigation, then it needs to be in
the
Header section of the form (subform), not the Detail section. And it
should
not be bound to any field.

In a subsequent post, you mentioned the following:

"
Next I have made the control Source for the fsubGroup Subform into a
query,
qrtQuery1:

SELECT tblItem.idsItem_ID, tblItem.chrItem, tblItem.hlkReference_Address,
tblItem.idrCompany_ID FROM tblItem;

The Control Source for cboItem on fsubGroup is chrItem, and the Row
Source
is qryQuery1, Bound To Column 1.

The fsubGroup Click Event code now contains the code you gave me:
"


A subform control does not have a control source; it has a SourceObject
property. The form that is the SourceObject has a RecordSource property.
I
assume that this latter term is what you meant?

I think that you can solve your problem if you put the combo box in the
header section and leave it unbound (its control source should be empty).
--

Ken Snell
<MS ACCESS MVP>

«Or do you want to use the combo box as a "navigation" device to move
the
subform to a desired record?»

That's the plan.

I tried inserting your code both in the After_Update of the subform and
of
the Item combobox, with a Stop immediately before the rs.MoMatch line
so
that
I could check what I was getting in that Item value -- surprise (to me
anyway) when I click on a row below the top one the code doesn't stop.
Hmmmmm. At least I know now what I am NOT doing right <g>.

Needless to say -- help still needed! (had a lovely time reading a
novel
Friday <g>)
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

I'm not completely sure I understand what you're seeing, but if you're
running the code (which is slightly erroneous if you want to move only
if
a
record is found) you show in the AfterUpdate event of the combo box,
then
the subform will "move" to the first record that matches the value
found
in
the current record's Item field control (is that the name of the
combobox?).

Do you want the combo box to be used just for selecting a value for
the
Item_ID field in the current record? Or do you want to use the combo
box
as
a "navigation" device to move the subform to a desired record?

FYI -- Corrected code to move to a record (note use of NoMatch
property,
not
EOF property):

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

--

Ken Snell
<MS ACCESS MVP>


«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which is
Item,
the
text field containing the Item Names, the Combobox comes up empty.
Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header? form
detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After Update
for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

.. but it didn't help.
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

You're saying that the combobox moves to the first value in the
dropdown
list whenever you select a value in the dropdown list? Is the combo
box
bound to a field? Is the combo box locked? Where in the subform is
the
combo
box located -- form header? form detail? somewhere else?
--

Ken Snell
<MS ACCESS MVP>



message
This appears to be my final frontier on the current project. All
other
threads relating to it are now closed.

My data which displays in the Items Combobox of the Group Subform
is,
finally, the right data. The problem is that when I click on an
item
in
the
expanded combobox the pointer immediately jumps back tot he first
displayed
record.

The Combobox has the controlling table of the subform as its
control
source,
and displays a single field from that table. I have checked and
there
are
no
dangling Events that could be causing this.

Anyone got any ideas where I should be looking to stop this?

 

[Guest]

Doggondest thing I ever did see! When I tried your idea, I did put it in the
Header section of the Sub-form, and it was invisible when run as I mentioned.
This time I tried putting it in the Header section of the Main Form. Yeah,
Right! Dumb Idea, but anything for a giggle. Obviously that didn't work. So I
did a few Ctrl-Z's, but, having put it up there using the clipboard, I still
had it on the clipboard, and the last Ctrl-Z didn't restore it into the
Sub-form. So I pasted it back onto it's old stamping ground, the Sub-form.

Then just for giggles I ran the form again to make sure it still behaved as
before. And it didn't. Now, for no explainable reason, the pointer sticks
when I select a new row in the Items combo, and life is generally beautiful.

Of course, yesterday I took the new form I had re-build with the Wizard and
fancied it up with pretty pictures and other useless crap to make the boss
think I had actually been doing some real work, so this one is now not
needed. But all I can say at this point is -- once more and forever -- thanks
for being my guiding muse!!!!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Did you put the combo box in the Form Header, not the Page Header? Have to
put it in the Form Header.
--

Ken Snell
<MS ACCESS MVP>

It has all been hugely helpful to me. However moving the comboboxes up
into
the Header resulted in them not appearing on the main form/sub form
anywhere.

In the end I decided to start over with the Wizard, and then hack in with
some of the toys I had built in the previous mess. Took about an hour and
I
have the whole thing working. However, in the process I have learned a
whole
lot more than I ever would have done had I not passed along the road less
travelled!

Thanks again for ALL your help!!!
--
Dave
Temping with Staffmark
in Rock Hill, SC

I just noticed that you had used Recordset.Clone in your code, and I
copied
it without noticing. This is a better approach, using RecordsetClone:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub

If you want to use the combo box for navigation, then it needs to be in
the
Header section of the form (subform), not the Detail section. And it
should
not be bound to any field.

In a subsequent post, you mentioned the following:

"
Next I have made the control Source for the fsubGroup Subform into a
query,
qrtQuery1:

SELECT tblItem.idsItem_ID, tblItem.chrItem, tblItem.hlkReference_Address,
tblItem.idrCompany_ID FROM tblItem;

The Control Source for cboItem on fsubGroup is chrItem, and the Row
Source
is qryQuery1, Bound To Column 1.

The fsubGroup Click Event code now contains the code you gave me:
"


A subform control does not have a control source; it has a SourceObject
property. The form that is the SourceObject has a RecordSource property.
I
assume that this latter term is what you meant?

I think that you can solve your problem if you put the combo box in the
header section and leave it unbound (its control source should be empty).
--

Ken Snell
<MS ACCESS MVP>




«Or do you want to use the combo box as a "navigation" device to move
the
subform to a desired record?»

That's the plan.

I tried inserting your code both in the After_Update of the subform and
of
the Item combobox, with a Stop immediately before the rs.MoMatch line
so
that
I could check what I was getting in that Item value -- surprise (to me
anyway) when I click on a row below the top one the code doesn't stop.
Hmmmmm. At least I know now what I am NOT doing right <g>.

Needless to say -- help still needed! (had a lovely time reading a
novel
Friday <g>)
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

I'm not completely sure I understand what you're seeing, but if you're
running the code (which is slightly erroneous if you want to move only
if
a
record is found) you show in the AfterUpdate event of the combo box,
then
the subform will "move" to the first record that matches the value
found
in
the current record's Item field control (is that the name of the
combobox?).

Do you want the combo box to be used just for selecting a value for
the
Item_ID field in the current record? Or do you want to use the combo
box
as
a "navigation" device to move the subform to a desired record?

FYI -- Corrected code to move to a record (note use of NoMatch
property,
not
EOF property):

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

--

Ken Snell
<MS ACCESS MVP>


«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which is
Item,
the
text field containing the Item Names, the Combobox comes up empty.
Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header? form
detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After Update
for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

.. but it didn't help.
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

You're saying that the combobox moves to the first value in the
dropdown
list whenever you select a value in the dropdown list? Is the combo
box
bound to a field? Is the combo box locked? Where in the subform is
the
combo
box located -- form header? form detail? somewhere else?
--

Ken Snell
<MS ACCESS MVP>



message
This appears to be my final frontier on the current project. All
other
threads relating to it are now closed.

My data which displays in the Items Combobox of the Group Subform
is,
finally, the right data. The problem is that when I click on an
item
in
the
expanded combobox the pointer immediately jumps back tot he first
displayed
record.

The Combobox has the controlling table of the subform as its
control
source,
and displays a single field from that table. I have checked and
there
are
no
dangling Events that could be causing this.

Anyone got any ideas where I should be looking to stop this?

 

[Ken Snell \(MVP\)]

Can't take credit for what happened. I did indeed mean for you to put the
combo box in the subform's Form Header section, not the main form's Form
Header section.

But glad you have things working now!

--

Ken Snell
<MS ACCESS MVP>

Doggondest thing I ever did see! When I tried your idea, I did put it in
the
Header section of the Sub-form, and it was invisible when run as I
mentioned.
This time I tried putting it in the Header section of the Main Form. Yeah,
Right! Dumb Idea, but anything for a giggle. Obviously that didn't work.
So I
did a few Ctrl-Z's, but, having put it up there using the clipboard, I
still
had it on the clipboard, and the last Ctrl-Z didn't restore it into the
Sub-form. So I pasted it back onto it's old stamping ground, the Sub-form.

Then just for giggles I ran the form again to make sure it still behaved
as
before. And it didn't. Now, for no explainable reason, the pointer sticks
when I select a new row in the Items combo, and life is generally
beautiful.

Of course, yesterday I took the new form I had re-build with the Wizard
and
fancied it up with pretty pictures and other useless crap to make the boss
think I had actually been doing some real work, so this one is now not
needed. But all I can say at this point is -- once more and forever --
thanks
for being my guiding muse!!!!
--
Dave
Temping with Staffmark
in Rock Hill, SC

Did you put the combo box in the Form Header, not the Page Header? Have
to
put it in the Form Header.
--

Ken Snell
<MS ACCESS MVP>

It has all been hugely helpful to me. However moving the comboboxes up
into
the Header resulted in them not appearing on the main form/sub form
anywhere.

In the end I decided to start over with the Wizard, and then hack in
with
some of the toys I had built in the previous mess. Took about an hour
and
I
have the whole thing working. However, in the process I have learned a
whole
lot more than I ever would have done had I not passed along the road
less
travelled!

Thanks again for ALL your help!!!
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

I just noticed that you had used Recordset.Clone in your code, and I
copied
it without noticing. This is a better approach, using RecordsetClone:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.RecordsetClone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark
Set rs = Nothing
End Sub

If you want to use the combo box for navigation, then it needs to be
in
the
Header section of the form (subform), not the Detail section. And it
should
not be bound to any field.

In a subsequent post, you mentioned the following:

"
Next I have made the control Source for the fsubGroup Subform into a
query,
qrtQuery1:

SELECT tblItem.idsItem_ID, tblItem.chrItem,
tblItem.hlkReference_Address,
tblItem.idrCompany_ID FROM tblItem;

The Control Source for cboItem on fsubGroup is chrItem, and the Row
Source
is qryQuery1, Bound To Column 1.

The fsubGroup Click Event code now contains the code you gave me:
"


A subform control does not have a control source; it has a
SourceObject
property. The form that is the SourceObject has a RecordSource
property.
I
assume that this latter term is what you meant?

I think that you can solve your problem if you put the combo box in
the
header section and leave it unbound (its control source should be
empty).
--

Ken Snell
<MS ACCESS MVP>




«Or do you want to use the combo box as a "navigation" device to
move
the
subform to a desired record?»

That's the plan.

I tried inserting your code both in the After_Update of the subform
and
of
the Item combobox, with a Stop immediately before the rs.MoMatch
line
so
that
I could check what I was getting in that Item value -- surprise (to
me
anyway) when I click on a row below the top one the code doesn't
stop.
Hmmmmm. At least I know now what I am NOT doing right <g>.

Needless to say -- help still needed! (had a lovely time reading a
novel
Friday <g>)
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

I'm not completely sure I understand what you're seeing, but if
you're
running the code (which is slightly erroneous if you want to move
only
if
a
record is found) you show in the AfterUpdate event of the combo
box,
then
the subform will "move" to the first record that matches the value
found
in
the current record's Item field control (is that the name of the
combobox?).

Do you want the combo box to be used just for selecting a value for
the
Item_ID field in the current record? Or do you want to use the
combo
box
as
a "navigation" device to move the subform to a desired record?

FYI -- Corrected code to move to a record (note use of NoMatch
property,
not
EOF property):

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

End Sub

--

Ken Snell
<MS ACCESS MVP>


message
«Is the combo box bound to a field?»
Yes. Field 1, which is Item_ID. When I bind it to field 4 which
is
Item,
the
text field containing the Item Names, the Combobox comes up
empty.
Only
binding it to field 1 results in it being populated.

«Is the combo box locked?»
No

«Where in the subform is the combo box located -- form header?
form
detail?
somewhere else?»
Form Detail

I thought overnight that perhaps what was needed was an After
Update
for
this li'l pup, so I added this to the Combobox:

Private Sub Item_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Item_ID] = " & Str(Nz(Me![Item], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

.. but it didn't help.
--
Dave
Temping with Staffmark
in Rock Hill, SC


:

You're saying that the combobox moves to the first value in the
dropdown
list whenever you select a value in the dropdown list? Is the
combo
box
bound to a field? Is the combo box locked? Where in the subform
is
the
combo
box located -- form header? form detail? somewhere else?
--

Ken Snell
<MS ACCESS MVP>



message
This appears to be my final frontier on the current project.
All
other
threads relating to it are now closed.

My data which displays in the Items Combobox of the Group
Subform
is,
finally, the right data. The problem is that when I click on
an
item
in
the
expanded combobox the pointer immediately jumps back tot he
first
displayed
record.

The Combobox has the controlling table of the subform as its
control
source,
and displays a single field from that table. I have checked
and
there
are
no
dangling Events that could be causing this.

Anyone got any ideas where I should be looking to stop this?