using a variable for a textbox recordsource

[Chucky]

Hi,

Access 2007

I have a form with a public variable called myPublicVariable. The form has
a textbox. I want to use that public variable as the controlsource for the
textbox. I believe it should work if the controlsource of the textbox is set
to "=[myPublicVariable]" but all I'm getting on the form is "#Name?".

The database was sent to us by someone else and it is working as designed
for them. I have tried it on three machines here and they all have the same
issue. Would a reference be the cause of this ?

I can get it to work using other methods but that would require editing a
lot of forms and code and I'd rather not have to do this.

Thanks

 

[Douglas J. Steele]

I don't understand how it could be working for anyone else, as Access
doesn't let you refer to variables like that.

Try creating a public function that returns the value of the variable, and
use the function as the ControlSource.

 

[Chucky]

Well that's what i thought too and I have never referenced a variable
directly for a textbox controlsource. BUT, since it worked for the developer
of the DB i researched it a bit more and found a few references where people
said it should work if the variable is declared as Public in the form.

Also, in the form's VB module, if I type in "Me.", Intellisense does bring
up the public variable as a property of the form. That makes me think that
it _might_ be referenced for the control source.

I don't understand how it could be working for anyone else, as Access
doesn't let you refer to variables like that.

Try creating a public function that returns the value of the variable, and
use the function as the ControlSource.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

Access 2007

I have a form with a public variable called myPublicVariable. The form
has
a textbox. I want to use that public variable as the controlsource for
the
textbox. I believe it should work if the controlsource of the textbox is
set
to "=[myPublicVariable]" but all I'm getting on the form is "#Name?".

The database was sent to us by someone else and it is working as designed
for them. I have tried it on three machines here and they all have the
same
issue. Would a reference be the cause of this ?

I can get it to work using other methods but that would require editing a
lot of forms and code and I'd rather not have to do this.

Thanks

 

[Douglas J. Steele]

Sounds as though it may have been set as a Property of the form, not simply
a variable. That's the only way I can think of that it would show up through
intellisense.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well that's what i thought too and I have never referenced a variable
directly for a textbox controlsource. BUT, since it worked for the
developer
of the DB i researched it a bit more and found a few references where
people
said it should work if the variable is declared as Public in the form.

Also, in the form's VB module, if I type in "Me.", Intellisense does bring
up the public variable as a property of the form. That makes me think
that
it _might_ be referenced for the control source.

I don't understand how it could be working for anyone else, as Access
doesn't let you refer to variables like that.

Try creating a public function that returns the value of the variable,
and
use the function as the ControlSource.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Hi,

Access 2007

I have a form with a public variable called myPublicVariable. The form
has
a textbox. I want to use that public variable as the controlsource for
the
textbox. I believe it should work if the controlsource of the textbox
is
set
to "=[myPublicVariable]" but all I'm getting on the form is "#Name?".

The database was sent to us by someone else and it is working as
designed
for them. I have tried it on three machines here and they all have the
same
issue. Would a reference be the cause of this ?

I can get it to work using other methods but that would require editing
a
lot of forms and code and I'd rather not have to do this.

Thanks

 

[Chucky]

As soon as a varialbe is declared Public it becomes available as a property
of the form.

Sounds as though it may have been set as a Property of the form, not simply
a variable. That's the only way I can think of that it would show up through
intellisense.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well that's what i thought too and I have never referenced a variable
directly for a textbox controlsource. BUT, since it worked for the
developer
of the DB i researched it a bit more and found a few references where
people
said it should work if the variable is declared as Public in the form.

Also, in the form's VB module, if I type in "Me.", Intellisense does bring
up the public variable as a property of the form. That makes me think
that
it _might_ be referenced for the control source.

I don't understand how it could be working for anyone else, as Access
doesn't let you refer to variables like that.

Try creating a public function that returns the value of the variable,
and
use the function as the ControlSource.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Access 2007

I have a form with a public variable called myPublicVariable. The form
has
a textbox. I want to use that public variable as the controlsource for
the
textbox. I believe it should work if the controlsource of the textbox
is
set
to "=[myPublicVariable]" but all I'm getting on the form is "#Name?".

The database was sent to us by someone else and it is working as
designed
for them. I have tried it on three machines here and they all have the
same
issue. Would a reference be the cause of this ?

I can get it to work using other methods but that would require editing
a
lot of forms and code and I'd rather not have to do this.

Thanks

 

[Mike Painter]

