Need help with Access decision

[aualias]

I am not primarily an Access developer. My background is in C++ and coding
with the Windows API. Currently I am doing mostly C# development.

I have been working with a client with a small to medium sized investment in
Access. The application is not well written and I am finding Access to be
unstable. Here are a few of the problems:

· frequent crashes
· difficulty keeping track of the context in windows containing child
windows
· difficulty controlling when data is entered into the database (for
example, when the user wants to cancel)
· code that crashes, produces errors, etc. until I run the /decompile
switch - then it works
· I have saved, compiled, and run code only to find that all my
"saved" changes are gone
· changing the size of a group box cause a size increase of 1mb in
the mdb file

My gut feeling is that every addition to the user interface makes the
application less stable. They only need a few new features at the present
time, but anticipate continuous small changes in the future.

I could probably rewrite the whole thing in a few weeks using C# and Windows
Forms. However, this is a small company and I hesitate to recommend this
type of expensive development. Also, it would lock them into C# or VB.NET
development which might be more expensive.

So, my questions are:
· Is Access as unstable as I think it is? (the user interface, not
the actual database)
· How does one judge when to move away from Access? In this
application:
o the code is poorly written
o the user interface kinda works, but is clunky and does not allow
the user to cancel operations easily
o validation of data input is poor throughout the app
o the application is not extremely stable
· Is it inevitable that Access user interfaces will be moved to some
other technology?

To be honest, I do not know if most of my problems are due to lack of
experience with Access. I am used to developing in an environment where
everything is more transparent. However, I have talked to several people
who do not think Access is very stable.

Any comments would be appreciated. Thanks.

Alfredo

 

[Kevin3NF]

So, my questions are:

· Is Access as unstable as I think it is? (the user interface, not
the actual database)

Not when designed correctly.

· How does one judge when to move away from Access? In this
application:

Generally when size of the db or number of users becomes too high for the
Jet engine to work well...

o the code is poorly written

This can be avoided

o the user interface kinda works, but is clunky and does not allow
the user to cancel operations easily

Can be fixed in most cases

o validation of data input is poor throughout the app

Can also be fixed

o the application is not extremely stable

All sorts of possibilities here...first thing to check is: is the database
split, and does each user have a copy of the FE on their desktop.

· Is it inevitable that Access user interfaces will be moved to some
other technology?

I would guess that Access will be around in various forms for a long time
given the number of Access apps out there.

You are correct that locking a small-to medium sized business into a c# or
vb.net developmnet platform is probably a bad thing to do. Not knowing more
about the application, it sounds as if fixing or re-writing in Access may be
the easiest thing for them down the road.

If you want a general conversation about Access and its limitations, please
post back or even contact me offline.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

 

[Lynn Trapp]

Let me add something to what Kevin and Joseph said. You don't say anything
about where this database is stored. I'm assuming it is on a server. If that
is the case, do each of the users have a copy of the frontend on their own
PC? The vast majority of corruption problems are the result of network
traffic and/or problems. Putting a copy of the frontend on each PC will
greatly reduce the need for network traffic.

 

[Albert D. Kallal]

I am not primarily an Access developer. My background is in C++ and coding
with the Windows API. Currently I am doing mostly C# development.

I have been working with a client with a small to medium sized investment in
Access. The application is not well written and I am finding Access to be
unstable. Here are a few of the problems:

Poorly written software is simply poorly written software. I been
consulting, and writing software on a daily basic for 20 years now. I have
used a LOT of development platforms. Many were mini-and main frame based (I
still consult for those systems). I also consult to company's that develop
and built products around the office application suite. That generally means
VB, and VBA

On the pc side, I have written in assumer, and even written payroll systems
in Pascal without the aid of a database system (had to write my own).

I have coded on just about every platform I can think of. I have used a
good number of pc based database systems. They go all the way back to
Reflex, DataEase, KnowledgeMan, Advanced Revelation, and of course the
veritable dbaseeIII, and FoxPro.

I also currently have software of mine running on Linux computers in 6, or
more counties around the world as I speak.

