public var works in 2003 but not 2007

  • Thread starter Thread starter MNJoe
  • Start date Start date
M

MNJoe

I have a report that opens a form, on the form is a combo box with a
selection of resources. when the user selects a resource and clicks the start
button the form disappears and the report query uses the resource to generate
the report. The report query can not use the combo box value so I declared a
public variable and set it to the value of the selectioin in the combo box. I
use that variable in the report query. This works for Version 2003 but in
version 2007 when I run it. A msg box pops up wanting the user to input the
value into the query.

I know using public variables is not a real good idea (read that in a few
postings here) but, This was a little cleaner than creating a text box on the
form (which the user could see and would play around with) then setting the
value of the text box to the combo box. Do I have to change over to the text
box to get this to work in 2007?

any help or suggestions greatly appreciated.

the code I use in the query is

[forms]![resource_Load_Form].[STRResource_Selected]
 
MNJoe said:
I have a report that opens a form, on the form is a combo box with a
selection of resources. when the user selects a resource and clicks the
start
button the form disappears and the report query uses the resource to
generate
the report. The report query can not use the combo box value so I declared
a
public variable and set it to the value of the selectioin in the combo
box. I
use that variable in the report query. This works for Version 2003 but in
version 2007 when I run it. A msg box pops up wanting the user to input
the
value into the query.

I know using public variables is not a real good idea (read that in a few
postings here) but, This was a little cleaner than creating a text box on
the
form (which the user could see and would play around with) then setting
the
value of the text box to the combo box. Do I have to change over to the
text
box to get this to work in 2007?

any help or suggestions greatly appreciated.

the code I use in the query is

[forms]![resource_Load_Form].[STRResource_Selected]
Click to expand...


I'd have to test and compare A2003 and A2007 to see what gives witht he
public variable, since I never use them in that way, but you could resolve
your problem with either of two workarounds:

1. Use a text box on the form, but make it invisible (set its Visible
property to No). It will still be there, still hold any value you set it
to, and still be accessible in your query, but the user will not "see and
.... play around with it".

2. Instead of a public variable, use a private, module-level variable and a
public Property to return the value of it.

I'm not sure why "the report query can not use the combo box value ". If
the form is open, the query can certainly reference the value of the combo
box.
 
OK a couple of things that I was running into. First I could not get the
right syntax for the combo box in the query. Worked on it for a couple of
hours while searching here. finally found one post the said you could not
reference a combo box in a query. Don't remember the post. (sorry) So with
the text box. first I made the propert visible = NO. when I clicked on the
start button I would get the error Run-time error 2110: "Microsoft office
Access can't move the focus to the control. So I put this back to "YES".
When typing it in the form open event There is an "ISVisisble" but this does
nothing and when I set this to "False" , the text box still showed up on the
form. So I just left it for the time being.
(Please excuss me I am redoing all this a getting the errors the same way I
was before)
when I set the text value = to the combo box selected value in the start
button click event it came up with this error. Run-time error 2185: "You
can't reference a property or method for a control unless the control has the
focus." So I put a line above the line setting the text value in the start
button click event. "Me.Resource_Selected_Text.SetFocus" Now I can set the
text value even though the text box is still visible. I just shrink the text
box down and move it to a corner of the form. All the user can see is a
couple of lines on the form. This works for ver 2007.

Hope this helps some.


--
MNJoe


Dirk Goldgar said:
MNJoe said:
I have a report that opens a form, on the form is a combo box with a
selection of resources. when the user selects a resource and clicks the
start
button the form disappears and the report query uses the resource to
generate
the report. The report query can not use the combo box value so I declared
a
public variable and set it to the value of the selectioin in the combo
box. I
use that variable in the report query. This works for Version 2003 but in
version 2007 when I run it. A msg box pops up wanting the user to input
the
value into the query.

I know using public variables is not a real good idea (read that in a few
postings here) but, This was a little cleaner than creating a text box on
the
form (which the user could see and would play around with) then setting
the
value of the text box to the combo box. Do I have to change over to the
text
box to get this to work in 2007?

any help or suggestions greatly appreciated.

the code I use in the query is

[forms]![resource_Load_Form].[STRResource_Selected]
Click to expand...


I'd have to test and compare A2003 and A2007 to see what gives witht he
public variable, since I never use them in that way, but you could resolve
your problem with either of two workarounds:

1. Use a text box on the form, but make it invisible (set its Visible
property to No). It will still be there, still hold any value you set it
to, and still be accessible in your query, but the user will not "see and
... play around with it".

