paste a value into a combo box instead of typing it in

[Bill R via AccessMonster.com]

My users often use other applications (Excel, proprietary apps on the server,
etc.) to do their work. They work with drawings that have numbers
("123XYZ45678") as identifiers. They are used to using a cmd button that
opens the ctl F find dialog and then pasting the # in there to find the
drawing.

I have a combo box on a form to find drawings by drawing number in my db.
It's a common technique. It's rowsource is a query that returns a key id,
drawing #, and title. The 1st field is hidden and is used as the value to
search for in the field "txtDwgKey". When users type in a dwg # it works fine.
The code executes and finds the key id in txtDwgKey and goes to the record.
However, if they copy the drawing # ("123XYZ45678") from Excel or something,
and paste it into the combo and hit enter, nothing happens. I would like to
give them the same functionality using my combo box as they have using Ctl F.
How do I make that work?

Bill

 

[Guest]

Hi Bill,

I think this will work (tho' I didn't test it...):

Change the query source for your drop-down list so that the keyID field is
2nd, and the drawing# is the first field. Also make sure yoiu change the
'Bound Column' on the combo box's properties to column 2, and change the
column widths so column 1 has a non-zero width & column 2 a zero width. You
should then be able to copy & paste.

Helpful?

 

[Ken Snell [MVP]]

Changing the combo box's setup this way is not recommended...and I don't
believe it will be beneficial.

Are you running code using some event of the combo box? If yes, which one?
Post the code that you're using, too.

 

[Guest]

Hi Ken,

