Data from synchronized list box on a form not entered into table bound to the form

[Phil F]

In the tbl-Photographs table, I am categorizing ranges of digital
photographs by the following fields: ID (primary key autonumber),
SubjectName, SubjectID, Procedure1, Procedure2. Procedure3, Location1,
Location2, Location3, Comments. I originated the database table from a Word
table so all of this information is in one table. I have created a form
(frm-Photographs) for data entry based on the tbl-Photographs table. The
SubjectID field of the form is a combo box which is based on the tbl-Subject
table (includes subject names and subject ID) and bound to the SubjectID
field of the Photographs table. When the combo box is clicked the name and
subject ID are shown. I select the appropriate entry. I have synchronized
the SubjectID combo box with a list box in the SubjectName field
(http://support.microsoft.com/kb/97624/en-us) which is below the combo box.
The SubjectName field of the form is bound to the SubjectName field of
tbl-Photographs When the SubjectID is selected in the bound combo box above
the SubjectName list box, the correct subject name appears in the bound list
box below. Other information is entered and the record is saved by tabbing
all the way through the form.

The problem is that only the SubjectID from the bound combo box is entered
into the underlying tbl-Photographs table. The subject name that appeared
in the bound SubjectName list box of the frm-Photographs is not in the table
even though the list box is bound to the SubjectName text box of the
tbl-Photographs table.

I realize that I should break the table down into multiple queries.
However, the employees are used to all of the information being in one
table. One step at a time.

Can anyone help me figure out how to have the SubjectName field of the table
populate by only selecting the appropriate entry from SubjectID combo box?

Phil F

 

[CompGeek78]

What you need to do is break the table up into many tables, then use a
query to put the information back together again into a single view.
When the employees look at it, they will be able to see it all
together.

Seriously, this one step will make working with your database much,
much easier. Working on non-normalized data is going to continue to
give you problems.

You can use the table analyzer to help you alter the structure without
having to re-enter data. Go to Tools...Analyze...Table. If you need
help with what the structure should be, feel free to explain the pieces
of data better and we can give you some ideas.

In particular, how does the procedure relate to the photograph? Is that
like the f-stop, flash settings used, etc? And how can a photo be of
multiple locations? Only answer those if you want to break it up and
need some advice.

Keven

 

[Rafa Transisitor]

HI:

I am not sure if I am understanding you correctly, but if I am right you are
using a table for 2 purpose, one to get information for & From a fiels (The
combo box) and also to enter information) if that is the case, you should
have the combo Box looking to a different table/Query not the same source.

 

[Phil F]

The combo box on the data entry form is bound to the Chart Number. The list
box on the data entry form is synchronized to the above-mentioned combo box.
Chart Number is selected in the combo box, the correct patient name does
appear in the form list box. The chart number is entered into the bound
form but the name that appear in the list box is not entered into the
underlying form. That is the problem

The database is a medical photography database. The procedures are
operations and the locations are anatomical areas. Several operations and
several anatomical locations may pertain to one photograph or sequence of
photographs. The table is structured like this:
Field Data Type Description
ID AutoNumber primary key
Folder Number Folder location of the
digital pictures
File(s) Hyperlink Hyperlink to digital
picture file name(s) on server
Date Date/Time Date picture was taken
Chart Number Text Patient Chart Number
Name Text Patient Name
Pre/Post Text Preop/Intraop/Postop
Location1 Text Anatomical location
Location2 Text "
Location3 Text "
Operation1 Text Operation name
Operation2 Text "
Operation3 Text "

There is also a Patient Name table upon which the combo box mentioned above
is based:
Chart Number Text Patient Chart Number
Patient Name Text Last Name, First Name MI
A macro that requeries the ComboBoxQuery noted below is set to activate via
the After Update event of the combo box.

A ComboBoxQuery upon which the list box that is mentioned above is based:
Patient Name From the Patient Name Table
Chart Number From the patient name table
with criteria: [Forms]![FormName]![Chart
Number]

There are also Location and Operations tables.

The next proble I have is to write a macro that will change the file names
into hyperlinks on the network server.

I hope that this detail helps. I appreciate everyone's input.

Phil F