2. Instead of a public variable, use a private, module-level variable and a
public Property to return the value of it.

I'm not sure why "the report query can not use the combo box value ". If
the form is open, the query can certainly reference the value of the combo
box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Another quick thing I forgot. When the user clicks on the start button I set
the form property Visisble to "NO" and to bring up the report. Do you think
this is why the combo box value is not working in the query??
--
MNJoe


MNJoe said:
OK a couple of things that I was running into. First I could not get the
right syntax for the combo box in the query. Worked on it for a couple of
hours while searching here. finally found one post the said you could not
reference a combo box in a query. Don't remember the post. (sorry) So with
the text box. first I made the propert visible = NO. when I clicked on the
start button I would get the error Run-time error 2110: "Microsoft office
Access can't move the focus to the control. So I put this back to "YES".
When typing it in the form open event There is an "ISVisisble" but this does
nothing and when I set this to "False" , the text box still showed up on the
form. So I just left it for the time being.
(Please excuss me I am redoing all this a getting the errors the same way I
was before)
when I set the text value = to the combo box selected value in the start
button click event it came up with this error. Run-time error 2185: "You
can't reference a property or method for a control unless the control has the
focus." So I put a line above the line setting the text value in the start
button click event. "Me.Resource_Selected_Text.SetFocus" Now I can set the
text value even though the text box is still visible. I just shrink the text
box down and move it to a corner of the form. All the user can see is a
couple of lines on the form. This works for ver 2007.

Hope this helps some.


--
MNJoe


Dirk Goldgar said:
MNJoe said:
I have a report that opens a form, on the form is a combo box with a
selection of resources. when the user selects a resource and clicks the
start
button the form disappears and the report query uses the resource to
generate
the report. The report query can not use the combo box value so I declared
a
public variable and set it to the value of the selectioin in the combo
box. I
use that variable in the report query. This works for Version 2003 but in
version 2007 when I run it. A msg box pops up wanting the user to input
the
value into the query.

I know using public variables is not a real good idea (read that in a few
postings here) but, This was a little cleaner than creating a text box on
the
form (which the user could see and would play around with) then setting
the
value of the text box to the combo box. Do I have to change over to the
text
box to get this to work in 2007?

any help or suggestions greatly appreciated.

the code I use in the query is

[forms]![resource_Load_Form].[STRResource_Selected]
Click to expand...


I'd have to test and compare A2003 and A2007 to see what gives witht he
public variable, since I never use them in that way, but you could resolve
your problem with either of two workarounds:

1. Use a text box on the form, but make it invisible (set its Visible
property to No). It will still be there, still hold any value you set it
to, and still be accessible in your query, but the user will not "see and
... play around with it".

2. Instead of a public variable, use a private, module-level variable and a
public Property to return the value of it.

I'm not sure why "the report query can not use the combo box value ". If
the form is open, the query can certainly reference the value of the combo
box.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
Click to expand...
 
MNJoe said:
OK a couple of things that I was running into. First I could not get the
right syntax for the combo box in the query. Worked on it for a couple of
hours while searching here. finally found one post the said you could not
reference a combo box in a query. Don't remember the post. (sorry)
Click to expand...

It's worth forgetting, because it is total nonsense.
So with
the text box. first I made the propert visible = NO. when I clicked on the
start button I would get the error Run-time error 2110: "Microsoft office
Access can't move the focus to the control.
Click to expand...

There is no reason to set the focus to this control, if it exists only to
have its value set from code and read from a query. Are you under the
misapprehension that you have to set the focus to a control to set its
value? Maybe because you're doing this:

Me!MyControl.SetFocus
Me!MyControl.Text = something

?

That is a frequent mistake made by VB programmers coming to Access. In
Access, the Text property of a control is only used for very special
purposes. The Text property can only be set when the control has the focus,
*BUT YOU DON'T NORMALLY USE IT*. You use the Value property instead, which
is the default property of the control, so this:

Me!MyControl.Value = something

is the same as this:

Me!MyControl = something ' I use this -- it's simpler

You do not have to set the focus to a control to set its Value property.

So the problem you are trying to work around doesn't exist if you use the
control's Value property instead of the Text property, and don't try to set
the focus to the control.
 
MNJoe said:
Another quick thing I forgot. When the user clicks on the start button I
set
the form property Visisble to "NO" and to bring up the report. Do you
think
this is why the combo box value is not working in the query??
Click to expand...


No, there's no need for the form or a control on it to be visible in order
to be referred to in a query. Was your query trying to refer to the Text
property of the control? That would be a mistake, as I explained in my
other message.
 
Back
Top