Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 300
  • Last Modified:

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
0
DerekRoberts
Asked:
DerekRoberts
  • 8
  • 7
  • 4
2 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Call SLADays.SLAdays

Call SLADays (YouNeedADateValueHereForFromDate, YouNeedADateValueHereForToDate)

e.g.

Call SLADays (Me.MyFromDateTextbox, Me.MyToDateTextBox)

Call SLADays (Me.MyFromDateTextbox, Now())

Hope this helps.
-Jim
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
DerekRobertsAuthor Commented:
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?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
If result is the textbox where you want the value, then..

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

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

Thanks
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
DerekRobertsAuthor Commented:
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?
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Me.Result means you have a textbox (or some other control) named Result.  

If this is not the case, and you want the return value of SLADays to be in another control use Me.WhateverYouWantSLADaysValueShovedInHereName

If you want the return value of CalcSLA into a variable in your code, then use this...

Dim Return as Integer
Return = SLADays (Me.FromDt, Me.ToDt)
'Then do whatever you want with Return
0
 
DerekRobertsAuthor Commented:
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
0
 
harfangCommented:
Hello DerekRoberts

In addition to jimhorn's excellent tutorial on functions and how to use them, I suspect another potential problem.

> Call SLADays.SLAdays
> I get 'Argument not optional'
> If I remove the ".SLAdays" I get Expected Variable or procedure error

Your first syntax and the error message would be typical of a case where both the function (as posted in your question) and the *Module* itself are called SLADays.

For various reasons, module names are actually used in VB's "name space" and take precedence over function names. In other words, you would have to call your function like this:

    txtResult = SLADays.SLADays( datFrom, datTo )

This is considered bad practice. Instead, give a different name to your module, for example "basDateFunctions" or just "Module1" for now. You can then call your function directly.

(°v°)
0
 
DerekRobertsAuthor Commented:
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

0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>( 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
0
 
DerekRobertsAuthor Commented:
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.

0
 
harfangCommented:
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°)
0
 
DerekRobertsAuthor Commented:
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
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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
0
 
harfangCommented:
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°)
0
 
DerekRobertsAuthor Commented:
Ta Harfang and Ta Jim

Hope I've split the points OK

0
 
harfangCommented:
(^v^)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 8
  • 7
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now