Allowing different values in one field depending on the selected values of another

J

jlee9562

Registered User.
Local time
Today, 14:49
Joined
Nov 6, 2013
Messages
60
Hi All,

I have several result fields which are all drop down lists. I want each result field's drop down list values to be different depending on the selected value of the Test1 drop down list. After doing a thorough search on this topic I came up with using the .rowsource keyword. My syntax seems to be fine but I'm not getting any values under the result fields when I run the form (I left the values for the result fields in row source blank in table design mode). Here is my code so far:

Private Sub Test1_AfterUpdate()
If Me.Test1 = "Stress Echo" Or Me.Test1 = "Stress SPECT" Or Me.Test1 = "Stress PET" Or Me.Test1 = "Stress MRI" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
Me.Test1Result4.RowSourceType = "Value List"
Me.Test1Result2.RowSource = "Anterior;Inferior;Anterior Septal;Posterior;Inferior Septal;Lateral"
Me.Test1Result3.RowSource = "Base;Mid;Distal;Apical"
Me.Test1Result4.RowSource = "Ischemia only;Ischemia+Infarct;Infarct only;Normal"
Me.Test1Result5.Visible = False
ElseIf Me.Test1 = "Stress ECG" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
Me.Test1Result4.RowSourceType = "Value List"
Me.Test1Result5.RowSourceType = "Value List"
Me.Test1Result2.RowSource = "Anterior;Inferior;Posterior;Septal;Lateral"
Me.Test1Result3.RowSource = "Upsloping;Horizontal;Downsloping"
Me.Test1Result4.RowSource = "ST Depression;ST Elevation"
Me.Test1Result5.RowSource = ">=1 mm;>=2 mm"
ElseIf Me.Test1 = "CTA" Then
Me.Test1Result2.RowSourceType = "Value List"
Me.Test1Result3.RowSourceType = "Value List"
Me.Test1Result2.RowSource = "LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG"
Me.Test1Result3.RowSource = "0%;<50%;50-69%;70-99%;100%"
Me.Test1Result4.Visible = False
Me.Test1Result5.Visible = False
End If
End Sub

Any help would be much appreciated.

Thanks!!
Jim
 
Last edited:
Private Sub Test1_AfterUpdate()
Debug.Print "Me.Test1 = " & Me.Test1
Stop
...... Your code
End Sub

When the program stop, press CTRL+G and take a look at the value for Me.Test1 control.
I bet 10:1 that this value is not one of the expected.
 
You also need to google database normalization. Otherwise when you are asked to add a fifth test cycle or change the options for Stress ECGs, you're going to cry at the amount of reworking you'll have to do...
 
Hi Mihail,

After running your code and pressing CTRL+G, I get me.test1=Stress Echo. This is the first value in the list I placed in the row source in table design mode. I checked the case and it seems to match. Not sure if this is a problem?

David: You're right. I've been having to type a lot of the same stuff out. I'm a beginner at this so I'm trying to do what I can in the time allotted. If I ever get into this stuff deeper I'll surely look into that.

Thanks,
Jim
 
So... I lose my bet. It is why I never play to lottery.

Try this code:
Code: 
Private Sub Test1_AfterUpdate()
    Me.Test1Result2.RowSourceType = "Value List"
    Me.Test1Result3.RowSourceType = "Value List"
    Me.Test1Result4.RowSourceType = "Value List"
    Me.Test1Result5.RowSourceType = "Value List"

    If (Me.Test1 = "Stress Echo") Or (Me.Test1 = "Stress SPECT") Or (Me.Test1 = "Stress PET") Or (Me.Test1 = "Stress MRI") Then
        Me.Test1Result2.RowSource = "Anterior;Inferior;Anterior Septal;Posterior;Inferior Septal;Lateral"
        Me.Test1Result3.RowSource = "Base;Mid;Distal;Apical"
        Me.Test1Result4.RowSource = "Ischemia only;Ischemia+Infarct;Infarct only;Normal"
        Me.Test1Result5.Visible = False
        
        Stop
