Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Module Newb - Module won't work - Easy points

Posted on 2006-06-16
20
Medium Priority
?
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 7
  • 4
20 Comments
 
LVL 66

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 66

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
10 Questions to Ask when Buying Backup Software

Choosing the right backup solution for your organization can be a daunting task. To make the selection process easier, ask solution providers these 10 key questions.

 
LVL 66

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 66

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 66

Assisted Solution

by:Jim Horn
Jim Horn earned 240 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
 
LVL 58

Accepted Solution

by:
harfang earned 260 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 66

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 66

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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 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