Unbound vs Bound Forms (1 Viewer)

[MajP]

The biggest thing that bound forms do well is Continuous Forms. This is made even more problematic in Access, in that Access does not have a native grid control. IMO this is one of the biggest shortcomings of Access. If Access had a .net gridview like control, it would really expand what you can do with a form. The gridview would allow you to build an unbound "continuous form like" control. The workaround is usually to write data to a temp table and bind that to a continuous form.

 

[Mike Krailo]

Here's a different point of view by Juan Soto from 2012 on the same topic
https://accessexperts.com/blog/2012/03/13/why-using-unbound-forms-are-a-bad-idea/

That was an interesting read. The decenters in the comments also echoed a few things that were in the original posted article. Those posts were also ten years old.

 

[CJ_London]

perhaps it's time to write a new article....

 

[CJ_London]

The workaround is usually to write data to a temp table and bind that to a continuous form.

The workaround I use is a disconnected ADO recordset - useful for being able to have an 'updateable' crosstab for example but still bound. updates etc being handled in vba. Only disadvantage with ado datasets is the inbuilt sort and filter functionality wont work, so you need to write your own shortcut menu if you want to users to have this functionality.

 

[Mike Krailo]

perhaps it's time to write a new article....

That's kind of what we are doing here in the form of a post instead. If someone creates a new article for real, I could link back to it at the top of the thread as well. As long as we get the end results everyone can agree on, it'll be good.

 

[gemma-the-husky]

Not necessarily an unbound form, but more a disassociated table. (probably similar to @CJ_London just above)

I often like to use a temporary table(s) as a work area, and then move the data to the real table when I'm done.

That way, for instance, a user can enter an order, and if they decide to accept it, I move the data into the real order table with an append query/queries. If they change their mind, I can just clear the temporary table(s)

 

[Pat Hartman]

• Quick loading of data since you only retrieve data the user needs to see in that moment.
• No locks placed on your tables
• You can parse the data through all kinds of business rules before posting back to your database.

From Juan's list. Only No Locks is actually valid. A proper bound form should return a single record whenever possible. Or a non-updateable form might return a small list that needs further filtering. If you know what event to use, you can apply whatever business rules you want before allowing Access to save a record.

If I've said it once, I've said it a hundred times. Access is a RAD tool. The entire point of a RAD tool is its RADness and for Access, that means BOUND forms. If you think you are too smart to use bound forms because you can do it better, you don't understand how Access works and you should be using a different tool.

Unbound forms have their place in the universe but they are not the norm. I've been using Access for 25 years or more and can count on one hand the number of unbound form's I've needed to create and most of my applications use some RDBMS rather than Jet/ACE. Usually SQL Server these days but early on, DB2, Oracle, Sybase, Progressive and others - whatever the client was using for his ERP usually.

These days with the push to webify everything, MS has tried and failed 4 times because they didn't understand the request to use Access on the web. They think for some reason that people are asking to run Access in a web browser so that is the solution they keep building but the result is NOT "Access" so it is never widely adopted. Access projects weren't "Access" either and that is why they were never widely adopted. The request is actually to optimize ODBC so that Access can link to tables over the internet at a speed faster than watching paint dry but still run as a Windows client. People here have had success using Azure but only Azure that they hosted themselves. I could never get a third party to spend the time to understand how to optimize interaction with Access so I was able to connect but the result was unusable.

Access is very chatty and that gets in the way when you are working at internet speed rather than LAN speed to retrieve/update data.

So, the only real reason for using updateable unbound forms is to use an Access client but use tables over the internet. However, there are far better ways to do this because unbound forms require YOU to do the heavy lifting for form processing instead of Access. If you need to access remote data, Access is not the tool you should be using. There are far better, more flexible options.

People who recommend unbound forms for reasons of control do not understand how to use the Access form's event model. You have complete control if you simply use the correct events. The only weakness is subforms. Because of the way relational databases work, it is not possible to add child records without a parent record so this makes some business rules difficult to implement. Not impossible, just a little different but not problematic at all if you understand the issue from the beginning so you can allow the parent record to be created but have a way to make it inactive until all the child records have been added.

 

[Pat Hartman]

I often like to use a temporary table(s) as a work area, and then move the data to the real table when I'm done.

A simpler solution is to use an "incomplete" status on the parent record. So the only query that ever selects the "incomplete" records is the one used by the edit form. The problem is that people don't recognize this issue until well into development and rather than examine and potentially change a dozen queries, they build a Rube Goldberg parallel structure. Been there. Done that. Learned my lesson.

 

[NauticalGent]

If someone creates a new article for real,

I nominate you...

Here is a plug for unbound forms (for adding records only). The discussion afterwards was interesting too...

 

[MajP]

Here is a plug for unbound forms (for adding records only). The discussion afterwards was interesting too...

This is the same logic fallacy as the first article.
In the first article the solution for a horribly designed bound form is to make it unbound. Instead of simply designing a good bound form.
In this article the solution of separating data entry from data editing is to make the entry form unnbound, instead of simply making bound forms for data entry and data editing. It is dumb logic.

If you are an expert in writing VBA then you might find creating an unbound form is easier than figuring out how to build a proper bound form. For someone who can easily do both proper bound and proper unbound, I will tell you that you will do more work to do an unbound. That is not debatable. However, for some this may be easier if that is your skill set. The problem is when I see a novice who can barely write vba code, feel that the solution is to create an unbound form because they have read one of these uninformed articles. It is far easier to figure out how a bound form can do the exact same thing.