So, given the above..what is my experience with ms-access?..

· frequent crashes

No, I have not experienced the above at all. I have clients running access
applications, and in most cases they are MORE reliable then email, word, or
most applications that they run on a daily bases. About the ONLY thing you
need to ensure is that the updates to JET, and to the particular version of
office/access are installed. I suppose it goes without saying that service
packs,a nd updates are required to anyone who is serous about software
development, and takes at least SOME pride in their workmanship. (so, I
doubt I even have to point out that ensuing updates tot he software tools
need to be installed).

· difficulty keeping track of the context in windows containing child
windows

Gee, not sure what you mean by the above. As always, when a developer is
faced with a new tool, they instantly blame the tools, and not their lack of
knowledge, and how the tools is to be used.

Ms-access follows the MIDI interface that Excel, and word have had for about
10+ years. Only since office 2000 has Microsoft started to move away from
the MIDI interface..and gone to SDI. However, I find control of focus and
ease at which forms follow each other VERY easy in ms-access. I would say
that 80% of my forms are model..but that much my preference, and my desire
to "control" users.

I also going to mention that trying to figure out ms-access which has a FAR
steeper learning curve then VB does can NOT be learned in quick afternoon.

I would consider finding a good developer to evaluate this situation for
you. Anytime you get involved in a project, you need someone with some real
good skills.

Here is a great quote on the Sven Stages of Expertise in Software
Engineering

The general levels of expertise are:

<quote>

Stage 1 Innocent (never heard of the product)

Stage 2 Aware (Has read an article about X)

Stage 3 Apprentice (has attended a three-day seminar)

Stage 4 Practitioner (ready to use X on a real project)

Stage 5 Journeyman (uses X naturally and automatically in his job)

Stage 6 Master (has internalized X, knows when to break the rules)

Stage 7 Expert (writes books, gives lectures, looks for ways to extend x)

</quote>
Page-Jones, Meilir. "The Seven Stages of Expertise in Software Engineering",
American Programmer, July-Aug 1990

One should NEVER attempt a project with a team consisting with Stage 3 or
lower people. This is a sure fire formula for failure. The team can consist
of stage 4's, but they should have at least access to Stage 5, or 6.

· difficulty controlling when data is entered into the database (for
example, when the user wants to cancel)

There are a number of events on the form that you need learn about (before
update is a good example). If you are talking about a general form, and
controlling the update..there is as a general rule FAR better controls here
then what you have in environments like VB.

The ONLY exception to this rule would be when you use sub-forms. (you can't
wrap forms, and sub-forms in a transaction). So, if you are just talking
about a general form, and controlling the update, I have to disagree with
you. If you are taking about sub-forms..then yes..I agree, this is a weak
spot.

· code that crashes, produces errors, etc. until I run the /decompile
switch - then it works

The production version of your software should be a mde file..not a mdb.
And, this mde is placed on each pc..(right?). Or, are we even talking about
a multi-user application here?

· I have saved, compiled, and run code only to find that all my
"saved" changes are gone

Hum, have not experienced the above. However, as mentioned. access 2000 in
my option was NOT very stable until the service packs came out.

My gut feeling is that every addition to the user interface makes the
application less stable. They only need a few new features at the present
time, but anticipate continuous small changes in the future.

I find that for small applications, adding forms is not a problem. For
example, I have a small sized application in ms-access. It have 160 forms,
and about 100 reports. it is had a modest amount of code (27,000 lines). (of
course, in a traditional environment like VB, or c#, you don't get 160 forms
very easily..as those 3 months would take 12 or more months to develop the
same thing). I should note that the resulting application is less then 6
megs in size, and this mde can be zipped onto a floppy disk.

What truly great is that to install the application we simply copy it to the
target pc (we had x-copy development for 10 years now..and the folks in .net
are JUST starting to rant and rave about the wonders of such a setup. We
done this for 10+ years!).

It is possible that your application has far more then 160 forms, and is
rather large. However, even up to the 250 to 300 range for forms, you are
still talking about a fairly small mde file.

