Unbound form questions

I'm looking for input into my decision to switch to ADO and unbound
forms. I get tired of having to block all of the ways a user can
unknowingly save a record, like using the PageUp, PageDown keys and the
scroll wheel on the mouse. Also, I think that as the database grows in
size that it will eventually be moved to SQL Server 2005 Express.

With all of that said I started using ADO to manually work with my
unbound forms. It is a lot of work to get it to work. My questions
are these:

1. Have I missed something about controlling the saving of bound forms?

2. Any problems using ADO to connect with the backend database which is
for now Access?

3. If I use unbound forms how should I handle record locking?

I know I have more questions on this subject but this is enough for
now!

Thanks,
TD

re: Unbound form questions

"TD" <dlee@adsi-sc.com> wrote

No matter whether you use ADO or DAO, you are reimplementing function that
is included in Access for bound forms. And, not only has it already been
implemented, it has been tested by tens of thousands of users for years and
years.
[color=blue]
> 1. Have I missed something about controlling
> the saving of bound forms?[/color]

Controlling the saving of bound forms is accomplished with code in the
Form's BeforeUpdate event. It is relatively simple to give the user a choice
to either Save or Return to the Form. You can include an Ignore button on
the Form that Undoes any changes, too, if you wish.

It is better to realize the (relatively few) circumstances that cause Access
to save, and code so that "inadvertent saves" are not a problem. Over the
years, I have only rarely had any need to take particular action because of
Access' automatic saving, but I have saved uncounted hours by learning how
to use, and using bound forms.
[color=blue]
> 2. Any problems using ADO to connect with the
> backend database which is for now Access?[/color]

As you may know, I am not a "fan" of ADO, but I don't know of any particular
_problems_ connecting with a split/back end Jet database. In the next
version, that will actually be an "Access" database, but, in the current and
previous versions, it is a Jet database engine database. I don't like the
idea of investing time and energy in a technology that has already been
superceded in the Microsoft development area that "really counts", DotNet,
by ADO.NET which is built on a different object model. Others, however,
think it is the neatest thing since the invention of sex, or at least since
the invention of pre-sliced bread.
[color=blue]
> 3. If I use unbound forms how should I handle record locking?[/color]

Perhaps it would be facetious to say "in your code"? In general, you look
at the requirements of your application, and determine what is
appropriate -- and, in general, that would be "however you'd handle it with
bound forms". You do have additional flexibility, in that you can use
Transactions.
[color=blue]
> I know I have more questions on this subject but this is enough for
> now![/color]

From my initial answer, as well as the specific one, I think it is likely
that you are heading in the wrong direction unless you have some very, very
unusual application requirements.

Larry Linson
Microsoft Access MVP

re: Unbound form questions

"TD" <dlee@adsi-sc.com> wrote in message
news:1147142399.638636.166260@u72g2000cwu.googlegr oups.com...[color=blue]
> I'm looking for input into my decision to switch to ADO and unbound
> forms. I get tired of having to block all of the ways a user can
> unknowingly save a record, [snip][/color]

Your problem is one of perception. You believe that users "unknowingly saving a
record" is an actual problem that needs to be solved. The millions of Access
apps out there that don't concern themselves at all with this expose this belief
as incorrect.

If you want to eliminate accidental edits then lock the form and provide a
button to unlock it. This way the user has to clearly indicate intent when they
change a record. Then simply inform the users that changes are automatically
saved and move on to *real* problems.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

re: Unbound form questions

Per TD:[color=blue]
>2. Any problems using ADO to connect with the backend database which is
>for now Access?[/color]

"Problem" may be too strong a word, but if the data is in a .MDB, I would stick
with DAO because everything I've heard so far suggests that DAO is the best tool
for going after .MDBs.

Rightly or wrongly, I do unbound forms for almost everything. I find them
reasonably quick and easy to code now that I've done a few and I like having
complete control over what happens in "Browse", "Add", and "Edit" modes. No
tricks, nothing hidden - it's all up front in the VBA code.

Every so often, I drift back - but it always seems like that's the time the
users want to do something like validate a screen's contents in light of other
records before allowing changes tb saved.
--
PeteCresswell

re: Unbound form questions

