Undefined function but no MISSING REFERENCES

[wrightyrx7]

Hi all,

Looked online and cannot find anything to correct this. The module is not named the same as the Function which seems to be the popular reason for this.

When the Database is opened by a new user or i send out an updated front-end to current users an error message appears saying: -

-Undefined function 'weekdays' in expression

This is referring to my function that calculates working days which i use in a couple of my queries. I found this function online and it seems to work but i cannot get rid of the error when opening. The code was obtained from the Microsoft website.



Hope someone can help.

Many thanks
Chris

ALSO posted HERE

Public Function Weekdays(ByRef startDate As Date, ByRef endDate As Date) As Integer
    ' Returns the number of weekdays in the period from startDate
    ' to endDate inclusive. Returns -1 if an error occurs.
    ' If your weekend days do not include Saturday and Sunday and
    ' do not total two per week in number, this function will
    ' require modification.
    On Error GoTo Weekdays_Error
    
    ' The number of weekend days per week.
    Const ncNumberOfWeekendDays As Integer = 2
    
    ' The number of days inclusive.
    Dim varDays As Variant
    
    ' The number of weekend days.
    Dim varWeekendDays As Variant
    
    ' Temporary storage for datetime.
    Dim dtmX As Date
    
    ' If the end date is earlier, swap the dates.
    If endDate < startDate Then
        dtmX = startDate
        startDate = endDate
        endDate = dtmX
    End If
    
    ' Calculate the number of days inclusive (+ 1 is to add back startDate).
    varDays = DateDiff(Interval:="d", _
        date1:=startDate, _
        date2:=endDate) + 1
    
    ' Calculate the number of weekend days.
    varWeekendDays = (DateDiff(Interval:="ww", _
        date1:=startDate, _
        date2:=endDate) _
        * ncNumberOfWeekendDays) _
        + IIf(DatePart(Interval:="w", _
        Date:=startDate) = vbSunday, 1, 0) _
        + IIf(DatePart(Interval:="w", _
        Date:=endDate) = vbSaturday, 1, 0)
    
    ' Calculate the number of weekdays.
    Weekdays = (varDays - varWeekendDays)
    
Weekdays_Exit:
    Exit Function
    
Weekdays_Error:
    Weekdays = -1
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
        vbCritical, "Weekdays"
    Resume Weekdays_Exit
End Function

 

[gemma-the-husky]

try compiling the whole dbs.

 

[wrightyrx7]

try compiling the whole dbs.

Hi Gemma,

Thanks for your reply. Do you mean split the database again into a front and back end? If so i just tried that and got the same error. If not then im sorry I dont know how to do it.

Chris

 

[wrightyrx7]

I think i have found the problem. The function runs in a query, however the query cannot run until it has data.

On the first occasion when someone opens the database it asks them to "enable content". Once this has been done the database works fine until i send out a new front end.

Is there a way to automatically enable the content? It is the data being pulled into the database via ODBC

 

[CJ_London]

gemma is talking about in the vba editor clicking debug>compile

Also recommend you put 'option explicit' below 'option compare database' at the top of each module (form/report modules as well as standard modules)

Can you clarify what you mean by

When the Database is opened by a new user or i send out an updated front-end to current users an error message appears saying: -

do you mean literally when the db is opened or when a query is run that uses the function?

Have you checked your references in VBA? perhaps one is missing? - this is the usual reason for the error - the same name reason being one of the reasons it is missing

Have you tried creating a .accde front end and distributing that instead?

Have you tried creating a new front end and copying all forms, reports, queries and modules across?

You've said your db is split - does every user have their own copy of the front end? if not, they should do.

Are they all running the same version of access - or are you developing in a later version?

 

[wrightyrx7]

gemma is talking about in the vba editor clicking debug>compile

Also recommend you put 'option explicit' below 'option compare database' at the top of each module (form/report modules as well as standard modules)

Can you clarify what you mean by

do you mean literally when the db is opened or when a query is run that uses the function?

Have you checked your references in VBA? perhaps one is missing? - this is the usual reason for the error - the same name reason being one of the reasons it is missing

Have you tried creating a .accde front end and distributing that instead?

Have you tried creating a new front end and copying all forms, reports, queries and modules across?

You've said your db is split - does every user have their own copy of the front end? if not, they should do.

Are they all running the same version of access - or are you developing in a later version?

Hi CJ_London,

I think I figured out the problem while you was typing this reply, apologies.

After enable content is click and the database the next time it is opened it is fine.

The function runs on a query that runs when the database is opened.

Basically my main form has a sub form with all our 'cases'. In the sub form is a field that calculates how many working days the case has been open using the function.

 

[CJ_London]

Is there a way to automatically enable the content?

Yes and No

Yes - If the user has the front end in an access trusted location then they don't need to enable. This is user device specific, you can't set a location in your copy and expect it to be there for a user copy

To create a trusted location go to file>options>trust centre>trust centre settings>trusted location>add new location and follow the prompts

You can do this in VBA but

No - the code to do this won't run until enabled by the user - however this will only effect new users and providing subsequent updates are placed in the same location, you do not need to update trusted locations again.

 

[CJ_London]

further info on trusted locations

1. you have the same for excel, but an excel trusted location will not trust an access file - and an access trusted location will not trust a excel file. The same probably goes for word and other office apps that use vba/macros.

2. Be aware IT can choose to disable the ability to create trusted locations and/or limit where those locations may be - which is a real pain, but worth checking out - follow the trusted location path, see what is already trusted and try to add a new one to test. Suspect it may be to do with mirroring desktop/mydocs.

3. a good location is c:\users\username\appdata\local\appfolder where username is the name of the user and appfolder is named as required for your application

 

[wrightyrx7]

Thank you CJ_London,

I have just checked an our IT department has disabled the Trusted Locations.

Its fine, hopefully there will not be to many updates needed to the front end so newer version will not need to be released. Because once they click enable content it doesnt prompt them again.

Thanks again for your help, I have learnt some new things.

Chris