Prevent combo box autoexpand for new records

[BruceM]

I have a continuous subform for selecting Part Number (PN). Each PN has a
description. The combo box (cboPN) for selecting PN has the Parts table
(tblPart) as its Row Source. Its autoexpand property is set to Yes. PartID
is the bound field; the next two columns are PN and Description. An unbound
text box (txtDescr) next to cboPN has as its Control Source:

=[cboPN].Column(2)

If the user enters a PN that is not in the list the Not In List event gives
them the opportunity to add a new Part. If they accept they are presented
with an input box for adding the Description. It all works as it should. I
hope this is enough explanation.

When I start to type the PN, autoexpand works as intended. txtDescr shows
the matching matching description as each new possibility appears in . This
is not a big deal, but if it is a new record the text box keeps showing the
latest possible value for the Description, even when the data entry has
reached the point where no parts match. For example, the PN I need to enter
is 123456. PN 1234 is a gear. PN 1111 is a flange. There is no PN 12345
or 123456. When I type the "1", "Flange" is shown in txtDescr, and remains
there until I get to "1234", at which time "Gear" is shown in txtDescr.
When I type "5" there are no more autoexpand suggestions, but txtDescr keeps
showing "Gear".

Users tend to find this confusing. They tell me that "123456" is a pulley,
but it keeps showing as "Gear". Is there a way to prevent this, maybe with
KeyPress code or something that clears txtDescr if there is no match? It is
a continuous form, so it is has been difficult finding an approach that does
not affect all records.

 

[Mike Painter]

I have a continuous subform for selecting Part Number (PN). Each PN
has a description. The combo box (cboPN) for selecting PN has the
Parts table (tblPart) as its Row Source. Its autoexpand property is
set to Yes. PartID is the bound field; the next two columns are PN
and Description. An unbound text box (txtDescr) next to cboPN has as
its Control Source:
=[cboPN].Column(2)

If the user enters a PN that is not in the list the Not In List event
gives them the opportunity to add a new Part. If they accept they
are presented with an input box for adding the Description. It all
works as it should. I hope this is enough explanation.

When I start to type the PN, autoexpand works as intended. txtDescr
shows the matching matching description as each new possibility
appears in . This is not a big deal, but if it is a new record the
text box keeps showing the latest possible value for the Description,
even when the data entry has reached the point where no parts match. For
example, the PN I need to enter is 123456. PN 1234 is a gear. PN 1111 is
a flange. There is no PN 12345 or 123456. When I type
the "1", "Flange" is shown in txtDescr, and remains there until I get
to "1234", at which time "Gear" is shown in txtDescr. When I type "5"
there are no more autoexpand suggestions, but txtDescr keeps showing
"Gear".
Users tend to find this confusing. They tell me that "123456" is a
pulley, but it keeps showing as "Gear". Is there a way to prevent
this, maybe with KeyPress code or something that clears txtDescr if
there is no match? It is a continuous form, so it is has been
difficult finding an approach that does not affect all records.

I don't see any non convoluted way the system could know when there was a
match for just what you are looking for.
The combo box is working as it should.
Since there is no uniform way of determining what a part is (e.g. the first
4 digits) there is no way, without a lot of work for the machine to know
when to switch.

It sounds like your cboPN is one item behind so shows the previous value
until teh second new item comes up.

If the people know what the part number is, it would seem this is the place
to start.
Selecting "flange" from one list and the part number from a second would be
quick, even with a large number of each part.

 

[Marshall Barton]

I have a continuous subform for selecting Part Number (PN). Each PN has a
description. The combo box (cboPN) for selecting PN has the Parts table
(tblPart) as its Row Source. Its autoexpand property is set to Yes. PartID
is the bound field; the next two columns are PN and Description. An unbound
text box (txtDescr) next to cboPN has as its Control Source:

=[cboPN].Column(2)

If the user enters a PN that is not in the list the Not In List event gives
them the opportunity to add a new Part. If they accept they are presented
with an input box for adding the Description. It all works as it should. I
hope this is enough explanation.

When I start to type the PN, autoexpand works as intended. txtDescr shows
the matching matching description as each new possibility appears in . This
is not a big deal, but if it is a new record the text box keeps showing the
latest possible value for the Description, even when the data entry has
reached the point where no parts match. For example, the PN I need to enter
is 123456. PN 1234 is a gear. PN 1111 is a flange. There is no PN 12345
or 123456. When I type the "1", "Flange" is shown in txtDescr, and remains
there until I get to "1234", at which time "Gear" is shown in txtDescr.
When I type "5" there are no more autoexpand suggestions, but txtDescr keeps
showing "Gear".

Use code in the combo box's Change event instead of that
text box expression:

If Me.cboPN.ListIndex >= 0 Then
Me.txtDescr = Me.cboPN.Column(2)
Else
Me.txtDescr = Null
End If

 

[BruceM]

Thanks for the reply. I may not have described the situation very well, but
the code you suggested does not work well for a continuous form that is both
for viewing existing records and entering new ones.

However, it seems I was crawling around lost in a maze of my own making,
when all I had to do was stand up to see the way clear. The subform Record
Source includes the Description field. All I had to do was bind txtDescr to
that field. Now I don't see the part description in txtDescr until after
the combo box is updated.

I have a continuous subform for selecting Part Number (PN). Each PN has a
description. The combo box (cboPN) for selecting PN has the Parts table
(tblPart) as its Row Source. Its autoexpand property is set to Yes.
PartID
is the bound field; the next two columns are PN and Description. An
unbound
text box (txtDescr) next to cboPN has as its Control Source:

=[cboPN].Column(2)

If the user enters a PN that is not in the list the Not In List event
gives
them the opportunity to add a new Part. If they accept they are presented
with an input box for adding the Description. It all works as it should.
I
hope this is enough explanation.

When I start to type the PN, autoexpand works as intended. txtDescr shows
the matching matching description as each new possibility appears in .
This
is not a big deal, but if it is a new record the text box keeps showing
the
latest possible value for the Description, even when the data entry has
reached the point where no parts match. For example, the PN I need to
enter
is 123456. PN 1234 is a gear. PN 1111 is a flange. There is no PN 12345
or 123456. When I type the "1", "Flange" is shown in txtDescr, and
remains
there until I get to "1234", at which time "Gear" is shown in txtDescr.
When I type "5" there are no more autoexpand suggestions, but txtDescr
keeps
showing "Gear".

Use code in the combo box's Change event instead of that
text box expression:

If Me.cboPN.ListIndex >= 0 Then
Me.txtDescr = Me.cboPN.Column(2)
Else
Me.txtDescr = Null
End If