Unbound vs Bound Forms

[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.

 

[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.

 

[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.

 

[CJ_London]

There is no "after delete" event.

there is also the before and after delete data macros.

 

[gemma-the-husky]

I might start a new thread, but where I was coming from was that we upload orders to a RestAPI service, so when we modify an orderline, we have to upload the whole thing (the current order). modify is OK, because we get a form afterupdate event for the amended line. But when we select a row, and click delete there's no event, as far as I can see. You just have an order that previously had 5 lines, and now has only 4.

So I don't really need the order header form to know an orderline was deleted, but I do want the order lines subform to know, and I can't see how it can using any available event.

Hence I disabled the "allow deletes" option on the form, and forced the use of a button click, so that I can do something after the delete instruction.

 

[Mike Krailo]

@gemma-the-husky I have a demo of updating tabs with record counts of sub forms on different tabs. This seems like a similar problem that came up on deletes and it was very frustrating. I ended up using the same solution you did by creating a delete button in the sub form itself and turned off allow deletions unless that delete button is pressed. Now I turned the record selectors back on and use the DEL key and somehow it's working properly. Not sure how it is working, but it is. See attached file.

@Pat Hartman , I tested those events and in order for those events to work, the Confirm Record Changes has to be turned on. To suppress the popup message for confirmation of deletion just set Response = acDeleteOK.

 

[Mike Krailo]

I would never in a million years consider turning off "Confirm record changes" so I never noticed that.

That's funny, I would never want to turn them on in favor of custom delete message or no message at all because in some cases I don't care about the warning. But maybe you know something I don't. What am I losing by turning them off other than that annoying default warning message? I clicked delete to do... guess what?

Now, if is something critical that would cause cascade deletes or some other important change, then I use a customized message that includes the record info to be deleted. It just looks better than the default one that you allow everyone to see.

On a side note, I had fun today learning more about how the delete event order works. I now understand that much better now. The reason I never learned it before was that nothing ever got deleted. So now I'm playing around with it.

 

[gemma-the-husky]

I would never in a million years consider turning off "Confirm record changes" so I never noticed that.

Adding a button to the detail item isn't a bad solution but Dave was looking for some event on the main form that got triggered by action on the subform and that is not available, nor would it ever be since it implies violating normal forms.

I was just looking for an event. I didn't expect one on the main form, but I couldn't find one on the subform either. I couldn't find any way of signalling that a record delete had just taken place, so do something. Once it had taken place, I need to re-process the entire order, As I say, the only way I could find was to treat the form as if it were unbound, and manage the process steps with a button click.

Maybe the AfterDelConfirm would have worked. I thought the after delete confirm was still before the delete took place, but I have just read it carefully, and it seems to fire after the delete has taken place. I thought it fired after you had confirmed you wanted the delete to happen but before the delete took place.

Form.AfterDelConfirm event (Access) | Microsoft Docs

Hmm. I am using a SQL Server back end, and this part of that article says the delete has not yet happened with SQL Server (in the AfterDelConfirm.)

"If you confirm the deletion, Access opens a transaction on SQL Server, issues the DELETE statement to delete the record or records, and fires the form's Delete event. If you choose No when prompted to confirm the deletion, Access does not open a transaction on SQL Server to delete the record and does not fire the form's Delete event."

 

[gemma-the-husky]

@Pat Hartman

Sorry, there isn't an afterdelete event as far as I can see.

Form.Delete event (Access) | Microsoft Docs

This says in the first line
"Occurs when the user performs some action, such as pressing the Delete key, to delete a record, but before the record is actually deleted" (although the article seems to say something different)

I have an order record with 4 order lines. After I delete an order line, I now have an order with 3 order lines. Let's say I want a way to signal to the user "Order line deleted. There are now 3 order lines on this order". (What I really want is to update a RestAPI at this point because the number of order lines has just changed) I can't see an easy way to do that using any events that deal with the delete. Until the record HAS been deleted, there are still 4 order lines. The order line count drops to 3 only AFTER the delete is completed, but the seems to be no way to identify that point.

Maybe it's different because it's SQL Server, rather than a Jet/Ace back-end. I referred to that earlier in #67. MS say the afterdelconfirm event also occurs before the deletion, with a SQL Server database.

Anyway, this is where I see the similarity between using an unbound form and managing a deletion.

 

[MajP]

Maybe it's different because it's SQL Server, rather than a Jet/Ace back-end. I referred to that earlier in #67. MS say the afterdelconfirm event also occurs before the deletion, with a SQL Server database.

I do not think so. I think the issue is the same both ways. You are right there is not after delete, and I have no idea why not. Like you I wrestled with this and try all kinds of ways. Resorted to only allowing deletes from a form button. If you keep the confirm method then you can store the number of records before the delete confirm and the number of records after. Then you can raise a custom event if the number is different. This is counterintuitive because if the number is different then the delete was canceled. You would think in the before delete confirm the number of records would be the number before deleting, but it is the number you would have after deleting. I am guessing this is a transaction and if you cancel it rolls back.