I'm fascinated by your response to my suggestion - and always willing to
learn! My normal practice is to put the display field in cloumn 0 in a combo,
and the bound field (if it's not the display field, as in this example) in a
later column, usually with zero width as the client doesn't need to see it.
[I'm using the VBA column count here - ie zero based - unlike in my original
posting]

I have to admit that the idea that there could be some code running hadn't
occurred to me!

--
Laury Burr a.k.a. Doogle
data dot dynamics at virgin dot net

Changing the combo box's setup this way is not recommended...and I don't
believe it will be beneficial.

Are you running code using some event of the combo box? If yes, which one?
Post the code that you're using, too.

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

I did not mean to say your setup is invalid... a more conventional approach
for a combo box is to have the Bound Column be the first column, and to make
its width zero if it's not to be displayed in the dropdown list and it's not
to be the value that is entered into the combo box by the user. You can
setup a combo box the way you describe (it's not wrong! < g >), but it
doesn't impact the behavior that the original poster describes.


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I'm fascinated by your response to my suggestion - and always willing to
learn! My normal practice is to put the display field in cloumn 0 in a
combo,
and the bound field (if it's not the display field, as in this example) in
a
later column, usually with zero width as the client doesn't need to see
it.
[I'm using the VBA column count here - ie zero based - unlike in my
original
posting]

I have to admit that the idea that there could be some code running hadn't
occurred to me!

 

[Guest]

Thanks Ken!

I didn't even realise that merely setting col1 to zero width would result in
column 2 being displayed!! Just proves we're never too old to learn (even
after 10 years of Access from 2 to 2003!!)

All the best
--
Laury Burr a.k.a. Doogle

I did not mean to say your setup is invalid... a more conventional approach
for a combo box is to have the Bound Column be the first column, and to make
its width zero if it's not to be displayed in the dropdown list and it's not
to be the value that is entered into the combo box by the user. You can
setup a combo box the way you describe (it's not wrong! < g >), but it
doesn't impact the behavior that the original poster describes.


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I'm fascinated by your response to my suggestion - and always willing to
learn! My normal practice is to put the display field in cloumn 0 in a
combo,
and the bound field (if it's not the display field, as in this example) in
a
later column, usually with zero width as the client doesn't need to see
it.
[I'm using the VBA column count here - ie zero based - unlike in my
original
posting]

I have to admit that the idea that there could be some code running hadn't
occurred to me!

--
Laury Burr a.k.a. Doogle
data dot dynamics at virgin dot net

Changing the combo box's setup this way is not recommended...and I don't
believe it will be beneficial.

Are you running code using some event of the combo box? If yes, which
one?
Post the code that you're using, too.

--

Ken Snell
<MS ACCESS MVP>


Hi Bill,

I think this will work (tho' I didn't test it...):

Change the query source for your drop-down list so that the keyID field
is
2nd, and the drawing# is the first field. Also make sure yoiu change
the
'Bound Column' on the combo box's properties to column 2, and change
the
column widths so column 1 has a non-zero width & column 2 a zero width.
You
should then be able to copy & paste.

Helpful?

--
Laury Burr a.k.a. Doogle


:


My users often use other applications (Excel, proprietary apps on the
server,
etc.) to do their work. They work with drawings that have numbers
("123XYZ45678") as identifiers. They are used to using a cmd button
that
opens the ctl F find dialog and then pasting the # in there to find
the
drawing.

I have a combo box on a form to find drawings by drawing number in my
db.
It's a common technique. It's rowsource is a query that returns a key
id,
drawing #, and title. The 1st field is hidden and is used as the value
to
search for in the field "txtDwgKey". When users type in a dwg # it
works
fine.
The code executes and finds the key id in txtDwgKey and goes to the
record.
However, if they copy the drawing # ("123XYZ45678") from Excel or
something,
and paste it into the combo and hit enter, nothing happens. I would
like
to
give them the same functionality using my combo box as they have using
Ctl F.
How do I make that work?

Bill

 

[Ken Snell [MVP]]

Just FYI... when I have multiple columns in a combo box or list box, I
always specify a width value for each column... never trust ACCESS (or other
software) to have a default to do what I specifically want!
< g >

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken!

I didn't even realise that merely setting col1 to zero width would result
in
column 2 being displayed!! Just proves we're never too old to learn (even
after 10 years of Access from 2 to 2003!!)

All the best
--
Laury Burr a.k.a. Doogle

I did not mean to say your setup is invalid... a more conventional
approach
for a combo box is to have the Bound Column be the first column, and to
make
its width zero if it's not to be displayed in the dropdown list and it's
not
to be the value that is entered into the combo box by the user. You can
setup a combo box the way you describe (it's not wrong! < g >), but it
doesn't impact the behavior that the original poster describes.


--

Ken Snell
<MS ACCESS MVP>

Hi Ken,

I'm fascinated by your response to my suggestion - and always willing
to
learn! My normal practice is to put the display field in cloumn 0 in a
combo,
and the bound field (if it's not the display field, as in this example)
in
a
later column, usually with zero width as the client doesn't need to see
it.
[I'm using the VBA column count here - ie zero based - unlike in my
original
posting]

I have to admit that the idea that there could be some code running
hadn't
occurred to me!

--
Laury Burr a.k.a. Doogle
data dot dynamics at virgin dot net


:

Changing the combo box's setup this way is not recommended...and I
don't
believe it will be beneficial.

Are you running code using some event of the combo box? If yes, which
one?
Post the code that you're using, too.

--

Ken Snell
<MS ACCESS MVP>


Hi Bill,

I think this will work (tho' I didn't test it...):

Change the query source for your drop-down list so that the keyID
field
is
2nd, and the drawing# is the first field. Also make sure yoiu change
the
'Bound Column' on the combo box's properties to column 2, and change
the
column widths so column 1 has a non-zero width & column 2 a zero
width.
You
should then be able to copy & paste.

Helpful?

--
Laury Burr a.k.a. Doogle


:


My users often use other applications (Excel, proprietary apps on
the
server,
etc.) to do their work. They work with drawings that have numbers
("123XYZ45678") as identifiers. They are used to using a cmd button
that
opens the ctl F find dialog and then pasting the # in there to find
the
drawing.

I have a combo box on a form to find drawings by drawing number in
my
db.
It's a common technique. It's rowsource is a query that returns a
key
id,
drawing #, and title. The 1st field is hidden and is used as the
value
to
search for in the field "txtDwgKey". When users type in a dwg # it
works
fine.
The code executes and finds the key id in txtDwgKey and goes to the
record.
However, if they copy the drawing # ("123XYZ45678") from Excel or
something,
and paste it into the combo and hit enter, nothing happens. I would
like
to
give them the same functionality using my combo box as they have
using
Ctl F.
How do I make that work?

Bill

 

[Guest]

Ken,

Many thanks - I couldn't agree more!!

Laury
--
Laury Burr a.k.a. Doogle

Just FYI... when I have multiple columns in a combo box or list box, I
always specify a width value for each column... never trust ACCESS (or other
software) to have a default to do what I specifically want!
< g >

--

Ken Snell
<MS ACCESS MVP>

Thanks Ken!

I didn't even realise that merely setting col1 to zero width would result
in
column 2 being displayed!! Just proves we're never too old to learn (even
after 10 years of Access from 2 to 2003!!)

All the best
--
Laury Burr a.k.a. Doogle

I did not mean to say your setup is invalid... a more conventional
approach
for a combo box is to have the Bound Column be the first column, and to
make
its width zero if it's not to be displayed in the dropdown list and it's
not
to be the value that is entered into the combo box by the user. You can
setup a combo box the way you describe (it's not wrong! < g >), but it
doesn't impact the behavior that the original poster describes.


--

Ken Snell
<MS ACCESS MVP>





Hi Ken,

I'm fascinated by your response to my suggestion - and always willing
to
learn! My normal practice is to put the display field in cloumn 0 in a
combo,
and the bound field (if it's not the display field, as in this example)
in
a
later column, usually with zero width as the client doesn't need to see
it.
[I'm using the VBA column count here - ie zero based - unlike in my
original
posting]

I have to admit that the idea that there could be some code running
hadn't
occurred to me!

--
Laury Burr a.k.a. Doogle
data dot dynamics at virgin dot net


:

Changing the combo box's setup this way is not recommended...and I
don't
believe it will be beneficial.

Are you running code using some event of the combo box? If yes, which
one?
Post the code that you're using, too.

--

Ken Snell
<MS ACCESS MVP>


Hi Bill,

I think this will work (tho' I didn't test it...):

Change the query source for your drop-down list so that the keyID
field
is
2nd, and the drawing# is the first field. Also make sure yoiu change
the
'Bound Column' on the combo box's properties to column 2, and change
the
column widths so column 1 has a non-zero width & column 2 a zero
width.
You
should then be able to copy & paste.

Helpful?

--
Laury Burr a.k.a. Doogle


:


My users often use other applications (Excel, proprietary apps on
the
server,
etc.) to do their work. They work with drawings that have numbers
("123XYZ45678") as identifiers. They are used to using a cmd button
that
opens the ctl F find dialog and then pasting the # in there to find
the
drawing.

I have a combo box on a form to find drawings by drawing number in
my
db.
It's a common technique. It's rowsource is a query that returns a
key
id,
drawing #, and title. The 1st field is hidden and is used as the
value
to
search for in the field "txtDwgKey". When users type in a dwg # it
works
fine.
The code executes and finds the key id in txtDwgKey and goes to the
record.
However, if they copy the drawing # ("123XYZ45678") from Excel or
something,
and paste it into the combo and hit enter, nothing happens. I would
like
to
give them the same functionality using my combo box as they have
using
Ctl F.
How do I make that work?

Bill

 

[Bill R via AccessMonster.com]

Sorry it took so long to get back to this post. You and Doogle have been
having a lively conversation in my absence. ;-)

The code in the afterupdate event of the combo is "off the shelf":

Private Sub cmbFindSpec_AfterUpdate()
With DoCmd
.GoToControl "txtSPECID"
.FindRecord Me.cmbFindSpec
.GoToControl "cmbFindSpec"
End With
End Sub

So, do you suppose the code is the problem, or should I follow Doogle's
recommendation (I haven't tried it yet).

Thanks,

Bill

Changing the combo box's setup this way is not recommended...and I don't
believe it will be beneficial.

Are you running code using some event of the combo box? If yes, which one?
Post the code that you're using, too.

Hi Bill,

[quoted text clipped - 32 lines]

 

[Ken Snell [MVP]]

I must admit that I am not familiar with the code structure that you posted
for doing this. Here is how I would do what you're doing (assuming that
SPECID is the name of the field that is bound to txtSPECID, and assuming
that SPECID is a numeric field):

Private Sub cmbFindSpec_AfterUpdate()
With Me.RecordsetClone
.FindFirst "SPECID=" & Me.cmbFindSpec
If .NoMatch = True Then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub


--

Ken Snell
<MS ACCESS MVP>

Sorry it took so long to get back to this post. You and Doogle have been
having a lively conversation in my absence. ;-)

The code in the afterupdate event of the combo is "off the shelf":

Private Sub cmbFindSpec_AfterUpdate()
With DoCmd
.GoToControl "txtSPECID"
.FindRecord Me.cmbFindSpec
.GoToControl "cmbFindSpec"
End With
End Sub

So, do you suppose the code is the problem, or should I follow Doogle's
recommendation (I haven't tried it yet).

Thanks,

Bill

Changing the combo box's setup this way is not recommended...and I don't
believe it will be beneficial.

Are you running code using some event of the combo box? If yes, which one?
Post the code that you're using, too.

Hi Bill,

[quoted text clipped - 32 lines]

Bill

 

[Guest]

Hi Ken,

Interesting. I'm more familiar with Bill's coding but I'm noting yours for
future reference. Although aware of bookmarking it's something I've not
really investigated, but I guess it'll be important when I finally get round
to SQL Server etc.

As I've said, it's never too late too learn!

And hello Bill - I apologise for hijacking your posting!!

All the best to you both
--
Laury Burr a.k.a. Doogle

I must admit that I am not familiar with the code structure that you posted
for doing this. Here is how I would do what you're doing (assuming that
SPECID is the name of the field that is bound to txtSPECID, and assuming
that SPECID is a numeric field):

Private Sub cmbFindSpec_AfterUpdate()
With Me.RecordsetClone
.FindFirst "SPECID=" & Me.cmbFindSpec
If .NoMatch = True Then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub


--

Ken Snell
<MS ACCESS MVP>

Sorry it took so long to get back to this post. You and Doogle have been
having a lively conversation in my absence. ;-)

The code in the afterupdate event of the combo is "off the shelf":

Private Sub cmbFindSpec_AfterUpdate()
With DoCmd
.GoToControl "txtSPECID"
.FindRecord Me.cmbFindSpec
.GoToControl "cmbFindSpec"
End With
End Sub

So, do you suppose the code is the problem, or should I follow Doogle's
recommendation (I haven't tried it yet).

Thanks,

Bill

Changing the combo box's setup this way is not recommended...and I don't
believe it will be beneficial.

Are you running code using some event of the combo box? If yes, which one?
Post the code that you're using, too.

Hi Bill,

[quoted text clipped - 32 lines]

Bill

 

[Bill R via AccessMonster.com]

I'm having no trouble at all finding the record in the combo box on the after
update event of the combo box. It goes right to the correct txt box and finds
the record.

Then entire subject of this post is to find a way to paste in a string that
in all likelihood WILL match an entry in field 2 of the combo box's row
source. As it stands, pasting a value in there, whether it has a legitimate
match in the list or not, results in failure (as in, absolutely nothing
happens). Will Doogle's suggestion work? I.E., make the bound column the 2nd
column, move the id field over to the 2nd field of the query, and set it's
width to 0.

Thanks,

Bill

I must admit that I am not familiar with the code structure that you posted
for doing this. Here is how I would do what you're doing (assuming that
SPECID is the name of the field that is bound to txtSPECID, and assuming
that SPECID is a numeric field):

Private Sub cmbFindSpec_AfterUpdate()
With Me.RecordsetClone
.FindFirst "SPECID=" & Me.cmbFindSpec
If .NoMatch = True Then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

Sorry it took so long to get back to this post. You and Doogle have been
having a lively conversation in my absence. ;-)

[quoted text clipped - 27 lines]

 

[Ken Snell [MVP]]

Bill-

What I am understanding from your post is that the Row Source of the combo
box contains these columns:
PrimaryKeyID <--- Bound Column
DrawingNumber
DrawingTitle

In this setup, whether one selects a drawing number from the dropdown list,
types in a valid drawing number manually, or pastes a valid drawing number
into the comobox directly, the combobox's Value will be the PrimaryKeyID
value of the row that contains the entered/selected drawing number, and the
AfterUpdate code will work for any of these situations.

Doogle's code does not affect what the combobox will show in the dropdown
list nor how the combobox matches up entered values to a row in its Row
Source. It just changes the setup regarding the location of the PrimaryKeyID
column in the Row Source (instead of being the first (zero) column, it'll
the be the second (one) column -- it still is the bound column and thus it
dictates the actual value of the combo box).

If you're not having success when the user pastes a drawing number into the
combobox, I will guess that it's because there are extraneous characters in
the text string from the EXCEL cell that prevent a match. For example, if
the EXCEL sheet's cell is using "Alt+Enter" character (actually the LineFeed
character) to make multiple lines in the cell, this will prevent the correct
matching of the drawing number in the combo box. Or that there are blank
spaces in beginning or at end of string in the EXCEL cell.

Does the combo box work correctly if you type in a drawing number instead of
selecting it from the dropdown list? If yes, then I am even more sure that
the problem is the EXCEL string that is being captured.

Do you have the LimitToList property of the combobox set to Yes? If not, set
it to that value and then paste your EXCEL string into the box. If there is
no match to the combobox's list of drawing numbers, you'll get an error
message from ACCESS.

Double check that what is being pasted into the combo box actually IS a
valid drawing number per the combobox's Row Source results.

--

Ken Snell
<MS ACCESS MVP>

I'm having no trouble at all finding the record in the combo box on the
after
update event of the combo box. It goes right to the correct txt box and
finds
the record.

Then entire subject of this post is to find a way to paste in a string
that
in all likelihood WILL match an entry in field 2 of the combo box's row
source. As it stands, pasting a value in there, whether it has a
legitimate
match in the list or not, results in failure (as in, absolutely nothing
happens). Will Doogle's suggestion work? I.E., make the bound column the
2nd
column, move the id field over to the 2nd field of the query, and set it's
width to 0.

Thanks,

Bill

I must admit that I am not familiar with the code structure that you
posted
for doing this. Here is how I would do what you're doing (assuming that
SPECID is the name of the field that is bound to txtSPECID, and assuming
that SPECID is a numeric field):

Private Sub cmbFindSpec_AfterUpdate()
With Me.RecordsetClone
.FindFirst "SPECID=" & Me.cmbFindSpec
If .NoMatch = True Then
MsgBox "Record not found"
Else
Me.Bookmark = .Bookmark
End If
End With
End Sub

Sorry it took so long to get back to this post. You and Doogle have been
having a lively conversation in my absence. ;-)

[quoted text clipped - 27 lines]

Bill