One field determining the value of another field (1 Viewer)

[DocIke]

Hi,
I believe this to be an exceptionally simple problem. So simple that I can’t figure it out. This is the situation:

Table 1: has 3 fields: id(auto), dues, and memtype
Form: Contains fields for “dues” and memtype
Memtype: can be either “A” or “F”
Dues: can be either 15 or 25
Option Group: a two button option group (“Full”(default) and “Associate”) is on the form. The “Full” option has a value of 25 and “Associate” value equals 15. The values of 15 or 25 are stored in “dues”.

What I want to do: When I make an option group selection of “Full” then I want 25 in the “dues” and “F” in memtype. In similar fashion when I select “Associate” then I want 15 to enter the “dues” field and “A” in the memtype field.
The Problem: As of now when I select “Full” 25 appears in “dues”; when I select “Associate” 15 appears in “dues”. How do I get “F” or “A” to appear in the memtype field?

 

[sneuberg]

You could assign these values in the afterupdate event of the option group. Just test the value of the Dues and set the Memtype accordingly.

 

[static]

Data belongs in tables. F/Full/25 and A/Associate/15 is data.
By hard coding the values into the form you'd need to edit the form if the data ever needs to be changed.
Memtype and Dues should be fields in a separate table that links to table1 and the options displayed in a list on the form.

 

[plog]

Can you ever have Memtype=A and Dues=25? Or Memtype=F and Dues=15?

 

[DocIke]

No, can never have Memtype=A and Dues=25 or Memtype=F Dues=15

 

[plog]

Then you don't need both fields in your table, those are redundant fields.

You should change your table to eliminate one of those fields and then fix your form to just allow whichever input you choose.

 

[DocIke]

The following was placed in BeforeUpdate. SelectFA is the name of the option group.

Private Sub SelectFA_BeforeUpdate(Cancel As Integer)
Select Case Me.SelectFA
Case 1
Me.CurrDues = 25
Me.MemType = "F"
Case Else
Me.CurrDues = 15
Me.MemType = "A"

End Select
End Sub

The above behaves as follows: When I open the form, the Memtype and Dues text boxes are blank.
If I press the “Full” (top) button nothing happens, however if I then press the “Associate” (bottom) button the Memtype box correctly shows “A” and the Dues text box shows 15. However, if I then press the “Full” button the Memtype box still shows “A” but the Dues box now shows 25. Have any further thoughts. I should note that I’m using Access 2010.

Thanks

 

[DocIke]

Hi,
In desperation to try anything I changed your code to:
Private Sub SelectFA_BeforeUpdate(Cancel As Integer)
Select Case Me.SelectFA
Case 25
Me.MemType = "F"
Case Else
Me.MemType = "A"
End Select
End Sub

I should have said that when I set up the option group I gave “Full” the value of 25 and “Associate” the value of 15. Note the change in Case from 1 to 25. Only God, the Shadow, and you probably know why IT WORKS. I’m particularly interested why changing Case from 1 to 25 made the difference. I’d appreciate your thoughts. Just to note, the above is for data entry into a table containing a member’s Id, membership status, and dues. Also, given your experience is there any place, book, etc. that does a good job with Access code syntax?

 

[DocIke]

Hi,
I tried what you suggested and it worked with the addition of a query. However, I just had to try to figure out how to make my approach work. I finally got it to work.

Thanks for your help

 

[DocIke]

Hi,

Thank you for your help. Could you recommend any resource that dedicated to Access command syntax.

 

[sneuberg]

Hi,

Thank you for your help. Could you recommend any resource that dedicated to Access command syntax.

I suggest downloading the Microsoft VBA Language Specification which you can get here