Quicken like autofill

[Randy]

I'm creating a database that will have the need to edit membership records.
I know how to use the find buttons, but what I would like to do is start
typing in a partial name, address, whatever in a field on a form and have
the form start to display partial hits like the data entry quicken auto-fill
functions.

Examples would be like trying to find a member based on last name. I would
start typing in the members last name, and the form would start to display
the hits based on the data entered into the last name field.

ie: start typing smi - and the hits might start with smith if that were the
first in the alphanumeric smi list.

Any help would be appreciated....

Randy

 

[Brendan Reynolds]

What you have described is the default behaviour of an Access combo box
control.

 

[Randy]

Can you elaborate please. I understand the combo box setting from the table
perspective, but how do I use it within a form thats setup for data entry?
Randy

 

[Brendan Reynolds]

You don't need to do anything to enable this behaviour, Randy, as I said,
it's the default behaviour. If you examine the properties of a combo box,
you'll find an 'Auto Expand' property (on the 'Data' tab in the 'Properties'
window in form design view). If this property is set to 'Yes' the combo box
behaves as you described - and 'Yes' is the default value of the property.

 

[Dave]

~How would you get this feature to work for all the form

IE.


I have a text/combo box at the top of my screen with a list box underneath
When the text box is empty it displays all of the records
the as i type the results in the list box become moire detailed
so i type 'S'
and the listbox changes to only show names beginning with S
then i type 'M'
Now the text box shows SM
and the list box shows all records that begin with SM
etc.....

untill i get to SMITH

and the list box shows all SMITHS
(and any names that start with smith like smithson etc)


Dave

 

[Brendan Reynolds]

Base the list box on a query something like ...

SELECT tblPerson.FamilyName
FROM tblPerson
WHERE (((tblPerson.FamilyName) Like [Forms]![Form1]![Text0] & "*"))
ORDER BY tblPerson.FamilyName;

Then in the Change event procedure of the text box ...

Private Sub Text0_Change()

Me.Recalc
'Me.Refresh

End Sub

I've commented out the Me.Refresh line - my tests indicate that either
Recalc or Refresh will work. You might want to try both (separately, there's
no need to use both together) to determine if either one is noticeably
faster with your actual form and data.

 

[Dave]

The problem with that is if you use the change event the whole field gets
selected.
then when you type the next letter it overtypes the first

Base the list box on a query something like ...

SELECT tblPerson.FamilyName
FROM tblPerson
WHERE (((tblPerson.FamilyName) Like [Forms]![Form1]![Text0] & "*"))
ORDER BY tblPerson.FamilyName;

Then in the Change event procedure of the text box ...

Private Sub Text0_Change()

Me.Recalc
'Me.Refresh

End Sub

I've commented out the Me.Refresh line - my tests indicate that either
Recalc or Refresh will work. You might want to try both (separately, there's
no need to use both together) to determine if either one is noticeably
faster with your actual form and data.

--
Brendan Reynolds (MVP)

~How would you get this feature to work for all the form

IE.


I have a text/combo box at the top of my screen with a list box underneath
When the text box is empty it displays all of the records
the as i type the results in the list box become moire detailed
so i type 'S'
and the listbox changes to only show names beginning with S
then i type 'M'
Now the text box shows SM
and the list box shows all records that begin with SM
etc.....

untill i get to SMITH

and the list box shows all SMITHS
(and any names that start with smith like smithson etc)


Dave







combo
box combo
box

name.

I

 

[Brendan Reynolds]

Not on my PC, it doesn't.

Note that in my example the control into which we are typing is a text box.
Perhaps you tried it with a combo box? I wouldn't do it with a combo box,
because, as I said, with a combo box the feature is built-in.
--
Brendan Reynolds (MVP)

The problem with that is if you use the change event the whole field gets
selected.
then when you type the next letter it overtypes the first

Base the list box on a query something like ...

SELECT tblPerson.FamilyName
FROM tblPerson
WHERE (((tblPerson.FamilyName) Like [Forms]![Form1]![Text0] & "*"))
ORDER BY tblPerson.FamilyName;

Then in the Change event procedure of the text box ...

Private Sub Text0_Change()

Me.Recalc
'Me.Refresh

End Sub

