YES or NO option selection based on result of another control 1

[mraetrudeaujr]

I have a combo box that is based on a table (tbl DACS Country Code Table) for the 'selectable' values. This table contains all of the possible country codes that we use in our office. We also must distinguish between "Special Interest Region" countries and "Non-Special Interest Region" countries. So, armed with this, I made an 'option group' that populates a field in the "2005 LOG" table. It is a simple 'YES' or 'NO' option group.

Here is the request of the endusers; "Can you make it so that if we select an SIR country, that the 'YES/NO' SIR box will automatically check "YES"?

Right now I have it set to a default of "NO" and the endusers have to select "YES" if they encounter one of these SIR countries. How would I go about this? I have another table built that has ONLY these SIR countries...if this would help.

The breakdown:
table name - 2005 LOG
table name - tbl DACS Country Code Table
table name - tbl Special Interest Region Countries
combobox name - cboCountryPage2
option group name - optgrpSIRCountry

I hope that this will help someone to help me with the necessary VB code that will get this to work, thank you.

 

[xicana]

what i would do is add a column to the "tbl DACS Country Code Table" that specifies whether that country is a SIR or not...then in the after update event of the combo box I would have the YES/NO SIR box change based on the value of that column in the DACS Country Code table.

Does that make sense?

Sandy

 

[mraetrudeaujr]

Sure, but how would this look? I'm sure that you would go into the 'property' sheet and run an event procedure based on this 'After Update' property...but how would this look in code?

 

[xicana]

I wrote out a quick example:

Private Sub Combo1_AfterUpdate()
    If Me!Combo1.Column(2) = True Then
        Me!chkYes = True
        Me!chkNo = False
    Else
        Me!chkYes = False
        Me!chkNo = True
    End If
    
End Sub

I believe you are using an option group where I used check boxes...but hopefully this gives you an idea - if you still need help I can come up with a quick example using option groups (I've never really used them but I'm sure the idea is similar).

Sandy

 

[xicana]

oops...maybe I should have made clear what was in the combo box

Column(0) was the country code
Column(1) was the country name
Column(2) was a YES/NO field indicating whether the country was an SIR country or not.

Sandy

 

[mraetrudeaujr]

Xicana,

I tried to use what you gave me...and it almost worked (I think); but it hung up on the first statement after the 'Else' operator;

Private Sub cboCountryPage2_AfterUpdate()
    If Me!cboCountryPage2.Column(4) = True Then
        Me!optSirCountryYESPage2 = True
        Me!optSirCountryNOPage2 = False
    Else
        [highlight]Me!optSirCountryYESPage2 = False[/highlight]
        Me!optSirCountryNOPage2 = True
    End If
    
End Sub

Combo box name = "cboCountryPage2"
Table name = "tbl DACS Country Code Table"
Column(1) SearchValue (..a five digit alpha)
Column(2) ValueCode (..a three digit alpha...not used)
Column(3) TableText (Actual spelled-out country name)
Column(4) SpecialInterestCountry (1=YES; 2=NO)(option group..NOT a checkbox setup)
Option Group Name = "optgrpSIRCountryPage2"
I took out the 'default' value of "2" for now...in the hopes that I could get this code ironed out!

Is this enough information? Thanks for the help.

Al

 

[mraetrudeaujr]

Xciana,

I finally got it to work. Here is the final code, used on the 'combo box' After Update event;

Private Sub cboCountryPage2_AfterUpdate()
    If Me.cboCountryPage2.Column(3) = 1 Then
    Me.optgrpSIRCountryPage2.Value = 1
Else
    Me.optgrpSIRCountryPage2.Value = 2
End If
    
End Sub

Between you and another expert (in another Access forum on the web) I was able to understand and piece together the right code. Thank you very much for helping me!

Al

 

[xicana]

You're welcome! I knew you were going to have to use a slightly different variation of the code i provided since you were using options.

Glad you were able to figure it out.

Sandy