Is the entire form unbound and what does the controlsource for that textbox
show ?

 

[Chucky]

Yes the form is unbound. In design view, the controlsource is
"=[myPublicVariable]" (without the quotes). In Form View, "#Name?" shows up.

Is the entire form unbound and what does the controlsource for that textbox
show ?

Hi,

Access 2007

I have a form with a public variable called myPublicVariable. The
form has a textbox. I want to use that public variable as the
controlsource for the textbox. I believe it should work if the
controlsource of the textbox is set to "=[myPublicVariable]" but all
I'm getting on the form is "#Name?".

The database was sent to us by someone else and it is working as
designed for them. I have tried it on three machines here and they
all have the same issue. Would a reference be the cause of this ?

I can get it to work using other methods but that would require
editing a lot of forms and code and I'd rather not have to do this.

Thanks

 

[tina]

well, you're right on both counts - the property issue and the syntax. i ran
a quick test, setting the value of the public variable in the form's Open
event, and the unbound textbox control displayed the value just fine in Form
view. is your textbox control named the same as the variable? or do you have
another variable in the form, or in a standard module, named the same as the
one you're referencing in the textbox ControlSource?

hth

As soon as a varialbe is declared Public it becomes available as a property
of the form.

Sounds as though it may have been set as a Property of the form, not simply
a variable. That's the only way I can think of that it would show up through
intellisense.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Well that's what i thought too and I have never referenced a variable
directly for a textbox controlsource. BUT, since it worked for the
developer
of the DB i researched it a bit more and found a few references where
people
said it should work if the variable is declared as Public in the form.

Also, in the form's VB module, if I type in "Me.", Intellisense does bring
up the public variable as a property of the form. That makes me think
that
it _might_ be referenced for the control source.

:

I don't understand how it could be working for anyone else, as Access
doesn't let you refer to variables like that.

Try creating a public function that returns the value of the variable,
and
use the function as the ControlSource.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Access 2007

I have a form with a public variable called myPublicVariable. The form
has
a textbox. I want to use that public variable as the controlsource for
the
textbox. I believe it should work if the controlsource of the textbox
is
set
to "=[myPublicVariable]" but all I'm getting on the form is "#Name?".

The database was sent to us by someone else and it is working as
designed
for them. I have tried it on three machines here and they all have the
same
issue. Would a reference be the cause of this ?

I can get it to work using other methods but that would require editing
a
lot of forms and code and I'd rather not have to do this.

Thanks

 

[tina]

perhaps i should add that i ran the test in A97, and in an A2000 db which
was built and run in A2003. i don't know if the version you're using is
significant or not.

hth

well, you're right on both counts - the property issue and the syntax. i ran
a quick test, setting the value of the public variable in the form's Open
event, and the unbound textbox control displayed the value just fine in Form
view. is your textbox control named the same as the variable? or do you have
another variable in the form, or in a standard module, named the same as the
one you're referencing in the textbox ControlSource?

hth

As soon as a varialbe is declared Public it becomes available as a property
of the form.

The

form

controlsource

for

the
textbox. I believe it should work if the controlsource of the textbox
is
set
to "=[myPublicVariable]" but all I'm getting on the form is "#Name?".

The database was sent to us by someone else and it is working as
designed
for them. I have tried it on three machines here and they all

have

the

 

[Chucky]

the variable and the textbox have different names.

You say that your textbox is unbound. Mine is bound to the variable. In
other words, in design view, the controlsource property is
"=myPublicVariable" (without the quotes). This results in "#Name?" being
displayed.

If I leave it unbound and assign to controlsource property in code using
this syntax, it works. But this is something i don't want to do because i'm
using someone else DB and i'll be getting updates regularly.

me.mycontrol.controlsource = "='" & myPublicVariable & "'"

well, you're right on both counts - the property issue and the syntax. i ran
a quick test, setting the value of the public variable in the form's Open
event, and the unbound textbox control displayed the value just fine in Form
view. is your textbox control named the same as the variable? or do you have
another variable in the form, or in a standard module, named the same as the
one you're referencing in the textbox ControlSource?

hth

As soon as a varialbe is declared Public it becomes available as a property
of the form.

Sounds as though it may have been set as a Property of the form, not simply
a variable. That's the only way I can think of that it would show up through
intellisense.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Well that's what i thought too and I have never referenced a variable
directly for a textbox controlsource. BUT, since it worked for the
developer
of the DB i researched it a bit more and found a few references where
people
said it should work if the variable is declared as Public in the form.

