Need a lookup for an autonumber field

[Ronald Marchand]

Hello Group:

I have a table of members where the key field is an autonumber type. The
form for this table displays one record at a time. There is a record
selector at the bottom where you can type the record number and jump to it
with proper display. This is cute, but hardly practical when there are >
hundreds of records.

On my form I want to do a lookup on the current table where the user an see
the ID number and LastName etc and select the record to display/edit.

How do you set the autonumber control = selected autonumber???

For example, I am displaying record 33 and I want to display record 432 ...
as the result of a lookup.

Can anyone point me in the right direction?

TIA
Ron Marchand

 

[Beetle]

You can use an unbound combo box to select records. You will find info at
this link;

http://support.microsoft.com/default.aspx/kb/287658

HTH

 

[Ronald Marchand]

Thank you. Believe me, I searched ms.com for quite a while and never
stumbled on that article.

Ron

 

[Ronald Marchand]

I used option 1 of the help document and the record is selected as it should
be.

The problem is that as you move on, the last selection is in the combobox
control. What can do in the Event to clear the last selection. It is not
hurting, just annoying.

Ron

 

[UpRider]

Ron, if your code is like this, add the one line.
Combo20 MUST be unbound or it will put nulls in your data row.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(Me![Combo20], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
combo20 = vbNull 'add this line, if combo20 is text, use
=vbnullstring
End Sub

HTH, UpRider

 

[Ronald Marchand]

Combo120 = vbNullstring and Combo120 = "" both worked.
I thought that I had tried the latter with no joy.

Anyway, thanks for the help.

Ron

Ron, if your code is like this, add the one line.
Combo20 MUST be unbound or it will put nulls in your data row.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[ProductID] = " & Str(Nz(Me![Combo20], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
combo20 = vbNull 'add this line, if combo20 is text, use
=vbnullstring
End Sub

HTH, UpRider

I used option 1 of the help document and the record is selected as it
should be.

The problem is that as you move on, the last selection is in the combobox
control. What can do in the Event to clear the last selection. It is
not hurting, just annoying.

Ron