Variables instead of controls? (1 Viewer)

When I first started writing VBA back in the '90s, some book I read told me that it was good practice never to use the information in controls directly, but use it through variables instead


So, for example, whilst you can do : RS1!fldName = txtName (etc)

You should do:

strName = txtName (etc)
then
RS1!fldName = strName (etc)

This I have always religiously done - but I can no longer really remember why?

Does everyone do this? And can anyone remember why?

(I know it's easier, for example, to test the information whilst you're filling the variables than during the middle of a recordset update - but is there a more fundamental reason?)

Hi George. I can't speak to the original reason given by the book you read, but one possible reason for using variables is to keep the code "portable," I suppose. For example, let's say you have a few lines of code using the same control's value, then if you later change the design of your form and maybe change the name of the control or simply decided to use a different one, then you'll have to update the code in a bunch of places. Whereas, if you used a variable, then you just simply change the code assigning the value to the variable.

It is not a practice I've ever followed though I have seen it taught - it may depend on the language. Back then, memory management was much more important so it might have been something to do with that?

I learned Pascal first and there the mantra was to pass variables/objects as parameters to subs and functions rather than use global variables - which is something I still do.

I don't follow it religiously either. I'd say my rule of thumb is to use a variable if I'm going to need the value more than once, use the control directly if just once.

I'd say my rule of thumb is to use a variable if I'm going to need the value more than once, use the control directly if just once.

Click to expand...

I agree with PBaldy, that is good coding practice.

it was good practice never to use the information in controls directly

Click to expand...

The original guidance of "always use variables" is silly in my opinion. Kind of discounts OOP. I dislike most of these "wear belts and suspenders" just in case some exceptional case happens. In this example I cannot even think of the weird case. If you are trying to cast the variant to a strong type then there are ways to handle that. This is like you "always need to set objects to nothing" when in fact you rarely ever need to. But instead of understanding those cases when it makes sense, people find it easier just to say "always" do this. This is also like a recent thread suggesting never to use the Parent property. In fact there are cases where the Parent of a control may not be what you naturally expect, but I would say it is better to understand those cases then discount its use.

MajP said:

This is like you "always need to set objects to nothing" when in fact you rarely ever need to.

Click to expand...

Whaaaaaaat?????

I've only just got myself into the habit of doing that... :banghead:

George-Bowyer said:

Whaaaaaaat?????

I've only just got myself into the habit of doing that... :banghead:

Click to expand...

Don't worry it's a good habit!



Sent from my SM-G925F using Tapatalk

it's a good habit!

Click to expand...

I see people say that all the time, but never any justification for why writing unnecessary code that is unlikely to really do anything is a good habit. It does not hurt so nothing wrong, but I try to avoid unnecessary code. I set objects to nothing about .5% of the time and I do it when there is a reason. I do not do it the other 99.5% of the time just to be safe.
Here is a good discussion on why people may think this overgeneralized practice makes sense
https://blogs.msdn.microsoft.com/er...n-are-you-required-to-set-objects-to-nothing/

The other one I picked up at about the same time is that you should never bind a form directly to a table. You should always bind a form to a query based on the table, even if the query has no criteria.

Any justification in that one?

Yes.
Using a query or SQL statement allows you to select just the fields or records needed and to provide a sort order. Restricting what is included will make it load faster.

I would also recommend tidying up all objects after use though not all developers do so.

I agree with Colin on that one. just using a table as a recordsource (or rowsource to combo/listbox) is the best way to slow your app down if it contains any significant amounts of data. Only time I can think of when it might be OK to use a table is if the form is set to Data Entry - I don't think that pulls all the data through, but could be wrong.

I'll admit to violating that one. The guy who taught me used forms bound to tables, so that's how I got started. I normally open in data entry mode or with a wherecondition though, so I'm not pulling whole tables over the network. I have forms bound to tables with several million records and they open instantly, so I never felt the need to change. I am admittedly lazy though.

I have forms bound to tables with several million records and they open instantly

Click to expand...

bet if you did a filter or sort when the form opened it will take some time since it can't complete the action until the entire recordset is loaded. The form will normally display once there is sufficient information to display - a single form or a screenful of rows for continuous/datasheet

CJ_London said:

bet if you did a filter or sort when the form opened it will take some time since it can't complete the action until the entire recordset is loaded. The form will normally display once there is sufficient information to display - a single form or a screenful of rows for continuous/datasheet

Click to expand...

