A Couple of Questions, Could Be Tricky

[CodeCracker0291]

Ok I have a few questions about forms and what not. We are trying to record data from our customers..blah blah...Ok What we want is to keep records of the posititions our customers have held. Ex. If John Doe was a governer in 1987 and then in 1989 he changed to secretary or something. Well we want to be able to keep that on each person. Our form is set up for each member and we only need to keep info on them if they have been one of the officers, ect.

2nd Question:
I'm trying to get an if statement or a (like Excel) a VLookup...I want to say...if Membership Status is Active then in another field I want it to display $103.00. Ect.

3rd Question:
Again I need help. I want a box to appear if the Membership Status is "Deseased" then I want a box to appear that keeps the year they died in ("YearMemberDied")

Thanks So Much,
CodeCracker0291

 

[CodeCracker0291]

OK

Ok, I've gotten the field to show when I type deseased..but if the first record is deseased all the others show the date field...which they shouldn't because there not deseased. HELP!!

 

[Pat Hartman]

1. You need a separate table to store the position history.
tblPositionHistory:
CustomerID (primary key field 1, foreign key to customer table)
PositionStartDate (primary key field 2)
PositionTypeID (primary key field 3, foreign key to position type table)
PositionEndDate
etc.

tblPositionType
PositionTypeID (autonumber primary key)
PositionName (values will be president, secretary, etc.)

2. Use the MemberStatusCombo to obtain the fee. Use a query such as:
Select StatusID, StatusDescription, MemberFee From YourStatusTable;
Then in the AfterUpdate event of the combo, copy the fee value from the combo and place it in a bound field in the Dues Transactions table
Me.MemberFee = Me.MemberStatusCombo.Column(2)
The columns of a combo are a zero based array. So column(2) is actually the third column which in this case contains the fee amount.

3.Also in the AfterUpdate event of the MemberStatusCombo, unhide the deceased date field. This code also needs to go in the Form's current event so that when you scroll to existing records the field is unhidden.

Me.txtDeceasedDate.Visible = True

Be aware that if you are using a continuous form, you can't control the visiblilty of controls separately for each record.