I've commented out the Me.Refresh line - my tests indicate that either
Recalc or Refresh will work. You might want to try both (separately, there's
no need to use both together) to determine if either one is noticeably
faster with your actual form and data.

--
Brendan Reynolds (MVP)

~How would you get this feature to work for all the form

IE.


I have a text/combo box at the top of my screen with a list box underneath
When the text box is empty it displays all of the records
the as i type the results in the list box become moire detailed
so i type 'S'
and the listbox changes to only show names beginning with S
then i type 'M'
Now the text box shows SM
and the list box shows all records that begin with SM
etc.....

untill i get to SMITH

and the list box shows all SMITHS
(and any names that start with smith like smithson etc)


Dave







"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
You don't need to do anything to enable this behaviour, Randy, as I said,
it's the default behaviour. If you examine the properties of a combo box,
you'll find an 'Auto Expand' property (on the 'Data' tab in the
'Properties'
window in form design view). If this property is set to 'Yes' the combo
box
behaves as you described - and 'Yes' is the default value of the property.

--
Brendan Reynolds (MVP)

Can you elaborate please. I understand the combo box setting from the
table
perspective, but how do I use it within a form thats setup for data
entry?
Randy

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
What you have described is the default behaviour of an Access combo
box
control.

--
Brendan Reynolds (MVP)

I'm creating a database that will have the need to edit membership
records.
I know how to use the find buttons, but what I would like to

do

is form
and name.

start

to

 

[Dave]

I did use a text box,
Could it be that im using A97
or that our options are set up differently?

Not on my PC, it doesn't.

Note that in my example the control into which we are typing is a text box.
Perhaps you tried it with a combo box? I wouldn't do it with a combo box,
because, as I said, with a combo box the feature is built-in.
--
Brendan Reynolds (MVP)

The problem with that is if you use the change event the whole field gets
selected.
then when you type the next letter it overtypes the first

Base the list box on a query something like ...

SELECT tblPerson.FamilyName
FROM tblPerson
WHERE (((tblPerson.FamilyName) Like [Forms]![Form1]![Text0] & "*"))
ORDER BY tblPerson.FamilyName;

Then in the Change event procedure of the text box ...

Private Sub Text0_Change()

Me.Recalc
'Me.Refresh

End Sub

I've commented out the Me.Refresh line - my tests indicate that either
Recalc or Refresh will work. You might want to try both (separately, there's
no need to use both together) to determine if either one is noticeably
faster with your actual form and data.

--
Brendan Reynolds (MVP)

~How would you get this feature to work for all the form

IE.


I have a text/combo box at the top of my screen with a list box underneath
When the text box is empty it displays all of the records
the as i type the results in the list box become moire detailed
so i type 'S'
and the listbox changes to only show names beginning with S
then i type 'M'
Now the text box shows SM
and the list box shows all records that begin with SM
etc.....

untill i get to SMITH

and the list box shows all SMITHS
(and any names that start with smith like smithson etc)


Dave







"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
You don't need to do anything to enable this behaviour, Randy, as I
said,
it's the default behaviour. If you examine the properties of a combo
box,
you'll find an 'Auto Expand' property (on the 'Data' tab in the
'Properties'
window in form design view). If this property is set to 'Yes' the combo
box
behaves as you described - and 'Yes' is the default value of the
property.

from
the

table
perspective, but how do I use it within a form thats setup for data
entry?
Randy

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
What you have described is the default behaviour of an Access combo
box
control.

--
Brendan Reynolds (MVP)

I'm creating a database that will have the need to edit membership
records.
I know how to use the find buttons, but what I would like to

do

is
start
typing in a partial name, address, whatever in a field on a form
and
have
the form start to display partial hits like the data entry quicken
auto-fill
functions.

Examples would be like trying to find a member based on last name.
I
would
start typing in the members last name, and the form would

start

to
display
the hits based on the data entered into the last name field.

ie: start typing smi - and the hits might start with smith

if
that

were
the
first in the alphanumeric smi list.

Any help would be appreciated....

Randy

 

[Brendan Reynolds]

You're right, Dave, I was able to reproduce the behaviour you describe in
Access 97. The following modification to the On Change event procedure fixes
it ...

Private Sub Text0_Change()

Me.Recalc