That would be why I stopped binding directly to tables for anything other than data entry once I started working here. Having to put a sort order on a 1.6 billion record table with 150 fields (1.8 billion now) made form loading a bit slow.

CJ_London said:

bet if you did a filter or sort when the form opened it will take some time since it can't complete the action until the entire recordset is loaded. The form will normally display once there is sufficient information to display - a single form or a screenful of rows for continuous/datasheet

Click to expand...

You miss that I use a wherecondition to open forms like this, so it's not loading all records. The form is also sorted, and users work with it immediately.

Regarding binding a form to a table vs. query...

I cannot tell you why it happened, but this is a true story. I had a really complex front-end and a highly-normalized back-end. The FE contained the "working forms" but it also contained "maintenance forms" that allowed me to tweak entries in a given linked table in some degree of isolation. When I created the maintenance form bound to a table, every time I did opened that form, it asked me where to find the table. I found on this forum, perhaps in the range of seven to ten years ago, a tip that suggested I create a single-table query that had every field in the table and use THAT to bind to the maintenance forms. Lo and behold, the moment I did that, the forms shut up about finding the tables. In essence, I could open the linked table from the FE but the form that was bound to it could not. But if I built that single-table query, the form had no issues. Just to make it more organized, I made sure that the query had an ORDER BY on the primary key, just in case - but that was done after the fact.

As to that "always set objects to nothing" advice...

While I am a proponent of keeping your cleanup code orderly and thorough, there are times when this action is not needed. If the object is local to a subroutine, it might be adequate to simply assure that you close the object (assuming it is that type of object). Setting it to "NOTHING" isn't an issue for objects created in subroutine-level code. For objects that were passed in, I believe it is the case that all such passages are forced to "BY REF" and therefore, you need to consider whether the original caller still needs the thing that was passed in. I usually take the approach that it DOES need it, and therefore do not close or set to nothing. That isn't a hard and fast rule, but generally if it is not originally yours and you didn't create it, I would not attempt to close it unless that is the purpose of the function or subroutine that you called. And I would CERTAINLY NOT set it to nothing unless object disposal was the purpose of the called code.

There is a big distinction to be had in closing vs. setting to nothing. It is always a good idea to close what you opened. Setting some object to nothing makes sense if you are using a global variable and your app stays open for a long time and you constantly create and release objects through the same variable. Then, a more thorough cleanup is in order. Otherwise, you should know that exiting from a subroutine wipes out all of the local variables including the object variables. Also, if your app is fast-running and closes quickly, then process rundown will wipe memory for you.

Frothingslosh said:

That would be why I stopped binding directly to tables for anything other than data entry once I started working here. Having to put a sort order on a 1.6 billion record table with 150 fields (1.8 billion now) made form loading a bit slow.

Click to expand...

Hmm. This raises another unrelated issue.

There appears to be a perception amongst potential business clients to whom I have spoken that Access is a bit "low spec".

"Oh, no, our needs are too big/complicated for an Access Database, we need a 'proper' database in SQL Server".

I suspect, however, that none of them are dealing with 1.8 Billion records...

I've always rather accepted this "Access is only for small scale" theory, largely because I have been told it by people I thought were in the know.

Am I selling myself too short...

No, we use a SQL Server back end. We have tables that are in the terabyte size range, and Access files corrupt and die once you pass 2 Gb. Also, security is a joke with Access backends, so if you need to keep your data secure, you NEED to go with other solutions for the database itself.

Your clients may be right, or they may be operating under the assumption that Access is 'for kids', as it were. The answer lies in whether or not they have security issues they need to deal with, if they're using a WAN to link multiple campuses, and just how much data storage they need.

My company has to deal with all of the issues (it's health insurance), so we have to use SQL server for the database itself. Doesn't mean we don't still use Access to build the front ends, though.

It tends to be that no matter how small they are, they think that Access is for smaller.

Most of the people I have been talking to have been talking about thousands of records, tens of thousands at most - so I would have thought that scale would have been a problem.

I'm working on learning Access at the moment. Talking on SQL Server as well may be a step too far...

The basics aren't too hard, but a full SQL Server install requires them to keep a DBA on-hand to handle the maintenance. A smaller solution is available with SQL Server Express, which is free but limited to 10 Gb databases.

You can learn the basics of using SQL server fairly easily by jumping in and doing it, if you want, but be prepared to do a lot of searching on Stack Overflow.

And just so you know, even the people helping you here still learn new things about Access from time to time.