Difficulty Getting Module To Work

[jamiesuperman420]

Hello!! I have a database for a property maintenance company... There's a combo box called "Freq" (for Frequencies - of which there are 13 different ones), a field called "Total Fee" (how much we're billing the client) and another called "Monthly Total Avg" (it's a hidden field, important for other pages in the database).

This is displayed on a datasheet.

For the Monthly Total Avg, if Freq is 1X/YR then I need Total Fee to be divided by 12 to get the monthly average.

I tried doing this in a module, but with limited experience I couldn't get it working properly. I can't do it with an iif statement because it's too long (there are 1X/YR, 6X/YR, 1X/MO, etc, etc)...

Also, I need the data for some queries (not sure if that makes a difference or not - thankfully I know how to make queries). I tried using an expression to give [Monthly Total Avg] a value after Frequency is updated, but it updated ALL of the records on the sheet, not just the one I changed the frequency for.

Any help would be greatly appreciated!! Thank you!!

 

[hellind]

Again some code snippets, or the whole mdb uploaded would be great.

 

[WayneRyan]

jamie,

Not much info to go on here.

Assume that you have a table for the frequencies:

tblFrequencies
==============
FreqID - AutoNumber
FreqType - Text (Annual, Qtrly, Monthly ...)
FreqDivisor - Number (12, 4, 1)

Select A.CompanyName As CompanyName,
       A.TotalFee As TotalFee,
       A.TotalFee / B.FreqDivisor As MonthlyAvg
From   YourTable As A Inner Join tblFrequencies As B On
          A.FreqType = B.FreqType
Order By A.CompanyName

Then you alleviate the requirement for code in the first place.

If you want to get your code working:

1) Make sure the code is in your modules collection on the database Tab
2) Make sure that is a Public Function
3) Make sure that the function and the module don't have the SAME name
4) In your query add a new column --> NewClm: YourFunction([TotalFee], [FreqType])

You need to add the field names in the call so that it will fire for EVERY row.

hth,
Wayne

 

[jamiesuperman420]

Hey!! Thank you very much for taking the time to respond. To be honest with you, I didn't exactly understand your code (haha - not so smart), HOWEVER, in the bottom section, you said to ensure the function and module have different names... I switched the function name and my code started working!!

Thank you so much!!

 

[WayneRyan]

Jamie,

The first part was just to show you how to do it without code.

With code there are a few common problems ... glad that yours was one of them.

Glad you have it working.

Wayne