I could probably rewrite the whole thing in a few weeks using C# and Windows
Forms.

You cold, but then again, you will spend about 2 to 3 times the effort as
compared to ms-access. Some people rate the development speed in ms-access
much higher..but lets just agree on the low end of the spectrum in terms of
RAD tools. You are talking about a good 3 times the amount of work to
accomplish the same functionality here.

So, my questions are:
· Is Access as unstable as I think it is? (the user interface, not
the actual database)

I have found when you ensure the target pc has a good install of access,
then the resulting application is very stable.

· How does one judge when to move away from Access?

I guess it depends on if it is worth saving. In the summer of 2002, I did
consult for firm that had development a application in VB. It really was a
disaster and part of the problem was poor designs, but worse was that so
much code was used to control referential integrity on the data when the
database engine can do so much of this kind of thing. These most offenses
applications I seen are ones written in VB, or c++ that try to do data
management when tools like ms-access would have been much better. They are
going to keep the application...but man, what a mess! (good developers do
NOT necessary make good database developers).

· Is it inevitable that Access user interfaces will be moved to some
other technology?

Well, what other technology..and at what cost? I mean, we had ms-access out
for 10+ years, and the team is hard at work on the next version (can't say
that about VB6..can we now?). I think the real issues are of how many
desktops are you talking about here? (vb, and .net applications have
advantages when the team of developers grows in size, and also the number of
desktops you plan to support also is gong to be large.Further, as the
project gets larger, then use of class objects etc becomes more important as
developers much work with each other).

I have good document I wrote on converting a application from one platform
to ms-access. You might give the document a good read, as it does have some
good info on the processes involved.

http://www.attcanada.net/~kallal.msn/Articles/fog0000000003.html

 

[Fred Boer]

Dear Mr. Meehan:

Very true and I believe the more you know databases before the harder it
is to move into Access.

Forgive me, but I'm a little confused by this statement. Are you saying that
the more you know about databases before beginning to learn Access the
harder it is to learn Access?

Thanks!
Fred Boer

 

[Albert D. Kallal]

Albert D. Kallal wrote:
...

Very true and I believe the more you know databases before the harder it
is to move into Access.

--

I will say that ms-access has some quirks, but you can use the product with
sql-server, or <insert your favatore database engine> here.

My above point referees to that on initial use, ms-access forms are EASIER
then VB forms. However, you need to spend time learning the ms-access forms
event model, and the very large number of properties that are associated
with ms-access forms (that VB forms do NOT have). Thus, my point was is that
MORE time is needed to learn the complex event model that ms-access has.
Once the ms-access model is learned..then (and only then) does the
productivity of the developers really go up over tools like VB.

Further, I don't really consider ms-access a database. Really, all it is a
forms developer wrapped around the VB language. You database is going to be
JET, Sql - server..or whatever you use. Ms-access is thus only a client
development tool to your favorite database.

I can't really say, or think of any real major issue that prevented me from
learning ms-access, or anything that made it difficult based on my numerous
experiences with other database systems. All ms-access is a collection of
Microsoft technologies (ado, dao, VB etc). Developing two tried applications
in ms-access is not such much different then say VB or whatever, it just
that you have a bound forms model.

How does previous database experience make learning tools like VB, or c++,
or c#, or ms-access more difficult? All of these products simply allow you
to develop the UI to the database engine of your choice anyway.

I may be miss understanding your point here. I suspect I view ms-access as
only a client tool, where as you view it as the database product it self
(this would explain your point of view).

 

[david epsom dot com dot au]

1) The Access 2000 development environment was and is a bit unstable.
By reputation, the 2002 and 2003 versions are a bit better. What are
you using?

Because the IDE is a bit unstable, it can damage your VBA project.

If you do development in Access, you need to learn to avoid things
that can crash your development environment. Basically, Close and
Save frequently, close objects when switching between design and
data view mode, decompile and compact occasionally, avoid situations
that cause code exceptions. You also need to ensure that your
Windows environment is stable, including any patches you have
applied.