"TD" <dlee@adsi-sc.com> wrote in
news:1147142399.638636.166260@u72g2000cwu.googlegr oups.com:
[color=blue]
> I'm looking for input into my decision to switch to ADO and
> unbound forms. I get tired of having to block all of the ways a
> user can unknowingly save a record, like using the PageUp,
> PageDown keys and the scroll wheel on the mouse. . . .[/color]

Er, why not just control those, instead of adding all the work of
unbinding a form?

And if you're going unbound and your data is stored in a Jet back
end (i.e., MDB), then why in the hell would you choose ADO instead
of DAO? That would make no sense whatsoever.
[color=blue]
> . . . Also, I think that as the database grows in
> size that it will eventually be moved to SQL Server 2005 Express.[/color]

That would be a reason to use ADO, but still no reason to use an
unbound form.
[color=blue]
> With all of that said I started using ADO to manually work with my
> unbound forms. It is a lot of work to get it to work. My
> questions are these:
>
> 1. Have I missed something about controlling the saving of bound
> forms?[/color]

If you have problems with particular user actions saving a record,
then trap for those actions and prevent them from happening.

If other things still lead to a save, then use the record-based
events to check for those and cancel them if appropriate.
[color=blue]
> 2. Any problems using ADO to connect with the backend database
> which is for now Access?[/color]

ADO is a nonsensical choice for a Jet back end, but I think going
unbound is equally senseless.
[color=blue]
> 3. If I use unbound forms how should I handle record locking?[/color]

You have to write a whole buttload of code and hit the back end
extra times.
[color=blue]
> I know I have more questions on this subject but this is enough
> for now![/color]

Don't go unbound. Just fix the original problems instead of going
with a Draconian solution that will cause more problems than it
could ever possibly solve.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: Unbound form questions

"(PeteCresswell)" <x@y.Invalid> wrote in
news:ek7162hctes1a4sgri2486ue6i5534e9di@4ax.com:
[color=blue]
> Rightly or wrongly, I do unbound forms for almost everything. I
> find them reasonably quick and easy to code now that I've done a
> few and I like having complete control over what happens in
> "Browse", "Add", and "Edit" modes. No tricks, nothing hidden -
> it's all up front in the VBA code.[/color]

How can you do without the events, and the .Dirty property?

I just find myself re-implementing way too many things that I just
don't see any value in it just to get control over adding and
editing and saving.

Of course, on principle, I no longer use the same form for adding
records and editing them, except for relatively trivial forms. That
took care of 90% of the problems I had (which were mostly involved
with abandoning newly created records).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: Unbound form questions

Per David W. Fenton:[color=blue]
>How can you do without the events, and the .Dirty property?[/color]

I use events extensively.

Maybe I'm just not using it properly, but I find that .Dirty gets too
complicated with, say, a half-dozen subforms.

Instead, I code a .MakeDirty function in the parent form and call it from the
child forms whenever something changes.

e.g.
---------------------------------------------
Private Sub cboWhatever_AfterUpdate
afterUpdateAllFields
End Sub

Private Sub afterUpdateAllFields
With me
.Parent.MakeDirty .Name
End With
End Sub

Public Sub MakeDirty(ByVal theFormName As String)
Select Case theFormName
frmOne
mDirty_FormOne = True
frmTwo
mDirty_FormTwo = true
frmThree
mDirty_FormThree = true
end select

with me
.cmdClose.Caption = "Cancel"
.cmdSave.Enabled = true
end with
End Sub
---------------------------------------------


That way I even have the option of keeping track of which children have been
changed. Also .MakeDirty can manage the .Enabled status/captions of the "Save"
and "Cancel/Close" buttons.
--
PeteCresswell

re: Unbound form questions

"(PeteCresswell)" <x@y.Invalid> wrote in
news:f68262do7o5tfkugkdht1ka6bnurohgdsh@4ax.com:
[color=blue]
> Per David W. Fenton:[color=green]
>>How can you do without the events, and the .Dirty property?[/color]
>
> I use events extensively.[/color]

