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

DocIke

New member
Local time
Today, 15:05
Joined
Nov 28, 2017
Messages
8
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

AWF VIP
Local time
Today, 15:05
Joined
Oct 17, 2014
Messages
3,506
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:05
Joined
Feb 19, 2002
Messages
42,976
Option Groups provide numeric values. I'm assuming that "A" is the first option. If it isn't, then change "Case 1" to "Case 2" and that will work.
Code:
Select Case Me.NameOfOptionGroupControl
        Case 1
            Me.MemType = "A"
            Me.Dues = 15
        Case Else
            Me.MemType = "F"
            Me.Dues = 25
    End Select
 

static

Registered User.
Local time
Today, 22:05
Joined
Nov 2, 2015
Messages
823
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

Banishment Pending
Local time
Today, 17:05
Joined
May 11, 2011
Messages
11,612
Can you ever have Memtype=A and Dues=25? Or Memtype=F and Dues=15?
 

DocIke

New member
Local time
Today, 15:05
Joined
Nov 28, 2017
Messages
8
No, can never have Memtype=A and Dues=25 or Memtype=F Dues=15
 

plog

Banishment Pending
Local time
Today, 17:05
Joined
May 11, 2011
Messages
11,612
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

New member
Local time
Today, 15:05
Joined
Nov 28, 2017
Messages
8
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:05
Joined
Feb 19, 2002
Messages
42,976
Look at the option group in design view. Check that the Option Value for each option matches the assumption I made when I wrote the code. I assumed that the F option had an Option value of 1 and the A option had the option value of 2. As I said in my post - you may need to adjust "Case 1" and make it "Case 2" if the options were not defined as I anticipated.

I happen to agree with the others regarding putting these values in tables. But since there were only two and it seemed likely that the number wouldn't increase for a while at least, to just do this as simply as possible. If you were to tablize the values, you would only store the ID of the appropriate entry. If you don't tablize them, then you need both fields in the table as you currently have it.
 

DocIke

New member
Local time
Today, 15:05
Joined
Nov 28, 2017
Messages
8
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

New member
Local time
Today, 15:05
Joined
Nov 28, 2017
Messages
8
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
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:05
Joined
Feb 19, 2002
Messages
42,976
why changing Case from 1 to 25 made the difference
Because rather than using the default values of 1 and 2 which the option wizard would have assigned, you manually changed the values to 25 and 15. I was trying to get you to look at the correct property on the option group because I thought you might have defined the two items in the opposite order from how you presented them in the question. So I thought we would be swapping 1 and 2. Little did I know that 1 and 2 were actually 25 and 15.

I attached two pictures
 

Attachments

  • OptionGroup.JPG
    OptionGroup.JPG
    71.3 KB · Views: 72
  • OptionGroup2.JPG
    OptionGroup2.JPG
    65.7 KB · Views: 69

DocIke

New member
Local time
Today, 15:05
Joined
Nov 28, 2017
Messages
8
Hi,

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

Pat Hartman

Super Moderator
Staff member
Local time
Today, 18:05
Joined
Feb 19, 2002
Messages
42,976
I'm not sure I've ever seen anything that explains controls property by property so some things you simply discover.

I can recommend three VBA books.
1. Access Cookbook by Ken Getz, Paul Litwin & Andy Baron, O'Reilly - this is an oldie but goodie. It was last updated for A2003 but VBA really hasn't changed much. Some hints are out dated because Access no longer supports the feature such as replication and in other cases, Access solved the problem the code was intended to circumvent.
2. Access Solutions Tips, Tricks, and Secrets from MS Access VBps - Arvin Meyer and Douglas Steele, Wiley
3. Fixing Access Annoyances Phil Mitchell & Evan Callahan, O'Reilly. Also an oldie but a goodie

As far as other books go, it's really too bad that Borders is gone. That used to be the best bookstore to find technical books. My technique would be to go to the store and take a couple of likely candidates to a comfey chair and read the table of contents and scan some of the entries. If the book solved a problem I had or gave me at least two interesting ideas, I spent the $40-$50 for the book. Now, you have to do this on line and it is much less satisfying but if the book turns out to be a disappointment, just send it back.
 

Users who are viewing this thread

Top Bottom