There are also a few things that can crash the runtime environment:
basically damaged installations of Access or required components,
which can in particular cause Access to crash instead of displaying
an error message when a code exception occurs. Basically, you need
to ensure that your Windows environment is stable, and that you fix
any coding bugs before deploying the application.

2) I don't have any difficulty keeping track of context: I guess
that must be just familiarity.

3) Cancellation is tricky in Access: By default Access will
automatically commit stuff that you type in. If you don't
want this automatic action, you may have to give up on some
of the 'free' features of Access, (like forms bound directly
to your data tables), and do some coding yourself. All of
my critical data entry is done on local tables: the result
is committed to the remote data by coded actions. On the
other hand, all of my static data uses forms directly bound
to live data: the tables are small, and if the user doesn't
like what they get, they can change it back again.

4) Our large application has now well over 1000 forms, and
is commercially deployed: we are not experiencing any
stability problems.

5) Some of our stuff is written in C++ and VB. The user
interface on all the VB stuff is clunkier than in Access:
it's just a lot more work to get the basic stuff going,
and there never seems to be client interest in spending
money on making the interface look good. The user interface
on all the C++ stuff is better than in Access: Firstly,
Access ties you to a particular version of the MS interface,
so you aren't using the latest and greatest look, secondly,
the kinds of clients we get who choose C++ are so in love
with the product they are developing that they are willing
to hire graphics people to get the look right.

(david)

 

[aualias]

Hi Albert,

As I said, this is a poorly written app. Here is an example of keeping
track of the focus...

There are clients and clients have jobs. On on form the client data is on a
form and the majority of the data entry is for a job on a subform. The
close button is on the main (outer) form. The form also has a button for a
new job which will appear on the subform where the jobs that were entered
appear.

They (my client) want to be notified by email when a new job is entered
(i.e. when the user clicks the close button after entering a new job). The
only way to get to the new job is to look up an existing job and click the
new job button. So, when a new job is entered the data goes into the
database correctly. When the form closes an email is created and sent, but
all the job data is from the job that was there before the new job button
was clicked.

I ended up storing the data in global variables to be able to retrieve the
values. Pretty ugly and hell to maintain. I have no idea why closing the
outer form reverted back to the previous data in the subform.

Alfredo

 

[Albert D. Kallal]

They (my client) want to be notified by email when a new job is entered
(i.e. when the user clicks the close button after entering a new job). The
only way to get to the new job is to look up an existing job and click the
new job button.

It would seem that should first look up a client name. That client form
would thus display a list of jobs, (in the sub-form..right?).

So, when a new job is entered the data goes into the
database correctly. When the form closes an email is created and sent, but
all the job data is from the job that was there before the new job button
was clicked.

Huh? I don't understand the above problem at all. Something seems wrong with
the UI here?

You can force a disk write with me.refresh. That will force the data to
disk, and you can then do what you please.

I mean, obviously, if you don't move to another record, then how does the
current data get written to disk?

Also, what record has the focus in the sub-form? If you do a requery (force
a re-load) of the data in eh sub-form, you do loose your position in that
form.

It seems to me that you simply write out the data, and likely save the "Id"
of the record with the data you want. You can then pass the single ID to
your routines that works to email this (I have no idea why you are using
global vars..but like in any system..you SHOULD avoid that). Can you not
simply pass the forms object to that email routine? So, even better is to
pass the current forms object to those routines. Those routines will then
enjoy full access and contorl of that forms object.

Call MyCoolRouinte(me)

The above routine would pass the current forms object to a routine. That
routine can be:

Public Sub MyCoolRoutine(frm as form)

msgbox "last name is " & frm!LastName

Tell the forms object to write the data to disk

frm.Refresh

.....do whatever....


I don't see any need for globals here?

 

[aualias]

David,

I am using Access 2002. I have learned to close things constantly, back up
the mdb file frequently, compact, and sometimes use /decompile and I have
had better luck with the crashes (although they are not eliminated). My os
is up to date with all the patches.

