Filtering Data On Entry

[iain]

I'm trying to speed up data entry on a form in such a manner that when I
enter the first character in a field, it displays a list of all previously
stored values that begin with that character, then reduce the number of
choices as I type each character.

If the string that I want to enter is already listed I wish to select it
using the mouse, otherwise type a completely new string and use the enter key
or tab to the next field.

Can this be achieved with a Combo Box?

Does anyone onow of usefull links where I might find more information on
this technique?

Thanks,

Iain

 

[Wayne-I-M]

Hi

You can set the Auto Expand to Yes (in the Data column)
This will do what you want

 

[iain]

Thanks Wayne,

that was quick.

The Auto Expand is already set to 'yes'. Nothing happens when I type a
character in the field when creating a new record. In other words, I still
have to click on the down arrow to open up the combo box list of previous
data entries.

 

[Wayne-I-M]

Hi

Sorry for not getting back to you sooner - I have been away.

Open the form in design view and right click the combo.
Open the properties box
In the Event column select the Got Focus row
Right click and select build (...)
Select code
Add this (change ComboName to what it really is)

Private Sub ComboName_GotFocus()
Me.ComboName.Dropdown
End Sub

Good luck

 

[Wayne-I-M]

Just had a thought
If you are (or maybe) adding or searching for a record that it not already
on file you may want to add this to the combo.

Again change ComboName and also change TableName (to the table name of the
table that holds the records you are searching). Change ID to the name of
the primary field (of the table that you searching)

This, along wioth the OnFocus I already gave should sort out your problem.

Good luck

(don't forget to change ComboName, TableName and ID to what they really are
or it will not work)



Private Sub ComboName_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![CDSurname] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub


Private Sub ComboName_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![ComboName])
Me.Bookmark = rs.Bookmark
End Sub

 

[Wayne-I-M]

Soory - should read what I send 1srt

change this
rs![CDSurname] = NewData

to
rs![*****] = NewData

Cahnge ***** to the name of the control that you are searching,
So if you are searching for a Surname and the name of the control (not the
table field) is ClientSurname it would be
rs![ClientSurname] = NewData

Searching a control called Cars it would be
rs![Cars NewData

etc
etc




--
Wayne
Manchester, England.

Just had a thought
If you are (or maybe) adding or searching for a record that it not already
on file you may want to add this to the combo.

Again change ComboName and also change TableName (to the table name of the
table that holds the records you are searching). Change ID to the name of
the primary field (of the table that you searching)

This, along wioth the OnFocus I already gave should sort out your problem.

Good luck

(don't forget to change ComboName, TableName and ID to what they really are
or it will not work)



Private Sub ComboName_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![CDSurname] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub


Private Sub ComboName_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![ComboName])
Me.Bookmark = rs.Bookmark
End Sub




--
Wayne
Manchester, England.

Thanks Wayne,

that was quick.

The Auto Expand is already set to 'yes'. Nothing happens when I type a
character in the field when creating a new record. In other words, I still
have to click on the down arrow to open up the combo box list of previous
data entries.

 

[KenSheridan via AccessMonster.com]

Iain:

A combo box works in a slightly different way than that which you describe as
it progressively goes to the nearest match in its list as characters are
entered rather than progressively restricting a list to a set of possible
values. To do the latter you'd need to use two controls, a bound text box
and an unbound list box. The list box would have a RowSource property such
as:

SELECT DISTINCT MyField
FROM MyTable
WHERE Myfield LIKE Form!MyTextBox & "*"
OR Form!MyTextBox IS NULL
ORDER BY MyField;

where MyField is the name of the field in question, and MyTextBox is the name
of the text box to which it is bound. Note the use of the Form property to
refernce the form rather than a full reference; this is possible as both
controls are in the same form.

In the text box's Change event procedure requery the list box:

Me.MyListBox.Requery

Do the same in the form's Current event procedure.

In the list box's AfterUpdate event procedure assign its value to the text
box:

Me.MyTextBox = Me.MyListBox

You can if you wish show the list box in the text box's GotFocus event
procedure and hide it in both the text box's and the list box's AfterUpdate
event procedure, though in the latter you'd also have to move focus to the
text box first:

Me.MyTextBox.SetFocus
Me.MyTextBox = Me.MyListBox
Me.MyListBox.Visible = False

Ken Sheridan
Stafford, England

 

[iain]

