adding to information already entered into table

[stacie]

Hi

I have a table which two different groups will enter data. For example, the
first group will enter demographic data, diagnosis, admit date on a form to
populate the table. The next group will enter discharge dates and charges,
etc on a different form. I want the second group to have to pick from the
names the first group has already entered, so that all information flows into
one table. Any ideas or suggestions?

I am rather new to access, so any instructions or suggestions given, I will
need all the details.

Thanks in advance!!

 

[John W. Vinson]

Hi

I have a table which two different groups will enter data. For example, the
first group will enter demographic data, diagnosis, admit date on a form to
populate the table. The next group will enter discharge dates and charges,
etc on a different form. I want the second group to have to pick from the
names the first group has already entered, so that all information flows into
one table. Any ideas or suggestions?

I am rather new to access, so any instructions or suggestions given, I will
need all the details.

Thanks in advance!!

It sounds like you may need to step back a bit and think about table design
FIRST. Tables are fundamental; forms are very much secondary - they're just
tools to enter data into tables.

Given that one patient may be admitted multiple times (right??), you need at
least TWO tables - a demographics table with personal information, which need
be entered only once, no matter how many times that person is admitted; and an
admissions table with fields for admission date, discharge date, etc.
Diagnoses also needs another table - a person doesn't have "a diagnosis", and
even an admission doesn't have "a diagnosis"; a patient might be admitted for
multiple indications (hypertension and angina and....)

Another concern is that any database containing medical and personal records
must meet the (very stringent) HIPAA patient privacy regulations. Some would
argue that Access databases are simply not sufficiently secure to meet these
regs.

It's very easy to do what you ask - have two different forms bound to the same
table, and use the Combo Box wizard to put a combo on the form so the second
user can locate a record entered by the first - but I really fear that what
you ask is not really what you'll want!!!

 

[Ken Sheridan]

Stacie:

The second group should use a form whose AllowAdditions property is set to
False (No) and which includes a means of going to a particular record, e.g. a
navigational combo box set up as follows:

ControlSource: Leave blank.

RowSource: SELECT PatientID, FirstName & " " & LastName FROM Patients
ORDER BY LastName, FirstName;

BoundColum: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

In the combo box's AfterUpdate event procedure put code like this:

Dim rst As Object
Dim ctrl As Control

Set rst = Me.Recordset.Clone
Set ctrl = Me.ActiveControl

With rst
.FindFirst "PatientID = " & ctrl
If Not .NoMatch Then
Me.Bookmark = .Bookmark
End If
End With

where PatientID is the primary key of the table and is of number data type.
If it were text data type you'd use:

.FindFirst "PatientID = """ & ctrl & """"

You enter code in an event procedure like this:

Select the combo box in form design view and open its properties sheet if
its not already open. Then select the After Update event property in the
properties sheet. Click on the 'build' button; that's the one on the right
with 3 dots. Select 'Code Builder' in the dialogue, and click OK. The VBA
window will open at the event procedure with the first and last lines already
in place. Enter the lines of code between these two existing lines.

For the RecordSource of the form use a query which sorts the records by
patient, e.g.

SELECT *
FROM Patients
ORDER BY LastName, FirstName;

In reality for this sort of thing two tables would really be necessary as a
patient (I'm assuming this scenario hypothetically) could be admitted more
than once, so you should have a Patients table and an Admissions table
related one-to-many on PatientID. That way you avoid redundancy (redundancy
is a bad thing as it allows inconsistent data to be entered!) by having each
patient's names, address etc stored only once. The Admissions table would
contain only data about the admissions; date diagnosis etc as the patient
data is pulled in from the Patients table via the relationship on the
PatientID columns.

The admissions records would be in a subform based on the Admissions table
in the patients form, the subform being linked to the main form on PatientID.
The first group would then either create a new patient record and enter the
preliminary admissions data, or select an existing patient (with a combo box
as above) and enter a new row in the admissions subform. So, versions of the
same form/subform can be used by both groups, in one case with the
AllowAdditions property of the main form and subform True, in the other case
with them False.

For an example of a form with a subform take a look at the categories form
in the sample Northwind database which comes with Access. You'll see this
includes a products subform with its LinkMasterFields and LinkChildFields
properties set to CategoryID to link it to the parent form.

Ken Sheridan
Stafford, England