I do have to admit that having "Compact and Repair Database" in the menu
makes me think that something in the design of Access did not go well...

I explained one of my "keeping track of context" problems answering another
reply. Basically, my client's application attempts to pack as much as
possible on each form (especially the one I am mainly working on).
Originally, the form I have had trouble with had one table behind it. Now
it has a join because they want data across tables available to edit. I
think that this affected the stability of the application, but am not 100%
sure. I am getting the impression that making the forms simple is a key to
Access development. Do you think this is true?

My client also wants to be able to cancel things easily. In C# this is
trivial. In Access it seems that I need to hand code to make this appear
natural. If so, what do I gain over C# (which I know far better than
Access)?

When you say that your critical data is done on local tables, are you
creating temporary tables and moving to the "real" tables when the user
commits? Do you do this to simulate a transaction? Make cancellation
possible? More?

Thanks for your comments.

Alfredo

 

[aualias]

I'll try to explain - bear with me...

On the main menu you click "View a Client/Job"
A dialog allows you to view all the jobs by the client you choose
Double click a job and you get the form I am working on. It shows the
client information and the job information (in a large subform).
The close button is on the outer (client) form.
One of the buttons on the subform is "Add a new job". Clicking on this
button replaces the selected job with a new job.
You can add data to this new job, clicking the close button saves it.

In the subform (the new job) handler there is a call to the function that
sends the e-mail. The function gets the data from the current form (which I
expected to have the data for the new job). Unfortunately, the current form
at this point contains the data from the job that was selected before "Add a
new job" was clicked. Passing the form reference (Me) to the function would
just pass the wrong data with the form object.

Do you have any idea of what could cause the context to revert to the old
data? It probably has something to do with the outer form closing before
the subform. In any event, something is confused here and I think that the
form needs to be redone in a much cleaner manner.

Alfredo

 

[david epsom dot com dot au]

Do you have any idea of what could cause the context to revert to the old

data? It probably has something to do with the outer form closing before
the subform. In any event, something is confused here and I think that

the

Yes, you can't use a form after it is closed: that's like using
memory after it is released in C++: It may look right, It may seem
to work right, but it's wrong and immoral anyway. The on-close event
is too late for reading data from a forms recordsource - although
obviously the code object still exists at this point.

(david)

 

[david epsom dot com dot au]

sure. I am getting the impression that making the forms simple is a key
to

Access development. Do you think this is true?

There are lots of different ways to do simple things in Access.
An office worker with no programming experience can throw together
a useful business application that gets the job done.

But for complicated things, there may be really only one way to
get it to work, and all the other simple ways lead to dead ends.

For the developer, there are three obvious paths:

1) Just do things that are easy. Access is a fairly full
application environment, and you can do pretty much anything
you need, just not all you want.

2) Seek the arcane knowledge, and bend Access to your will.
I have a form that is useful in both Form View and Datasheet
view. I'm proud of it, but it was a stupid idea. I could have
just built two forms.

3) Use Access as a RAD environment. We started with lots
of simple forms, and where we need more complicated
processing, we choose to code some features. For example,
cancelling data entry is trivial. You just have to code
it, as you would in any other library/language.
Data Validation is trivial: you get form level data validation,
field level data validation, and table level data validation.
We bind our forms to local tables to get field level data
validation because that works so well. It also simplifies
the next step, which is updating the data tables.
The local tables are not temporary: they are a fixed part
of the interface design. Updating the data tables in not
a 'simulated' transaction: the update actions are done as
an actual, real transaction. Simple atomic cancellation is
one of the features of this system. Yes, data is cleared
out of the local tables.

There is no particular gain for us in doing these forms
in Access instead of C# (although the situation could be
different if you were doing a one-off application instead
of full time development). But there is an advantage for
us in doing the Application in Access instead of C#. We
spend much more of our time doing simple forms and complex
reports than we ever spent working on the data handling of
the few forms that are interfaces to large complex data
systems. Your mileage may vary.

(david)