Exit Sub
    End If
    
    If Me.Test1 = "Stress ECG" Then
        Me.Test1Result2.RowSource = "Anterior;Inferior;Posterior;Septal;Lateral"
        Me.Test1Result3.RowSource = "Upsloping;Horizontal;Downsloping"
        Me.Test1Result4.RowSource = "ST Depression;ST Elevation"
        Me.Test1Result5.RowSource = ">=1 mm;>=2 mm"
        
        Stop
Exit Sub
    End If
    
    If Me.Test1 = "CTA" Then
        Me.Test1Result2.RowSource = "LM;LAD;Diag;LCx;OM;RCA;PDA;PL;LIMA;RIMA;SVG"
        Me.Test1Result3.RowSource = "0%;<50%;50-69%;70-99%;100%"
        Me.Test1Result4.Visible = False
        Me.Test1Result5.Visible = False
        
        Stop
    End If
    
    Stop
End Sub
Should do the same things as yours but it is a bit easier to debug
Is the code stopping somewhere before the last Stop statement ?
 
Hi Mihail,

Yes. The code stopped at the very first Stop. Does this mean there's no error?

I should also mention that I have a bunch of variables set to missing within each If statement. I didn't think it would affect the code so I took it out for easier reading.

Thanks,
Jim
 
Yes. The code stopped at the very first Stop. Does this mean there's no error?
Click to expand...
Yes. No error. Hope you have not a statement OnError here.
So, replace (comment) that stop and do a try in order to see if the combos are populated.

BTW:
I tried your code and work very well for me.
 
Hi Mihail,

I ran your code w/o the Stop and Exit Subs, but I still don't see any values under the results field after I select a value for Test1. I don't have any OnError statements in the code. I'm wondering whether the field's structure defined in table design as anything to do w/ the problem? I mean in table design mode, I defined the row source type to value list but kept the row source field empty (I had defined the fields using the look up wizard for short text).

I also tried placing the me.test1result2="" statements before the rowsource statement. But that didn't seem to do the trick either. :(
 
Ups. I think that I tried until now to defeat wind mills.
Until now I have think that you can't fill the combos with values.

Ufff.
You need to set the Control Source for your combos.
The Control Source is the field that is bounded to the control

Do this in Design View using the Properties window.

Also in Design view set the value for RowSourceType to Value List and remove the lines that set the same property from your code.
 
Hi Mihail,

I'm not sure I understand completely. But everytime I write code for each field I add [event procedure] and click on code builder. The control source for each field is basically the field where the user enters or selects values, so this would already be set? I can change the control source to something else but that would just change it to another field.

I've set the value for RowSourceType to Value List in the property sheet and removed that same line from my code. Ini the property sheet, should I have values for RowSource, or simply state it in my code? I think for the Test1 field I have to have values in the RowSource field in the property sheet first, right?

Thanks,
Jim
 
Here is a very good description for the combos properties.
Note that this is applied for a list box too.

PS.
Can you upload your DB ?
Convert to Access 2003 format (I use 2007)
 
Hi Mihail,

I've uploaded my access program. I am using Access 2013 but converted it to 2002-2003 format. There's definitely some bugs in the program but it's almost a working prototype. The Row Source problem is on the "Prior Tests" tab in the first form. The VBA code is placed in the Test1 AfterUpdate sub, however, when I select a value for Test1, I do not see any values for Result2, Result3, and Result4.

I'm also getting an error for my Submit Records button. It doesn't seem to like the if then statements before the docmd.gotorecord, , acnewrec ... but one problem at a time :)

Jim
 

Attachments

Your table fields are set as combo box/lookup type, not as text box type!
 
Thank you JHB!! I now see the values under each field!

If I have a different question should I post under a different thread?

Jim
 
jlee9562 said:
Thank you JHB!! I now see the values under each field!

If I have a different question should I post under a different thread?

Jim
Click to expand...
You're welcome. But Mihail did the most work!

Yes it is better to post each question in its own thread!
 
@JHB
You're welcome. But Mihail did the most work!
Click to expand...
This is not a problem. I'm glad to help and happy because the OP has the solution.

I reread the first post and... yes. The question is clear. Just I was blinded because the code that the OP has posted.
 
You must log in or register to reply here.

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom