Unbound vs Bound Forms

[TheSearcher]

MajP - I think Access is excellent at supporting unbound forms. Also, its GUI is superb. I agree that VS is excellent as well, and I have used it many times, but I don't care much for the .NET framework.
PH - Why do you think I think Access is inferior? I've been using it for over 25 years. It's my favorite tool for desktop apps. Access doesn't demand that you use bound forms. It's just an option. I don't understand your "cutting hedges with a lawnmower" analogy. Why would anyone do that?

 

[MajP]

MajP - I think Access is excellent at supporting unbound forms.

IMO Access is pretty limited in support for unbound forms. Sure you can do it, just not a lot of features to do it. If you are going to do the work to bypass Access' strength of bound forms then use VS which is excellent for unbound forms. The biggest thing is the datagridview. So you can easily do unbound continuous form like things. You can fake this in access with a ton of work and a have inferior product. VS has a native treeview which to me is huge. Doing dynamic controls in Access is a non-starter. So with a lot of work you can get very average unbound forms in Access. Or for little work you can get the power of bound forms. As I said, I use unbound forms only to display data in ways that cannot do with a standard form, but never for data entry or editing.
Here is one I recent unbound concept I was suggesting.

Continuous Form

Hello everyone hope you all are doing well, i want to design attached image form in ms access, i have tried a lot but couldn't find the solution, can anyone please help me to suggest how to design this dynamic form which is based on table records which name is tblproduct, condition is that when...

www.access-programmers.co.uk

You probably could do this bound as well, but this is a case were unbound requires less work. However, if you were doing that with a datagridview it would be clean and simple.

 

[The_Doc_Man]

When I put together my biggest project, only about three out of over forty forms were unbound. In one case, it was a dispatcher or switchboard types of form. The other two cases were my "template" forms that I copied when creating new bound forms - one for a create/update/delete situation, one for a "viewer-only" form where the user couldn't make it generate anything if s/he tried. The trick was that the templates weren't bound - yet - because the development process started by copying one of the two forms, forms that had a lot of infrastructure already built in, such as having control buttons and headers and things in the Form_Load that looked up who you were from the network. But not bound until I specialized them. And that is the three unbound forms I made in my biggest project.

 

[Pat Hartman]

I don't understand your "cutting hedges with a lawnmower" analogy.

Tools have purposes. Some tools, like Access, can be used incorrectly and we see a lot of that here. Knowing how to use your tool correctly is critical to efficiency. You are set in your ways and not going to change your opinion and apparently your employer is OK with projects taking longer than they need to and to paying more than necessary. When you refuse to use a tool the way it is intended to be used, it is because you think you know better. I'm sure your projects are wonderful but I'm also sure that you could achieve a similar result with less effort using bound forms if you understood the object mode.

 

[Mike Krailo]

I'm sure your projects are wonderful but I'm also sure that you could achieve a similar result with less effort using bound forms if you understood the object mode.

It would be nice to see some side by side examples of a simple application done both ways. So if for instance TheSearcher showed us one of his predominantly unbound app, then one of us can do the same app using bound RAD techniques. Then we would have a con create example to discuss and point out the differences.

This then leads into a better understanding of the event model and object mode (whatever that is). Pat, did you mean object model?

 

[sonic8]

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.

While this is obviously true, it might lead to a short-sighted view. It strongly emphasizes the initial development of a solution and often disregards the ongoing maintenance of a project. I've seen many projects where the quickest (=cheapest) possible approach was choosen for the initial development of a feature but by this the effort for maintaining and extending the functionality was drastically increased. - Finding the right balance can be a tough call. Planning for maintainability and extensibility of a feature involves a lot of guessing the future.

 

[MajP]

It would be nice to see some side by side examples of a simple application done both ways.

This example does not real showcase what you are asking, but was an attempt to answer a question. The applications that I have seen with heavy use of unbound forms are very brute force. They hardwire a lot of one-off code. I wanted to see why no one has made a wrapper to use unbound forms. I think I got a pretty close solution, then lost interest and never finished. Anyway I made a class called UnboundControl. An unbound control has a property to identify which table and which field it is "bound" to. I also built a collection of UnboundControls so that these can all work together.

The demo shows that using the classes I can Load a form by a simply passing the PK to the class or pass in a criteria for another field. Update the underlying table after edits while verifying the data types. Here is the entire code to do loading, data verification, and editing of 9 form fields. As you see this would be orders of magnitude shorter and easier than brute force.

Private UCS As New UnboundControls
Private Sub Form_Load()
  UCS.Initiliaze "tblDemoUnbound"
  UCS.Add Me.UCFirst, "first_Name"
  UCS.Add Me.UClast, "Last_Name"
  UCS.Add Me.UCemail, "Email"
  UCS.Add Me.UCPersonID, "PersonID"
  UCS.Add Me.UCdatefield, "DateField"
  UCS.Add Me.UClongfield, "longField"
  UCS.Add Me.UCdoublefield, "doubleField"
  UCS.Add Me.UCcurrencyfield, "currencyField"
 
