combo box has repeating options

[Jennifer]

I'm trying to set up a combo box (on a form regarding
hospital patients) such that when a new hospital name is
added to the combo box, it will allow that choice to be
available for a later record. I set up a query for
hospital name, and the table upon which the query is
based does not allow duplicates. However, there are
duplicate names (including multiple spaces, for those
records where hospital name was not specified) within the
combo box. Any suggestions? I currently have one table
with all of the patients information on it, and a
separate table with hospital name only. If I form a
relationship between the two of them, then my combo box
has only ONE hospital name available. Any suggestions?

Thanks!

Jennifer

 

[Jeff Boyce]

Jennifer

Have you looked into the "NotInList" event? One of the classic uses of this
event is to allow the user to add a new item to the table underlying a combo
box on a form. I believe the example in Access HELP walks through the
entire code needed.

Not being there, I don't understand what you're describing when you say

If I form a
relationship between the two of them, then my combo box
has only ONE hospital name available. Any suggestions?

On what (query) do you currently base your form? ?your combo box? Please
post the SQL statements.

 

[Jennifer]

I was not aware of the "NotInList" event, and have
scratched the query that I mentioned before. I have
tried finding the code example through Access HELP, but
was unsuccessful. I can only find defintions of the
various events, not the actual code to program them. Can
you point me in the right direction?

Thanks!
Jennifer

 

[tina]

re-reading your original post, it sounds like you've based your combo box on
linked tables, which is probably incorrect design. to repeat Jeff's request,
please post the SQL statement from the RowSource of your combo box. and the
SQL statement from the form's RecordSource.

 

[Guest]

Here are the requested SQL statements:

Combo Box RowSource:
SELECT tblPathForm.Institution
FROM tblPathForm
ORDER BY tblPathForm.Institution;

Form RecordSource:
SELECT
FROM tblPathForm;

Please let me know if you need anymore information.

Jennifer

 

[tina]

based on your original post, here's my assumption of what you're trying to
do (i'll give the objects names, easier to refer to):

table about hospital patients - tblPatients
tblPatients has a field for the name of the hospital they're in -
PatHospital
table about hospitals - tblHospitals.
tblHospitals has a primary key field - HID, and a name field - HName.

form based on tblPatients - frmPatients
combo box control bound to field PatHospital - cboPatHospital

when you use the form to enter or update a record about a patient, you want
cboPatHospital to show a list of hospitals to choose from.

is all of the above correct, so far? if so, your current setup is the
equivalent of basing cboPatHospital on tblPatients. instead you need to set
the combo box's RowSource to the equivalent of

SELECT HID, HName FROM tblHospitals ORDER BY HName.

Column Count: 2
Bound Column: 1
Column Widths: 0";1"
(make the second column more or less than 1 inch, as appropriate)

now, in each patient record's PatHospital field, the saved value will be the
ID of a specific hospital. but when you look at the record using the form,
what you'll *see* is the hospital name.

hth

 

[Jennifer]

I really would like to be able to do this without having
to have a separate table for Hospital, if possible. What
I would like to have is one table with patient
information, including hospital name (no other data is
needed about the hospital, such as address, etc., so
having a separate table with hospital names would be
extraneous) in a combo box which I would like to be
updated with new hospital names as they arise. From the
research I've done, it seems like the "NotInList" option
would work really well for this; however, I'm relatively
new to VB programming and have been unable to come up with
a code that works. All of the code examples I've found
involve a message box to the user about adding a new value
to the list and I don't need that; I just need the combo
box to be updated. I know that "LimitToList" should be
set to "True" for this to work, but I'm confused about
what code I would need. Also, is a query necessary to
make this happen?

Jennifer

 

[tina]

a separate table is not "extraneous", it's standard database design. you'll
save space in the database, for one thing, by saving a numeric ID rather
than an entire hospital name, in each record. also, the kind of setup you're
talking about will not allow for easy correction of name entries. for
instance, if a hospital name is typed as "Green Memorl Hospital" (are you
an ER fan? <g>) and that entry is used in subsequent records before
somebody notices it is misspelled - there is no mechanism to easily correct
the error in all records. somebody has to perform periodic "clean-up" of the
data manually.
having said all that, to do what you're asking, do the following:
in the form, set the combobox RowSource to

SELECT DISTINCT Institution FROM tblPathForm ORDER BY Institution

set the combo box's LimitToList property to No.
add a macro or VBA procedure to the combo box's OnEnter event, to requery
the combo box (*not* to requery the form).

hth

 

[Guest]

Thank you, thank you, thank you for all of your patience!
It worked!

Jennifer