'Need to append vbNullString to coerce the value to
'a string - otherwise we'll get a type mismatch error
'when the value is Null.
Me!Text0.SelStart = Len(Me!Text0 & vbNullString)

End Sub

--
Brendan Reynolds (MVP)

I did use a text box,
Could it be that im using A97
or that our options are set up differently?

Not on my PC, it doesn't.

Note that in my example the control into which we are typing is a text box.
Perhaps you tried it with a combo box? I wouldn't do it with a combo box,
because, as I said, with a combo box the feature is built-in.
--
Brendan Reynolds (MVP)

The problem with that is if you use the change event the whole field gets
selected.
then when you type the next letter it overtypes the first

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Base the list box on a query something like ...

SELECT tblPerson.FamilyName
FROM tblPerson
WHERE (((tblPerson.FamilyName) Like [Forms]![Form1]![Text0] & "*"))
ORDER BY tblPerson.FamilyName;

Then in the Change event procedure of the text box ...

Private Sub Text0_Change()

Me.Recalc
'Me.Refresh

End Sub

I've commented out the Me.Refresh line - my tests indicate that either
Recalc or Refresh will work. You might want to try both (separately,
there's
no need to use both together) to determine if either one is noticeably
faster with your actual form and data.

--
Brendan Reynolds (MVP)

~How would you get this feature to work for all the form

IE.


I have a text/combo box at the top of my screen with a list box
underneath
When the text box is empty it displays all of the records
the as i type the results in the list box become moire detailed
so i type 'S'
and the listbox changes to only show names beginning with S
then i type 'M'
Now the text box shows SM
and the list box shows all records that begin with SM
etc.....

untill i get to SMITH

and the list box shows all SMITHS
(and any names that start with smith like smithson etc)


Dave







"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
You don't need to do anything to enable this behaviour, Randy,

as

I to
do a
form

 

[Dave]

Excellent!!
now for the cruch question
how do i allow for spaces in the names ?
atm if i type a space it doesn't register
When the curser is moved i think that spaces arn't counted when calculating
length?

Thanks so much for getting me this far
If its not possible or a real pain then ill just have to make sure there are
none in the field
Thanks again
Dave

You're right, Dave, I was able to reproduce the behaviour you describe in
Access 97. The following modification to the On Change event procedure fixes
it ...

Private Sub Text0_Change()

Me.Recalc

'Need to append vbNullString to coerce the value to
'a string - otherwise we'll get a type mismatch error
'when the value is Null.
Me!Text0.SelStart = Len(Me!Text0 & vbNullString)

End Sub

--
Brendan Reynolds (MVP)

I did use a text box,
Could it be that im using A97
or that our options are set up differently?

Not on my PC, it doesn't.

Note that in my example the control into which we are typing is a text box.
Perhaps you tried it with a combo box? I wouldn't do it with a combo box,
because, as I said, with a combo box the feature is built-in.
--
Brendan Reynolds (MVP)

The problem with that is if you use the change event the whole field gets
selected.
then when you type the next letter it overtypes the first

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Base the list box on a query something like ...

SELECT tblPerson.FamilyName
FROM tblPerson
WHERE (((tblPerson.FamilyName) Like [Forms]![Form1]![Text0] & "*"))
ORDER BY tblPerson.FamilyName;

Then in the Change event procedure of the text box ...

Private Sub Text0_Change()

Me.Recalc
'Me.Refresh

End Sub

I've commented out the Me.Refresh line - my tests indicate that either
Recalc or Refresh will work. You might want to try both (separately,
there's
no need to use both together) to determine if either one is noticeably
faster with your actual form and data.

--
Brendan Reynolds (MVP)

~How would you get this feature to work for all the form

IE.


I have a text/combo box at the top of my screen with a list box
underneath
When the text box is empty it displays all of the records
the as i type the results in the list box become moire detailed
so i type 'S'
and the listbox changes to only show names beginning with S
then i type 'M'
Now the text box shows SM
and the list box shows all records that begin with SM
etc.....

untill i get to SMITH

and the list box shows all SMITHS
(and any names that start with smith like smithson etc)


Dave







"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
You don't need to do anything to enable this behaviour, Randy,

as

