Not wanting to get into any preference debate (I never went to MVP school, I rather wonder what it's like :-s) and I've used both variables, local tables and form stored values over time - I did just want to address a couple of technical points raised in this thread.
To comment briefly first on the concept of what's logical - well that's surely dependent to an extent on the developer (individual).
I'm not a huge proponent for developing to assist the "next" developer to work on a project (it's never happened so far - and if it did then... well, they should be able to follow what's going on anyway if they've their wits about them).
But I do what I feel is logical. I like grouping objects where reasonable.
So a form reference is an option - but could be wrapped in a function call in the same module which performs other persisted value functionality.
(No doubt some will say "then why not use variables instead" - again it's personal choice and technical belief... which I'll come to now).
The issues I wanted to address were mainly raised by Bob - so I'll quote him from time to time if I may.
>> I would suggest NOT using a DLookup everytime you need it as that adds uncessessary overhead to the program
Yes - addressing a variable in memory is quicker than a read from disk (though it's going to be cached most likely anyway).
But assuming you stash these local persisted settings into a local table, then the DLookups aren't hurting the network and will perform very quickly (the domain functions perform excellently on local tables - people give them a bad rap but they blow recordsets away locally).
>> PUBLIC VARIABLES can lose their values at times in the program. They are volatile, and a hidden form is not.
Well this is a common statement. It really applies to any variable - though obviously we expect procedure scope variables to reset. Proc, module, public - it's all the same.
But the whole issue is dependent.
If you release MDBs (ACCDBs) in a retail version of Access to your users then yes - you expose them to the potential of unhandled errors and then ending code (and this resets the variables - not the error).
Of course if you use the runtime then boom - unhandled error and your application crashes. (How good an incentive is that for error handling to noobs? ;-)
However IMO if you release open MDBs, then unhandled errors and reset variables are the least of your worries. users poking around modules and design scares the... well it fills me with dread. ;-)
Consequently MDEs (ACCDEs) are the predominant released application form.
Errors raised in an MDE will not break - and hence will not reset variables and objects. MDEs are essentially robust little robots marching on regardless.
>> It [a public variable] has to be called from a wrapper function (you can't reference it directly)
That's true - but then if you do so by force of habit with form references, then your queries which do so need to have that parameter evaluated explicitly when opening that query into a recordset. A standard procedure for opening recordset is an easy solution - but it's a requirement.
Form references in queries are a fantastic piece of Access functionality. The expression service in general is far more involved, active and helpful than many give it credit for. But it is very much Access functionality.
>> the wrapper function is called for every single record
Well this could happen - but it would be unusual.
If you have a variable holding the current username (let's say mstrUserName) and a procedure which retrieves this
Public Function fGetUserName As String
fGetUserName = mstrUserName
End Function
and that is referenced in a query
SELECT * FROM TableName WHERE CreatedBy = fGetUserName()
That's perfectly common. And calls that function precisely once.
Suppose that same function calls more than one piece of information.
Public Function fGetUserCredential(pstrCredentialName as String) As String
fGetUserCredential= mstrUserCol(pstrCredentialName)
End Function
and so we query
SELECT * FROM TableName WHERE CreatedBy = fGetUserCredential("UserName")
then that function is called by the query (by the expression service) precisely once.
Only if you're passing a parameter which could vary by row will the function be called for each row.
SELECT * FROM TableName WHERE CreatedBy = fGetUserCredential(SomeFieldName)
This is typical of heaping overhead on queries filtered by a calculated column and is best avoided (though sometimes necessary without tweaking normalisation ;-).
But in this case I don't see why a field dependent parameter would be required.
So, all in all - you make your own choice of preference.
There are nuances - but no right and wrong.
(Don't get me wrong - I believe there are cases where a development choice is right and another wrong - but I don't see this as one of them).
i.e. it's unworthy of making rigid statements about one way or the other.
Cheers!