PS: Compact and repair is just normal database admin,
consolidated into one menu item: Update database statistics,
check validity of indexes, check database state, sort
clustered tables etc. Historically, 'repair' mostly meant
checking for partial transactions, caused by network dropouts.

 

[Albert D. Kallal]

I'll try to explain - bear with me...

You can add data to this new job, clicking the close button saves it.

Which close button here? you mean on the main/parent form?

Do you have any idea of what could cause the context to revert to the old
data? It probably has something to do with the outer form closing before
the subform. In any event, something is confused here and I think that the
form needs to be redone in a much cleaner manner.

I would say that you can't really use the close event of the sub-form.
Further, what happens if the users does NOT close, and decides to add
another record? How then do you catch this? (the answer follows).

My guess is that the close event in a sub-form is way way too late to even
try and deal with that problem. Data is long ago written, and we even went
past the forms un-load event (that occurs before the close).

I would suggest you use the after update event. The beauty (advantage) of
this event is that ONCE all your verification , and error checking is done
(which by the way goes in the before update event), then in the after update
event..I would then pass the values..or "me" to your other routine. It seems
to me that "after you update", you want to send the email. So, the after
update event sounds about right..and is even appropriately named!

As you seem to have it now..what happens if the user instead of closing the
form decides to whack add again?

Even worse, what happens if the users DOES NOT add new record, and you whack
close? How the heck do you now know a new record was added, (or was not
added?). (lets hope a whole whack of code was not written to manage this..as
the forms event model handles this with ease, and little code..if any).

Again, using the after update event seems about right here..and of course
the event does NOT get fired if a new record is not added.

As I said before, the event model in ms-access is far more complex the lame
VB forms. These events when used correctly can save you hundreds and
hundreds of lines of code (note my above comments about where data checking
goes..there is a event JUST for that).

While it might be some what of a surprise that a sub-form is re-set to the
first record (or likely re-queried) when you close the parent form, I can't
really see any design that going to work by using the close event anyway for
the above reasons (ie: record was not added, or more then one record was
added, or perhaps even a record was deleted). It seems to me that the after
update event of the sub-form is the way to go.

Trying to pull out data of form when you ALREADY executed the close event
sounds quite harsh. I mean, exactly when do you expect the form to go out of
scope (and, worse..how will you control this?).

A good deal of event driven programming means you have to think through the
event que.

Use the sub-forms after update event, and don't even worry about the close
event. Fact is, the user might not close..but might add another record, and
you need to deal with that (so, before update handles this well)......

 

[aualias]

You can add data to this new job, clicking the close button saves it.

Which close button here? you mean on the main/parent form?

Close on the parent form.

I would say that you can't really use the close event of the sub-form.
Further, what happens if the users does NOT close, and decides to add
another record? How then do you catch this? (the answer follows).

Heh, heh...
All I can say is that I did not design it this way (do I sound like a
politician?). Much of this application relies on the user to do the correct
thing. Sometimes a mistake is costly...

My guess is that the close event in a sub-form is way way too late to even
try and deal with that problem. Data is long ago written, and we even went
past the forms un-load event (that occurs before the close).

I would suggest you use the after update event. The beauty (advantage) of
this event is that ONCE all your verification , and error checking is done
(which by the way goes in the before update event), then in the after update
event..I would then pass the values..or "me" to your other routine. It seems
to me that "after you update", you want to send the email. So, the after
update event sounds about right..and is even appropriately named!

I had thought that the update event was fired each time the data on the form
was updated. That would send a lot of email! Part of the problem that I am
having with Access is that the online documentation is not great; I did
actually look up the update event. I believe you when you say that this is
the correct place, but I'm still going to test it...

As you seem to have it now..what happens if the user instead of closing the
form decides to whack add again?

Even worse, what happens if the users DOES NOT add new record, and you whack
close? How the heck do you now know a new record was added, (or was not
added?). (lets hope a whole whack of code was not written to manage this..as
the forms event model handles this with ease, and little code..if any).

