Solved

Module Newb - Module won't work - Easy points

Posted on 2006-06-16
20
287 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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

896 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

18 Experts available now in Live!

Get 1:1 Help Now