can't get module to work on a form

[blackbeltrrf]

I have a module in Access 2007 with the code:
Option Compare Database
Option Explicit
Function TitleValue()
If TitleValue = 1 Then
Auto_Title0.Caption = "All Open 'Critical' Priority Work Orders"
End If
If TitleValue = 2 Then
Auto_Title0.Caption = "All Open 'Normal' Priority Work Orders"
End If

End Function

This module will go on with many more "If" statements but condensed here to save space.

This code works fine if I put it in the "Form Load" event procedure but will not work if I create an "Event Procedure" to run the function, Runcode Titlevalue()

What I am trying to do:
I have a lot of Queries that filter a table by a variety of different parameters but I want the output to come back to 1 base form and simply change the title dynamically.

What I have done is in each of the different queries I have just put in an expression to assign each query a different "Titlevalue" number to key off of in my form.

Based on the "TitleValue" from each query I want to change the value of the forms AutoTitle0.

Any help would be greatly appreciated on this as to what I am doing wrong.

 

[boblarson]

1. You should not be "hard coding" these values into the code. You should use a table for this.

2. What do you mean if you "...create an Event Procedure to run the function? I'm not understanding what you mean or what you've tried.

3. You should not need multiple queries to filter the form by. You can set the filter easily enough and I show a prime example (this uses a report but it can be modified for a form) here:
http://downloads.btabdevelopment.com/Samples/reports/Sample-GenericReport.zip

 

[blackbeltrrf]

Thank you for the quick reply,

The form(s) are an output that different people see based on the choices they pick from switchboard buttons. Different people can only see and choose certain outputs from their switchboard. The switchboards are different for different people.
Based on who you are you can only choose certain things and thus see certain things as an output.
All these choices at the moment are handled by returning each different choice of information through many different forms.
My goal is to cut this down to one form with all fields included and hide the fields based on who you are. All that I have done with the exception of changing the title of the form dynamically based on the choice you made from the switchboard.

From your comments:
1. You should not be "hard coding" these values into the code. You should use a table for this.
Answer: My methodology madness is such that based on the button you chose on the switchboard assign a number to to that and put it as a value in that now single form. Use that new number and assign a form Title.
That I have done as well but wanted to get this to a working module that I can call on form load.

2. What do you mean if you "...create an Event Procedure to run the function? I'm not understanding what you mean or what you've tried.
Answer: This is my original dilema and base question. If I go to the form design properties and go to the "Form Load" line item and choose an "Event Procedure" and do the following:
a: Choose expession Builder and choose my Module "Titlevalue" = won't work.
b:Choose Macro and choose "Runcode" then my Module "Titlevalue" = won't work.
c. Choose code builder and enter my code (which is the same code in the function) as a "Private Sub" = Works fine.

I must not be calling the function correctly? (My question and dilema)

3. You should not need multiple queries to filter the form by. You can set the filter easily enough and I show a prime example (this uses a report but it can be modified for a form) here:
http://downloads.btabdevelopment.com...ericReport.zip
Answer: The user is not getting a choice on what they can filter. They are simply choosing from a switchboard button. That example is really great though!

The tangled web has even more variables and complexity not to get into here but that has all been overcome.

I guess in conclusion the question is with the code above what am I doing wrong for this not work on a form Load module function but works as a private sub?

Thanks in advance for the help.