Thanks Wayne,

I'll have to try this out a few times to see if it does the trick, so it may
be a while before you hear from me.

Soory - should read what I send 1srt

change this
rs![CDSurname] = NewData

to
rs![*****] = NewData

Cahnge ***** to the name of the control that you are searching,
So if you are searching for a Surname and the name of the control (not the
table field) is ClientSurname it would be
rs![ClientSurname] = NewData

Searching a control called Cars it would be
rs![Cars NewData

etc
etc




--
Wayne
Manchester, England.

Just had a thought
If you are (or maybe) adding or searching for a record that it not already
on file you may want to add this to the combo.

Again change ComboName and also change TableName (to the table name of the
table that holds the records you are searching). Change ID to the name of
the primary field (of the table that you searching)

This, along wioth the OnFocus I already gave should sort out your problem.

Good luck

(don't forget to change ComboName, TableName and ID to what they really are
or it will not work)



Private Sub ComboName_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![CDSurname] = NewData
rs.Update
Response = acDataErrAdded
End If
End Sub


Private Sub ComboName_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Str(Me![ComboName])
Me.Bookmark = rs.Bookmark
End Sub




--
Wayne
Manchester, England.

Thanks Wayne,

that was quick.

The Auto Expand is already set to 'yes'. Nothing happens when I type a
character in the field when creating a new record. In other words, I still
have to click on the down arrow to open up the combo box list of previous
data entries.

:

Hi

You can set the Auto Expand to Yes (in the Data column)
This will do what you want
--
Wayne
Manchester, England.



:

I'm trying to speed up data entry on a form in such a manner that when I
enter the first character in a field, it displays a list of all previously
stored values that begin with that character, then reduce the number of
choices as I type each character.

If the string that I want to enter is already listed I wish to select it
using the mouse, otherwise type a completely new string and use the enter key
or tab to the next field.

Can this be achieved with a Combo Box?

Does anyone onow of usefull links where I might find more information on
this technique?

Thanks,

Iain

 

[iain]

Thanks Ken,

this is quite a lot for me to work on, so it'll take some time before I get
it working, but I'll give it a whirl.

Iain

 

[Linq Adams via AccessMonster.com]

Actually, you should not have to drop down the combobx for AutoExpand to work,
which would make me tend to think the control is corrupted. You might try
simply re-creating it.

 

[AccessVandal via AccessMonster.com]

Allen Browne has a sample Find As You Type.

http://allenbrowne.com/AppFindAsUType.html

 

[iain]

Well Ken, I've had a go at this and it is very close to what I'm trying to
achieve.

The 'Find as you Type' function is probably what I need in the long run but
I've read it and it is way beyond my capabilities and understanding.

I'm having the following difficulties with your suggestion:

The form name is frmContents and the field name is Subjects, and the form
module now has 3 private subs for each of the events. As soon as I opened
the form after adding the list box, I now get a message box popping up "Enter
Parameter Value" i.e. frmContents!Subjects. I have to click the cancel
button on the message box, three times before the form is displayed.

The same thing happens when I try to type anything in the field. This list
box only filters the choice after I type everything I want into the text box,
but not as I type each character.

Any further suggestions?

Iain

 

[KenSheridan via AccessMonster.com]

Iain:

I think you may have missed out the reference to the Forms collection in the
parameter. It should be either:

Forms!frmContents!Subjects

or simply:

Form!Subjects

The former is a fully qualified reference to the Subjects control. The In
the latter 'Form' is the form's Form property. This can be used in cases
like this where the referencing control (the list box in this case) is in the
same form as referenced control (the Subjects text box).

Ken Sheridan
Stafford, England

Well Ken, I've had a go at this and it is very close to what I'm trying to
achieve.

The 'Find as you Type' function is probably what I need in the long run but
I've read it and it is way beyond my capabilities and understanding.

I'm having the following difficulties with your suggestion:

The form name is frmContents and the field name is Subjects, and the form
module now has 3 private subs for each of the events. As soon as I opened
the form after adding the list box, I now get a message box popping up "Enter
Parameter Value" i.e. frmContents!Subjects. I have to click the cancel
button on the message box, three times before the form is displayed.

The same thing happens when I try to type anything in the field. This list
box only filters the choice after I type everything I want into the text box,
but not as I type each character.

Any further suggestions?

Iain

Iain:

[quoted text clipped - 56 lines]