Using Public Functions 1

[sspayne]

Hi all,

I can only apologise for posting what is a really basic question, but they've just sent me on an 'Intro to Access' course and I am now expected to solve all their problems.

Here I go: how do I actually make use of a function? I have copied a Public Function taken from a FAQ into a Module. The Module is called 'WorkingDays2'. I want to view the results of the function but don't know how. I created a form based on the table containing the fields the function should use. I then added a text box and pasted the function name into the Expression Builder within the text box's Control Source. Was this right? When I view the form I get a #Name? error in the new text box.

Again, sorry for bothering you all with this. I did try a search but no joy. I will crawl under a rock when this is fixed. Any help appreciated.

 

[KenReay]

Hi

The Function Should be in a module

Its definition should read:

Public Function MyFunction() as String
...etc

ok name of function and return type may differ in your context

DO NOT name your function (say) WorkingDays2 and name your module with the same name

You can then use your function in code anywhere in that Mdb file, just as if it was a built in function so

If WorkingDays2() = 5 Then
..whatever

or you can use it in a query so

WDays:=WorkingDays2([StartDate],[EndDate])

Does that help?



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sspayne]

Thanks Ken, that has certainly progressed it a bit. As you suggest, I have built the function into a query. Next problem! It flags up an error within the function at the line Dim rst As DAO.Recordset (user-defined type not defined). I think I have read elsewhere that this may be because the function was written for an earlier version of Access (I have 2000). Do you have any further advice for that one?

Again, many thanks.

 

[KenReay]

Hi

You are exactly right

In Access97 and earlier DAO was the default object library for data access (D)ata (A)ccess (O)bjcts if you are interested

From Access2000 on the default library for data access is ADO

So if you want to use DAO in Access2000 and later you must add reference to the DAO library, to do this, open any code module in design view and choose tools\references, look down the list of references for a one called DAO 3.5 (version may vary a little eg 3.6 or whatever, choose the latest one) and selct it by putting a tick in the box

It is also helpful to ALWAYS qualify DAO object names with the DAO. prefix eg Dim Db As DAO.Database, Dim Rs as DAO.Recordset, as you haev done, that way you cannot confuse access with object type, since some types are duplicated in the two libraries (notably Recordset)



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[sspayne]

Again, thanks for the quick response Ken. This is incredibly frustrating - I know now what I need to do, but the References option under Tools is greyed out so I can't make the necessary changes. I expect that will be to do with the installation?

I suspect I'm on my own with this one now! Thanks for your help to get me to this stage.

 

[sspayne]

Ignore that last one!! No longer greyed out. It works, and I'm a happy chap. Thanks again for your help and enjoy the purple star.

SS Payne