Passing an optional value (1 Viewer)

o1110010

Registered User.
Local time
Today, 08:13
Joined
Mar 6, 2004
Messages
182
*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.. :eek: )

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).
 
Last edited:

billyr

Registered User.
Local time
Today, 09:13
Joined
May 25, 2003
Messages
123
Look in Help

From Access VB Help.

Sub OptionalArgs(strState As String, Optional strCountry As String)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:13
Joined
Feb 19, 2002
Messages
42,970
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.
 

o1110010

Registered User.
Local time
Today, 08:13
Joined
Mar 6, 2004
Messages
182
So I just put the word "Optional" in front of the arguement? :eek: (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?
 
Last edited:

o1110010

Registered User.
Local time
Today, 08:13
Joined
Mar 6, 2004
Messages
182
Pat Hartman said:
.. move it to a standard module..

Meaning not found under "Microsoft Access Class Objects" but under "Modules", right?

Pat Hartman said:
If it is, you'll have a lot more changes to make.

Uh oh. Tedious or troublesome changes (from a person new to VBA)?
 
Last edited:

o1110010

Registered User.
Local time
Today, 08:13
Joined
Mar 6, 2004
Messages
182
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?
 

Mile-O

Back once again...
Local time
Today, 13:13
Joined
Dec 10, 2002
Messages
11,316
o1110010 said:
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

Back once again...
Local time
Today, 13:13
Joined
Dec 10, 2002
Messages
11,316
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)
 

o1110010

Registered User.
Local time
Today, 08:13
Joined
Mar 6, 2004
Messages
182
Mile-O-Phile said:
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. :eek: :p

Mile-O-Phile said:
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. :p :D :cool:

Thank you Mile-O-Phile!
 

o1110010

Registered User.
Local time
Today, 08:13
Joined
Mar 6, 2004
Messages
182
Mile-O-Phile said:
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." :(
 
Last edited:

ChrisO

Registered User.
Local time
Tomorrow, 00:13
Joined
Apr 30, 2003
Messages
3,202
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.
 

o1110010

Registered User.
Local time
Today, 08:13
Joined
Mar 6, 2004
Messages
182
ChrisO said:
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.
 

ChrisO

Registered User.
Local time
Tomorrow, 00:13
Joined
Apr 30, 2003
Messages
3,202
G’day o1110010


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 :rolleyes: , 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


[color=green]'   This needs to be a Subroutine because it was generated by the System.[/color]
Private Sub cmdButton1_Click()

    Call SendButtonInfo(Me.cmdButton1)

End Sub


[color=green]'   This needs to be a Subroutine because it was generated by the System.[/color]
Private Sub cmdButton2_Click()

    SendButtonInfo Me.cmdButton2

End Sub


[color=green]'   This needs to be a Function because it was generated by the User.[/color]
Private Function CommonCallFor3And4(ByVal strButtonName As String)

    SendButtonInfo Me(strButtonName)
    
End Function


[color=green]'   This needs to be a Function because it was generated by the User.[/color]
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
 [color=green]'  If IsNull(ctl.Tag) Then[/color]
        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
    
    [color=green]'   The first argument is NOT optional.[/color]
    strFunction = "=" & strFunctionName & "("
    
    [color=green]'   All the remaining arguments are optional.
    '   Loop through argument range, if passed.[/color]
    For lngElement = LBound(ArgList) To UBound(ArgList)
        strFunction = strFunction & Chr$(34) & ArgList(lngElement) & Chr$(34) & ", "
    Next lngElement
                                 
    [color=green]'   Did we receive any arguments?
    '   If so, trim off trailing ", ".[/color]
    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.
 

Attachments

  • Test_Function_Call_A97.zip
    16.7 KB · Views: 346

o1110010

Registered User.
Local time
Today, 08:13
Joined
Mar 6, 2004
Messages
182
Fair Thee Well ChrisO,

ChrisO said:
G’day o1110010

Sounding a bit Aussie? :D 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). :confused:

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.)

ChrisO said:
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?

ChrisO said:
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.) :confused:

Thank you for your help so far!
 

ChrisO

Registered User.
Local time
Tomorrow, 00:13
Joined
Apr 30, 2003
Messages
3,202
G’day or (o1110010 :confused: )

There are a few questions here… :D

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. :mad:

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.

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.

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.

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.

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! ;)

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.
 
Last edited:

o1110010

Registered User.
Local time
Today, 08:13
Joined
Mar 6, 2004
Messages
182
ChrisO said:
G’day or (o1110010 :confused: )

Heh. Since most free web-related services (aim/hotmail/etc) don't allow someone to register an account starting with a number. I represented the first ZERO with an "O". In binary, 1110010 would actually be a negative number. To be positive, it must start with a zero. To represent a character, it must be positive and 8 in length. ;)

I had time to go over the code piece by piece today. Quite interested in one procedure.

Code:
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

I was going to ask "How does this process?" BUT I just realised that lngSuffix is being initialised in the first line of the FOR loop. And then that suffix gets slapped together with cmdButton to create the control name on the form.. cool! Now I should be able to use this sub, my equivalent to CommonCallFor5To10, SendButtonInfo, and testMe procedures and not need any of the other procedures, right? (So I don't need to have a seperate OnClick for each button!! :-D)

ChrisO said:
Since you are using A2000 you can convert the A97 demo database, that’s one of the two main reasons I stick to A97.

Converted it. Works fine and dandy. Good examples for the differences between ways I can go about getting it done. :)


ChrisO said:
Option Explicit...

So all your modules use this? Remember a time you used the default?

ChrisO said:
In both “C” and “VBA” arguments passed ByRef, ... a subtle trap..

I looked up ByRef & ByVal yesterday. I had not noticed them before in VBA because they are optional. I can see your reasoning though. :)

ChrisO said:
First thing for sure each button... must have a unique name...

I've always used unique names.. the Form_Open threw me off since I didn't understand what was going on. Thought maybe somehow one didn't need to have unique control names and then they were judged on their other properties instead. Ah but since I understand Form_Open now, life is good. :-D

Once again. Thanks for your help so far. :cool:
 

ChrisO

Registered User.
Local time
Tomorrow, 00:13
Joined
Apr 30, 2003
Messages
3,202
G’day o1110010

I wanted to see what the name might mean, so I took the “o” and converted “1110010” to 114 decimal, looked up the ASCII character and got “r”; hence “or”. :eek:

Now I should be able to use this sub, my equivalent to CommonCallFor5To10, SendButtonInfo, and testMe procedures and not need any of the other procedures, right? (So I don't need to have a seperate OnClick for each button!! :-D)
Again correct. In fact you don’t need to manually populate the OnClick event property, or any other event property and most other properties, once the Controls in the collection are named sequentially.

Also worth pointing out, remember more than one person is reading this ;) , is that the Event Handler property is just a string and can be set, changed and re-set at runtime; it’s just a string. For example, to turn off event handling for any event under program control, simply set the Event Handler property to a ZLS (“”).

So all your modules use this? Remember a time you used the default?

There are two defaults mentioned here, Option Explicit and Option Compare Database.

Option Explicit…
I have never written anything with it turned off. I have turned it off to compile some other databases but to be quite frank; I don’t usually spend much time on those databases unless someone pays me.

Option Compare Database…
I don’t know how long ago I started using Option Compare Text but it was immediately after reading the article by Michael Kaplan here posted. Did I or do I have a need to do so? No! But I can’t see a single reason to take the risk, as the English saying goes… “You’re on a hiding to nothing.”

Good rapport, excellent questions and hopefully a good reply. ;)

Kind regards,
Chris.
 

Users who are viewing this thread

Top Bottom