End Sub
Private Sub cmdLoad_Click()
  UCS.LoadFromPK (Me.PersonID)
End Sub
Private Sub cmdUpdate_Click()
  Dim pk As Long
  pk = Me.PersonID
  UCS.UpdateFromPK (Me.PersonID)
  Me.Requery
  Me.Recordset.FindFirst "personID = " & pk
End Sub
Private Sub cmdEmail_Click()
  If Not Trim(Me.cmboEmail & " ") = "" Then
   UCS.LoadFromCriteria ("email = '" & Me.cmboEmail & "'")
  End If
End Sub

If I personally was going to do unbound forms, I think I would finish these classes and leverage them. I think I determined that no matter how much I added to these classes there would always be exceptions that needed to get hard coded.

 

[GPGeorge]

While this is obviously true, it might lead to a short-sighted view. It strongly emphasizes the initial development of a solution and often disregards the ongoing maintenance of a project. I've seen many projects where the quickest (=cheapest) possible approach was choosen for the initial development of a feature but by this the effort for maintaining and extending the functionality was drastically increased. - Finding the right balance can be a tough call. Planning for maintainability and extensibility of a feature involves a lot of guessing the future.

Very true. My point was that the relative cost to create a relational database application around bound and unbound forms has to be included in any discussion of the pros and cons. That said, of course, short cuts and sloppy design impact both approaches negatively.

In fact, at the risk of going further than I should, I sometimes think that developers who come at the task from a coding orientation are more likely to ignore sound table design because they are confident they can manage it all with elegant code regardless of whether it's the most efficient design in a fundamental sense. That bleeds over into the bound/unbound discussion, but has primary implications in other areas.

To give an example that I recently bumped into in the real world, it's possible to create a table with repeating columns, such as "PartInspected" (yes/no), "PartInspectedDate", "PartPassedInspection" (yes/no), and "PartPassedInspectionDate" (date/time). Sound relational database design says that's redundant and inefficient and it opens the door for data anomalies. The developer who comes at the problem from the point of view of relational database design will immediately change that to two date field, "DateInspected", and "DateInspectionPassed", or one Date field and one Yes/No, "DateInspected", and "InspectionPassed". That allows you to simplify not only the interface, but removes the need for validation code to ensure that you don't end up showing the date an inspection was passed with a No value in the inspection passed field. Long winded explanation for a simple example, I guess.

My point is that, if you approach development from the point of view of the coder who sees writing that validation code as a chance to exercise their skills you are also more likely to lean towards unbound forms. If you approach development from the point of view of the relational database developer, you're more likely to implement a sound table structure that takes advantage of the bound forms' strengths.

 

[Pat Hartman]

@sonic8 Why would you think that unbound forms are easier to maintain? The more code you write, the more code you have to change when you have a change to make. And the more code you have to debug.

@MajP It seems to me that if you if you are making a class of a class and using control names, it would have very limited reusability.

An Access form has a navigation control but many people like to make their own for whatever reason. Usually, they just prefer the look of their buttons to the built in control, not for any technical or functional reason. I've seen good procedural implementations of this that are very easy to reuse as well as hardcoded implementations that are not reusable at all. I think you also showed a class.

As a solution, the built-in functionality is "free" and the class is not but it is at least a rational use for a class even though it doesn't provide any functionality that the default control does because it can be used on all forms not just one or two.

 

[Mike Krailo]

I think I determined that no matter how much I added to these classes there would always be exceptions that needed to get hard coded.

That's unfortunate because that is a very interesting idea. I might delve into that deeper to see what happens. You might be right though. Thanks for posting that demo.

My point is that, if you approach development from the point of view of the coder who sees writing that validation code as a chance to exercise their skills is also more likely to lean towards unbound forms. If you approach development from the point of view of the relational database developer, you're more likely to implement a sound table structure that takes advantage of the bound forms' strengths.

That's an interesting point that I had initially assumed both were using solid database design, but until I can get my hands on some of these completed projects that work using unbound forms from the expert (at least they consider themselves to be) unbound form developers, I won't know for sure if that is the case.

And @sonic8, I think that ease of maintainability and debugging was actually one of the big reasons they go the unbound route. I think what I heard from one of them was roughly:

"I know my code works and I can easily debug it because I know how my code works."

 

[GPGeorge]

"not for any technical or functional reason."

I did have one occasion when I felt that the navigation control was not adequate. It involved a main form/subform design with multiple navigation controls visible in the subforms. In order to make the main form's navigation more prominent, we replaced it with a custom version. But that was a unique situation to be sure. Hardly one worth replicating elsewhere.

 

