Updating a filed according to value selected in another field. PLEASE HELP ?!!

[johnport]

I am a newbie in Access and I truly need your help, please.



I have a database with two tables.

A main table (MainTable) where all the data is stored.
This table has several fields:

1.name of the patient,
2.date of birth,
3.address,
4.country…,
.
.
.
11.Diagnosis,
12.Diagnosis_group,
.
.
21. Treatment
22. response to treatment…

I have a form through which users input data into this main table.

I have another table (DiagnosisTable) which has 2 fields:

Diagnosis
DiagnosisGroup

(Please see attachment)

So what happens is different diagnosis are grouped by categories under DiagnosisGroup.

When users (using the main form) get to the Diagnosis field input they have to chose from a combo box one value from the field Diagnosis of the table DiagnosisTable and this value will be stored in the respective field in the MainTable.
What I wish is to automatically update the DiagnosisGroup field in the MainTable when the user choses a specific Diagnosis, so that the user doesn’t have to input into the DiagnosisGroup field as this field is already updated whenever the user inputs a Diagnosis (or later decides to change the diagnosis into another value).

Remember several different Diagnosis can result in the same DiagnosisGroup as in the table above.

Can you help me?

Thank you so much.

John

PS - I am sorry the diagnosis in the Diagnosis table are in portuguese...

[weekend00]

you can search for "linked combo box" for help.

[ssanfu]

From your description of the main table, it soulds like it should be split into at least two tables.

But to answer your question, try this:

I will call the Diagnosis combo box "cboDiagnosis" and the Diagnosis Group control cboDiagnosisGroup.

For the control "cboDiagnosis", set

DATA TAB
------------
Row Source ........ SELECT Diagnosis, DiagnosisGroup FROM DiagnosisTable ORDER BY Diagnosis
Bound column ..... 1
Limit to List ........ Yes

FORMAT TAB
--------------
Column count ........... 2
Column widths ........ 2"; 0"
(set the first column to the width you need)


The AfterUpdate eventof "cboDiagnosis" should look like

 
Sub cboDiagnosis_AfterUpdate()
  Me.cboDiagnosisGroup = Me.cboDiagnosis.Column(1) ' second column - (zero based)
End Sub

[johnport]

I am going to try this out and will get back to you to let you know if I did it right.
Thank you so much for your help.

John

From your description of the main table, it soulds like it should be split into at least two tables.

But to answer your question, try this:

I will call the Diagnosis combo box "cboDiagnosis" and the Diagnosis Group control cboDiagnosisGroup.

For the control "cboDiagnosis", set

DATA TAB
------------
Row Source ........ SELECT Diagnosis, DiagnosisGroup FROM DiagnosisTable ORDER BY Diagnosis
Bound column ..... 1
Limit to List ........ Yes

FORMAT TAB
--------------
Column count ........... 2
Column widths ........ 2"; 0"
(set the first column to the width you need)


The AfterUpdate eventof "cboDiagnosis" should look like

 
Sub cboDiagnosis_AfterUpdate()
  Me.cboDiagnosisGroup = Me.cboDiagnosis.Column(1) ' second column - (zero based)
End Sub

[johnport]

I tried your solution step by step.
Everything was going fine.
On the final step I went for the private sub for AfterUpdate exactelly as you said:

Sub cboDiagnosis_AfterUpdate()
Me.cboDiagnosisGroup = Me.cboDiagnosis.Column(1) ' second column - (zero based)
End Sub

but then I got an error that said Expected end of statement and the word Column was highlighted.

Maybe it's a question of syntax but I got stuck here.

What's your advice?

Thank you.

John

From your description of the main table, it soulds like it should be split into at least two tables.

But to answer your question, try this:

I will call the Diagnosis combo box "cboDiagnosis" and the Diagnosis Group control cboDiagnosisGroup.

For the control "cboDiagnosis", set

DATA TAB
------------
Row Source ........ SELECT Diagnosis, DiagnosisGroup FROM DiagnosisTable ORDER BY Diagnosis
Bound column ..... 1
Limit to List ........ Yes

FORMAT TAB
--------------
Column count ........... 2
Column widths ........ 2"; 0"
(set the first column to the width you need)


The AfterUpdate eventof "cboDiagnosis" should look like

[ssanfu]

Things to try:

[ssanfu]

Things to try:
- Do all of the names in the code match the control names on the form?
-Are the first two lines
Option Compare Database
Option Explicit
- Compile the code - on the menu-Debug/Compile

- Is the code for the afterupdate connected to the control?
(in design view of the form,
click on cboDiagnosis,
open the properties dialog,
click on the event tab,
click on Afterupdate (you should see [Event Procedure]),
click on the ellipsis (...).
It should take you to the code for after update event.)

[johnport]

Things to try:

1-Yes

2-No (option compare, explicit are not there. Just Pirvate sub etc etc)

3-I use comile and debug - always colloring the code with yellow

4 - i was almost obsessive, tried and retried...no luck. Maybe I am doing something wrong and just can't see it.

Thank you, Steve.

I will keep trying to see where I did something wrong.

[ssanfu]

If you want, in a COPY if your mdb, delete any sensitive info, do a compact and repair, then (if it is small enough) post it here. I will take a look at it...

[johnport]

Thank you!
I will do it asap.
I will try to send it to you tonight but if not tonight surely tomorrow I will send it you you.
Thank you again.

John

If you want, in a COPY if your mdb, delete any sensitive info, do a compact and repair, then (if it is small enough) post it here. I will take a look at it...

[johnport]

I did erased or modify any sensitive data.
I did repair and compact database.
Even so it's almost 2,5 megabites, so it's to heavy.

I can put it on a skydrive and send you the link.
I will do it tomorrow morning.

I also send you a doc file with more exact explanations.

Thank you so much!

John

[johnport]

I am sending my database in a zip file.

Thank you again, Steve!

John

[ssanfu]

John,

Take a look at the attached zip. I added "Option Explicit" at the top (second line) of every code page.

I modified the row source for [txt_ICD10-1] and added the code to update [GrupoDiag] upon selection of combo box [txt_ICD10-1]. I also added a text box for a different way of displaying the info for "AJUDA".

I don't know if you are still in development or if you are actually using the database, but you should really think about normalizing your tables. (Google: normalization) It might take more work now, but it will save you lots of headaches later on.

If you have questions, post back or PM me.

[johnport]

Thank you SO much, Steve!!

And the box for AJUDA was a wonderful idea!

I am developing this database, yes. I am a medical doctor (psychiatrist in a public hospital in Portugal) who started to learn MsAccess after realizing my Department had no way of analizing the population of patients by several items (demographic and clinical data). No software. Nothing at all. So I started making this database but I have never had one formal lesson about MsAccess and A LOT of what you see was made with the precious help of friends in foruns as you did helped me this time.

Thanks for your advices as well. And you are right: I must organize the names of the controls (when I started the database I had no ideia that would be important).

I am going to open the database and try to understand *how* did you solve the problems I have put to you. That's the only way to learn.

Again thank you so much!

John

[ssanfu]

John,

Here are a few of the sites I rely on for info and ideas:

Getting started, Crystal has a tutorial and some video clips at

http://www.accessmvp.com/Strive4Peace/


For coding examples, these are two I keep bookmarked:

http://mvps.org/access/

http://allenbrowne.com/tips.html


For MDB examples (to tear apart) from a variety of people:

http://rogersaccesslibrary.com/




Also look here. These are Access MVP sites:

http://mvps.org/links.html#Access