Writting two subforms to the same table.

[robochick84]

I have two subforms sitting on a form that I am attempting to write to the
same table.

On the two subforms (A and B) exists the same/identical fields from the
single table (table 1).

What unique qualifier or identifier (or even programming) is needed to allow
me to write both of these subforms (A and B) to the table (table 1).

I am able to write to the table now, but when I go back into the form to look
at the subforms and call up the subforms through the forms unbound field, it
cannot retrieve the information successfully. I believe due to the lack of a
unique identifier on the subforms for some reason.

Thanks.

 

[Jeff Boyce]

You've described "how" you are trying to accomplish something (i.e., two
subforms, both based on the same table).

You've not described "what" or "why" -- the underlying business need.

I ask, not out of curiosity, but because there may be alternatives to the
approach you've chosen, if only we understood what (not "how") you want to
accomplish.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[robochick84 via AccessMonster.com]

The database as a whole starts with one form (screening) writing to table
(person). This is what creates the information for the rest of the database.

The user then opens the second form (visit) which contains four subforms
(Baseline, Test 2) which write to person table, and (Test 3, Test 4) which
write to Visit table.

The user selects an unbound drop box at the top of the visit form which pulls
in the information for all of the subforms.

The problem lies with the subforms Test 3 and Test 4 which contain identical
information except for the test name (i.e. Test 3 and Test 4).

I have put a drop box at the top of the form for the user to select Test 3 or
Test 4, and it gets written to the table Visit (with the autoID) however, you
can only write one due to the autoID being unique (this may be a problem.)

So...... table visit has 20ish fields that are contained on forms Test 3 and
Test 4. Auto ID doesn't work to keep the information unique (from what I
have done).

The forms will write to the table without the auto ID, however when the forms
are called up again in the future, they only bring up one (Test 3 or Test 4).
Ideally, they should display Test 3 and Test 4 pulled from the table, but it
isn't recalling from the table correctly.

Sorry, I can't give any more unique identifiers as it is a medical study.

Thanks.

 

[Jeff Boyce]

No need for specific data values (HIPAA & all!)...

Your description is still focusing more on the "how" (use this form, use
that subform, ...) rather than the underlying table structure.

I may not have understood your explanation, but it sounds like you have
repeating fields (Baseline, Test2, ... TestN) in your tblPerson. This might
be how you'd have to do it if you were using a spreadsheet, but Access
doesn't perform well if you feed it 'sheet data.

And there's more ... if I've correctly understood (and you have repeating
fields), you have a maintenance nightmare! If you have your test (results)
showing as columns/fields in your tblPerson, then you have to customize your
queries, forms (and subforms), reports (and subreports), code, macros, etc.
to handle the table structure. Then the regulations change and you have to
add (or subtract) a Test! Now you have to modify your table structure,
queries, forms, subforms, code ...! What a pain!!

If, on the other hand, you used a table to hold test results, and a table to
hold tests, you can add a new test (or end-date an existing test) without
having to make ANY changes to your database/application structure!

Or have I misunderstood your description?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[robochick84 via AccessMonster.com]

I guess I don't understand the underlying table structure question, I will
try again.

tblPerson is not really the problem, and I can ignore, tying to give some
background as to how it fits in.

tblVisit, with primary keys infusionType and subjID has a list of 20+ other
'test values'

I am trying to use subform (Test 3) and subform (Test 4) to both write to
tblVisit.

All 20+ 'test values' are identically displayed in the same fashion on both
subforms.