[GPGeorge]

That's unfortunate because that is a very interesting idea. I might delve into that deeper to see what happens. You might be right though. Thanks for posting that demo.


That's an interesting point that I had initially assumed both were using solid database design, but until I can get my hands on some of these completed projects that work using unbound forms from the expert (at least they consider themselves to be) unbound form developers, I won't know for sure if that is the case.

And @sonic8, I think that ease of maintainability and debugging was actually one of the big reasons they go the unbound route. I think what I heard from one of them was roughly:

I used the phrase "more likely" because it's dangerous to assume that anything is true 100% of the time.

 

[Pat Hartman]

think that ease of maintainability and debugging

Do you have any evidence? Remember, the developers using bound forms are using them for the most part because they do not understand how to control the form's event model.

 

[MajP]

That's unfortunate because that is a very interesting idea. I might delve into that deeper to see what happens. You might be right though. Thanks for posting that demo.

I write a lot of class modules to encapsulate functionality. The goal is to create a black box that is completely reusable without writing new code. That means it should handle any possibilities. The user should be able to use the class without modifying or even understanding the inner workings. I think I can come up with a 90% solution that would work for most cases, but there will likely be cases the user would have to write additional code. It still needs features, but if interested you can play with it. Take a simple database you have and see if you can convert it to unbound using these classes. That is the way to identify what still needs to be added.

 

[NauticalGent]

I sometimes think that developers who come at the task from a coding orientation are more likely to ignore sound table design because they are confident they can manage it all with elegant code regardless of whether it's the most efficient design in a fundamental sense.

Exactly. I found that the better I got at coding, the more I moved away from the Access Basics and would find myself in mid development saying "Wait a minute, Access already does this and does it a LOT better than I can!"

I even started a thread called The Pitfalls of VBA Proficiency

 

[Mike Krailo]

I even started a thread called The Pitfalls of VBA Proficiency

@NauticalGent I just ran through that thread. What would be nice is a list of common pitfalls and how to overcome each and every one of them. I still love the Greg's Bash Wiki site as a great example of this. I became a much better BASH shell programmer because of this very site. Notice how there are concrete examples of what wrong is and then examples of the correct way to do things. Even if you don't know bash, you get the idea.

Bash Pitfalls

 

[gemma-the-husky]

Something that occurs to me which is really nothing to do with bound and unbound forms per se, is managing deletions. The effect is somewhat similar though. A way of having just a bit more control in certain cases.

I have a form with a subform (eg, order and order lines). If a user deletes an order line I want the order container form to know about it and do something. I just can't find a way to do that - to notify the order that an order line was deleted. There is no "after delete" event. The only way I can do it is to disable deletes on the subform, and click a button to delete a record.

This does fit a model where you have a form with an order header record, and a subform with the orderlines linked to that order header - but using events, there is just no way I can see for the order header to "know" that one or more, or even all of the order lines have just been deleted, and "know" that it now needs to do something.

 

[NauticalGent]

@NauticalGent I just ran through that thread. What would be nice is a list of common pitfalls and how to overcome each and every one of them.

Using unbound forms for starters!

Seriously though, there are no real hard and fast rules but when get hit by the "Good Idea Fairy" before I start writing a quick Sub or Function, I try my best to do it with Access instead of VBA. On thing that comes to mind is Access's bulit in "NotInList" event for Combo Boxes. Although code is needed, I had no idea how robust the process was (including what form to use) until I think it was @jdraw that mentioned it one day. It is a classic example of how working WITH Access is much easier and efficient than trying to bypass it.

 

[jdraw]

Recently saw/found/stumbled on this that seems related to the topic

An Access form, because of data bindings: a data-bound form is inherently coupled with the underlying database storage, and any effort to decouple the UI from the database is working directly against everything Access is trying to make easier for you.

 

[Pat Hartman]

there is just no way I can see for the order header to "know" that one or more, or even all of the order lines have just been deleted, and "know" that it now needs to do something.

Think about normalization for the answer. There is no reason that the mainform should ever have to know that a subform record was deleted. So, Access would have no reason to give you this "feature". relationships are uni-directional. they are not bi-directional. Making them bi-directional would make them impossible to implement. i.e. Pathological. You couldn't add a record to the main form because a record would be required in the subform as well as vice versa.

There is no "after delete" event.

Take a look at both the BeforeDelConfirm and the AfterDelConfirm events. I'm pretty sure that if you select multiple records to delete, these events run once for each row being deleted. Also be aware that the AfterDelConfirm event would no longer have any of the deleted data available.

Bottom line, the only reason you would need the Mainform to know about an add or delete in the subform is because you are doing something you shouldn't do like storing a total.

If you have a business rule that at least one record is required, you would count the child records and refuse to delete the "last" one.