UI question: using AutoComplete in a broader sense?

[Pete]

I'm currently doing a database that uses comboboxes to look up records
in other tables, whether they be lookup tables or otherwise. When a
user needs to add an item to one of these tables, the user has to
either double-click on the combobox or go to the appropriate form via
an item on the main menu.

So, using an example, if someone is entering enrollment information,
they would definitely need a student and a course. If the course has
not been entered as a row in the "tblCourses" table yet, they could
double-click on the course combobox, which brings up the Course
Information form (modal). Then they enter the information, close the
Course Information form, and the combobox is requeried.

I am using all bound forms.
I know that it's possible to add items to simple lookup tables using
code and the NotInList() event. I think there have been recent
threads similar to this vein. But what about more complex lookups,
like the Course info? Courses have start dates, end dates, costs,
trainers, etc.

There are many ways to achieve something akin to AutoComplete by
allowing the ability for users to natively enter data in the 'main'
enrollment form, check to see if a course matching the typed info
exists, and if so, just set the foreign key equal to whatever matched.
Or, if nothing similar exists, then create a new course, get the ID,
and store that in the 'main' form.

So back to example--you enter in "12-10-2000" for the start date. Tab
over, which causes the AfterUpdate() event, which sees only one
matching course for that date. Since there's only one course, it
fills out the rest of the information for you and stores the ID of
this course.

Another--you enter in "12-11-2000" for the start date. Tab over.
"Billg" is the trainer. Tab over. Etc. Tab. Etc. You update the
last field, and it notices that (1) all fields are filled in, and (2)
there is no matching course. So it creates a record in the table. It
then stores this record in the enrollment information.
Now for my question: has anyone actually implemented something like
this? If so, was it worth the hassle? Or is there a simpler way, or
am I doing it correctly right now (see paragraph 1)? I'm just looking
for a word of experience telling me that this is a (1)good idea, or
(b)bad idea, or (e)all of the above. This is an Access-specific
question because it involves questions about using bound forms, doing
multiple and possibly frequent lookups, and because I'm using Access
97 for all this.
Pete

[PC Datasheet]

Pete:

Put a button beside the Course combobox and label it Add New Course. Put the
following code in the Click event of the button:

