run module problem

[Guest]

I am not a programmer but have written code to calculate registration fee
based upon several variables. I need the code to apply to all queries and
resulting forms and reports so created a "findregfee" function in a module.
Now I can't figure out how to make it run when the data is entered that
should generate the fee. Help would be greatly appreciated.

 

[Guest]

Nancy,

It is hard to answer the question as there is not enough information provided:
how does the data get "entered" - via forms, code or directly into the tables?
do you need to store the "fee" in the tables, or display it in reports?

In Access you can refer to global functions from within code in forms &
reports, and you can call them from queries as well.

will be happy to help.

 

[Guest]

Thanks. I appreciate your help and was afraid I had not given enough info.
First -- data gets entered in a form. The fee generated by entries into the
form should be stored in the table and thus be available for display in the
form and in other queries and reports.

Second -- in an earlier effort, I had put the code in a private function
called by an afterupdate event on the form. It worked. Problem was that the
data was not stored in the table and was then not available to other queries.
I thought the problem was that the code was private to the form and needed
to be public. That's why I put it into a module, but then I had no idea how
to cause it to run.

Third -- see the code below:
Function FindRegFee(varRegStat As String, varRegDeadline As String, _
varRegSched As String) As Integer

If varRegStat = "Complimentary" Then
RegFee = 0

ElseIf varRegStat = "Member" _
And varRegDeadline = "EB" _
And varRegSched = "Both Days" Then
RegFee = 75
ElseIf varRegStat = "Student" _
And varRegDeadline = "EB" _
And varRegSched = "Both Days" Then
RegFee = 50
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "EB" _
And varRegSched = "Both Days" Then
RegFee = 105
ElseIf varRegStat = "Member" _
And varRegDeadline = "PR" _
And varRegSched = "Both Days" Then
RegFee = 100
ElseIf varRegStat = "Student" _
And varRegDeadline = "PR" _
And varRegSched = "Both Days" Then
RegFee = 75
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "PR" _
And varRegSched = "Both Days" Then
RegFee = 130
ElseIf varRegStat = "Member" _
And varRegDeadline = "OS" _
And varRegSched = "Both Days" Then
RegFee = 120
ElseIf varRegStat = "Student" _
And varRegDeadline = "OS" _
And varRegSched = "Both Days" Then
RegFee = 100
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "OS" _
And varRegSched = "Both Days" Then
RegFee = 150
ElseIf varRegStat = "Member" _
And varRegDeadline = "EB" _
And varRegSched = "Friday Only" Then
RegFee = 50
ElseIf varRegStat = "Student" _
And varRegDeadline = "EB" _
And varRegSched = "Friday Only" Then
RegFee = 40
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "EB" _
And varRegSched = "Friday Only" Then
RegFee = 75
ElseIf varRegStat = "Member" _
And varRegDeadline = "PR" _
And varRegSched = "Friday Only" Then
RegFee = 65
ElseIf varRegStat = "Student" _
And varRegDeadline = "PR" _
And varRegSched = "Friday Only" Then
RegFee = 55
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "PR" _
And varRegSched = "Friday Only" Then
RegFee = 100
ElseIf varRegStat = "Member" _
And varRegDeadline = "OS" _
And varRegSched = "Friday Only" Then
RegFee = 80
ElseIf varRegStat = "Student" _
And varRegDeadline = "OS" _
And varRegSched = "Friday Only" Then
RegFee = 75
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "OS" _
And varRegSched = "Friday Only" Then
RegFee = 120
ElseIf varRegStat = "Member" _
And varRegDeadline = "EB" _
And varRegSched = "Saturday Only" Then
RegFee = 50
ElseIf varRegStat = "Student" _
And varRegDeadline = "EB" _
And varRegSched = "Saturday Only" Then
RegFee = 40
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "EB" _
And varRegSched = "Saturday Only" Then
RegFee = 75
ElseIf varRegStat = "Member" _
And varRegDeadline = "PR" _
And varRegSched = "Saturday Only" Then
RegFee = 65
ElseIf varRegStat = "Student" _
And varRegDeadline = "PR" _
And varRegSched = "Saturday Only" Then
RegFee = 55
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "PR" _
And varRegSched = "Saturday Only" Then
RegFee = 100
ElseIf varRegStat = "Member" _
And varRegDeadline = "OS" _
And varRegSched = "Saturday Only" Then
RegFee = 80
ElseIf varRegStat = "Student" _
And varRegDeadline = "OS" _
And varRegSched = "Saturday Only" Then
RegFee = 75
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "OS" _
And varRegSched = "Saturday Only" Then
RegFee = 120
ElseIf varRegStat = "Member" _
And varRegDeadline = "EB" _
And varRegSched = "PreConf only" Then
RegFee = 0
ElseIf varRegStat = "Student" _
And varRegDeadline = "EB" _
And varRegSched = " PreConf only " Then
RegFee = 0
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "EB" _
And varRegSched = " PreConf only " Then
RegFee = 0
ElseIf varRegStat = "Member" _
And varRegDeadline = "PR" _
And varRegSched = "PreConf only" Then
RegFee = 0
ElseIf varRegStat = "Student" _
And varRegDeadline = "PR" _
And varRegSched = " PreConf only " Then
RegFee = 0
ElseIf varRegStat = "Non Member" _
And varRegDeadline = "PR" _
And varRegSched = " PreConf only " Then
RegFee = 0
End If


End Function

I am very grateful for your assistance.

 

[Guest]

Nancy,

If you input your data via forms - it makes the task much easier, as you
have full control of what is happening. I presume that your form is bound
(correct me if I am wrong). You would need to bind the fee field to one of
the textboxes on the form, say txtFee. Then ensure that you set Locked
property on the textbox to true - thus preventing users from directly
modifying the field.

Then in the AfterUpdate event for the controls on the form that define your
fee (say, regStat, RegDeadLine, RegSched) you write the code to update the
fee field:
txtFee = FindRegFee(...)

That is it.

Let me know if you have any problems.

 

[Guest]

Thanks. I am making progress -- had it working and now it's not. The fee
field is "regfee" and its field type is currency. Your suggestion for a text
field was not saying it had to be text was it?

 

[Guest]

Nancy,

In your table make sure that the field is of Currency type, then in the Form
design view select View-> Field List menu and drag the field from the list
onto your form. Access will automatically create a bound textbox for you (and
it should take care of formatting as well)

HTH

 

[Guest]

Thanks. Yes -- I already knew that and have done that. I appreciate your
assistance. At the moment I am diverted to another project but will get back
to this problem tomorrow. Can I email you directly or should I continue to
use the discussion group?

 

[Guest]

Nancy,

I do not mind to give my direct address - (e-mail address removed).

But if you believe that your question is relatively generic and may benefit
others - you should post it via the groups.

Sergey