Need VBA Code for Conditional Default Field Value

[dlhappy]

Hi:

I would greatly appreciate it if someone would please convert the following into VBA code for an Access 2007 field on a form. It is needed to create a conditional default value based on another field's category: (Note: TransType, MembershipYear and Dues are field names and are all on the form). Based on the TransType (which is really a category of membership) I want the Dues field to have the applicable default value automatically entered.


For MembershipYear > 2008
If TransType="Individual" or TransType="I" Dues=35.00
If TransType="Family" or TransType="F" Dues=50.00
If TransType="Founder" Dues=100.00
If TransType="Student" Dues=10.00
If TransType="Lifetime" Dues="" or is Null
If (TransType is Null or TransType="") and TotalPaid >0, Dues=35.00


Thanks for any help offered.

 

[Cronk]

Google ElseIf and you will see code. I'd suggest you put the code in the OnCurrent dvent of your form.

 

[dlhappy]

I decided to have the entire database affected, and thus did not use the MembershipYear > 2008.

I looked up Google, as suggested, and also tried to use syntax that I found in other VBA code in my database, that was written for me by others.

This is what I did:

Function Default_Dues_Function() As Currency

If TransType = "Individual" Or TransType = "I" Then Dues = 35
ElseIf TransType = "Family" Or TransType = "F" Then Dues = 50
ElseIf TransType = "Founder" Then Dues = 100
ElseIf TransType = "Student" Then Dues = 10
ElseIf TransType Is Null And TotalPaid > 0 Then Dues = 35
ElseIf TransType = "" And TotalPaid > 0 Then Dues = 35
End If
End Function

I don't know how to put OnCurrent event on my form, but if that means only for new postings, that will not accomplish filling in the default values for old records. Yesterday I added the dues field and also an AdditionalContribution field. The AdditionalContribution field is a derived field from a query. The query is just actually all of the fields from tblTrans. The transactions table.
Prior to yesterday, I only had one field for TotalPaid. So, now I want the code to put the default value, per membership category, and the Additional Contribution field will automatically do the math by showing the result of the difference between TotalPaid and the Dues.

Anyway, although I entered the above code for the form, and saved it, nothing happens, even if I close and reopen the database. Btw, the form in question automatically opens (is on the screen) when I open the database (that is what I want). Btw, I tried with and without the As Currency statement.

Thanks.

 

[dlhappy]

Problem Solved -- I did a little research and learned about using an update query. That solved the problem.