Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 03-24-2004, 04:42 PM   #1
o1110010
Registered User
 
o1110010's Avatar
 
Join Date: Mar 2004
Location: USA
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
o1110010 is on a distinguished road
Send a message via AIM to o1110010
Arrow Passing an optional value

*Note: I will be omitting the body of the code since it works as directed. Just need help with the idea/change.

Currently, a button on a form runs a specific procedure. I want to have the option of passing a value to the procedure (something I have not done in VBA yet). Notice I said "option". I do not want to pass a value to the procedure all the time. The optional passed value will be an integer. If a value is passed, it will determine how many times a loop is executed within the procedure. If no value is passed (NULL), the procedure will initialize it to a default value.

And since we're speaking of this procedure, how do you change a Private Sub procedure to be accessed to all forms? I am guessing there is more work than just changing Private to Public, right? (So far all procedure's I've used have been Private.. )

The procedure right now triggered from frmAddDate with...

Private Sub cmdAddDate_Click()
'blah blah excluding because it works and should be more of a nuisance than helpful to you
'blah blah blah
End Sub

Any help would be cool. Thank you.


I've searched the forum: Saw alot of posts on public variables, a thread passing a value from parent form to child form, and a thread passing parameters with command buttons (but the latter had no replies).

__________________
If an expected response takes a few days longer than expected, send a private message to ensure they didn't miss the post.

Last edited by o1110010; 03-24-2004 at 10:06 PM.
o1110010 is offline   Reply With Quote
Old 03-24-2004, 05:11 PM   #2
billyr
Registered User
 
Join Date: May 2003
Location: Auburn, Indiana
Posts: 123
Thanks: 0
Thanked 0 Times in 0 Posts
billyr
Look in Help

From Access VB Help.

Sub OptionalArgs(strState As String, Optional strCountry As String)
__________________
Senior moments are a gift to all who live long enough to have them!
billyr is offline   Reply With Quote
Old 03-24-2004, 05:26 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,804
Thanks: 13
Thanked 1,509 Times in 1,435 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
If you want the procedure to be accessible from all forms, you'll need to change it to Public and move it to a standard module. This assumes that the procedure is not using any form objects. If it is, you'll have a lot more changes to make.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 03-24-2004, 05:27 PM   #4
o1110010
Registered User
 
o1110010's Avatar
 
Join Date: Mar 2004
Location: USA
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
o1110010 is on a distinguished road
Send a message via AIM to o1110010
So I just put the word "Optional" in front of the arguement? (Wish VBA was more "technical" sounding.) so..

Sub cmdAddDate_Click(Optional countLoop As Integer)
If IsNull(countLoop)
countLoop = 7
End If
'Blah blah
'blah blah
End Sub

That's all the change to do to the procedure?
__________________
If an expected response takes a few days longer than expected, send a private message to ensure they didn't miss the post.

Last edited by o1110010; 03-24-2004 at 05:38 PM.
o1110010 is offline   Reply With Quote
Old 03-24-2004, 05:29 PM   #5
o1110010
Registered User
 
o1110010's Avatar
 
Join Date: Mar 2004
Location: USA
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
o1110010 is on a distinguished road
Send a message via AIM to o1110010
Quote:
Originally Posted by Pat Hartman
.. move it to a standard module..
Meaning not found under "Microsoft Access Class Objects" but under "Modules", right?

Quote:
Originally Posted by Pat Hartman
If it is, you'll have a lot more changes to make.
Uh oh. Tedious or troublesome changes (from a person new to VBA)?
__________________
If an expected response takes a few days longer than expected, send a private message to ensure they didn't miss the post.

Last edited by o1110010; 03-24-2004 at 05:37 PM.
o1110010 is offline   Reply With Quote
Old 03-24-2004, 07:49 PM   #6
o1110010
Registered User
 
o1110010's Avatar
 
Join Date: Mar 2004
Location: USA
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
o1110010 is on a distinguished road
Send a message via AIM to o1110010
To help explain what this thread was mainly trying to get, I've mocked up this tiny example.

The database has a form (Form1). On Form1, there are multiple command buttons: testMe0, testMe1, & testMe2.

The OnClick procedures for these buttons:
Code:
Private Sub testMe0_Click()
    Call testMe
End Sub

Private Sub testMe1_Click()
    Call testMe(1)
End Sub

Private Sub testMe2_Click()
    Call testMe(2)