I
said,
it's the default behaviour. If you examine the properties of a combo
box,
you'll find an 'Auto Expand' property (on the 'Data' tab in the
'Properties'
window in form design view). If this property is set to 'Yes' the
combo
box
behaves as you described - and 'Yes' is the default value of the
property.

--
Brendan Reynolds (MVP)

Can you elaborate please. I understand the combo box setting from
the
table
perspective, but how do I use it within a form thats setup for
data
entry?
Randy

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
What you have described is the default behaviour of an Access
combo
box
control.

--
Brendan Reynolds (MVP)

I'm creating a database that will have the need to edit
membership
records.
I know how to use the find buttons, but what I would

like

to

on

a smith
if

 

[Brendan Reynolds]

To the best of my knowledge, spaces should be treated exactly the same as
any other character. What's happening?

--
Brendan Reynolds (MVP)

Excellent!!
now for the cruch question
how do i allow for spaces in the names ?
atm if i type a space it doesn't register
When the curser is moved i think that spaces arn't counted when calculating
length?

Thanks so much for getting me this far
If its not possible or a real pain then ill just have to make sure there are
none in the field
Thanks again
Dave

You're right, Dave, I was able to reproduce the behaviour you describe in
Access 97. The following modification to the On Change event procedure fixes
it ...

Private Sub Text0_Change()

Me.Recalc

'Need to append vbNullString to coerce the value to
'a string - otherwise we'll get a type mismatch error
'when the value is Null.
Me!Text0.SelStart = Len(Me!Text0 & vbNullString)

End Sub

--
Brendan Reynolds (MVP)

I did use a text box,
Could it be that im using A97
or that our options are set up differently?



"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Not on my PC, it doesn't.

Note that in my example the control into which we are typing is a text
box.
Perhaps you tried it with a combo box? I wouldn't do it with a combo box,
because, as I said, with a combo box the feature is built-in.
--
Brendan Reynolds (MVP)

The problem with that is if you use the change event the whole field
gets
selected.
then when you type the next letter it overtypes the first

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
Base the list box on a query something like ...

SELECT tblPerson.FamilyName
FROM tblPerson
WHERE (((tblPerson.FamilyName) Like [Forms]![Form1]![Text0] & "*"))
ORDER BY tblPerson.FamilyName;

Then in the Change event procedure of the text box ...

Private Sub Text0_Change()

Me.Recalc
'Me.Refresh

End Sub

I've commented out the Me.Refresh line - my tests indicate that either
Recalc or Refresh will work. You might want to try both (separately,
there's
no need to use both together) to determine if either one is noticeably
faster with your actual form and data.

--
Brendan Reynolds (MVP)

~How would you get this feature to work for all the form

IE.


I have a text/combo box at the top of my screen with a list box
underneath
When the text box is empty it displays all of the records
the as i type the results in the list box become moire detailed
so i type 'S'
and the listbox changes to only show names beginning with S
then i type 'M'
Now the text box shows SM
and the list box shows all records that begin with SM
etc.....

untill i get to SMITH

and the list box shows all SMITHS
(and any names that start with smith like smithson etc)


Dave







"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
You don't need to do anything to enable this behaviour,

Randy,
as

I
said,
it's the default behaviour. If you examine the properties of a
combo
box,
you'll find an 'Auto Expand' property (on the 'Data' tab in the
'Properties'
window in form design view). If this property is set to

'Yes'
the

combo
box
behaves as you described - and 'Yes' is the default value of the
property.

--
Brendan Reynolds (MVP)

Can you elaborate please. I understand the combo box setting
from
the
table
perspective, but how do I use it within a form thats setup for
data
entry?
Randy

"Brendan Reynolds" <brenreyn at indigo dot ie> wrote in message
What you have described is the default behaviour of an Access
combo
box
control.

--
Brendan Reynolds (MVP)

I'm creating a database that will have the need to edit
membership
records.
I know how to use the find buttons, but what I would

like

to
do
is
start
typing in a partial name, address, whatever in a field

on

a
form
and
have
the form start to display partial hits like the data entry
quicken
auto-fill
functions.

Examples would be like trying to find a member based

on
last

name.
I
would
start typing in the members last name, and the form would
start
to
display
the hits based on the data entered into the last name field.

ie: start typing smi - and the hits might start with smith
if
that
were
the
first in the alphanumeric smi list.

Any help would be appreciated....

Randy