Optional Arguments in Sub Procedure

[Guest]

I'm trying to use a Sub Procedure with optional arguments but I keep getting
an error when I call the procedure excluding the optional arguments. I've
tried overloading the procedure but I get another error.
The error I'm getting is "Run Tim Error '19': Object variable or with block
variable not set"
When I click Debug it highlights the code with the ****

Sub setVisible(ctl1 As Control, ctlVis1 As Boolean, Optional ctl2 As
Control, _
Optional ctlVis2 As Boolean, Optional ctl3 As Control, _
Optional ctlVis3 As Boolean, Optional ctl4 As Control, _
Optional ctlvis4 As Boolean)

ctl1.Visible = ctlVis1

If Not IsMissing(ctl2) _
Then
ctl2.Visible = ctlVis2
End If

If Not IsMissing(ctl3) _
Then
ctl3.Visible = ctlVis3 ****
End If

If Not IsMissing(ctl4) _
Then
ctl4.Visible = ctlvis4
End If

End Sub

I'm passing Controls to the procedure; ComboBoxes, ListBoxes, Rectangles,
and Buttons.
In the debug mode when I put my mouse over the ctl3 it says "ctl3=Nothing"
so I tried adding And Not ctl3 = Nothing but then I get the Error "Invalid
use of Object"
I've also tried adding And Not IsNull(ctl3) that didn't work either.
I tried looking through this website and the help files to find a function
that checks to see if the control is initialized but I haven't found anything.

Any help would be apreciated - Thanks

 

[Douglas J. Steele]

Rather than And Not ctl3 = Nothing, try using

And ctl3 Is Not Nothing

 

[Guest]

Thanks for the post
I got the same error - "Compiler Error: Invalid use of object"

Why is it going into the If statement if the Control is missing?

 

[Douglas J. Steele]

How are you calling the sub?

 

[Guest]

I call it from another procedure... I can post all of my code if it will help
you help me
But here is the initial call:

setVisible CP_AddNwPhBtn, True, CP_BX, True, CP_CmboBX, False, CP_AddPhBtn,
False

A few lines down from that code is:

setCntrlProp CP_ListBX, CP_DelBtn, False, True, False, True, 1, "3.75 in"

Which goes to this procedure:

Sub setCntrlProp(bx As Control, btn As Control, btnVis As Boolean, bxVis As
Boolean, _
enab As Boolean, lckd As Boolean, col As Integer, width As
String)

setVisible bx, bxVis, btn, btnVis ******
bx.Enabled = enab
bx.Locked = lckd
bx.ColumnCount = col
bx.ColumnWidths = width

End Sub


The ****** is the call that is getting messy

I call it in other places but my code hasn't ran that far yet, I'm assumming
that it will do the same if it ever gets to it.
Should I just write seperate procedures, or write the code from where I call
the procedure insted of calling it?

Thanks

 

[Douglas J. Steele]

To be honest, I don't think trying to overload the SetVisible function like
you are accomplishes anything.

I'd make it accept 1 control and 1 boolean value, and call it multiple
times.

 

[George Nicholson]

AND ctl3 Is Not Nothing

I think that AND wants to be an OR, doesn't it?


HTH,

 

[Guest]

George -
Unfortunatly it dosen't matter if it's and AND or an OR when I try to use
the 'Nothing'
word it throws a compiler error saying "Invalid use of object". I'm very
green when it comes to programming in VB so I think I know where the error is
but I could be way off. I just know that when it gets to the IsMissing
statement it tries to access the control that isn't there and then throws an
error.

Thanks for your post and trying to help.

 

[Guest]

Yea I think your right, it's time to throw in the towel. I think I'm just
going to get rid of the entire procedure and just put the code in where I was
calling it from.
Thanks for all your time and help yesterday I realy appreciate it.

 

[Guest]

I wanted to ask a quick question, I hope I'm not going to get yelled at for
not being in the right forum, but I see a lot in the help files in Access and
see a lot of posts refering to ADO DAO and .NET. I think I'm using ADO in
some of my code when I get info from the database using ADODB. What is the
difference between them and is one the correct thing to use? Or will pretty
much anything work and they are all interchangeable?

Thanks

 

[Douglas J. Steele]

DAO, ADO and ADO.Net are simply different methods for getting at data.

DAO (the oldest) was developed specifically for Jet databases (i.e. .MDB or
..MDE files), although it can also go against other data sources. If your
data is in a Jet database, it's usually more efficient to use DAO.
(Actually, DAO has been replaced by ACE in Access 2007)