End Sub
And this procedure as a standard module..
Code:
Public Sub testMe(Optional numValue As Integer)
    MsgBox "yay? " & numValue
End Sub
Is there a way to pass a value from the buttons to the procedure? (I've noticed the "Tag" property on form objects but does this help me?) I know I am passing a value from Call testMe with two of the buttons but am curious if there is a different way.

Anyone follow me?
__________________
If an expected response takes a few days longer than expected, send a private message to ensure they didn't miss the post.
o1110010 is offline   Reply With Quote
Old 03-25-2004, 01:04 AM   #7
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
Quote:
Originally Posted by o1110010
Sub cmdAddDate_Click(Optional countLoop As Integer)
If IsNull(countLoop)
countLoop = 7
End If
'Blah blah
'blah blah
End Sub

IsNull() ? Use IsMissing()

Mile-O is offline   Reply With Quote
Old 03-25-2004, 01:16 AM   #8
Mile-O
Back once again...
 
Mile-O's Avatar
 
Join Date: Dec 2002
Location: Glasgow, UK
Posts: 11,312
Thanks: 4
Thanked 118 Times in 108 Posts
Mile-O will become famous soon enough
Yes, you can use the Tag property, but you'll get an Invalid Use of Null error if you try to pass a Null value (empty Tag) to a procedure, even if the argument is Optional.

So, put the value in the Tag, if need be, and in the click of the button:

Code:
If IsNull(Me.MyButton.Tag) Then
    Call testMe
Else
    Call testMe(Me.MyButton.Tag) Then
End If
As this would appear to be a repetitive bit of code, you are better making a Public Sub - it can be a Private Sub, however, if it's only relevant to this form.

Code:
Public/Private Sub SendButtonInfo(ctl As Control)
    If IsNull(ctl.Tag) Then
        Call testMe
    Else
        Call testMe(ctl.Tag)
    End If
End Sub
and on each command button, put:

Code:
    Call SendButtonInfo(Me.MyButton)
Mile-O is offline   Reply With Quote
Old 03-25-2004, 07:33 PM   #9
o1110010
Registered User
 
o1110010's Avatar
 
Join Date: Mar 2004
Location: USA
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
o1110010 is on a distinguished road
Send a message via AIM to o1110010
Quote:
Originally Posted by Mile-O-Phile
Yes, you can use the Tag property, but you'll get an Invalid Use of Null error if you try to pass a Null value (empty Tag) to a procedure, even if the argument is Optional.
So Public Sub SendButtonInfo(ctl As Control) skips this because you will always be passing the control instead of the Tag? Witty! I saw this post when I woke up this morning.. but sadly it confused me more than when I went to bed. I'm finally understanding it.

Quote:
Originally Posted by Mile-O-Phile
Code:
Public/Private Sub SendButtonInfo(ctl As Control)
    If IsNull(ctl.Tag) Then
        Call testMe
    Else
        Call testMe(ctl.Tag)
    End If
End Sub
and on each command button, put:

Code:
    Call SendButtonInfo(Me.MyButton)
That's what I will do then since I want to overload the user and make them think I did more work than I really did.

Thank you Mile-O-Phile!
__________________
If an expected response takes a few days longer than expected, send a private message to ensure they didn't miss the post.
o1110010 is offline   Reply With Quote
Old 03-25-2004, 07:46 PM   #10
o1110010
Registered User
 
o1110010's Avatar
 
Join Date: Mar 2004
Location: USA
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
o1110010 is on a distinguished road
Send a message via AIM to o1110010
Quote:
Originally Posted by Mile-O-Phile
Code:
    Call SendButtonInfo(Me.MyButton)

Hm, Do I have to put this in a macro? I don't see any Expression Functions relating to Call. (I suppose one macro is better than multiple/unlimited OnClick VBA calls.)

Edit: I've thrown it in a macro using RunCode command but it says "The expression you entered has a function name that Microsoft Access can't find."
__________________
If an expected response takes a few days longer than expected, send a private message to ensure they didn't miss the post.

Last edited by o1110010; 03-25-2004 at 08:10 PM.
o1110010 is offline   Reply With Quote
Old 03-25-2004, 08:04 PM   #11
ChrisO
Newly Registered User
 
ChrisO's Avatar
 
Join Date: Apr 2003
Location: Brisbane, Australia
Posts: 3,202
Thanks: 7
Thanked 274 Times in 195 Posts
ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice
You may be better off testing for a Null and a ZLS: -

Code:
Public Sub SendButtonInfo(ctl As Control)

    If Nz(ctl.Tag, "") = "" Then
        Call testMe
    Else
        Call testMe(ctl.Tag)
    End If
    
End Sub
Hope that helps.

Regards,
Chris.
__________________
Access 2003, Win7, GMT +10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ChrisO is offline   Reply With Quote
Old 03-26-2004, 12:08 PM   #12
o1110010
Registered User
 
o1110010's Avatar
 
Join Date: Mar 2004
Location: USA
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
o1110010 is on a distinguished road
Send a message via AIM to o1110010
Quote:
Originally Posted by ChrisO
You may be better off testing for a Null and a ZLS: -... If Nz(ctl.Tag, "") ...
Thank you for the note Chris. In this instance, I think IsNull will suffice though. Possibly only because I enjoy the word 'Null'.

At any rate, I'm still trying to figure out this recommendation..
Code:
Call SendButtonInfo(Me.MyButton)
See my previous post for additional details.

Thanks for the time.
__________________
If an expected response takes a few days longer than expected, send a private message to ensure they didn't miss the post.
o1110010 is offline   Reply With Quote
Old 03-26-2004, 03:52 PM   #13
ChrisO
Newly Registered User
 
ChrisO's Avatar
 
Join Date: Apr 2003
Location: Brisbane, Australia
Posts: 3,202
Thanks: 7
Thanked 274 Times in 195 Posts
ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice
G’day o1110010


Quote:
Thank you for the note Chris. In this instance, I think IsNull will suffice though. Possibly only because I enjoy the word 'Null'.
Well you may not continue to enjoy it if you use it in this instance.

From the AccessA97 help file…
You can enter a string expression up to 2048 characters long. The default setting is a zero-length string ("").

In this case the NZ function is redundant but causes no harm. If it is a Null, which it shouldn’t be because the Help file says so , it would get converted to a ZLS and the test would be based on that. Simple testing for Null should fail, according to the Help file.

Anyway, back to… Call SendButtonInfo(Me.MyButton)

In a previous post you referred to some of the equivalence between “C” and “VBA”

In this case: -

Call SendButtonInfo(Me.MyButton)

Is calling a Subroutine called SendButtonInfo and passing by reference a pointer to the control MyButton which is part of the control collection on the Form which contains it.

It could have been written: -

SendButtonInfo Me.MyButton

Argument Me is a pointer to the Forms properties and Control collection.
Argument Me.MyButton is a pointer to MyButton on this (Me) Form.

Subroutine SendButtonInfo receives a Control pointer type and hence argument ctl, the received argument, points to the Property collection of MyButton on Form Me.

And finally ctl.Tag points to the Tag property of that control, some text or ZLS.

Some different ways to do it…

Behind the Form: -

Code:
Option Explicit
Option Compare Text


'   This needs to be a Subroutine because it was generated by the System.
Private Sub cmdButton1_Click()

    Call SendButtonInfo(Me.cmdButton1)

End Sub


'   This needs to be a Subroutine because it was generated by the System.
Private Sub cmdButton2_Click()

    SendButtonInfo Me.cmdButton2

End Sub


'   This needs to be a Function because it was generated by the User.
Private Function CommonCallFor3And4(ByVal strButtonName As String)

    SendButtonInfo Me(strButtonName)
    
End Function


'   This needs to be a Function because it was generated by the User.
Private Function CommonCallFor5To10(ByVal strButtonName As String)

    SendButtonInfo Me(strButtonName)
    
End Function


Private Sub Form_Open(ByRef intCancel As Integer)
    Dim lngSuffix      As Long
    Dim strControlName As String
    
    For lngSuffix = 5 To 10
        strControlName = "cmdButton" & CStr(lngSuffix)
        Me(strControlName).Tag = strControlName
        Me(strControlName).OnClick = MakeFunctionCall("CommonCallFor5To10", strControlName)
    Next lngSuffix
    
End Sub
And in a Standard/Global/Pubic Module: -

Code:
Option Explicit
Option Compare Text


Public Sub SendButtonInfo(ByRef ctl As Control)

    If Nz(ctl.Tag, "") = "" Then
 '  If IsNull(ctl.Tag) Then
        Call testMe
    Else
        Call testMe(ctl.Tag)
    End If
    
End Sub


Public Sub testMe(Optional strValue As String = "No Value")

    MsgBox "yay? " & strValue
    
End Sub


Public Function MakeFunctionCall(ByVal strFunctionName As String, _
                                  ParamArray ArgList() As Variant) As String
    Dim lngElement  As Long
    Dim strFunction As String
    
    '   The first argument is NOT optional.
    strFunction = "=" & strFunctionName & "("
    
    '   All the remaining arguments are optional.
    '   Loop through argument range, if passed.
    For lngElement = LBound(ArgList) To UBound(ArgList)
        strFunction = strFunction & Chr$(34) & ArgList(lngElement) & Chr$(34) & ", "
    Next lngElement
                                 
    '   Did we receive any arguments?
    '   If so, trim off trailing ", ".
    If Right$(strFunction, 2) = ", " Then
        strFunction = Left$(strFunction, Len(strFunction) - 2)
    End If
    
    MakeFunctionCall = strFunction & ")"
                                 
End Function
Hope that helps.

Simple demo attached.

Regards,
Chris.
Attached Files
File Type: zip Test_Function_Call_A97.zip (16.7 KB, 297 views)
__________________
Access 2003, Win7, GMT +10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ChrisO is offline   Reply With Quote
Old 03-26-2004, 04:38 PM   #14
o1110010
Registered User
 
o1110010's Avatar
 
Join Date: Mar 2004
Location: USA
Posts: 182
Thanks: 0
Thanked 0 Times in 0 Posts
o1110010 is on a distinguished road
Send a message via AIM to o1110010
Fair Thee Well ChrisO,

Quote:
Originally Posted by ChrisO
G’day o1110010
Sounding a bit Aussie? I'm using Access 2000 but I have no doubts your Access 97 demo works. I am trying to use this as a learning experience so I am a bit weary to just be satisfied with working code. That make sense? At any rate, you are correct. I have mentioned I have a C/C++ background. Thank you for explaining it in the manner you did. It made more sense that way.

I noticed some differences in the code that you provided than I expected nor do I understand these differences yet.

First difference I noticed is..
Code:
Option Explicit
Option Compare Text
What do these do? I notice all my modules have Option Compare Database. I've never cared about what that meant because it was always above a line that split up my procedures :-p and I've never had issues with that line before.

What's the difference between Function and Sub? Going from your comments, I would think that if it's generated by me, it needs to be a function? But I've had it working referring to a Sub before I started messing with passing a value directly from the button (Tag).

Finally, the last difference that wasn't spoken of earlier. I noticed you use ByVal strButtonName As String but Mile-O-Phile used ctl As Control. Is the difference just a DAO vs ADO thing? If not, what?

(Relevant links would be just as welcome.)

Quote:
Originally Posted by ChrisO
You can enter a string expression up to 2048 characters long. The default setting is a zero-length string ("").
I missed that when I read about the Tag property the first time. However, my help file makes it appear as if there is a space " " between the quotes. But it doesn't matter because a ZLS is never null?

Quote:
Originally Posted by ChrisO
Anyway, back to… Call SendButtonInfo(Me.MyButton)
So for every button, I need to change MyButton to the corresponding button's name being clicked. No way around that? I was hoping to have one universal call from the button where the only difference between button's would be their visual appearance, name, & Tag. No possible? Let me rephrase that.. Not the easiest way?

I am aware that Call SendButtonInfo(Me.MyButton) from an OnClick procedure works. I was wondering if Mile-O-Phile was referring to a macro or directly in the event portion of the button. For every button I use for this example, I will need to have a seperate procedure? If so, is there a point to mess with "Tag" in this instance?

Lastly, what are the procedures Form_Open and MakeFunctionCall for? Can describe 'em? (After looking at them, I wouldn't think I'd need them for what I want to do.)

Thank you for your help so far!
__________________
If an expected response takes a few days longer than expected, send a private message to ensure they didn't miss the post.
o1110010 is offline   Reply With Quote
Old 03-26-2004, 07:27 PM   #15
ChrisO
Newly Registered User
 
ChrisO's Avatar
 
Join Date: Apr 2003
Location: Brisbane, Australia
Posts: 3,202
Thanks: 7
Thanked 274 Times in 195 Posts
ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice ChrisO is just really nice
G’day or (o1110010 )

There are a few questions here…

Since you are using A2000 you can convert the A97 demo database, that’s one of the two main reasons I stick to A97. The other is that it has a help file system that works, not as good as the one in Borland compilers but better than later versions of Access.

Option Explicit…
Forces the declaration of variables before they are referenced, always good practice when writing code. Only reason I know for turning it off is if we get a database that has not declared variables and we don’t want to re-write it before we can compile it. Of course, if it was always on then we would not have that problem because we could not get databases written like that.

Option Compare Text…
I would not like to re-write or disagree with Michael Kaplan in this article.

What's the difference between Function and Sub?
Two main differences, one of which you will be aware of, coming from “C”, one of which you will become aware of going to VBA.

In “C” a Function must return a value, a Subroutine must not return a value except by reference; both indiscretions will be picked up by the “C” pre-compiler.

In “VBA” a Function need not return a value, a Subroutine cannot return a value except by reference.

In both “C” and “VBA” arguments passed ByRef, and modified, can ‘reflect’ back to the caller.
This can become a subtle trap, which can be difficult to find, if it causes a problem

Why does a System generated Event Handler have to be a Subroutine?
Why does a User generated Event Handler have to be a Function?
I have absolutely no idea and over to Bill $x10^11 and the people at Micro$oft.

Quote:
Finally, the last difference that wasn't spoken of earlier. I noticed you use ByVal strButtonName As String but Mile-O-Phile used ctl As Control. Is the difference just a DAO vs ADO thing? If not, what?
I’m not sure what you mean by the comparison here, perhaps you could please point me in the right direction. In any case it is not a difference between DAO and ADO.

Quote:
I missed that when I read about the Tag property the first time. However, my help file makes it appear as if there is a space " " between the quotes. But it doesn't matter because a ZLS is never null?
Correct.
My view of the similarity between “C” and “VBA” is that a ZLS has been declared and initialized to zero length and has, the address that is, been assigned a valid value. A Null has been allocated memory space for the pointer and not initialized with the address of the data. Hence; if it contains anything at all, the address that is, it would contain junk left over from the last user of that memory space, if any.

Quote:
So for every button, I need to change MyButton to the corresponding button's name being clicked. No way around that? I was hoping to have one universal call from the button where the only difference between button's would be their visual appearance, name, & Tag. No possible? Let me rephrase that.. Not the easiest way?
First thing for sure each button, or whatever, must have a unique name otherwise there is no way to reference that control and you won’t even get passed the editor.

You can reference a Control without referencing its Name but the name still needs to be unique.
Pointers to Controls “live” in an array, at run time, and can be referenced by array sub-script.
However, to reference a particular Control, or set of Controls, in that array you still need a way to make the reference unique. The system creates the array and the position of any particular control can’t be relied on.

There are a few ways to achieve that “Control Uniqueness” but would require another article to do so.

Quote:
I am aware that Call SendButtonInfo(Me.MyButton) from an OnClick procedure works. I was wondering if Mile-O-Phile was referring to a macro or directly in the event portion of the button. For every button I use for this example, I will need to have a seperate procedure? If so, is there a point to mess with "Tag" in this instance?
First part of the question;
I have never been able to get that syntax to work but would appreciate any guidance.

Second part of the question;
I doubt very much if Mile-O was referring to a Macro.
My understanding of Macros is that they are like training wheels on a bicycle…
Once you have got your balance they should be dispensed with.
I think Mile-O has his balance.

Third part of the question;
Separate procedure? Absolutely not.
I have a form with 500+ text boxes on it, one more on leap years. It’s a graphical display that has nothing to do with the underlying structure of the database. It displays some booking details of a particular unit for each day over a period of time. Display starts at the beginning of the selected year and stops at the end of the next financial year. Each box has a MouseMove, Click, and DblClick event as well as a Tag property, the Tag property has the booking ID in it.

All of the above are set in code, mainly because I’m lazy. Four parameters for each of 500+ text boxes. 2000+ parameters to possibly change manually?

Never…I’m lazy!

Quote:
Lastly, what are the procedures Form_Open and MakeFunctionCall for? Can describe 'em? (After looking at them, I wouldn't think I'd need them for what I want to do.)
The Form Open event is where you set-up the code to configure your private collection. The MakeFunctionCall function helps to remove the complexity of writing code that writes code.

Above all else…have fun with Access and VBA.

Regards,
Chris.

__________________
Access 2003, Win7, GMT +10,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

Last edited by ChrisO; 03-26-2004 at 07:38 PM.
ChrisO is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump




All times are GMT -8. The time now is 05:24 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World