DoCmd.OpenForm "NameOf formToAddCourses",,,,,acDialog
If Not IsLoaded("NameOf formToAddCourses") Then
Exit Sub
End If
Me!NameOfCourseCombobox.Requery
Me!NameOfCourseCombobox = Forms!NameOf formToAddCourses!CourseID
DoCmd.Close acForm, ("NameOf formToAddCourses"

Notes:
1. Put an OK button on your form to add courses and put code in the Onclick
event to make the form not visible.
2. The IsLoaded function is in a standard module in Northwind.
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Pete" <ps********@zombieworld.com> wrote in message
news:98**************************@posting.google.c om...

I'm currently doing a database that uses comboboxes to look up records
in other tables, whether they be lookup tables or otherwise. When a
user needs to add an item to one of these tables, the user has to
either double-click on the combobox or go to the appropriate form via
an item on the main menu.

So, using an example, if someone is entering enrollment information,
they would definitely need a student and a course. If the course has
not been entered as a row in the "tblCourses" table yet, they could
double-click on the course combobox, which brings up the Course
Information form (modal). Then they enter the information, close the
Course Information form, and the combobox is requeried.

I am using all bound forms.
I know that it's possible to add items to simple lookup tables using
code and the NotInList() event. I think there have been recent
threads similar to this vein. But what about more complex lookups,
like the Course info? Courses have start dates, end dates, costs,
trainers, etc.

There are many ways to achieve something akin to AutoComplete by
allowing the ability for users to natively enter data in the 'main'
enrollment form, check to see if a course matching the typed info
exists, and if so, just set the foreign key equal to whatever matched.
Or, if nothing similar exists, then create a new course, get the ID,
and store that in the 'main' form.

So back to example--you enter in "12-10-2000" for the start date. Tab
over, which causes the AfterUpdate() event, which sees only one
matching course for that date. Since there's only one course, it
fills out the rest of the information for you and stores the ID of
this course.

Another--you enter in "12-11-2000" for the start date. Tab over.
"Billg" is the trainer. Tab over. Etc. Tab. Etc. You update the
last field, and it notices that (1) all fields are filled in, and (2)
there is no matching course. So it creates a record in the table. It
then stores this record in the enrollment information.
Now for my question: has anyone actually implemented something like
this? If so, was it worth the hassle? Or is there a simpler way, or
am I doing it correctly right now (see paragraph 1)? I'm just looking
for a word of experience telling me that this is a (1)good idea, or
(b)bad idea, or (e)all of the above. This is an Access-specific
question because it involves questions about using bound forms, doing
multiple and possibly frequent lookups, and because I'm using Access
97 for all this.
Pete

[Fletcher Arnold]

"Pete" <ps********@zombieworld.com> wrote in message
news:98**************************@posting.google.c om...

I'm currently doing a database that uses comboboxes to look up records
in other tables, whether they be lookup tables or otherwise. When a
user needs to add an item to one of these tables, the user has to
either double-click on the combobox or go to the appropriate form via
an item on the main menu.

So, using an example, if someone is entering enrollment information,
they would definitely need a student and a course. If the course has
not been entered as a row in the "tblCourses" table yet, they could
double-click on the course combobox, which brings up the Course
Information form (modal). Then they enter the information, close the
Course Information form, and the combobox is requeried.

I am using all bound forms.
I know that it's possible to add items to simple lookup tables using
code and the NotInList() event. I think there have been recent
threads similar to this vein. But what about more complex lookups,
like the Course info? Courses have start dates, end dates, costs,
trainers, etc.

There are many ways to achieve something akin to AutoComplete by
allowing the ability for users to natively enter data in the 'main'
enrollment form, check to see if a course matching the typed info
exists, and if so, just set the foreign key equal to whatever matched.
Or, if nothing similar exists, then create a new course, get the ID,
and store that in the 'main' form.

So back to example--you enter in "12-10-2000" for the start date. Tab
over, which causes the AfterUpdate() event, which sees only one
matching course for that date. Since there's only one course, it
fills out the rest of the information for you and stores the ID of
this course.

Another--you enter in "12-11-2000" for the start date. Tab over.
"Billg" is the trainer. Tab over. Etc. Tab. Etc. You update the
last field, and it notices that (1) all fields are filled in, and (2)
there is no matching course. So it creates a record in the table. It
then stores this record in the enrollment information.
Now for my question: has anyone actually implemented something like
this? If so, was it worth the hassle? Or is there a simpler way, or
am I doing it correctly right now (see paragraph 1)? I'm just looking
for a word of experience telling me that this is a (1)good idea, or
(b)bad idea, or (e)all of the above. This is an Access-specific
question because it involves questions about using bound forms, doing
multiple and possibly frequent lookups, and because I'm using Access
97 for all this.
Pete

It really seems to be a fine balance between making it easy to add a new
course 'on-the-fly' but not making it so trivially easy that wrong or
duplicate data is added by careless users. A huge factor is the number of
courses there are, but if you had, say, a couple of hundred then a combobox
soon becomes a pain - especially if you need to find the course by a number
of different criteria.

In this case, I could imagine a form/subform with a single search textbox to
find the course. Allow the user to type in either the name of a trainer /
all or part of a course name, or even a course date and query your subform
accordingly. With a few refinements to the code, I have built these sort of
things to make entering key data extremely fast and user-friendly, and these
structures have usually been quite popular with the users. For an example
of the sort of thing open, say Outlook, and see how they do it when you
write a new e-mail and click 'To:'

However, you will notice it's not very sophisticated. Had I written this,
you would be able to enter any part of the first name, surname or both and
the list of matching contacts would be shown, sortable by either column.

In my opinion, your question seems to be about not 'wasting the user input'
once CourseName, CourseTutor, CourseDate have been entered in the search
fields. It may be that you have thousands of courses and you need to search
that way, but at a guess, the biggest improvement you could make to keep
your users happy is a simple and quick search routine that might (at least a
standard / default search) use a single textbox to find.

Fletcher

[Pete]

"Fletcher Arnold" <fl****@home.com> wrote in message

It really seems to be a fine balance between making it easy to add a new
course 'on-the-fly' but not making it so trivially easy that wrong or
duplicate data is added by careless users. A huge factor is the number of
courses there are, but if you had, say, a couple of hundred then a combobox
soon becomes a pain - especially if you need to find the course by a number
of different criteria.

In this case, I could imagine a form/subform with a single search textbox to
find the course. Allow the user to type in either the name of a trainer /
all or part of a course name, or even a course date and query your subform
accordingly. With a few refinements to the code, I have built these sort of
things to make entering key data extremely fast and user-friendly, and these
structures have usually been quite popular with the users. For an example
of the sort of thing open, say Outlook, and see how they do it when you
write a new e-mail and click 'To:'

However, you will notice it's not very sophisticated. Had I written this,
you would be able to enter any part of the first name, surname or both and
the list of matching contacts would be shown, sortable by either column.

In my opinion, your question seems to be about not 'wasting the user input'
once CourseName, CourseTutor, CourseDate have been entered in the search
fields. It may be that you have thousands of courses and you need to search
that way, but at a guess, the biggest improvement you could make to keep
your users happy is a simple and quick search routine that might (at least a
standard / default search) use a single textbox to find.

Fletcher

Thanks for your input. This is what I was looking for.
Pete