Also, in the form's VB module, if I type in "Me.", Intellisense does bring
up the public variable as a property of the form. That makes me think
that
it _might_ be referenced for the control source.

:

I don't understand how it could be working for anyone else, as Access
doesn't let you refer to variables like that.

Try creating a public function that returns the value of the variable,
and
use the function as the ControlSource.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Access 2007

I have a form with a public variable called myPublicVariable. The form
has
a textbox. I want to use that public variable as the controlsource for
the
textbox. I believe it should work if the controlsource of the textbox
is
set
to "=[myPublicVariable]" but all I'm getting on the form is "#Name?".

The database was sent to us by someone else and it is working as
designed
for them. I have tried it on three machines here and they all have the
same
issue. Would a reference be the cause of this ?

I can get it to work using other methods but that would require editing
a
lot of forms and code and I'd rather not have to do this.

Thanks

 

[tina]

You say that your textbox is unbound. Mine is bound to the variable. In

other words, in design view, the controlsource property is
"=myPublicVariable" (without the quotes). This results in "#Name?" being
displayed.

yes, i understood what you meant. "bound" refers to the control being bound
to a field in the form's underlying RecordSource. a control with
ControlSource set to "=" any expression is a calculated control, by
definition unbound - that is, not bound to a field in the RecordSource.

so your textbox control has a different name than the variable, okay. did
you check for duplicate variable names *in the code* as well?

hth

the variable and the textbox have different names.

You say that your textbox is unbound. Mine is bound to the variable. In
other words, in design view, the controlsource property is
"=myPublicVariable" (without the quotes). This results in "#Name?" being
displayed.

If I leave it unbound and assign to controlsource property in code using
this syntax, it works. But this is something i don't want to do because i'm
using someone else DB and i'll be getting updates regularly.

me.mycontrol.controlsource = "='" & myPublicVariable & "'"

well, you're right on both counts - the property issue and the syntax. i ran
a quick test, setting the value of the public variable in the form's Open
event, and the unbound textbox control displayed the value just fine in Form
view. is your textbox control named the same as the variable? or do you have
another variable in the form, or in a standard module, named the same as the
one you're referencing in the textbox ControlSource?

hth

As soon as a varialbe is declared Public it becomes available as a property
of the form.

:

Sounds as though it may have been set as a Property of the form, not simply
a variable. That's the only way I can think of that it would show up through
intellisense.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Well that's what i thought too and I have never referenced a variable
directly for a textbox controlsource. BUT, since it worked for the
developer
of the DB i researched it a bit more and found a few references where
people
said it should work if the variable is declared as Public in the form.

Also, in the form's VB module, if I type in "Me.", Intellisense

does
bring

up the public variable as a property of the form. That makes me think
that
it _might_ be referenced for the control source.

:

I don't understand how it could be working for anyone else, as Access
doesn't let you refer to variables like that.

Try creating a public function that returns the value of the variable,
and
use the function as the ControlSource.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi,

Access 2007

I have a form with a public variable called myPublicVariable.

The
form

has
a textbox. I want to use that public variable as the

controlsource
for

the
textbox. I believe it should work if the controlsource of the textbox
is
set
to "=[myPublicVariable]" but all I'm getting on the form is "#Name?".

The database was sent to us by someone else and it is working as
designed
for them. I have tried it on three machines here and they all

have
the

same
issue. Would a reference be the cause of this ?

I can get it to work using other methods but that would require editing
a
lot of forms and code and I'd rather not have to do this.

Thanks

 

[Chucky]

no duplicates anywhere in the form or in the db.

 

[tina]

hmm, well, at this point i'm stumped. i was able to duplicate your "good"
results (and this was a learning experience for me, btw, since i never knew
you could refer to a public variable directly in the Property sheet in
Design view), but as to why you're getting the error on one form only...
it's possible the variable isn't initialized until after Access evaluates
the expression in the control, i guess. have you tried doing a requery
directly on the control, in the form's Current event?

Me!ControlName.Requery

and when is the value of the variable set? in my test, i set the variable in
the form's Open event. seems like you mgiht get a #Error in the control if
Access recognized the variable but found no value in it, rather than #Name?,
but maybe not. it's also possible that the form is corrupted, but if that's
the case you'd need to get your supplier to fix his/her database - otherwise
you'll just have to keep rebuilding the form every time you get a new copy
of the db.

sorry i'm not more help. maybe somebody else will have better ideas.

hth