Public variable problem (1 Viewer)

[lana]

Dear All,

I wrote a program in Access 2003 and it works perfectly on my computer but when I run it in another one , there is a problem.

The problem is : on a form Ioad I calculate a value and I declared it as public. I need to use this value in my form but it does not recognize it. As if public statement does not work.

Is this from the Windows?

Again, it works on my computer but I have a problem elsewhere.

I would appreciate any help.

Thanks
LANA

 

[pr2-eugin]

Not entirely sure ! show he declaration of the variable. Where have you declared this variable? In a common module? Or behind a Form?

 

[lana]

I have declared it on the form. The interesting part is, it works without any problem in my system but it does not work on the other.

 

[pr2-eugin]

I have declared it on the form.

Public variables that are declared in Forms, will only last/live as long as the form is open. If the form is closed the variable is removed from the memory. If you wish the variable all through the application declare them in a Standard module.

The interesting part is, it works without any problem in my system but it does not work on the other.

Are you sure you have not made any changes? Maybe the old one has the form kept open all through (probably hidden) but the new one closes it or something?

 

[lana]

Thanks for the reply.

Yes I need the variable on the same form.

I forgot to mention my windows is 7 but access is 2003 , in which I wrote the program and it works fine. I checked the program on a system using XP with 2007 and XP with 2010, also on a system with 7 and 2010. In all of them the variable disappears except in my system.
The variable I am using is for a Text box Default value. Without any error msg, on the text box I get ?#Name.
Thanks again
LANA

 

[Phivosz]

I don't know if this is good programming practice but rather than using public variables I prefer to have a table with two fields (Variable_Name, Variable_Value). I have some code that writes the Name and Value to the table and then this can be retrieved whenever needed (even after closing and re-opening the database).

 

[Galaxiom]

Public or Private, the scope VBA variables does not include Access objects so putting a VBA variable into the Default property of a form shouldn't work at all.

A variable can be passed to a control on the form using a Private or Public function in the form's module or by a Public function in a Standard Module.

I don't have Access 2003 to test it but I am surprised that it would work there.

 

[ChrisO]

Lana.

I think you still have this problem because you can not describe the problem.

I doubt if anyone could replicate the fault from the information you have supplied.

Can you please post accurate information?

Chris.

 

[Galaxiom]

Chris. Can you check in Access 2003 if the scope of a variable in a form's module includes the form object itself?

While it certainly doesn't in 2010, it iwould be a surprise to me is that what Lana describes could work in any version of Access.

 

[MarkK]

In older versions of Access, public user defined properties and variables on a form could be used as a control's ControlSource using syntax like . . .

=[MyPublicProperty]

. . . where a form might expose a . . .

Public Property Get MyPublicProperty As String
   MyPublicProperty = "Hello World!"
End Property

If you want to test it, create a form, and put four new textboxes on it, and copy in this code, and open it . . .

Public MyText0 As String

Property Get MyText2() As String
    MyText2 = Me.MyText0
End Property

Function MyText4() As String
    MyText4 = Me.MyText0
End Function

Private Sub Form_Load()
    Me.MyText0 = "Hello world!"
    
    Me.Text0.ControlSource = "=[MyText0]"
    Me.Text2.ControlSource = "=[MyText2]"
    Me.Text4.ControlSource = "=MyText4()"
    Me.Text6.ControlSource = "=[Name]"
End Sub

Note that in later versions of Access, Text4 and Text6 will work, but not the first two. In older versions of Access they all would have worked.

 

[Galaxiom]

Well Lagbolt has the answer and it is good to know.

Definitely a disruption to backwards compatibility from 2007 on. But not for me because I never expected it to work in the first place.

So the solution is to make it s property or use a function.

 

[MarkK]

No, the user defined property fails now too. You have to use a function--including removing the square brackets and adding "()" at the end--or use an unbound control and assign the value explicitly in code.

 

[ChrisO]

I still think we are wasting our time until Lana can specify the problem.

At this point she has not posted any code at all so we really are guessing at the requirement.

What we really need is:-
1. This is how the variable is defined.
2. This is how it is initialised.
3. This is how it is being used.

 

[lana]

Many many thanks for taking the time and replying.

When the form is opened, I calculate a variable on the form Load procedure using a defined function, Like this :

A = Function(???)

Variable A is declared in the form's declaration section : Public A

On the form, I have a Text box which I need it to display variable A as a default value. So, I put [A] in the Text box's default value property.

When the form is opened then , in the text box value of A is displayed as a default value.

Cheers

LANA

 

[ChrisO]

The attachment is using Windows 7 and Access 2003. It works as expected.

Are you saying that it does not work on other systems?

Chris.

 

[lana]

Thanks , I do exactly as you did in your example.

It works perfectly on my system.

On other systems, variable becomes null and in the text box I get #Name?

Cheers
LANA

 

[nanscombe]

Using ChrisO's database Windows 7 + Access 2010

(Using the Property Sheet)
Default Value [A] (Out of the box) #Name
=[A] #Name

Changing tack and using VBA.
Me.Text0.DefaultValue = A #Name

I noticed in the Access help for DefaultValue the following syntax.

Forms!frmInvoice!PaymentMethod.DefaultValue = """Cash"""

Me.Text0.DefaultValue = """" & A & """" ABC

Going back to the original way of doing things.
(Using the Property Sheet)

Default Value ="""" & [A] & """" ABC

Bingo, for Access 2010 +Windows 7

 

[lana]

& """" ABC

What do you mean by this? This does not work on the default value. It returns #ERROR.

If the variable name is A then how this must be written?

 

[nanscombe]

I'm currently trying to repeat the experiment on an old laptop with Access 2003, it's taking a while. I'll post the results when I've finished.

 

[nanscombe]

Repeating Using ChrisO's database Windows XP + Access 2003

(Using the Property Sheet)
Default Value [A] (Out of the box) ABC
=[A] ABC

Changing tack and using VBA.
Me.Text0.DefaultValue = A #Name

Me.Text0.DefaultValue = """" & A & """" ABC

Going back to the original way of doing things.
(Using the Property Sheet)

Default Value ="""" & [A] & """" "ABC"

Oh bother, the results seem to be different for Access 2003 and Access 2010.


Access 2003 (Property Sheet):
Default Value =[A] ABC
Default Value ="""" & [A] & """" "ABC"

Access 2010 (Property Sheet):
Default Value =[A] #Name
Default Value ="""" & [A] & """" ABC

However using VBA code

Access 2003: Me.Text0.DefaultValue = """" & A & """" ABC
Access 2010: Me.Text0.DefaultValue = """" & A & """" ABC