Hi
Say you have a combo box called ComboSurnameSearch
In this combo Put the
1stName
Surname
ID
Set the column widths to 2;2;2
Set the bound column to 3 (thats the ID field column of the combo)
The reason for doing it like this is so that you can set the Surname 1st -
as you will have people will the same surnname - and you will be able to see
the 1st names - this will make it simpler to identify the people you want.
But when you select someone you are actually selecting the ID (as this is the
bound field - this is important as you may have people with the same 1st and
surnames)
In the properties box (data column) chane the AutoExpand to = Yes
add this to the AfterUpdate event
Private Sub ComboSurnameSearch_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![ComboSurnameSearch])
Me.Bookmark = rs.Bookmark
End Sub
In the code this may look wrong
rs.FindFirst "[ID] = " & Str(Me![ComboSurnameSearch])
but don't forget that you may slect the name but you are really selecting
the ID as this is the bound column - so it's right
Now of course you may type in some name that does not exist so you can
improve the combo like this (untest code but should be ok) - in this code I
assume that add a new record normally by adding a surname to the form into a
field called [surname] change this if you want. Also change the TABLENAME to
what it really is
Private Sub ComboSurnameSearch_NotInList(NewData As String, Response As
Integer)
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Dim msg As String
msg = "'" & NewData & "' is not on file." & vbCr & vbCr
msg = msg & "Do you want to add New Record?"
If MsgBox(msg, vbQuestion + vbYesNo) = vbNo Then
Response = acDataErrContinue
MsgBox "Try again."
Else
Set Db = CurrentDb
Set rs = Db.OpenRecordset("TABLENAME", dbOpenDynaset)
rs.AddNew
rs![Surname] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub
There are lots of other ways to improve this but these 2 shuld start you off
Hope this helps
--
Wayne
Manchester, England.
Code Agent said:
I have a table with thousands of names in it. I would like a function in a
form field that would allow me to type in the first few letters of the last
name, click a button and all of the names that have the same letters typed
into the aformentioned field auto populate into a combo box where I can
select the specific record. I can figure out how to go to the record from a
combo box selection, but I can't figure out how to only show what I'm looking
for in a combo box. Any help would be greatly appreciated!!!!!
