Form Design Methodology

[ShredDude]

Newbie here... coming from a world of creating UserForms for Excel.

While recently starting to tackle Access, I've created some initial forms and learned about Bound vs. Unbound columns. I've created a significant form bound to a Table. Several Text Boxes on Form need to contain values that are derivatives of the values in various fields on the Table, so I wrote VBA Macros to derive these values.

Looking back, it seems this might not be the best way.

I've now learned more about queries, and the ability to create these derived fields within the Result table of the Query.

As a matter of practice, is there a "best" way to design Forms?

It seems my initial attempt would be less efficient than using Bound columns to a Query that contains the values for the derived fields. The query would run once, doing the calculations, providing a source for the form's fields, versus the Form's Events needing to fire off and recalc the values needed each time the record changes on the form.

Is a good method to employ thus as follows:

1.) Design Layout of Form
2,) Create Query to Support Fields needed on Form
3.) Bind Form to Query, and Form's controls to Query fields

Further, is there a prevailing opinion on using VBA Functions/Subs versus Access' Macro builder to, for example, calculate the value for a Query field?

Any advice would be much appreciated.

Thanks,

Shred

 

[Instance]

In my opinion, it kind of depends, but you don't need to use bound fields for query-calculated values, anway. If you have SomeValue and SomePct, you can set a field's data source to "=[SomeValue] * [SomePct]" and it will show you the calculated value. Or you can do it in a query and use the query as the form's data source. In various situations I would likely prefer one or the other, but if you're only going that deep, either is perfectly valid.

If you're looking to do layers of calculations, you'll almost always want the first step(s) to be done in the query, to simplify form design. I try to keep both about equally complex so they're about the same difficulty level to debug. Otherwise, you get a strange problem in the one that's more complex and spend forever trying to track it down.

*Suggestion: If you do that type of thing, though, I would suggest setting both 'Enabled' to No, and 'Locked' to Yes. The reason is that it will still look editable, and you can highlight and copy from the field, but you can't change the data therein. If you don't understand, try various combinations of values for Enabled and Locked to see what looks best for your application.

 

[Banana]

I personally use nothing but queries as recordsource for tables. A bit of overkill, as I'm sure some don't object to binding the form to table in certain cases. One reason for this is I usually end up providing some kind of criteria to filter the recordset, keeping it small as possible. Nobody can work with hundred or even thousands of rows, let alone millions at once.

Furthermore, if it can be done in SQL, I'd do it in SQL. Generally speaking, it's more effective in SQL (e.g. Jet's native language) than doing same thing in VBA. Not to say everything should be done with queries, as I'm sure there are cases where a calculated control makes more sense than doing it in query. One good example would be where the calculation may reference different data source, which would just complicate the query with yet another join. In this case, simply binding the control with the calculation is more effective.

Finally, I avoid macros like plague. I'm sure this is based on an irrational prejudice (I got burned pretty bad on macros when I was starting out), but whatever macros can do, VBA can do and do it better. So I think VBA is by far a better investment of your development efforts and time, though macros provide faster and easier but when time comes to implement some level of sophisticated or even just simple error handling, you're SOL.

HTH.

 

[dkinley]

Agreed ....

Finally, I avoid macros like plague. I'm sure this is based on an irrational prejudice (I got burned pretty bad on macros when I was starting out), but whatever macros can do, VBA can do and do it better. So I think VBA is by far a better investment of your development efforts and time ...

I can't say I ever got burned by macros, I just never understood them.

I like my code where I can read it and it is presented plainly in front of me (of course, if I understood macros I might be able to read them). I think VBA gives me more flexibility (unproven) but I can quickly scan the code for LOGIC, writing, troubleshooting, etc ...

There was a discussion on this board once where it was said heavy programmers are strictly VBA although there was a caveat that some programmers used macros for simple functions because of the ease of implementation.

-dK

 

[Instance]

Well, you can call VBA functions in your queries, ala:

MyField: IIF(Isnull([Table2].[PK],ChildFound(),"")

But you're going to be running it on any record the query finds, I believe. Certainly on any record where you run a condition on that calculated field.

This whole discussion is getting deeper than the original post was going for, I think, however. If you see slow-downs, you'll have to start learning how to evaluate your code, Forms, Queries and Reports for where you're losing time and how you can speed it up.

I always look for a balance between performance, ease of debugging, and functionality(rarely a question in Access -- it's a versatile beast!)

 

[Banana]

A cavaet.

Some time you really don't need VBA functions. For exacmple IsNull() is a VBA function but in Jet, it should be "SELECT foo FROM bar WHERE foo IS NOT NULL;" or "SELECT foo FROM bar WHERE foo IS NULL;".

Likewise, Iif() is actually different in VBA and Jet. To prove this, try this:

Jet's Iif()

SELECT Iif(1=0, 1/0, 0);

VBA's Iif() (In immediate window)

?Iif(1=0,1/0,0)

Tell me what will give you an error. It will be beneficial to know about the Jet's SQL and VBA, and how they differs.

Instance does touch on a important point- ideally the application should be easy to maintain and that means keep all objects discoverable as possible. Using VBA instead of functions inside events (which is legit) or macros means all business logic are now in one place (the module for the form) so it's easy to find code.

 

[ShredDude]

Thanks for everyone's input. Given my Excel Userform background where the concept of Bound controls doesn't really exist, my tendency when first approaching Access forms was to program any sort of calculated field in a VBA function.

I appreciate the simplicity of creating a calculated field in a Query when the calculation is relatively simple like the example given of "=[SomeValue] * [SomePct]" . I've got several values to calculate that are a function of today's date, and other static values stored in a table. Often some sort of looping is required to complete the calculation. I've found those type of things easier to do in VBA.

In the Query, I then made a field's data source as a reference to my VBA function which receives variable values. eg. fMyVBACustomFunction([Amount],[ThingDate], [someotherfactor]), where [Amount],[ThingDate], [someotherfactor] are other fields in the Query.

As always, I guess there's never one way to do something. But I gather from the comments that the Pros tend to shy away from the Access macros, and keep their code in VBA Modules.

Thanks again for the perspectives.

Shred

 

[Instance]

Excellent info from Banana, many thanks. Hadn't thought of some of that. I'll have to play with it some to get it straight in my mind, but very good information.

Glad to help how I could. Welcome to Access World (though I'm new, too, hah!). Best of luck with your application-building.

 

[Banana]

Just to note- as a general rule of thumb, we don't store calculations in tables. Rather, we just store the 'ingredients' needed to calculate the given formulas and always calculate it on fly. This help us avoid the problems associated with anomalies created by storing calculated values then later updating the underlying values.

Maybe you already knew, but I wanted to make sure.