Not the built-in record events, since they don't fire in an unbound
form (e.g., OnCurrent, BeforeUpdate, BeforeInsert, etc.). You have
to recreate them in some form if you need them, and that is a real
pain, since there is often no single place to call them from, as
your example of replacing the Dirty property shows perfectly.
[color=blue]
> Maybe I'm just not using it properly, but I find that .Dirty gets
> too complicated with, say, a half-dozen subforms.
>
> Instead, I code a .MakeDirty function in the parent form and call
> it from the child forms whenever something changes.
>
> e.g.
> ---------------------------------------------
> Private Sub cboWhatever_AfterUpdate
> afterUpdateAllFields
> End Sub
>
> Private Sub afterUpdateAllFields
> With me
> .Parent.MakeDirty .Name
> End With
> End Sub
>
> Public Sub MakeDirty(ByVal theFormName As String)
> Select Case theFormName
> frmOne
> mDirty_FormOne = True
> frmTwo
> mDirty_FormTwo = true
> frmThree
> mDirty_FormThree = true
> end select
>
> with me
> .cmdClose.Caption = "Cancel"
> .cmdSave.Enabled = true
> end with
> End Sub
> ---------------------------------------------
>
>
> That way I even have the option of keeping track of which children
> have been changed. Also .MakeDirty can manage the .Enabled
> status/captions of the "Save" and "Cancel/Close" buttons.[/color]

Why couldn't the built-in OnDirty event do the same thing? The
OnDirty event for the parent form wouldn't affect the Dirty
properties of the child forms (or vice versa, except for a new
record).

I've done all of this myself, and see no advantage whatsoever to
recoding all of this when it's all available in a bound form without
any coding. Of course, I also have never even contemplated an
unbound form with unbound subforms. That just strikes me as complete
insanity, sorry.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: Unbound form questions

Per David W. Fenton:[color=blue]
>Not the built-in record events, since they don't fire in an unbound
>form (e.g., OnCurrent, BeforeUpdate, BeforeInsert, etc.). You have
>to recreate them in some form if you need them, and that is a real
>pain, since there is often no single place to call them from, as
>your example of replacing the Dirty property shows perfectly.[/color]

I guess I'm into semantics then. My "unbound" forms are actually bound - only
to work tables in C:\Temp.
--
PeteCresswell

re: Unbound form questions

"(PeteCresswell)" <x@y.Invalid> wrote in
news:u1f462lkof1coq2kku0gqg5ma6vdhrrtk3@4ax.com:
[color=blue]
> Per David W. Fenton:[color=green]
>>Not the built-in record events, since they don't fire in an
>>unbound form (e.g., OnCurrent, BeforeUpdate, BeforeInsert, etc.).
>>You have to recreate them in some form if you need them, and that
>>is a real pain, since there is often no single place to call them
>>from, as your example of replacing the Dirty property shows
>>perfectly.[/color]
>
> I guess I'm into semantics then. My "unbound" forms are actually
> bound - only to work tables in C:\Temp.[/color]

Well, that's not unbound by any definition that makes any sense to
me.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: Unbound form questions

Per David W. Fenton:[color=blue]
>Well, that's not unbound by any definition that makes any sense to
>me.[/color]

Technically, I'd have to agree.... Functionally, however, the essence of
unbound forms to me has always been that the user is not "in" the back end while
working with the data.

But you're right, I've got to overhaul my use of the term "unbound".
--
PeteCresswell

re: Unbound form questions

"(PeteCresswell)" <x@y.Invalid> wrote in
news:84t462h39os6ut5bl3gf0pv677k72eneu2@4ax.com:
[color=blue]
> Per David W. Fenton:[color=green]
>>Well, that's not unbound by any definition that makes any sense to
>>me.[/color]
>
> Technically, I'd have to agree.... Functionally, however, the
> essence of unbound forms to me has always been that the user is
> not "in" the back end while working with the data.[/color]

To me, unbound means that the form and its controls are not bound to
the fields in a table. That the table is not part of the live data
set is simply irrelevant to the idea of being bound or not.

The two sets of problems that exist with real unbound forms and with
using temp tables for editing are completely different and I see no
utility in porting the concept of "unbound forms" over to the temp
table scenario.
[color=blue]
> But you're right, I've got to overhaul my use of the term
> "unbound".[/color]

Either that or you need to explain that you're using a different
meaning of the term.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/