subform changes according to user input

[Guest]

Hi,
I am trying to update a database at work. The setting is that we hhave a form which is filled with demographic details and then a seperate section relating to the disease which the patient has. Currently we have different forms for handling these. I was wondering if it is possible to have a subform in the main form which changes according to the user input in a combo-box. For example if a user selects measles a particular subform comes up, if the user selects chickenpox a different subform comes up on the same page.
looking forward to your input

gianfranco

 

[tina]

yes, easy enough to set up. open your main form in design view and add a
subform control - there is a button for it on the Toolbox toolbar. think of
the subform control as being like a shoebox. it has a name - Access probably
named it Child0 when you placed it in the form - and you can put any pair of
shoes (any form) into it that you want.
next, for ease of maintenance, suggest you build a table, as

tblSubforms
SLabel
SName

the first column is for the "label" you want the users to see in the combo
box, like "Measles" and "ChickenPox". the second column is for the actual
name of the form object in the database window, such as "frmMeasles" and
"frmChickenpox".

in your main form, set your combo box as follows:

RowSource: SELECT SLabel, SName FROM tblSubforms ORDER BY SName
ColumnCount: 2
ColumnWidths: 0"; 1"
BoundColumn: 1
ListWidth: 1.25"
LimitToList: Yes

open the VBE window (click View, Code on the menu bar) and add the following
procedure, as

Private Sub MyCombName_AfterUpdate()

Me!MySubformCONTROLName.SourceObject = Me!MyComboName

End Sub

substitute the correct name of your combo box, and the correct name of the
subform *control* (the shoebox's name).

that takes care of your question; now i'm wondering about your table setup.
if you have separate subforms for different diseases, then do you have
separate tables for those diseases also? and if so, why separate tables - do
you collect very different types of data for different diseases? if you're
collecting pretty much the same data for each disease, probably you should
have one tblDiseases - and then you'd need only one subform, not several.
hmmm, food for thought.

hth

Hi,
I am trying to update a database at work. The setting is that we hhave a

form which is filled with demographic details and then a seperate section
relating to the disease which the patient has. Currently we have different
forms for handling these. I was wondering if it is possible to have a
subform in the main form which changes according to the user input in a
combo-box. For example if a user selects measles a particular subform comes
up, if the user selects chickenpox a different subform comes up on the same
page.

 

[Guest]

thanks for the answer - will try it on monday at work. Regarding the table point - i inhereted the database from other workers and it was set up with multiple databases for all the diseases. I have eben thinking about collecting everything in one database but that would mean lots of fields (close to two hundred i would estimate). I dont know if that would be a good idea what do you think?
thanks
gianfranco

 

[tina]

it was set up with multiple databases for all the diseases. I have eben
thinking > about collecting everything in one database but that would mean
lots of fields > (close to two hundred i would estimate)

when you say "database", do you really mean "table"? as in "multiple tables
for all the diseases" and "everything in one table"? assuming you mean
"table", read on:
you'll have to look at the data, and decide if it makes sense to put it in
one table. what you absolutely do *not* do, is just take all the fields from
each table and dump them into a single table together. any table with 200
fields is almost certainly not normalized.
can you post the details of at least 2 or 3 of the disease tables? use the
following format:

tblName
FirstFieldName (PK)
SecondFieldName
ThirdFieldName (fk from tblName)
FourthFieldName
etc

indicate which field is the primary key, with the (PK). indicate any fields
that are foreign keys, and the tables they link to, with the (fk from
tblName).
i'll check back later today to see if you've posted.

tina

thanks for the answer - will try it on monday at work. Regarding the table

point - i inhereted the database from other workers and it was set up with
multiple databases for all the diseases. I have eben thinking about
collecting everything in one database but that would mean lots of fields
(close to two hundred i would estimate). I dont know if that would be a good
idea what do you think?

 

[Guest]

i just tried the form out and it worked - however the two forms are linked via a field (caseid) - what do i need to do in order to keep the data linked in the same way?
thanks
gianfranco

 

[tina]

well, it's easy enough to do, but i need to know a little more about your
tables. as i asked before, does each disease have its' own table? or all
diseases in one table? whether one or many, what is the foreign key field
that is linked with the "caseid" field in the main table?

i just tried the form out and it worked - however the two forms are linked

via a field (caseid) - what do i need to do in order to keep the data linked
in the same way?