I am all for doing unbound forms if you have a strong reason. I just never can find anyone with a compelling reason. It is normally middle skilled developers who are over their head developing Rube Goldberg solutions. 99.9% of the time I see post when I ask what is the reason for going unbound, is because they do not know how to do the same thing bound in a far easier way. All this talk about the hidden complexity of the form events and when a record is saved... blah, blah is garbage. All anyone needs to know is the BeforeUpdate event. @Pat Hartman please write a thread on the BeforeUpdate, that is 99% of what you need.

 

[NauticalGent]

For the record, I don't really have an opinion one way or the other. I almost always use bound forms for the very reason(s) you stated. Much more work to add the forms content to the applicable tables then to simply use inherit properties of the forms record source. It was interesting to me how others disagreed with him and how he handled it.

 

[MajP]

It was interesting to me how others disagreed with him and how he handled it.

My point is that the article is not really about bound vs unbound. It is about separating data entry from data editing. I am all for that, but the answer is not unbound forms. I see novice user trying to get a single form that does everything. It is a parent child form with the ability to add and delete parent and child records, navigate,, edit parent or child. For me that is 2-4 separate forms. I almost always separate navigation from adding/editing. Separate adding parent information from adding child information.

 

[Pat Hartman]

@MajP and I are on the same page with this topic. If you are going to use a RAD tool, use it!! Don't avoid its best feature for some misguided reason. A programmer needs to understand his tools and use them the way they were designed to be used. You don't use a lawn mower to trim your hedges just because both tools cut things and you don't use a chainsaw to cut your grass although in a pinch you can probably trim your hedges with one. I do remember the time as newly weds and new homeowners with no tools to speak of, my husband drilled a hole through two kitchen cabinets with a screwdriver so he could connect the water line from the fridge to the cold water line under the sink. I think it only took him an hour (the cabinets were solid wood). It probably would have been faster with a Philips head but all we had was the normal single blade version.

I've probably written hundreds of dissertations on the form's BeforeUpdate event being the most important event of the entire pantheon of form events. And amazingly, I get experts who do it "differently" and seem to think that somehow needing to have code in 6 events is better than having code in one event but I digress.

Separating data entry from data edit just gives you opportunities to mistakenly have a different set of business rules. I'm not sure I've ever run into a situation where the validation code at data entry would be different from the validation code for changing a record. So, I NEVER separate the two actions.

Someone please enlighten me. Do you have business rules that are different for add than for change?

I occasionally find myself using "If Me.NewRecord" which means that I'm doing something for new records that I'm not doing for existing records or vice versa but it is probably something like generating a custom sequence number that when I do it, I do as the last action of the form's BeforeUpdate event to minimize the potential for conflict.

 

[TheSearcher]

Fascinating discussion. It seems that the people who design their applications using bound forms believe that using them is best while the people who design using unbound forms think the opposite. I suppose we will choose whatever works best for us. That’s perfectly okay. For me this is a no brainer. I never use bound forms in my apps. Certainly, this causes me to incorporate much more VBA in my programs but it also gives me almost total control of my projects. An experienced coder, using any language, will not want to be limited by his IDE – and this is exactly what Microsoft Access will do if you let it. Take advantage of all the great things Access has to offer but code your applications as you would using any other language (Java, VB etc.). In my humble opinion, this is the way to go.

 

[GPGeorge]

Project Cost = Developer Rate * Hours spent writing code and designing forms. If cost is not a factor (i.e. your client doesn't care how much you bill) by all means go for the method that expands hours billed.

 

[isladogs]

Take advantage of all the great things Access to offer....

By never using bound forms, you have immediately ruled out one of the best things Access has to offer

 

[NauticalGent]

Project Cost = Developer Rate * Hours spent writing code and designing forms. If cost is not a factor (i.e. your client doesn't care how much you bill) by all means go for the method that expands hours billed.

Great argument - casts a different light on the subject.

 

[Pat Hartman]

I suppose we will choose whatever works best for us.

You're absolutely right! I'm going to keep using my lawnmower to trim my hedges. It works for me

 

[MajP]

Building Access applications using Unbound forms is dumb! Not because unbound forms are dumb, but because Access is extremely poor at supporting unbound forms. If you have the skill set to write the code to build unbound forms why waste your time doing it in Access. For free you can do it in VS and do way more than Access can do. Dynamic controls, user controls, data grids, ado.net, etc. I do not get doing non-RAD things in a RAD development.

 

[Pat Hartman]

I came to Access from a mainframe environment where I coded everything. I got no help at all. If I wanted something to happen, I coded it. I thought I'd died and gone to heaven when I discovered Access and on the same project that I could link an Access form to DB2(IBM's relational database) and update data also. I went from having to write thousands of lines of code (I did keep templates so it wasn't all that bad) to writing hundreds and those hundreds were almost all related to data validation. I've written my million lines of code and I don't need the practice. I let Access be Access and I write code that Access can't write for me.

It is important to understand your tool. It is silly and even dangerous to cut your hedges with a lawnmower. And as MajP pointed out, it is just as silly to use Access but turn your nose up at the best tool in the box which is bound forms and reports. If you think Access is somehow inferior, why burden yourself with its very heavy footprint and limitations? If you're going to use Access, learn how the event model works. Once you understand which events should be used for which type of processing, you gain total control over the form for a fraction of the time/code expenditure.