Bound vs. Unbound data entry forms

Ah, but your missing the point. The user did not just hit save button, but hit record navigation button. So, when their mind see's a yes/no prompt, they not thinking about saving, but thinking that they don't want to say navigate, and they want to work more.

End user's minds when presented with a prompt tend to associate the prompt with what they JUST did, and the user did not just hit save, but hit record navigation button. Again, this is not some big huge deal, but I making a point that if you introduce a save button into your application, then you have to deal with a lot of issues that crop up.

Here is a better example:

This is a "classic" QuickBooks like amount distribution to GL accounts. In other words, the user enters $150 dollars on the left side, and then on the right side we will enter 3 amounts (that will total up to $150 that distributes the given amount across several GL account.

So, in this screen video, we are adding 4 records in total, and that would suggest 4 save prompts then, right?

Hence this:

So, in above, watch the screen cap gif as I enter 4 new records.

if you adopt a save prompt, then you have 4 save prompts for the above, would you not? Hardly a keyboard friendly kind of data entry form then, right?

I mean, it not the end of the world here what I trying to share with you.

I am simply pointing out that any form that has multiple records is going to be a REAL challenge to introduce save prompts, and if you start out with forms having a save prompt, then you are quite much committed to that UI design and consistency throughout the WHOLE application. And for forms like above, that's going to be a REAL challenge.

As as I kindly stated in my other post, there is no "one way" or "some only right way" to built your UI. However, given above typical types of forms in Access in which rows of data are being added, then it going to be a significant challenge for you to require a save button for all records. The above example form in which we JUST added 4 records is a great example.

Access forms don't support well at all un-bound forms, and this is especially the case if you going to have any kind of form with a master record, and then multiple child records. I'm only simply pointing out to keep this limitation in mind before you venture too deep into your project and design assumptions.

If your data forms don't have sub forms, and you don't expect to have sub forms with "multiple rows" of data, then you can well introduce a save prompt. And in fact, you can even introduce a save prompt with bound forms, since the before update event does have a cancel option.

And keep in mind that the boatloads of form events (about 15+ of them) that trigger and run in bound forms?

If you go un-bound, then you loose all of those data bound cool events (such as before update etc.). All of those events such as before update etc. don't fire for un-bound forms. So, it going to be difficult to determine when things are changed on that form.

While say .net has a boatload of wizards built around un-bound forms, Access does not have such tools and wizards for un-bound forms. The end result is you give up about 15+ cool and valuable form events that don't trigger for un bound forms, and then you don't have all those .net wizards for support of un-bound forms. You in effect wind up with the worst of both worlds. Had Access support for disconnected recordsets, then we have a VERY different narrative here - but Access does not have good support for disconnected recordsets like .net does.

And there ARE some UI's and some systems that will specify design requirements that you MUST have a save button for all data entry forms. If that is the case, then Access turns out to be the wrong tool for such design requirements. You then probably better to adopt a software tool that supports disconnected reocrdsets for such data entry forms.

As I stated, when in Rome, do as in Rome, and in Access land, for the most part bound forms are the way to go. The amount of time and pitfalls you encounter are significant indeed with un-bound data entry forms.

Albert D. Kalllal (Access MVP 2003-2010)

Edmonton, Alberta, Canada.

I haven't used many sub forms yet. Only for search forms so far. Parent form unbound with text/combo boxes to build a SQL Where clause and set/update the subform's record source when the search button is clicked.

One of my databases does have a reference table that needs a record before related records can be added to a child table. I just think I don't really like subforms for data entry (new records). The entry form for the reference table is unbound, and the entry form for the child table is bound because it has an attachment field (this is the topic of my original post).

As for edit forms, you can have a save prompt for the parent record on the subform's enter event. I think it's enough to have a note that the undo button only undoes changes to the current selected record. Transaction processing with rollbacks would make things better. But informing users of the limitations of the undo command is the next best thing you can do without making it too complicated to code.

I think the disconnect we're having in this discussion is that you guys have all been using Access for many years. And you probably build most of your databases for people that are familiar with using Access applications, even if they don't make things in Access themselves. Keep in mind, I'm using this only in an office environment. I work in an administrative division in government. As I said before, most people are familiar with Excel, but hardly anyone uses Access or knows much about databases. Most people don't want to use something different that they aren't used to. I see the value in it, but many others do not. So to have any chance of getting others on board, I have to make it easy and similar to what they are used to.

The flip side of being less experienced than you all is that I still remember what it's like to be unfamiliar with the functionality of Access. So while you may think something is intuitive because you are used to it, I think it may not be the expected behavior from a new user's perspective.

So, you hit move to next record button, now a dialog prompt pops up asking you to save. If you hit no, then what is to occur? Do you NOT save the record, and allow navigation to the next record? Or if they hit no, then you don't allow navigation and now they stay and are stuck on the current record? So, now they are forced to hit save first, and THEN hit the navigation button?

Is this supposed to be a gotcha question? Obviously the prompt would have yes, no, and cancel options. Yes saves and moves to the next record. No discards changes and moves to the next record. Cancel stays on the current record without saving changes (but doesn't discard them). Hide the default record navigation buttons and provide a NextRecord (and PreviousRecord) button:

Private sub NextRecord_click()

If Me.dirty then
    Dim Response as Integer

    Response = MsgBox("Save changes before moving to next record?", vbYesNoCancel)
    If Response = vbNo Then
        DoCmd.RunCommand acCmdUndo
    ElseIf Response = vbYes Then
        DoCmd.RunCommand acCmdSaveRecord
    Else
        Exit sub
    End If
End If

DoCmd.RunCommand acCmdRecordsGoToNext

End sub

I just don't like gaps in an auto-incrementing field. A gap should indicate that a record was deleted, without including any entry was started but aborted. That's useful information for the developer. Doesn't matter whether or not it's shown to the end user.

Hi WCZH,

By the way, the difference between a bound and an unbound form is very small.

When you have an unbound form and assign a table/query to the RecordSource of the form, and a fieldname to the ControlSource of a control, you have a bound form.

In fact it says: this control belongs to this field in this table/query.

If you really realise that, you can use either, that is assign the values to RecordSource and Controlsource (bound condition), or use a oneliner to update the field in that table/query (unbound condition). Or use a mix of both.

For simplicity and consistency, it is then easier to use the unbound approach.

Imb.

Hi WCZH,

Let me start with explaining how I use one unbound form for data entry. Perhaps it gives you some ideas to use, and many questions for details.

Here is a screen shot of how this form looks like. In fact it is a generalized form which is tuned with the specifics of the record you want to add.

The underlying form, Persoon_formx, displays the selection of Persoon-records with Achternaam (Lastname) = "Xxx). It is possible to use autofill the search criteria in the corresponding controls, as is done in the example.

The "labels" are filled from a metadata table in the FE, that specifies the control names, but also stores special conditions, a.o. if the field is an obliged field (the yellow controls). if it is a FK-field (underlined label), and many other constraints, depending on the "type" of the control.

This means that the user gets already a warning if the value would give a conflict at storage,

It is also possible to have controls that refer to field in other tables.

Pressing a key in a FK-control, opens a next form, where you can select on any field from the related record with many different condtions, type-dependant, and inline sorting on any field (dates even in calender-order independant of the year), again based on that metadata table.

See for instance:

Back to the entry form:

When the button "Opslaan" is pressed, a routine is started, a couple of checks is performed whether the new record would give any problems with key-violations, etc. If all is safe, the new record can be saved.

With a loop through the metadata table the program fills the defined fields with values from the filled controls, returns the newly defined Persoon_id, uses this id for all controls that refer to other entities (tables), closes the form, and activates the new created Persoon in the overview form.

The button "Opslaan en nieuw" saves the record, and opens a new (blank) entry form.

The button "Opslaan en sluit" saves the record, and closes also the underlying overview form.

Two other buttons:

"Copy record" fills all controls without value form the active record in the overview form.

"Copy veld" overwrites the current control with the value from the active record.

This same routine works now for some 2000 different tables in 100+ different applications. All next new tables have the feature automatically build in!

All the above is only a tip of the veil.

I have the feeling that there is not so much interest for such approaches in this forum. Perhaps it is the wrong forum for this.

But when this thread started, I could not suppress a smile. It remembered me to the lyrics of "Bloemen zijn rood", a song of Gerard van Maasakker, performed in Brabants dialect, and based on the original lyrics of "Flowers are Red" by Harry Chapin.

Imb.