How to build a drop down list of past entries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that users enter a person's name and title and I would like to
know if there is a way to have the field use a drop down list of past entries
(similiar to entering search terms in Google) and allow the user to click on
previous entry and use it instead of having to enter the person's name again.
I am using Access 2003 and vba with a FE/BE solution.

I know I can build a table and have the user add the person's name and then
select it on the next entry but I don't want to have to maintain the data -
users can share their data via import/export feature.

Any help, examples or suggestions would be greatly appreciated!

Jerry
 
Assuming that the control (combo box) is bound to a field in the form's
underlying table, just set the RowSource to the same field to which it is
bound in the table but go to the SQL view of the source and insert the word
DISTINCT after the word SELECT (i.e. SELECT UserName from... becomes SELECT
DISTINCT UserName from....). Also set the LimitToList property of the control
to False. This allows users to add new entries by typing them there, but also
to select the names later from the list of already-used names without seeing
duplicates.
 
Whether a combo is bound or unbound doesn't matter. A combo's row source has
nothting to do with wether it is a bound control. So, don't confuse Bound
Control with Bound Column. In a combo, the bound column is the column that
will be returned by the combo if it is bound to a field in the Control Source
property.
If you use the Auto Expand and Limit To List properties, having a previous
entries list doesn't make a lot of sense. You just start typing until you
see what you want.
 
Since the data has to be stored somewhere, you have really two options:

1. Set the RowSource of the combo box to a Value List and write code to add
entries not found in the Value List to the Value List. This would be way too
much work.

2. Make a one-field table to contain the entries. Set the RowSource of the
control to the field in the table. Set the LimitToList to false for the
control, then in the control's AfterUpdate event, append its current value to
the table. This way, the users maintain the table for you as they use the
form, and they can also use the dropdown and/or AutoComplete to use existing
entries.

For example.

Table named: UserNames
Text field named: UserName
Unbound combo box named: UserName
UserName.RowSource: "Select UserName from UserNames Order By UserName"
UserName.LimitToList: False

Private Sub UserName_AfterUpdate
if isnull(UserName) then Exit sub
If DCount("[UserName]","[UserNames]","[UserName] = '" & [UserName] & "'") =
0 Then
DoCmd.RunSQL "INSERT INTO UserName (UserName) SELECT UserName AS UserName;"
End If

Warnings
1. I have not tested this, so you may have to play around with the syntax a
little to get it to work.
2. With LimitToList set to False, there is nothing to prevent
almost-duplicates created by misspellings (e.g. George Smith vs. the
misspelled Gorge Smith)
 
You could use a Union query to join the static list with the recent entries.
However, in either case, how long do you keep recent entries? At some point,
you need to refresh the list.
IMHO, not a really useful idea.
--
Dave Hargis, Microsoft Access MVP


Brian said:
Since the data has to be stored somewhere, you have really two options:

1. Set the RowSource of the combo box to a Value List and write code to add
entries not found in the Value List to the Value List. This would be way too
much work.

2. Make a one-field table to contain the entries. Set the RowSource of the
control to the field in the table. Set the LimitToList to false for the
control, then in the control's AfterUpdate event, append its current value to
the table. This way, the users maintain the table for you as they use the
form, and they can also use the dropdown and/or AutoComplete to use existing
entries.

For example.

Table named: UserNames
Text field named: UserName
Unbound combo box named: UserName
UserName.RowSource: "Select UserName from UserNames Order By UserName"
UserName.LimitToList: False

Private Sub UserName_AfterUpdate
if isnull(UserName) then Exit sub
If DCount("[UserName]","[UserNames]","[UserName] = '" & [UserName] & "'") =
0 Then
DoCmd.RunSQL "INSERT INTO UserName (UserName) SELECT UserName AS UserName;"
End If

Warnings
1. I have not tested this, so you may have to play around with the syntax a
little to get it to work.
2. With LimitToList set to False, there is nothing to prevent
almost-duplicates created by misspellings (e.g. George Smith vs. the
misspelled Gorge Smith)

JWS315 said:
Both the form and field are unbound so I am not sure the approach will work.
Click to expand...
Click to expand...
 
Thanks for the clarification, I tried adding a combo box with the rowsource
set to the table where the entires are and it seems to work fine.

Thanks for the help

Jerry
 
Back
Top