Can't get module code to work with a form

[ke4peo]

So, I did some searching and couldn't find anything specific to what I'm trying to do so I figured I'd just ask.


I'm using the Office 365 version of Access. Trying to create a database to track my call statistics (current and historical). I've got the form laid out, and the code working in VBA to pull the stats based on overall or a specific client (selected by a drop down). However, I'd really like to streamline the code on the main form using modules, but I can't seem to get that working.


Right now, all the code (see snippet example below) is stored in the main form in an extremely long Select Case statement. It makes scrolling through to tweak something arduous. So, I created a module called BellevueStats and moved the code snipped below into the module. Then in the Case statement, I simply called the BellevueStats module. That resuled in a compile error: invalid outside procedure. For the life of me, I cannot figure out how to fix it. The code works fine when it's all in the VBA of the main form, but the moment I move a client out to a module, I get the invalid outside procedure error.

    'Sets the variables and their definitions
    currtotalcalls = DCount("EntryType", "StoredData", "Date BETWEEN #" & Me.tb_startdate & "# AND #" & Me.tb_enddate & "# AND SchoolCode='Bel' AND EntryType='Call'")
    prevtotalcalls = DCount("EntryType", "StoredData", "Date BETWEEN #" & Me.tb_prevstartdate & "# AND #" & Me.tb_prevenddate & "# AND SchoolCode='Bel' AND EntryType='Call'")
    diffcalls = currtotalcalls - prevtotalcalls
    mtdtotalcalls = DCount("EntryType", "StoredData", "Date BETWEEN #" & Me.tb_mtdstart & "# AND #" & Me.tb_mtdend & "# AND SchoolCode='Bel' AND EntryType='Call'")


    'Sets the value of the text boxes to the result of the associated variables
    Me.tb_totalcalls.Value = currtotalcalls
    Me.tb_prevtotalcalls.Value = prevtotalcalls
    Me.tb_difftotalcalls.Value = diffcalls
    Me.tb_mtdtotalcalls.Value = mtdtotalcalls

I'd really love to be able to put the individual schools/clients code into their own modules if that is possible. Thanks in advance for any advice!

[Welshgasman]

Me refers to the actual object, so no use whatsoever when that code is in a module.

You need to pass in whatever parameters you want to use or use TempVars.

However if you are just using this in one form, waste of time. You can still create a sub, but place it in the form.
You also have the issue that you want to return 4 values.

[Welshgasman]

Me refers to the actual object, so no use whatsoever when that code is in a module.

You need to pass in whatever parameters you want to use or use TempVars.

However if you are just using this in one form, waste of time. You can still create a sub, but place it in the form.
You also have the issue that you want to return 4 values.

However

I'd really love to be able to put the individual schools/clients code into their own modules if that is possible

leads me to think your DB is not normalised.

OR you pass in Schoolcode as a parameter.

[ke4peo]

Ok, that makes sense. When I'm putting the code into a module and calling the module it's thinking the 'Me.' is referring to the module. Think I may just have to leave it in the form for now. Thanks!

[Welshgasman]

I believe you can just have a sub in your form and call that, but also parameterise the school values.
Only worth it if, you are using it from more that one place TBH.

[Micron]

If the Select Case code is clunky, how is moving it to a different place going to make it any better? Maybe that portion could be improved instead.

[ke4peo]

That was kinda what I was looking to do with modules. The way it's set up right now is:

Select Case Me.drpdwn.clientlist
Case 1
<every variable declaration and code for that specific client which right now is about 3 full scrolls on the mouse wheel>

That repeats for all 7 clients/schools. Which leads to a massively large scroll in the form's VBA window. What I was trying to do was move each client/school code to a module and then just call the module. Something like this:

Select Case Me.drpdwn.clientlist
Case 1
Call BellevueStats

That way the Select statement would be immensely shorter, and if I needed to adjust the code for a client I could just open their module and make the changes rather than having to scroll through a massive amount of pages in the form's VBA to find them.

[Welshgasman]

Unless the code is radically different for each school, I would rethink this.

[Micron]

If I understand then all you need is the combo after update event and pass a value from the combo to a sub or function in the other module. You can pass as much or as little as you want. Perhaps just client value but could be anything else on the form, associated data that you have hard coded somewhere in the Select Case block, form name (to handle the Me problem) - anything. Then the module code takes the parameters and does something with it. What is not clear at this point is if your standard module has only one sub/function because whatever you do to one client is exactly the same for all, or if the variations are minor. In that case, the procedure could just handle the variations. Otherwise you might want several procedures in the one module or even several modules. If you want to copy your db, compact/repair it, zip it and post it we can take a look and offer suggestions.

[June7]

Are you saving these calcs to table as opposed to calculating when needed? All statistics are historical and calculations can be accomplished for any specified period. Are calculations different for each school?

Minght find MS Call Tracker template of interest https://support.microsoft.com/en-us/...8-1afcfc91a9cb