Combo Box

[Deac]

I have ID, First, Middle, and Last names (4) fields
I created a Combo Box for record selection with the following Event
Procedure:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDold] = '" & Me![ContactSelection] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

[ContactSelection] = ""

End Sub


This works fine --- as long as the ID field is a text field.
The database is growing and we need a easy way to select a record
or enter a new record number automatically.
If you change the ID to AutoNumber - Data Type Mismatch!

[Deac]

On Jan 24, 1:05 pm, Deac <mwal...@centurylink.net> wrote:
> I have ID, First, Middle, and Last names (4) fields

In my original post I should have asked for assistance on how to
change the data type, or change the data type to numeric or how
to redo the selection field or process.

Don't know much about Access - just a novice! Can anyone help?

[Patrick Finucane]

In a combo a person might display the First, Middle, and Last name.
Typically tho, if the data is coming from a query or table. you
typically select the ID, First, Middle, and Last in a combo's
rowsource. Then you set the column width of the ID to zero and the
number of columns to 4. Again, the only thing the user sees are the
First, Middle, and Last names and not the ID. But the value of the
combo box, if you set the bound column to 1 and ID is in the first
column, will be the ID..

Check it out. Assuming the combo's name is ComboName, enter the
following code, changing ComboName to your combobox name in the
afterUpdate event of the combo...
Debug.print "Value: " & Me.ComboName 'will be the id
Debug.print "First " & Me.ComboName.Column(0) 'zero based,
Debug.print "Second " & Me.ComboName.Column(1) 'first
Debug.print "Third " & Me.ComboName.Column(2) 'middle
Debug.print "Fourth " & Me.ComboName.Column(3) 'last. 0 based,
this is the 4th item.

[Neil]

"Deac" <mwa...@centurylink.net> wrote in message
news:cf775a6c-d507-4946...@t2g2000yqk.googlegroups.com...

Typically you'd just use two combo boxes - one for searching by name, and
the other for searching by ID. They both can have ID as the bound column in
the combo box, so you can use the same code for both - find based on ID. The
only difference would be what the user would see. In the name search, the ID
column (first column) would be hidden, and they'd only see the name. But the
combo box VALUE would still be the ID. And in the ID search, the ID column
would not be hidden, and there would be two columns (ID and name). But the
ID would still be the combo box value.

If you want to use one combo box for both, it would be a bit tricky, since
you'd have to make the combo box not be limited to the list (and you
wouldn't be able to use a hidden column). So if you want to do that you can.
You'd use IsNumeric to determine if the user entered a number or a name.

Also, your code above will not work with If Not rs.EOF (or, rather, it will
always set Me.Bookmark to rs.Bookmark, rather than only if there's a match).
If FindFirst doesn't find a match, it's not going to move past the end of
the recordset. It's just not going to move at all, if it doesn't find a
matching record. So the recordset cursor will remain where it is, not at
EOF.

What you want to use instead is the NoMatch property of the recordset. That
will tell you whether there's a match or not. So:

Set rs = Me.Recordset.Clone
rs.FindFirst "[IDold] = '" & Me![ContactSelection] & "'"

If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

To have it work with either name or ID entries:

Set rs = Me.Recordset.Clone
If IsNumeric(Me![ContactSelection]) then
rs.FindFirst "[ID] = " & Me![ContactSelection]
Else

rs.FindFirst "[IDold] = '" & Me![ContactSelection] & "'"

End If
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

(I'm assuming in the above that [ID] is the numeric ID, and [IDold] is the
name. But you should change the field names to whatever they actually are.)

HTH,

Neil

[Phil]

Maybe I'm missing the point here.
I assume that the OP has a form with peoples names on it and a combo box to
find that person. Firstly the ID should be an autonumber or at least indexed
No duplicates. SEcondly I strongly suggest the ID is not very obvious on your
form, it should be not be used as something the user can mess around with.

The Combo's RowSource should be
SELECT Last, First, Middle, ID FROM MyTable
ORDER BY Last, First, Middle;
There should be 4 columns, the bound column is column 4 (ID)
The column widths (depending on font etc) should be something like 3cm;
3cm;3cm;0cm so that the last one (ID) is hidden The code on AfterUpdate of
the combo box is

Private Sub ContactSelection_AfterUpdate()
' Find the record that matches the control.

ID.SetFocus

End Sub

[Phil]

Sorry this b....dy newreader program sends a message if you hit a tab key
Try again

Private Sub ContactSelection_AfterUpdate()
' Find the record that matches the control.

ID.SetFocus ' Move the focus to the ID control on the form
DoCmd.FindRecord ContactSelection
End Sub

Phil