Solved

Module Newb - Module won't work - Easy points

Posted on 2006-06-16
20
286 Views
Last Modified: 2008-02-01
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
Comment
Question by:DerekRoberts
  • 8
  • 7
  • 4
20 Comments
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16921154
>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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16921165
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
 

Author Comment

by:DerekRoberts
ID: 16921211
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16921283
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
 

Author Comment

by:DerekRoberts
ID: 16921300
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16921329
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
 

Author Comment

by:DerekRoberts
ID: 16921393
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
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 60 total points
ID: 16921452
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
 

Author Comment

by:DerekRoberts
ID: 16921513
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 58

Accepted Solution

by:
harfang earned 65 total points
ID: 16924648
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
 

Author Comment

by:DerekRoberts
ID: 16932617
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16934277
>( 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
 

Author Comment

by:DerekRoberts
ID: 16934391
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
 
LVL 58

Expert Comment

by:harfang
ID: 16934595
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
 

Author Comment

by:DerekRoberts
ID: 16934662
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
 
LVL 65

Expert Comment

by:Jim Horn
ID: 16934729
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
 
LVL 58

Expert Comment

by:harfang
ID: 16934861
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
 

Author Comment

by:DerekRoberts
ID: 16940295
Ta Harfang and Ta Jim

Hope I've split the points OK

0
 
LVL 58

Expert Comment

by:harfang
ID: 16940350
(^v^)
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now