Requesting for ways to autofill one field depending upon previous field

[Prakashpdl]

Hello I am new to access. I am using Access 2010 and designing simple database. I want to autofill a field depending upon a value selected in previous combo.

I have three situations

1. combo after combo
I have one combo
Hypertension -- yes/No/unknown
If hypertension is "yes", I would like to show 3 options - Mild/moderate/severe in next combo and to show "not applicable" if previous combo selects "No"


2. Text field after combo
combo name "ECG" options are - normal/abnormal
Next text field name - Abnormality
In this field, I will type abnormal finding if first combo selection is "abnormal"
if the combo selection is normal, then text field "abnormality" should autofill "not applicable"

3. Text field after combo
combo name "type" options are - focal/bifocal/multifocal/generalized
subsequent Text field "type cat" - If previous combo selection is one among 3 options "focal"/"bifocal"/"multifocal", text field should auto fill "focal" and if combo selects "generalized", text field should auto fill "non focal"

How can I do it? Requesting VBA code and where to put the codes?

 

[Ranman256]

You CAN use macros,
In the Hypertension combo,
Event AFTER UPDATE,click macro,
Use IF cbo=No,
Set the field using full paths: forms!fMyForm!cboBox= Not Applicable,

 

[bob fitz]

Hello I am new to access. I am using Access 2010 and designing simple database. I want to autofill a field depending upon a value selected in previous combo.

I have three situations

1. combo after combo
I have one combo
Hypertension -- yes/No/unknown
If hypertension is "yes", I would like to show 3 options - Mild/moderate/severe in next combo and to show "not applicable" if previous combo selects "No"


2. Text field after combo
combo name "ECG" options are - normal/abnormal
Next text field name - Abnormality
In this field, I will type abnormal finding if first combo selection is "abnormal"
if the combo selection is normal, then text field "abnormality" should autofill "not applicable"

3. Text field after combo
combo name "type" options are - focal/bifocal/multifocal/generalized
subsequent Text field "type cat" - If previous combo selection is one among 3 options "focal"/"bifocal"/"multifocal", text field should auto fill "focal" and if combo selects "generalized", text field should auto fill "non focal"

How can I do it? Requesting VBA code and where to put the codes?

Take a look at the attached db. This shows one way of achieving your aim for 1) and 2), assuming that the choices in the combo boxes are to be hard coded.

 

[Prakashpdl]

Thanks Bob! It worked

 

[bob fitz]

Glad to help

Have you worked out for yourself, how to accomplish 3) from your OP

 

[Prakashpdl]

Thanks again Bob. I did for 3 also, I had to keep VBA codes for each cases separately. It worked. 3 is reverse of 1. In 1, you have written row source. For 3, I could not put row source and I kept VBA codes individually for each case.

 

[Prakashpdl]

Glad to help

Have you worked out for yourself, how to accomplish 3) from your OP

Hello Bob,

Can you help me please with one more code
condition is like this --- have 2 combo boxes Combo1, Combo2
Combo 1 has options A, B, C
If I select option A in combo 1, Combo 2 should list items in column 1 of Table 1
If I select option B in combo 1, Combo 2 should list items in column 2 of the same table (table 1)
If I select option C in combo 1, Combo 2 should list items in column 3 of the same table (table 1)

Thank you

 

[Gasman]

PMFJI,
You need to set the rowsource property accordingly?

https://docs.microsoft.com/en-us/office/vba/api/access.combobox.rowsource

 

[bob fitz]

What you want to do is usually called "Cascading Combo Boxes". There will be many threads on this subject. A google search will even return some videos on the usual ways to handle this.
The attached db shows an alternative way to do this which works in this scenario.
As always, post back if you have further questions

 

[Prakashpdl]

What you want to do is usually called "Cascading Combo Boxes". There will be many threads on this subject. A google search will even return some videos on the usual ways to handle this.
The attached db shows an alternative way to do this which works in this scenario.
As always, post back if you have further questions
[/QUOT

 

[Prakashpdl]

Thanks Bob. I see that you have built querry in combo 2 and put VBA code as afterevent porcedure in combo 1. How do you set rowsource property?Can you let me know the steps how to do it? I tried to add case "D" in combo 1, I added column 4 in table 1, but I could not get the fourth set of lists (which I put in colFour of Tabl1) in combo 2. Can you please teach me how to do it?

 

[Gasman]

Thanks Bob. I see that you have built querry in combo 2 and put VBA code as afterevent porcedure in combo 1. How do you set rowsource property?Can you let me know the steps how to do it? I tried to add case "D" in combo 1, I added column 4 in table 1, but I could not get the fourth set of lists (which I put in colFour of Tabl1) in combo 2. Can you please teach me how to do it?

You have your structure all wrong.
Those records should be identfied by one fileld in the table. Then it would not matter how many your need.?

 

[bob fitz]

Thanks Bob. I see that you have built querry in combo 2 and put VBA code as afterevent porcedure in combo 1. How do you set rowsource property?Can you let me know the steps how to do it? I tried to add case "D" in combo 1, I added column 4 in table 1, but I could not get the fourth set of lists (which I put in colFour of Tabl1) in combo 2. Can you please teach me how to do it?

Take a look at the attached db.
On Form2 I have made the following changes:
Manually added "D" to the Row Source of Combo1
Added a field called ColFour to tblTable1
Added that field to the RowSource of Combo2
Added to the Select Case statement used in the AfterUpdate event of Combo1

I have added two combo boxes called "cmbOnePK" and "cmbTwo"
Unlike the first two combo boxes, these each use a table for their RowSource. cmbOnePK uses "tblCmbOne" and cmbTwo uses "tblCmbTwo" which have a one to many relationship. The benefit of this is that it is easier to add data to the tables that to keep changing properties of controls on the form.
The statement used as the Row Source of cmbTwo references the selected value of cmbOnePK to return the related records.
Code in the GotFocus event of cmbTwo re-queries cmbtwo to apply the correct criteria.

This method is shown as the first method of cascading combo boxes in this much quoted link: http://www.baldyweb.com/CascadingCombo.htm

EDIT:
Forgot to attach the db