"Set" command anomaly results in Null

[BogMonster]

Hi all,

Hope someone can help. This should be obvious but I can't seem to figure it
out.

Background...
Basically, my code creates a number of forms with textbox controls on them
(MiniGrid). These effectively create textbox "grids". These "grid" forms are
then inserted into another form (MainGrid), via subform controls, to create a
major grid (I went this route because I could end up with 1200+ textboxes and
Access won't allow that many controls in a single form, so I split them up).
Finally, I have another form (InputForm) that brings the "main grid" and a
couple of other subforms together in the appropriate layout.

I then have a main form with a subform control to refer to that last
mentioned form. So the path from MainForm to a specific textbox would be
MainForm->InputForm->MainGrid->MiniGrid.TextBox
Next, I created a class GridBox that has a WithEvents textbox control that
will reference one of the grid textboxes. The idea being that user activity
in any of the grid textboxes will trigger the appropriate events that will be
sunk by the class instance's code.
I don't know if any of that is relevant to this specific problem but thought
I'd mention it anyway.

Problem...
In the MainForm's module I have a routine that cycles through all the
controls on those lowest level grid forms, creates a New GridBox and tries to
set it to reference the current control in the loop. Then it will add the new
GridBox object to a collection.

For Each ctl In .Controls (the full path is included in an earlier With
statement)
If ctl.ControlType = acTextBox Then
Set moThisTextBox = New GridBox (This works fine)
Set moThisTextBox.TextBox =ctl (This fails in
the GridBox class because ctl

references NULL)
mcolGridBoxes.Add moThisTextBox, ctl.Name (This fails because ctl
= NULL)
End If
Next ctl

What I can't understand is that when I add statements like "Debug.Print ctl.
name" inbetween the offending lines, it prints the correct name of the
control! Furthermore, if I remove the 3 lines in the loop and replace them
with lines like "ctl.BackColor = vbYellow", all the textboxes on the form are
set to yellow. So for example, the following code in the loop works just fine

ctl.BackColor = vbYellow
Debug.Print ctl.Name
Set ctl2 = ctl
ctl2.BackColor = vbRed

Obviously this changes a texbox first to Yellow, prints its name, sets a
different ctl (ctl2) to refer to ctl and then changes ctl2 to Red. All the
textboxes end up Red.

Firstly, this tells me that the path used to locate the textboxes is correct!
Secondly, this tells me that the loop does indeed loop through all the
textboxes and references them using "ctl" as I want it to.

What's really weird is that if I halt execution on the "Set ctl2 = ctl" line
of code and hover over the "ctl" variable, the QuickInfo says that ctl=NULL
and after that line "ctl2" also equals NULL. Why then does it still proceed
to change the color to Red? Surely if either variable was NULL then the color
changes and name print would fail!

I've have a fair bit of VBA experience in Excel but this is my first crack at
Access VBA. I would still expect this kind of object referencing to be the
same though.

What's going on? I've now got a headache and mild pins & needles in my left
arm! :-o

Somebody please HELP!!!!

 

[Marshall Barton]

Background...
Basically, my code creates a number of forms with textbox controls on them
(MiniGrid). These effectively create textbox "grids". These "grid" forms are
then inserted into another form (MainGrid), via subform controls, to create a
major grid (I went this route because I could end up with 1200+ textboxes and
Access won't allow that many controls in a single form, so I split them up).
Finally, I have another form (InputForm) that brings the "main grid" and a
couple of other subforms together in the appropriate layout.

I then have a main form with a subform control to refer to that last
mentioned form. So the path from MainForm to a specific textbox would be
MainForm->InputForm->MainGrid->MiniGrid.TextBox
Next, I created a class GridBox that has a WithEvents textbox control that
will reference one of the grid textboxes. The idea being that user activity
in any of the grid textboxes will trigger the appropriate events that will be
sunk by the class instance's code.
I don't know if any of that is relevant to this specific problem but thought
I'd mention it anyway.

Problem...
In the MainForm's module I have a routine that cycles through all the
controls on those lowest level grid forms, creates a New GridBox and tries to
set it to reference the current control in the loop. Then it will add the new
GridBox object to a collection.

For Each ctl In .Controls (the full path is included in an earlier With
statement)
If ctl.ControlType = acTextBox Then
Set moThisTextBox = New GridBox (This works fine)
Set moThisTextBox.TextBox =ctl (This fails in
the GridBox class because ctl

references NULL)
mcolGridBoxes.Add moThisTextBox, ctl.Name (This fails because ctl
= NULL)
End If
Next ctl

What I can't understand is that when I add statements like "Debug.Print ctl.
name" inbetween the offending lines, it prints the correct name of the
control! Furthermore, if I remove the 3 lines in the loop and replace them
with lines like "ctl.BackColor = vbYellow", all the textboxes on the form are
set to yellow. So for example, the following code in the loop works just fine

ctl.BackColor = vbYellow
Debug.Print ctl.Name
Set ctl2 = ctl
ctl2.BackColor = vbRed

Obviously this changes a texbox first to Yellow, prints its name, sets a
different ctl (ctl2) to refer to ctl and then changes ctl2 to Red. All the
textboxes end up Red.

Firstly, this tells me that the path used to locate the textboxes is correct!
Secondly, this tells me that the loop does indeed loop through all the
textboxes and references them using "ctl" as I want it to.

What's really weird is that if I halt execution on the "Set ctl2 = ctl" line
of code and hover over the "ctl" variable, the QuickInfo says that ctl=NULL
and after that line "ctl2" also equals NULL. Why then does it still proceed
to change the color to Red? Surely if either variable was NULL then the color
changes and name print would fail!

I've have a fair bit of VBA experience in Excel but this is my first crack at
Access VBA. I would still expect this kind of object referencing to be the
same though.

What's going on? I've now got a headache and mild pins & needles in my left
arm! :-o

I won't pretend to follow much of that, but an object
variable can not be Null. This leads me to think that you
are confusing the object's Value property with the object.
If you want to check is an object is the same as another
object, you need to use the IS operator (an uninitialized
object variable is Nothing). Just looking at the object
variable as you did in the debug window will show you its
Value property so your interpretation of the problem's
symptoms is out of whack with what's really happening.

Are you really, really sure you have to have all those
controls? If all you need is to intercept click events on
each "control" in your grid, be aware the you can place an
invisible button controls over an area of a form and use the
button's MouseUp event to get the click's X,Y coordinates.

 

[BogMonster]

Marshall,

Many thanks for you prompt response. I guess I did go on a bit but I was
worried that I may leave something important out.

Naturally, I thought this was odd too and I think you're right on the
mistaken value (I've been working 14hrs/day for 6 weeks and my mind is not
what it should be!). I remember that I haven't set a default value for the
textboxes but the format setting shows it as 0. That would account for the
Null but it still failed at the line "Set mtxtBox = txtTextBox" in the Class
module. The txtTextBox is the control being passed in and both are textbox
controls.

However, I seemed to have accidentally sorted the problem. I went back to the
GridForm (the ones with the the textboxes in it) and changed the HasModule
property to yes. Viola! Everything works just fine. There's no code in the
modules but I read elswhere in this forum that in order for the WithEvents to
work, the form containing the target control must have its HasModule property
set to yes. Turn it off and the original problem returns, turn it on and
everythings peechy. I knew it would be something simple!

As an aside regarding the need for all the controls...the main form is an
input screen for a forecasting model. Thus most textboxes are used for input
but input for certain portions of the grid will be restricted after deadlines
pass. Other boxes will be calculations or results of queries. It effectively
mimicks a pivot table with columns being time frames (variable), rows being
revenue/costs etc and pages being contracts.

Thanks again for your response.

 

[Marshall Barton]

A seriously messy situaion and I am not convinced that you
must use this approach. However, it's easy for me to say
that when I don't have to produce your app ;-)

A form is not a class object unless it has a module, so the
HasModule setting is absolutely required when you want to
use it as a class. It's a rather subtle requirement that
can easily slip through the cracks if you haven't seen it
before.

 

[BogMonster via AccessMonster.com]

Thanks Marshall.

I agree it's messy. Like I said, I'm coming from an Excel background where
I've developed fairly sophisticated models but Excel is not a database and a
recent merger has resulted in the old model becoming obsolete overnight, due
to size. Hence a database solution became necessary. This is my first serious
venture into database development and I effectively have about 6 weeks to
design, develop and implement the new solution (while holding down my regular
responsibilities!). So I really had to run with my first instincts as I don't
have the time luxury or experience to try different approaches. There aren't
any other developers in my department and the formal IT group just don't want
to know my problems!! :-(

Yes, the HasModule was a subtlety as I don't have any code specifically in
those Grid forms and I read that performance is slightly better if you can
exclude them. One more notched down to experience. ;-)

When you mentioned that object can't be null , it was one of those "Doh"
moments. I've been programming long enough to know that but the class error
and lack of sleep combined to give me tunnel vision. :-(

Fortunately, this is a really good forum and I would be significantly further
behind schedule if it wasn't for people like you generously giving up their
time to answer other people's questions. Maybe one day I'll be good enough to
contribute!

I would certainly love to have the opportunity to have an experienced
developer look at my model when it's complete and critique it as that is an
excellent learning aid. Any volunteers?

I think I'll be posting another question later regarding how to get a control
that is off-screen but that has focus, back onto the screen. When I skip
across the grid textboxes, those that are off-screen get the focus but to see
them I have to use the scrollbar. SetFocus doesn't seem to do it. It's been
quite frustrating that Access forms don't seem to have any way of
programmatically controlling the scrollbars, like Excel does. I think there
are probably some API routines that will achieve this but I'm not strong on
those yet.

A seriously messy situaion and I am not convinced that you
must use this approach. However, it's easy for me to say
that when I don't have to produce your app ;-)

A form is not a class object unless it has a module, so the
HasModule setting is absolutely required when you want to
use it as a class. It's a rather subtle requirement that
can easily slip through the cracks if you haven't seen it
before.

Many thanks for you prompt response. I guess I did go on a bit but I was
worried that I may leave something important out.

[quoted text clipped - 39 lines]

 

[Marshall Barton]

Well, I hope you can make this arrangement work for you and
the performance is acceptable.

For API stuff in Access, your first stop must be
www.lebans.com
Be sure to check Stephen's SetGetSB. If it doesn't take
care of your problem, it will at least provide ideas and
example code.
--
Marsh
MVP [MS Access]

I agree it's messy. Like I said, I'm coming from an Excel background where
I've developed fairly sophisticated models but Excel is not a database and a
recent merger has resulted in the old model becoming obsolete overnight, due
to size. Hence a database solution became necessary. This is my first serious
venture into database development and I effectively have about 6 weeks to
design, develop and implement the new solution (while holding down my regular
responsibilities!). So I really had to run with my first instincts as I don't
have the time luxury or experience to try different approaches. There aren't
any other developers in my department and the formal IT group just don't want
to know my problems!! :-(

Yes, the HasModule was a subtlety as I don't have any code specifically in
those Grid forms and I read that performance is slightly better if you can
exclude them. One more notched down to experience. ;-)

When you mentioned that object can't be null , it was one of those "Doh"
moments. I've been programming long enough to know that but the class error
and lack of sleep combined to give me tunnel vision. :-(

Fortunately, this is a really good forum and I would be significantly further
behind schedule if it wasn't for people like you generously giving up their
time to answer other people's questions. Maybe one day I'll be good enough to
contribute!

I would certainly love to have the opportunity to have an experienced
developer look at my model when it's complete and critique it as that is an
excellent learning aid. Any volunteers?

I think I'll be posting another question later regarding how to get a control
that is off-screen but that has focus, back onto the screen. When I skip
across the grid textboxes, those that are off-screen get the focus but to see
them I have to use the scrollbar. SetFocus doesn't seem to do it. It's been
quite frustrating that Access forms don't seem to have any way of
programmatically controlling the scrollbars, like Excel does. I think there
are probably some API routines that will achieve this but I'm not strong on
those yet.

A seriously messy situaion and I am not convinced that you
must use this approach. However, it's easy for me to say
that when I don't have to produce your app ;-)

A form is not a class object unless it has a module, so the
HasModule setting is absolutely required when you want to
use it as a class. It's a rather subtle requirement that
can easily slip through the cracks if you haven't seen it
before.

Many thanks for you prompt response. I guess I did go on a bit but I was
worried that I may leave something important out.

[quoted text clipped - 39 lines]

invisible button controls over an area of a form and use the
button's MouseUp event to get the click's X,Y coordinates.

 

[BogMonster via AccessMonster.com]

Thanks again Marshall

I'll be sure to check it out. Maybe incorporate some of it into release 2 if
they haven't fired me by then! ;-))

Well, I hope you can make this arrangement work for you and
the performance is acceptable.

For API stuff in Access, your first stop must be
www.lebans.com
Be sure to check Stephen's SetGetSB. If it doesn't take
care of your problem, it will at least provide ideas and
example code.

I agree it's messy. Like I said, I'm coming from an Excel background where
I've developed fairly sophisticated models but Excel is not a database and a

[quoted text clipped - 47 lines]