I want to be able to select the 'infusionType' (somehow, or have it hidden to
automatically identify the subform). To be able to uniquely write both
subforms with the identical fields to the same table (which works, I just
can't bring it back up to display after writing to the table).

Thanks!!

No need for specific data values (HIPAA & all!)...

Your description is still focusing more on the "how" (use this form, use
that subform, ...) rather than the underlying table structure.

I may not have understood your explanation, but it sounds like you have
repeating fields (Baseline, Test2, ... TestN) in your tblPerson. This might
be how you'd have to do it if you were using a spreadsheet, but Access
doesn't perform well if you feed it 'sheet data.

And there's more ... if I've correctly understood (and you have repeating
fields), you have a maintenance nightmare! If you have your test (results)
showing as columns/fields in your tblPerson, then you have to customize your
queries, forms (and subforms), reports (and subreports), code, macros, etc.
to handle the table structure. Then the regulations change and you have to
add (or subtract) a Test! Now you have to modify your table structure,
queries, forms, subforms, code ...! What a pain!!

If, on the other hand, you used a table to hold test results, and a table to
hold tests, you can add a new test (or end-date an existing test) without
having to make ANY changes to your database/application structure!

Or have I misunderstood your description?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The database as a whole starts with one form (screening) writing to table
(person). This is what creates the information for the rest of the

[quoted text clipped - 34 lines]

Thanks.

 

[Jeff Boyce]

It all starts with the data...

If you don't have that well-normalized, you (*and Access*) will struggle to
make the forms and subforms do what you want.

Please post a description of your table structures ... table names, field
names, and a description of what kind of data is being recorded in those
fields.

For example, if you were working with signing folks up for classes, you
might have something like:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription
MaximumCapacity

trelRegistration
RegistrationID
StudentID
ClassID
DateOfRegistratioin

(I haven't described the contents of these fields because the fieldnames are
fairly self-explanatory. A fieldname like "Test2" doesn't really tell me
much about what is stored in the field.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I guess I don't understand the underlying table structure question, I will
try again.

tblPerson is not really the problem, and I can ignore, tying to give some
background as to how it fits in.

tblVisit, with primary keys infusionType and subjID has a list of 20+
other
'test values'

I am trying to use subform (Test 3) and subform (Test 4) to both write to
tblVisit.

All 20+ 'test values' are identically displayed in the same fashion on
both
subforms.

I want to be able to select the 'infusionType' (somehow, or have it hidden
to
automatically identify the subform). To be able to uniquely write both
subforms with the identical fields to the same table (which works, I just
can't bring it back up to display after writing to the table).

Thanks!!

No need for specific data values (HIPAA & all!)...

Your description is still focusing more on the "how" (use this form, use
that subform, ...) rather than the underlying table structure.

I may not have understood your explanation, but it sounds like you have
repeating fields (Baseline, Test2, ... TestN) in your tblPerson. This
might
be how you'd have to do it if you were using a spreadsheet, but Access
doesn't perform well if you feed it 'sheet data.

And there's more ... if I've correctly understood (and you have repeating
fields), you have a maintenance nightmare! If you have your test
(results)
showing as columns/fields in your tblPerson, then you have to customize
your
queries, forms (and subforms), reports (and subreports), code, macros,
etc.
to handle the table structure. Then the regulations change and you have
to
add (or subtract) a Test! Now you have to modify your table structure,
queries, forms, subforms, code ...! What a pain!!

If, on the other hand, you used a table to hold test results, and a table
to
hold tests, you can add a new test (or end-date an existing test) without
having to make ANY changes to your database/application structure!

Or have I misunderstood your description?

Regards

Jeff Boyce
Microsoft Office/Access MVP

The database as a whole starts with one form (screening) writing to
table
(person). This is what creates the information for the rest of the

[quoted text clipped - 34 lines]

Thanks.

 

[robochick84 via AccessMonster.com]

tblPerson
subjID
intAge
intHeight
intWeight
chkBloodWork

tblVisit
subjID
infusionType (Test type)
intRestBP (resting Blood Pressure)
intRestHR (restingHR)

The subforms screening, baseline and 'test1/DXA' are all storing their
information efficiently into tblPerson, works great and I use this in many
applications.

The problem *only* lies with tblVisit and the infusionType.

I want to record subjID, intRestBP, intRestHR from both subforms 'Test2/Asc'
and 'Test3/Sal'.

The only field that contains unique information on the subforms is the field
'infusionType' where I have a drop box to select 'Test2/Asc' or 'Test3/Sal'.

I am pretty sure that this is an 'onEvent' or 'onOpen' type of function that
needs just a little bit of VB.... I just have no knowledge of programming

It all starts with the data...

If you don't have that well-normalized, you (*and Access*) will struggle to
make the forms and subforms do what you want.

Please post a description of your table structures ... table names, field
names, and a description of what kind of data is being recorded in those
fields.

For example, if you were working with signing folks up for classes, you
might have something like:

tblStudent
StudentID
FirstName
LastName
DateOfBirth

tblClass
ClassID
ClassTitle
ClassDescription
MaximumCapacity

trelRegistration
RegistrationID
StudentID
ClassID
DateOfRegistratioin

(I haven't described the contents of these fields because the fieldnames are
fairly self-explanatory. A fieldname like "Test2" doesn't really tell me
much about what is stored in the field.)

Regards

Jeff Boyce
Microsoft Office/Access MVP

I guess I don't understand the underlying table structure question, I will
try again.

[quoted text clipped - 62 lines]

 

[Jeff Boyce]

It sounds like you've already decided "how" you'll handle this.

I'll just point out that your tblVisit doesn't appear to have any date/time
information. When did the visit occur? Can you have more than one visit?

I'll also point out that saving intAge means you'll have to regularly
(?!daily?!) re-check all the folks listed in tblPerson, since people have
birthdays most every day of the year, and your information will be
potentially invalid the day after Age is recorded. You're better off
recording DOB, and using a query to calculate "age" (as of 'today').

I don't get how "screening, baseline and 'text1/DXA' data are being stored
in tblPerson -- I didn't notice fields for whatever data you may be
recording about these.

I'll step back, as it seems you've already determined the way you will do
this.

Perhaps another newsgroup reader can provide a way to do it your way. My
concern is still that you are trying to do something that Access is not
designed to (easily) do.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[robochick84 via AccessMonster.com]

The 'how' behind the project is determined by a team of doctors and
statisticians long before I ever see it come to me.

date/time is stored on the tables but it is more for perspective of the study,
a patient can always only have one visit for one study that is why they are
called Test 1, 2, 3, etc.

intAge is determined, as above, by the PI, long before Analyst receive the
project. Along with HIPAA, there are many security reasons we do not handle
date of birth.

Screen is a form written to tblPatient.

Visit contains the subforms Base, DXA, Sal, and Asc. When opening Visit a
drop down box is selected that is populated by an unbound field from
tblPatient (studyID).

It sounds like you've already decided "how" you'll handle this.

I'll just point out that your tblVisit doesn't appear to have any date/time
information. When did the visit occur? Can you have more than one visit?

I'll also point out that saving intAge means you'll have to regularly
(?!daily?!) re-check all the folks listed in tblPerson, since people have
birthdays most every day of the year, and your information will be
potentially invalid the day after Age is recorded. You're better off
recording DOB, and using a query to calculate "age" (as of 'today').

I don't get how "screening, baseline and 'text1/DXA' data are being stored
in tblPerson -- I didn't notice fields for whatever data you may be
recording about these.

I'll step back, as it seems you've already determined the way you will do
this.

Perhaps another newsgroup reader can provide a way to do it your way. My
concern is still that you are trying to do something that Access is not
designed to (easily) do.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

tblPerson
subjID

[quoted text clipped - 28 lines]

needs just a little bit of VB.... I just have no knowledge of programming

 

[robochick84 via AccessMonster.com]

If anyone out there is looking for a solution in the future.

In order to have multiple subforms (in a tab format) that have identical
fields from the same table (read/write):

On the subform properties --> in the record source field --> I have written
a query
SELECT Visit.* FROM Visit WHERE (((Visit.infusionType)=0));

Visit = table
infusionType = only unique field between the two identical subforms (one is
set to 0 the other is set to 1 for the two unique types)


On the subform properties --> onCurrent ---> I created the following VB


Private Sub Form_Current()

infusionType = 0

End Sub

This sets the two forms (uniquely) to default to the type 0 or the type 1.

This will allow you to use two identical subforms/identical fields (with the
above exception) to write to the same table.

The 'how' behind the project is determined by a team of doctors and
statisticians long before I ever see it come to me.

date/time is stored on the tables but it is more for perspective of the study,
a patient can always only have one visit for one study that is why they are
called Test 1, 2, 3, etc.

intAge is determined, as above, by the PI, long before Analyst receive the
project. Along with HIPAA, there are many security reasons we do not handle
date of birth.

Screen is a form written to tblPatient.

Visit contains the subforms Base, DXA, Sal, and Asc. When opening Visit a
drop down box is selected that is populated by an unbound field from
tblPatient (studyID).

It sounds like you've already decided "how" you'll handle this.

[quoted text clipped - 30 lines]