Form Variable Scope

[gray]

HI

Quick question on variab;e scope within a form, If I declare a variable in the delararations section:-

Option Compare Database
Public Frm_RstADO As ADODB.Recordset
Public Frm_CnnADO As ADODB.Connection

Then set the variable:-

Set Frm_CnnADO = CurrentProject.AccessConnection
Set Frm_RstADO = New ADODB.Recordset

Use it and when finished with it set it to nothing

Set Frm_RstADO = Nothing
Set Frm_CnnADO = Nothing

Is is still avaiable later on in the form by just resetting it again with:-

Set Frm_CnnADO = CurrentProject.AccessConnection
Set Frm_RstADO = New ADODB.Recordset

??

Thnx

 

[Galaxiom]

Yes. I would have thought you could test this faster than posting a question so I am assuming you are having a problem. You would also have to Open it again as it will behave like an entirely new instance.

BTW. Do you really need to make it Public? This allows it to be addressed from outside of the form as a Property of the Form.

I would also recommend you use the Option Explicit declaration if you are not already.

 

[gray]

That's correct, I was having a problem... Access was actually crashing at the point they were being assigned.... and I began to wonder if these variables needed be re-dimmed somehow.

They are sited on a subform and so are public in order that they can be managed from the mainform too.

If I ever get my project finished, I'll use the Explicit option during final testing.

Thanks for the advice.

 

[tehNellie]

If I ever get my project finished, I'll use the Explicit option during final testing.

I think you greatly improve your chances of ever getting your project finished if you add Option Explicit sooner rather than later

You don't need to re-dim global variables, but you might want to check that they're not NOTHING before you try to manipulate them.

I think having a global connection object is fair enough and I'd be inclined to put it at a "high" a level in my application as possible rather than buried in a sub form, but if you're going to be closing and disposing of the Recordset option over here and then doing something different with it there then I'd opine that makes it a candidate for a more localised declaration.
Have an explict rsSalesSummary in the method that's using that data and a separate rsOrderSummary (or whatever you're doing) in the method over there instead.

 

[gray]

HI

Thanks for the reply...

As you probably guessed from their names I use them for setting up the recordsets of my forms. At one time I assumed they had to be there for the life of the form.. and I killed them off in the form unload event... I later realised that wasn't necessary but I quite liked the fact they made me police my naming standards... I have a number of them defined in each form and subform:-

Public Frm_RstADO As ADODB.Recordset
Public Frm_CnnADO As ADODB.Connection

Private Prc_RstADO_BMrk As ADODB.Recordset
Private Prc_RstADO_ReadA As ADODB.Recordset
Private Prc_RstADO_ReadB As ADODB.Recordset
Private Prc_RstADO_WriteA As ADODB.Recordset
Private Prc_RstADO_WriteB As ADODB.Recordset

I wonder if doing dim'ing like this uses a ton of resources though? I try to Nothing' them in each Sub/Function but then still check them in the unload... maybe I've got a touch of OCD?

Thanks

 

[tehNellie]

Personally I think disposing of an object when you're done with it is pretty good practice, especially with global objects like recordsets.

There are valid reasons to have form or even project level variables and objects, but if you're disposing of it in one method and recreating it again elsewhere, especially as something different, I'd question the need to have its scope that wide.

