subform problem 2

[badgateway]

Hey all,

I have a small problem using subforms. I have to convert a filmaker db to access, so far so good. What I have to accomplish is that I have to make a subform and in that subform I have a recordssource and I have to use checkboxes to add values in the access db. So when I click checkbox 1 value 1 has to be added to the table. When I click checkbox 2 value (f.e.) 4 has to be added. Inserting and deleting the records is not the biggest problem but showing the records into the checkboxes... Small detail the ID are stored in the same field so no different column per ID. Does anybody has useful hints or does anybody understands my problem.

Why I have to use checkboxes?
it's a program for medical input. So you have f.e. 10 sorts of a desease but there can be more than one desease be chosen. The people were used to work with checkboxes(old program was filemaker but it crashes constantly)so have to do almost a copy of the program in Access

thx all in advance

 

[pdldavis]

Hi, Your question is not very clear to me but I'll give it a go.

This sounds more like a table relationship or table structuring problem. I would guess that you should have a table that lists out all the problems / diseases. You have another table that lists out all the patients. There may be a third table - a link table that links patient 1 - disease 1, patient 1, disease 2, patient 1, disease 3....and so on.

If you have this sort of table structure then it would not be difficult to list out a patient with all of his / hers associated maladies via check boxes (the subform). The subform would link on the patient ID.

Helpful hint - It never hurts to give individuals recognition (stars *) when their contributions makes life easier for you.

Hope that helps.

 

[willir]

badgateway

It sounds like you are converting your data, and want to manually modify / adjust the record as you manually screen it. Is this close?

During any data conversion, the design schema as indicated by pdldavis is extremely important. Work done up front can save considerable time later on.

If this is a database design problem, or for good reading anyway, there is some pretty good documentation at JermeyNYC's web site...

http://www.alphabetcitydataworks.com/fundamentals.htm

As for updating the record depending on the disease condition, we would need to see more of your requirements.

 

[formerTexan]

In addition to the other relevant replies concerning table design, you and your users will be better served by using a listbox instead of a checkbox.

Assuming that the disease list will change or get added to over time, the only way to make changes to the checkbox and its disease list will require that someone open the form in design view and make actual form design changes. Data changes will by necessity get treated as design changes.

Use a listbox and the disease list can be handled like any other data.

I know your users are used to checkboxes. I don't suppose it would help to remind them blood letting and arsenic used to be common treatments. Some change is good.

Cheers,
Bill

 

[badgateway]

Hi all,

I’ll give it a try to explain it better.

I have the following dbs:

File_db Disease_db Disease_desc_db
File_id File_id Disease_id
Patient_id Disease_id Disease_desc

I have more tables like Disease_db and Disease_desc_db.
I managed to add a record using a checkbox and deleting but displaying in a checkbox?
I tried Iif(Diseas_Id=1; chk_box.value = true; chk_box.value = false) but that didn’t work.

@pdldavis
Can you help me cause I think you were pretty close(so I can add stars)
@others
I don’t think it’s possible to use a listbox(same problem as with checkbox). The doctors have to be able
To choose all records or just one or two and the result have to be displayed… So If there’s a new disease I have to add a listobx or checkbox in design view, I think. Unless you think there is a better way.

Hope I explained it a bit better.

Thanks for all your reactions
badgateway

 

[KenReay]

Hi

Are you saying that you have a checkbox for each entry in Disease_Desc_db?, given that I would expect teh lsit of possible diseases to be quite lengthy, this must be a very 'full' form

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[badgateway]

Hi Ryan,

I am afraid that's the case here. I don't know any other solution so far. Thing is that the users must see all possible disease and that they can choose as much as they want. So it's not that easy I think :-(

Regards
Badgateway

 

[KenReay]

Hi

I know this has been (partly) suggested before, but I would go for an approach using two list boxes, on the left a list a 'available' diseases, and on the right a list of 'got' diseases. Diseases would be selected by double clicking on the disease name in the left hand list

Alternatively, and perhaps neared what you already have, so that the Doctors can still have their check boxes (aren't doctors supposed to be intelligent people by the way who could adapt to a little bit of change?), by using a temporary table and a sub form you could present a scrolling list of disease names, each with a check box to the right, then you simply scroll down the list ticking or unticking as you wish



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[pdldavis]

Hi, I guess I'd need to have a look at what you've got in order to make a useful suggestion. If you like, send a zipped copy of the tables and forms in question with no sensitive data included and I'll have a look - slow day at the office.

pdldavis@yahoo.com

 

[badgateway]

Hi,

I'll send you a copy tomorrow because the one at home is already old. Thanks in advance for the help

badgateway

 

[willir]

badgateway

I am going to go back to the design issue because it will affect how you use your forms to enter your data and access your infomration.

I see a many-to-many relationship between disease and patient. A patient can have many diseases (okay, hopefully not many, but more than one), and many patients can have a disease.

For example: A patient may have diabetes, glycoma and now cancer. There can be many patients who have cancer.

To get away from a table design that uses Disease1, Disease2, etc you need to break the two apart using a joining table or intermediary table. Other wise, it will be awkward when you try gathering your infomration. Example: IF DISEASE1 = "CANCER" or DISEASE2 = "CANCER" or DISEASE3 = "CANCER"...

I have seen this. Moreover, a new disease comes along, and you have to add another check box to the table design.

A better solution is to use the joining table as such...

PatientTbl
PatientID - primary key
PatientName
etc

DiseaseTbl
DiseaseID - primary key
DiseaseName
etc

DiseaseGroupTbl
PatientID - foreign key to PatientTbl
DiseaseID - foreign key to DiseaseTbl
plus add fields for tracking this patient, this disease.

Primary key would be PatientID + DiseaseID
So a patient can only get a disease once. If this statement is false, then there are variations on this solution, but principle remains the same. This table tracks which patient has which disease.

Then when linking a disease to a patient, you can approach it two ways...

From the Patient main form
Add a subform based on the DiseaseGroupTbl table. Link the Parent / Child forms using the PatientID. Hide the PatientID on the subform. And change the DiseaseID text field to a combo box that binds the DiseaseID but displays the Disease name. Change the subfrom from a single form to a Contieous form. Now when you query a patient, you will see all diseases the patient is currently infected with.

And you can also create a main form using the Disease.
Add a subform based on the DiseaseGroupTbl table. Link the form / subform using the DiseaseID. Make the diseaseID invisible on the subform, and change the PatientID text field to a combo box that binds the PatientID and displays the patient's surname. Add another combbox using the PatientID but this time display the patient's first name. Now, when you look up a disease, you will see all infected patients with that disease.

The design can be more complicated such as history, re-occurences, etc. But the principle remains the same -- treat the relationship between disease and patient as a many-to-many.

Sorry to have thrown a spanner into the works. I suspect this is not what you wanted to hear.

Richard