ADO is a more generic approach. To make it generic, it needs to go through
more layers of abstraction, hence why it tends to be less efficient than DAO
against the same data source. Some people advise that if you plan on moving
from a Jet data source to another data source, you should use ADO right from
the start. This advice ignores the fact that in order to make an application
more efficient when you move from a Jet data source, you need to make other
changes as well. Converting from DAO to ADO at that time wouldn't add that
much additional work.

ADO.Net is the newer replacement for ADO.

 

[Guest]

Oh Ok, I get It.
Thanks
--
Piper

DAO, ADO and ADO.Net are simply different methods for getting at data.

DAO (the oldest) was developed specifically for Jet databases (i.e. .MDB or
..MDE files), although it can also go against other data sources. If your
data is in a Jet database, it's usually more efficient to use DAO.
(Actually, DAO has been replaced by ACE in Access 2007)

ADO is a more generic approach. To make it generic, it needs to go through
more layers of abstraction, hence why it tends to be less efficient than DAO
against the same data source. Some people advise that if you plan on moving
from a Jet data source to another data source, you should use ADO right from
the start. This advice ignores the fact that in order to make an application
more efficient when you move from a Jet data source, you need to make other
changes as well. Converting from DAO to ADO at that time wouldn't add that
much additional work.

ADO.Net is the newer replacement for ADO.

 

[George Nicholson]

You may be better off, (at least until the greenness wears off) handling one
control at a time, as Douglas suggested.

"Invalid use of Object" is an interesting message. Not "can't find object",
not "Object doesn't have this property/method". Invalid suggests misuse of
an existing property/method. One thing I can suggest is to make sure that
any control for which you are trying to set Visible/Enabled to False does
*not* have focus (trying to disable the active control will definitely raise
an error. Not 100% sure that hiding the active control would do the same
thing, but it would be consistent). I think you'd get a more specific error
message if this were the problem, but one never knows...

Why is it going into the If statement if the Control is missing?

As far as IsMissing() goes, afaik it only works if the Optional argument has
been declared as a Variant type. The VBA Language Ref Help entry actually
says this, sort of, once you realize you have to take it *very* literally.
It doesn't quite come out and say it won't work with any other data type,
but that's been my experience.

Consider the following (Access 2003):
********************************
Public Function LoadTasks1(Optional Dummy As Variant)
MsgBox (Dummy)
End Function

Public Function LoadTasks2(Optional Dummy As Boolean)
MsgBox (Dummy)
End Function

'(Functions identical except for DataType declared. Same results if Boolean
is changed to various Objects, etc.)

'In Immediate Window
Call LoadTasks1 ' Returns "True": IsMissing works w/Variant type
Call LoadTasks2 ' Returns "False": IsMissing does not work

'It seems that the only time it returns True is if it is specifically
declared as a Variant AND the argument is not passed. Otherwise it returns
False (including whenever an arguement is passed).
**********************************

Ditch IsMissing and stick with Is Nothing.

HTH,

George -
Unfortunatly it dosen't matter if it's and AND or an OR when I try to use
the 'Nothing'
word it throws a compiler error saying "Invalid use of object". I'm very
green when it comes to programming in VB so I think I know where the error
is
but I could be way off. I just know that when it gets to the IsMissing
statement it tries to access the control that isn't there and then throws
an
error.

Thanks for your post and trying to help.

 

[Guest]

Ha - It Works!!!!
Before I was trying to wite it exactly as George wrote it
"ctl3 Is Not Nothing"
That was giving me the compiler error "Invalid use of object". I had also
thought that I was passing an argument that didn't have the .Visible
property. After going line by line through the code I realized that the
argument, or Control, didn't exist. Meaning that I called the procedure
omitting the Optional argument. When it got to the If statement it was trying
to access the ctl3, wich didn't exist, in the IsMissing procedure, thus
giving me the error "Object variable or With block variable not set". I took
that to mean that the ctl3 didn't exist, which it didn't so I was trying to
find a way to check the Control to see if it existed. In the Debug mode when
I put the cursor over the ctl3 it said "ctl3 = Nothing" so I decided to try
the
If Not ctl3 = Nothing. So I had the right Idea just the wrong syntax.

When you wrote "... stick with Is Nothing" I decided to try re-writing the
If statement to:

If Not (ctl3 Is Nothing)

And low and behold it worked! I couldn't believe it.
Thanks to you and george for all the time and help!
It's much appreciated.

 

[George Nicholson]

Very sorry about the "Is Not Nothing" typo. Not sure if that originated with
Douglas or myself but neither of us caught it subsequently, so our bad!

Glad you managed to get it sorted out in spite of our help