Public scope, global, variables can seem like a good idea but in practice they can be incredibly difficult to manage especially when you have multiple different methods all interacting with them (and can't remember exactly where you declared it).

Nowadays I don't think that having a ton of variables all set to nothing places that much overhead on things, leaving them lying around full of data though is a different matter.

As for the naming convention I'm a great believer in giving variables meaningful names, for the sake of two seconds typing to turn

Dim x as integer

into

Dim intCounter as integer

Makes life so much easier when you come back to the code in 6 months time and try to figure out what x is supposed to be doing (ok, not a great example, but the principle stands I feel).

 

[Galaxiom]

At one time I assumed they had to be there for the life of the form.. and I killed them off in the form unload event...

I later realised that wasn't necessary but I quite liked the fact they made me police my naming standards... I have a number of them defined in each form and subform:-

Public Frm_RstADO As ADODB.Recordset
Public Frm_CnnADO As ADODB.Connection

Private Prc_RstADO_BMrk As ADODB.Recordset
Private Prc_RstADO_ReadA As ADODB.Recordset
Private Prc_RstADO_ReadB As ADODB.Recordset
Private Prc_RstADO_WriteA As ADODB.Recordset
Private Prc_RstADO_WriteB As ADODB.Recordset

Personally I find that style of naming system very clumsy. It is a lot of extra typing every time they are referred to. I especially avoid underscores firstly because they break up the name of an entity unnecessarily and secondly because the underscore has a meanng in event procedures.

I am not a fan of Hungarian Notation. Many experienced developers consider it an obsolete practice. It was originally invented for VB Script where variables were not able to explicitly typed. Modern variants of Visual Basic permit strong typing and hence the type of the variable is able to be determined very easily from the context menu. It is especially pointless in Visual Studio where the information is poping up as you hover.

The prefix dominates the identity of the variable especially in the extreme version you have adopted.

I also don't see the point of having separate read and read/write versions of the same recordset.

I wonder if doing dim'ing like this uses a ton of resources though? I try to Nothing' them in each Sub/Function but then still check them in the unload

It is a good practice to explicity Close connections rather than simply setting to Nothing and allowing the server end to time out. However the setting of object variables to Nothing just before they go out of scope is the subject of much debate.

In VB.NET developers are advised not to do so because the reference in the command actually forces the variable to remain in memory until the Set to Nothing line is reached. In VB.NET the garbage collection automatically removes the object from memory on the first pass that occurs after the last reference.

I would not be surprised if the same is true in VBA and the advice to Set to Nothing is really just a carried over habit over from the primitive VB Script.

 

[Galaxiom]

As for the naming convention I'm a great believer in giving variables meaningful names, for the sake of two seconds typing to turn

Dim x as integer

into

Dim intCounter as integer

Two seconds each time you refer to the variable.

I reserve single letters as integer counters and group them in patterns.
For example the following is easily read and understood:

For x = y To z
whatever = MyArray(x)

Compare that to:

For intCounter = intStartCount To intEndCount
whatever = MyArray(intCounter)

Does it really tell the reader anything besides the obvious or make it easier to read?

 

[tehNellie]

Two seconds each time you refer to the variable.

I reserve single letters as integer counters and group them in patterns.
For example the following is easily read and understood:

For x = y To z
whatever = MyArray(x)

Compare that to:

For intCounter = intStartCount To intEndCount
whatever = MyArray(intCounter)

Does it really tell the reader anything besides the obvious or make it easier to read?

There's some context and consideration to be taken and I did concede that throw away counters weren't the best example to use.

I see a lot of

public sub SomethingThatDoesntReallyDescribeWhatThisDoes(tr as string)
 dim x as integer (more likely variant if anything, but I digress)
 dim s as string
 dim r as recordset

 [300 lines of code with no comments or indentation]
 if tr = "SomeHardCodedValuethatMightBeInChineseForAllTheSenseItMakes"  Then  
[another 300 lines of code with no comments or indentation]
elseif s = "32"
[another 300 lines of code, a few loops, a massive great big SQL query and several places 
where the error handling is turned off in silly places rather than fix the bugs]
endif
end sub

So it does drive me a little round the twist that someone will spend weeks writing 1,000 lines of bad code but scrimp the couple of minutes extra it would have taken to turn x into lngCustomerID.

 

[Galaxiom]

Regarding indenting I totally agree. It is the most important factor for readability.

Sensible use of commenting is also important. However I see over-commenting as just as bad as under-commenting.

' rsOpen Recordset
Set rsSomeIncediblyVerboseDescrition = CurrentDb.OpenRecordset("blah")
'Move to last record to allow a count
rsSomeIncediblyVerboseDescrition.MoveLast
'get count of records
 lngNumberOfRecords = rsSomeIncediblyVerboseDescrition.RecordCount

Personally I prefer a short general description comment at the beginning of a procedure and occasionally a loop or tricky line than the clutter of stating the bleeding obvious. I totally disagee with, as one poster put it, "Code can never have too many comments".

Regarding the Hungarian Notation I really don't see the point of being reminded of the type every time a variable or object is mentioned. Especially for objects the type is just one of the properties and no more important than many others.

 

[tehNellie]

I know Hungarian notation is out of fashion but it's what I'm used to and I've no real intention of changing that , in VBA at least. I think consistency of approach is just as important. if strSQL or sSQL helps you then use it but use it everywhere.

Agree on the comments, you could turn your comment on the .movelast into a constructive one though, it's a common mistake people make taking counts from a recordset that they either omit it, or when they encounter it they remove it thinking it's redundant.

Code should be reasonably self documenting, I don't need to know that you're [generic 'you'] opening a recordset, I can see that from the db.openrecordset; but I am curious to know why you're dividing that variable by the square of another, that looks like something I should know about.

 

[Galaxiom]

Code should be reasonably self documenting

Definitely. That is why the cariables should be sensibley named too. I just don't get into Hungarian Notation. I do use it posting snippets here because it does tell the reader the variable type without having to include a Dim line for it.

One place I do use it is strSQL. It is meaningful as an SQL string.

but I am curious to know why you're dividing that variable by the square of another, that looks like something I should know about.

I had to think about that. It isn't what you think. It is the For Loop
It is a variant of the Do While and Do Until family of loops. It is a very tidy solution.

For x = y To z
   do stuff
Next

The counter is initialises at y and the loop terminates after the z value is processed. The increment of the counter is automatic at the Next.

It also has an optional Step setting that defines the increment.

For x = y To z Step w
   Do Stuff
Nest