Auto Find Records as you type in the key

[Christina]

I would like to reproduce something I have seen on other databases and electronic forms. We have a unique client id number. I would like to set the form so that as you type in the client number it automatically brings up the record and the more you type the more specific it gets. If you type in a unique number, then it will be a new record you can save. For example: If the client number you have is bradej09091954. When you type b, the first record of b's is brought up, when you type r, the first record of br's is brought up, but after you reach the first 9, there is nothing similar and you then have a blank form that appears. I know this is a little bit more complicated, but it would be ideal for the user if they need to find a client or for the data entry person to know if that client is already entered.

Any help would be greatly appreciated!

Thanks
Christina

 

[fredg]

I would like to reproduce something I have seen on other databases and
electronic forms. We have a unique client id number. I would like to
set the form so that as you type in the client number it automatically
brings up the record and the more you type the more specific it gets.
If you type in a unique number, then it will be a new record you can
save. For example: If the client number you have is bradej09091954.
When you type b, the first record of b's is brought up, when you type r,
the first record of br's is brought up, but after you reach the first 9,
there is nothing similar and you then have a blank form that appears. I
know this is a little bit more complicated, but it would be ideal for
the user if they need to find a client or for the data entry person to
know if that client is already entered.

Any help would be greatly appreciated!

Thanks
Christina

Christina,
It's called a ComboBox.
If you add a combo box to the header of your form, using the combo box
wizard, select the 3rd option on the first page of instructions ....
something like "Find a record ... etc".
That will then fill the form with that record's data.

You can then add code to the Combo's NotInList event to either open a
different form for entry of the new account if the number is not already
in the list, or add it to the list if it's just the number you need
added, without any other additional information.
That would be a separate post, however.

 

[Christina]

I was able to get a list of the client numbers, but when I enter a number it won't bring up the record. How do I get it to bring up the rest of the information on my form as well, instead of just the number. Having the number helps to eliminate duplicates, but won't help in searching for client information.

Thanks
Christina

 

[fredg]

I was able to get a list of the client numbers, but when I enter a
number it won't bring up the record. How do I get it to bring up the
rest of the information on my form as well, instead of just the number.
Having the number helps to eliminate duplicates, but won't help in
searching for client information.

Thanks
Christina

Christina,
Did you use the Combo wizard?
Did you select that 3rd option on the first page of instructions?

 

[Christina]

Yes I did. I used the wizard and selected the 3 option. But it doesn't bring up the rest of the client information.

 

[Kelvin]

Christina, it won't bring up the record until you hit the enter key or click
outside the combo box with the mouse. The way this routine works is to do
the search everytime the content of the box is changed. If you want the
records to change as you type it will require additional code. There is no
default feature to do that.

Kelvin

Yes I did. I used the wizard and selected the 3 option. But it doesn't

bring up the rest of the client information.

 

[Christina]

I did hit enter and click outside of the box...all it did was change the client number on that record. And if I tried to hit enter, it acted as if I tried to save it and a message popped up saying that I can't save because such and such record was left blank.

 

[fredg]

I did hit enter and click outside of the box...all it did was change the
client number on that record. And if I tried to hit enter, it acted as
if I tried to save it and a message popped up saying that I can't save
because such and such record was left blank.

Christina,
Check the code in the AfterUpdate event of the combo box.
When using the Wizard, the following code is generated (depending upon
your access version):

Using Access 2002 ....

' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & str(Nz(Me![Combo287], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark


Using Access 97 ....
' Find the record that matches the control.
Me.RecordsetClone.FindFirst "[NameID] = " & Me![Combo116]
Me.Bookmark = Me.RecordsetClone.Bookmark

Your code may use a different field than the [ID] or [NameID] fields
above, but the rest of the code should be the same.

use whichever works for you.

 

[Kelvin]

It looks like the wizard did not create the combo box correctly. Check the
code like Fred said and if the code doesn't match, recreate the combo box
again.

Also, the combo box needs to be unbound. It should not be linked to a
field. You should have another separate box to show this information. This
combo box is only for the search.

Kelvin

I did hit enter and click outside of the box...all it did was change the

client number on that record. And if I tried to hit enter, it acted as if I
tried to save it and a message popped up saying that I can't save because
such and such record was left blank.

 

[Christina]

Thanks, that was the trick...mine was bound and I had to create a separte combo box that wasn't bound. Thanks for your help!