Again, using the after update event seems about right here..and of course
the event does NOT get fired if a new record is not added.

As I said before, the event model in ms-access is far more complex the lame
VB forms. These events when used correctly can save you hundreds and
hundreds of lines of code (note my above comments about where data checking
goes..there is a event JUST for that).

While it might be some what of a surprise that a sub-form is re-set to the
first record (or likely re-queried) when you close the parent form, I can't
really see any design that going to work by using the close event anyway for
the above reasons (ie: record was not added, or more then one record was
added, or perhaps even a record was deleted). It seems to me that the after
update event of the sub-form is the way to go.

Trying to pull out data of form when you ALREADY executed the close event
sounds quite harsh. I mean, exactly when do you expect the form to go out of
scope (and, worse..how will you control this?).

I expected the close event to be a wrapper around the WM_CLOSE Windows
message. It is a signal for the window to close and you can cancel the
closure by not processing his message. As far as I know, every other
development environment wraps WM_CLOSE with a close event.

In Access, this is not the case. Obviously, I have to familiarize myself
with the event model and not make assumptions.

A good deal of event driven programming means you have to think through the
event que.

Use the sub-forms after update event, and don't even worry about the close
event. Fact is, the user might not close..but might add another record, and
you need to deal with that (so, before update handles this well)......

Thanks Albert. I am getting a lot of very good feedback from all of you. I
am convinced that this should stay an Access application. The client will
just have to spend some money on a redesign of the worst parts if they want
something that they will be happy with. They are on a tight budget; so far,
they have only asked for additions and small fixes.

Alfredo

 

[aualias]

David,

the

Yes, you can't use a form after it is closed: that's like using
memory after it is released in C++: It may look right, It may seem
to work right, but it's wrong and immoral anyway. The on-close event
is too late for reading data from a forms recordsource - although
obviously the code object still exists at this point.

I assumed that the close event wrapped the WM_CLOSE message and it's
functionality. If it did, everything would still be there. As has been
pointed out to me, the unload event is called before close.
Now I see the problem...

AU

 

[Albert D. Kallal]

I had thought that the update event was fired each time the data on the form
was updated. That would send a lot of email!

yes, I agree 100% However, how is that problem managed now? How does this
application decide that a new record was added vs an existing one being
edited?

We seem to be concentrating on the close event of a form..but not yet have
dealt with more then one record being added?

I expected the close event to be a wrapper around the WM_CLOSE Windows
message. It is a signal for the window to close and you can cancel the
closure by not processing his message. As far as I know, every other
development environment wraps WM_CLOSE with a close event.

In Access, this is not the case. Obviously, I have to familiarize myself
with the event model and not make assumptions.

