Problem with Option Group

[CarolOB]

I have a form named Initiation which contains two Option Groups. One Option Group is named Member Type and has 4 option buttons: H/W Junior Single and Student and their option values are 1 2 3 and 4 respectively. The other Option Group is named TERM and contains 3 option buttons: oneyear threeyears sevenyears and their option values are 1 2 and 3 respectively.
I also have a table called InitFees that contains the Member Types (1 2 3 4) and Terms (1 2 3) along with payment amounts.
What I want to do is this: After I click on the option button for the correct term, I want to look up the payment amount in the InitFees table where the Member Type and Term are the same as the Member Type and Term displayed on the form.
I would greatly appreciate any assistance you can offer me.
Thanks in advance for your help
CarolOB

 

[starsky51]

Carol,
You could use a DLookup as the control for the text box that will hold the payment:
In the control source of the text box type something similar to this:
=DLookUp("[Payment]","[InitFees]","[Member Type]=" & fraMemberType.Value & " AND [Term]=" & fraTerms.Value)

You may have to change the names of the fields and option group names to suit.
DLookup takes 3 parameters: 1)The name of the field to look up. 2)The name of the table to look in. 3)The criteria (or where clause) for the lookup.

hope this helps,

starsky51

 

[CarolOB]

Hi Starsky51,
Thank you for your response to my question. I have tried what you suggested, but the result I get is #Error.
The following is the code I used as the control source for the text box where I want to display the appropriate first payment:

=DLookUp("[FirstPymnt]","[InitFees]","[MemberType]=" & [TypeNumber].[Value] & " AND [Term]=" & [Term].[Value])

Any ideas what I might be doing wrong?

Thanks, CarolOB

 

[CarolOB]

I finally got it to work! I had to change my code to the following:

=DLookUp("[FirstPymnt]","[InitFees]","[MemberType]=" & [TypeNumber].[Value] & " And [TERM]=" & [TERM].[Value] & "&quot

Thanks for your help. CarolOB