How to sse value from form as Globall variable

[matt beamish]

How to use value from form as Global/public variable

I want to use several values entered in form controls as variables within multiple subs triggered by further form edits.
I do not want to define the variables in each sub as this will bloat my code, but I am not being successful in declaring my variables outside of an individual sub, and it is the 'Form!' reference that is throwing it I think.

Here are my variables:

Dim limit0txt, limit1txt, limit2txt, limit3txt As Integer
limit0txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit0
limit1txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit1
limit2txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit2
limit3txt = Forms!F_Samples_CF_FF_FLot!SF_EnviroCountLimitscntrl.Form!Limit3

If I put these at the top of a module I get an "invalid outside procedure" error message.
If I put these in their own module as 'Public' I get the same error.
Any advice on how within a forms code module I can make these variables available to as many events as I like?
thanks

 

[CJ_London]

well first, only limit3txt is declared as an integer, all the others are variants. Should be

Dim limit0txt as integer, limit1txt as integer....

or

Dim limit0txt as integer
Dim limit1txt as integer
...
...

it may not matter, but just so you are aware.

I guess you are talking about public variables which can be used by code in any module.

If that is the case, in a module put

Public limit0txt as integer
Public limit1txt as integer
...
...

then to assign these public variables a value you need to use a sub or function at the appropriate 'place' where the values to be assigned are known - I would suggest perhaps in the form used by this subform control - SF_EnviroCountLimitscntrl before update event or perhaps the current event, or both. The code would be

limit0txt = Limit0
limit1txt = Limit1
limit2txt = Limit2
limit3txt = Limit3

Other points

- if these values are held in a table, you could just use dlookup to get them
- your naming conventions could do with some work - calling an integer value limit0txt implies it is a text value
- are you aware that you can pass values as parameter - including whole forms, reports and recordsets

 

[matt beamish]

Thankyou for your reply.

1, My conventions - yes I see I am sloppy - I guess I am using 'txt to differentiate the variable from the datasource

2, re:

then to assign these public variables a value you need to use a sub or function at the appropriate 'place' where the values to be assigned are known

Where/how would I assign the variable - what would this look like with the refernece to my form control?

3, Can I use DLookup values in the event code?

Here is my event code

If Me.ActiveControl <= limit0txt Then Me.Bone = 0
If Me.ActiveControl >= limit1txt And Me.ActiveControl < limit2txt Then Me.Bone = 1
If Me.ActiveControl >= limit2txt And Me.ActiveControl < limit3txt Then Me.Bone = 2
If Me.ActiveControl >= limit3txt Then Me.Bone = 3

 

[CJ_London]

Where/how would I assign the variable - what would this look like with the refernece to my form control?

answered here

I would suggest perhaps in the form used by this subform control - SF_EnviroCountLimitscntrl before update event or perhaps the current event, or both. The code would be

limit0txt = Limit0
limit1txt = Limit1
limit2txt = Limit2
limit3txt = Limit3

not sure if activecontrol is right, but insufficient info determine - activecontrol is the one that currently has the focus. So if you click a button to run this code, it is the button that has the focus

But assuming it is right and the values you want to lookup are in a table with only one record then

If Me.ActiveControl <= Dlookup("Limit0","myTable") Then Me.Bone = 0

but a better way to do it would be

dim rst as dao.recordset
set rst=currentdb.openrecordset("SELECT Switch(" & me.activecontrol & "<=Limit0,0," & me.activecontrol & "<Limit1,1," & me.activecontrol & "<Limit2,2," & me.activecontrol & ">=Limit3,3) As Bone FROM myTable")
Bone=rst!Bone
set rst=nothing

This assumes you have a table called myTable with 4 fields called Limit0..Limit3 - change names to suit

 

[matt beamish]

Thanks - active control is working fine - the relevant control has the focus and has just been edited to trigger the update.
Now that code is very tidy thanks - I will give it go. Yes I have a table with a single row of values as assumed.
thanks again
MAtt

 

[matt beamish]

although

("SELECT Switch(" & me.activecontrol & "<=Limit0,0," & me.activecontrol & "<Limit1,1," & me.activecontrol & "<Limit2,2," & me.activecontrol & ">=Limit3,3) As Bone FROM myTable")

is not the same as

If Me.ActiveControl <= limit0txt Then Me.Bone = 0
If Me.ActiveControl >= limit1txt And Me.ActiveControl < limit2txt Then Me.Bone = 1
If Me.ActiveControl >= limit2txt And Me.ActiveControl < limit3txt Then Me.Bone = 2
If Me.ActiveControl >= limit3txt Then Me.Bone = 3

as they are single comparisons, and not "betweens"?

 

[CJ_London]

Ah - just realised you have a 'gap' between limit0 and limit1 where Bone won't be assigned anything, so you do need to modify the switch function slightly - you can use a between since this is a sql statement

....," & me.activecontrol & " Between Limit1 AND Limit2,1......