Well, actually each control on ms-access does NOT have a windows handle
(access fakes this!), but a access form does. However, using the wm_close is
far to late for the data stuff. (and, besides...I still DO NOT see a
strategy with using the close event if the user whacks the add button? How
will that scenario be handled? So, we can kind sit here and talk about the
problem/issue of using the wm_close, or even just the close event of the
ms-access form, but that does NOT provide I a solution to users that add
more then one record.

Anyway, since ms-access forms are data bound, then we have got events for
the data part, and also the forms part. So, for the form, we have the
un-load event, when CAN be canceled, and it will prevent the form from
closing (and thus the close event will not fire). So, often in most cases
with access forms you got far more granularity and control (so, we have both
un-load, and close). In most traditional environments you only have one
event to deal with this.

The exact opposite (symmetry) occurs on a form open. You got on-open, and
on-load. In the on-open event, you can't yet modify any values on the form.
But, you can examine values, and you can STILL cancel the form load! That
means error checking, or even code to check for disk locks, or other issues
(like no data!) can be tested for. If you set cancel = true..then the form
does not load (this is a ROYAL pain in most other environments, as either
you have to test things BEFORE you try and load the form. We can put the
code WITH the form..and NOT have to have a routine outside of the form (or
play with a whole bunch of visible/in-visible stuff. The access design
eliminates all of these design problems). Of course, once the form loads,
the on-load event now fires. The on-load event thus had setup code, for
defaults, variables initializing etc. (you CAN modify controls on the screen
at this point). Once again, having two events is not only a brilliant
designs, but it also means you have two different places for code. There is
a division now between the code that checks things on opening a form, and
the setup code for a form. (this also makes it easer for a developer to jump
to, and find the correct code for the correct task. I know as a developer
that I will look in the forms on-load event for setup and initializing code.
I know as a developer that any code to prevent the form from loading, or any
code that will stop the form from loading is in the on-open event. I REALLY
wish other platforms had this type of division. Simply billet this design is
(and is more then 10 years old now!).

Thanks Albert. I am getting a lot of very good feedback from all of you. I
am convinced that this should stay an Access application. The client will
just have to spend some money on a redesign of the worst parts if they want
something that they will be happy with. They are on a tight budget; so far,
they have only asked for additions and small fixes.

In looking at your problem, I am going to suggest using the before update
event (not the after). You can grab the data, but in fact it is actually NOT
yet committed to disk. However, the advantage of the before update is that
you can go:


if me.NewRecord = True then
bla bal bal
end if

If you use the after update, then newREcord is NOT true anymore.

So, my first suggestion was after update, BUT since you need to check for
new records, then I would use the before update event. What is VERY
interesting here is that all values of the form are available in the before
update event..but they are NOT yet committed to disk!

 

[aualias]

Albert,

From the documentation, it seems that the Unload event is the place to send
an e-mail. Since you can cancel the form's closing in that event, all the
data should still be there and the user cannot enter more.

To be honest, this form does work now (with my terrible kludge - global
variables for the e-mail). To fix the many problems correctly, my client
will just have to spring for some redesign. When they click the button to
enter a new job, it should certainly not be in the same window as the job
that they are on. That way, if they click on the new job button again it
will just pop up another new job window. Also, I like David's idea of local
tables for entering/updating data. That way they can accept or cancel all
input as a transaction.

You all have convinced me that they should not move away from Access unless
their needs change dramatically. I shall let them know. You have also
convinced me that being able to write COM code in C++ does not exempt me
from biting the bullet and learning the tools when working with Access. At
a bare minimum I have to know the event model and write some test apps to
see exactly how things work.

My client, as do many, does not want to spend too much money on this
application on which their livelihood is based. I am working for a friend
who is trying to keep the price down so he can keep the client. It has not
inspired me to spend much time in getting up to speed. Now I have to lay
down some conditions that I can work under. They do not have to pay for my
learning curve, but they do have to give the ok to enough hours to
restructure the application and to make the job worth doing.

Thanks to all of you. It's been a great help.

Alfredo

 

[Tony Toews]

When they click the button to
enter a new job, it should certainly not be in the same window as the job
that they are on. That way, if they click on the new job button again it
will just pop up another new job window.

Why have two forms, one for new jobs, one for existing jobs? I will
frequently put a New button on a form which does nothing more than
Docmd.GotoRecord acNew. Warning, air code. Or am in misunderstanding
something?

Or on a search form I will add a button to open the data entry/view
form but in Add mode.

Also, I like David's idea of local
tables for entering/updating data. That way they can accept or cancel all
input as a transaction.

Again why? I've never seen the use for this unless you're dealing in
an environment where the user, such as a person in a call centre, is
dealing with someone who changes their mind a lot? Surely there is a
reason to enter the data in the first place.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Tony Toews]

4) Our large application has now well over 1000 forms, and
is commercially deployed: we are not experiencing any
stability problems.

You haven't come across the following problem then?

Note that there is an unusual problem when creating MDEs.

ACC97: "Microsoft Access Was Unable to Create an MDE Database" Error
Message 177620
ACC2000: "Microsoft Access Was Unable to Create an MDE Database" Error
Message 202304

A97 allows a total of 1024 open TableIDs at a time, A2000 2048. Thus
if you have somewhere close, no definition of close, to 1024 objects
in A97 with code then you could run into troubles.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm