Link to home
Start Free TrialLog in
Avatar of DerekRoberts
DerekRobertsFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Module Newb - Module won't work - Easy points

Hi all

Been using Access for a while now but never had Modules explained to me. Now I'm trying to use them and just getting thougroughly confused. I have searched this forum for solutions but it hasn't helped (so far)...I think I'm over complicating things

Basically I have a module ('borrowed' from this forum)
======
Function SLADays(FromDt As Date, ToDt As Date) As Integer
    ' counts the number of business days between two dates (holidays are pulled from tblHolidays).
     
    Dim CheckDate As Date
    SLADays = 0
    CheckDate = FromDt
    Do Until CheckDate > ToDt
        If WeekDay(CheckDate) <> 1 And WeekDay(CheckDate) <> 7 And _
        IsNull(DLookup("Date", "tblHoliday", "[Date]=#" & CheckDate & "#")) Then
        SLADays = SLADays + 1
        End If
    CheckDate = CheckDate + 1
    Loop
    SLADays = IIf(SLADays > 0, SLADays - 1, SLADays)

Me.Result = SLADays
End Function
=========

I want to use this in various forms (and possibly later to amend tables)

Firstly I'm looking to get this to work from a button click and just display the result into the 'Result'box on the focus form....essentially so I can have a play and see how it all works.

Behind the button On_Click I ve put

Call SLADays.SLAdays

I get 'Argument not optional'

If I remove the ".SLAdays" I get Expected Variable or procedure error

Can someone point out where I'm going wrong please?

Also, as far as I understand, I should be able to call this Function from any form as long as there are the FromDt,ToDt and Result fields. Is that correct?

Thanks in advance
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

>Call SLADays.SLAdays

Call SLADays (YouNeedADateValueHereForFromDate, YouNeedADateValueHereForToDate)

e.g.

Call SLADays (Me.MyFromDateTextbox, Me.MyToDateTextBox)

Call SLADays (Me.MyFromDateTextbox, Now())

Hope this helps.
-Jim
And actually, since this is a function, you probably want to do something with the return value...

Me.SomeTextBox = SLADays (Me.MyFromDateTextbox, Me.MyToDateTextBox)

Using the 'Call' function treats it as a Sub and not a Function, which means there is no return value, or if there is it is ignored.
Avatar of DerekRoberts

ASKER

Blimey Jim

that was quick!!!!

I think you should change yer handle to Slick-Jim or summat

:o)

So if Ive got this right

My button Click should be

Me.result = SLADays (Me.FromDt, Me.ToDt)

If Result = ret val, Fromdt = 1st date and todt = 2nd date.

Is that correct?
If result is the textbox where you want the value, then..

Private Sub YourButtonName_Click()
Me.result = SLADays (Me.FromDt, Me.ToDt)
End Sub
Initially I need the ret val to be displayed in Result in the focus form

Tried the above, still getting "Expected variable.." error

Thanks
First, in the very top of your code module, make sure this is there :

Option Explicit

Then in code, go to Debug: Compile, and see if anything displays.
It did say Option Compare Db

But changed it and its still the same. As soon as I click Debug it goes into one!

I keep getting the "Expected Variable.." error with the SLADays bit highlighted in the 'Me.result = SLADays (Me.FromDt, Me.ToDt)' line under button click

Would this suggest there is something wrong with the Module or the button click code?

It's not something silly like I have to 'switch on' a reference is it?
SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Yes I have a textbox called Result.

Just tried your suggestion and it still hangs on the inital running/calling of the module as previous reported.

Thanks for your help. Iwill have another play after the weekend. If you do come up with any further suggestions they'd be greatly appreciated.

Thanks in advance
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Many thanks to JimHorn for initially helping out. Points went to Harfang as it solved my problem ( I would have split them if it allowed).

I did realise that naming a function and a module wasn't the best idea, but I thought with the naming conventions it would be able to tell which was refering to which bit....obviously not!!!!

I appreciate the help but could either of you reccommend a resource to help me through the basics and more on-line......it would save me hastling this forum please.........or at least with more technical questions as opposed to the 'Haven't got a clue,don't understand' type questions.......otherwise known as 'Monday morning' questions.

By Jove, it seems to be Monday morning now............

:o)

Thanks again

>( I would have split them if it allowed).

You may want to eyeball the 'Split Points' link between the textarea and the last comment made on open questions, which allows you to split points for a question among multiple experts.

Receiving multiple helpful comments from experts, without splitting points, will not motivate those experts to answer your questions in the future.

-Jim
Hi Jim

I suspect its too late now but I have requested a points split, but I have taken note for future reference.

Genuine mistake....from which I've learnt. Always the best mistakes to make I feel.

Many thanks again for your efforts.

DerekRoberts

To finalize this learning experience (^v^), you can try this: post a brief note to http:/Community_Support/ with something like:

    Please reopen my question

    Please reopen my question {http:/Q_21889085.html} to let me split points.
    Thank you

You will then be able to test the "split points" link right here.

When I was teaching Access, I often had questions about a good reference book. My answer was this: Go to "<insert the best software and IT bookstore in town>" and take to time to read a dozen pages at random and the table of contents of all books on Access you will find. Normally, one should stand out.

In this instance, it's very difficult to make up your mind on-line, a real bookstore is best.

Cheers!
(°v°)
Okey doke Harfang

Have already mailed Com Sup to request a split.

As for the bookshop.....can you give me directions.......heh heh heh

Only joking

:o)

Have got a couple of books...one professes to be a 'bible' although they both cover the main program and not a great deal about VBA.

Will go geeking at the weekend

Cheers
I started on the 'Access 97 Developers Handbook' by Litwin/Getz, and at the time this was the best book out there.  There's now a 2002 version which is two books, but the price is somewhere around $120.   Most developers I know of use this manual.

I also recommended this book when I taught Access, as the training manuals I had to use were only geared to what was being trained on.

I've also heard the MS Press book is very good.

Also, don't buy any book that does not have a CD, or a web presence, with all the code that's in the manual.

Thanks for rectifying the split.
-Jim
These are good recommendations, Jim. In addition, I would point to O'REILLY (www.oreilly.com). I have used their books extensively (I have a dozen on my bookshelf), and some of their CDs are available on-line even if you don't buy the book! (http://examples.oreilly.com/accesscook/CDROM/).

As for my personal experience, I started with Access 2.0, which came with a printed user manual. Reading that and trying out each and every example is what got me started... However, I already had a solid theoretical knowledge of SQL and AI engines (pen-and-paper excercises mostly) and had been programming in various BASIC dialects for many years.

Cheers!
(°v°)
Ta Harfang and Ta Jim

Hope I've split the points OK

(^v^)