Access 2007 Technical Questions

[Ronald Dodge]

I have exerted a lot of energy in the past testing things out in Access
2000/2002/2003, and here's the issues that I have faced.


Issue 1: Error checking isn't user friendly (Very Critical)

The biggest issue that is the very reason why I have had so many other
issues in Access is the fact that Access is not very user friendly when it
comes to error checking, especially for mouse users. Most times, you want
the validation rules to run, but there are a minor few possible reasons why
you may want to bypass the validation rules such as user clicking on the
"Help" command button (if there is one on the form), resetting the form, or
even backing out of the form as 3 possible reasons why to bypass the
validation rules. However, when the user goes to click on a command button
on the form, all validation rules on the active control are processed, and
if any of those validation rules causes the control to be in error, the
Enter Event doesn't even take place on the command button cause the focus is
forced to stay on the active control rather than to transfer to the command
button.

To get around this issue, I had to mimic the CausesValidation Property and
Validation Event as has been setup in VB6, which meant in order to get
around this issue, I had to UNBIND every single form and control else this
wasn't going to work. It took me a total of 3 full months to do the R&D to
get around this issue. Therefore, I ask, "Does Access 2007 have the
CausesValidation Property and Validation Event, that is comparable to VB6's
property and event?"


Issue 2: EditMode within DAO programming loses it's value for no apparent
reason (Critical)

Initially, I was using DAO to get around the issue above as in order to get
data to the any of the BE databases with all forms and controls unbound, can
only use ADO or DAO programming language to manipulate the data. However,
there were instances that I found, when the code initially put the recordset
into Edit Mode, even with the variable set at the module level of the form,
before it even reaches the Update Method, the EditMode property no longer
has it's enum value that it suppose to have. This more or less has rendered
DAO programming useless.


Issue 3: ADO programming doesn't allow for DynamicCursor Keyset against a
Jet Engine (Critical)

This one is actually documented, so I ask the question, how is one suppose
to setup a recordset using a Jet Engine to be able to see changes within it
when changes do take place? Without this capacity, it has rendered ADO
programming useless. Does Access 2007 address this issue?

There is a 4th issue as well, but it's the above 3 issues that has more or
less turned me against from using Access as with those issues above, Access
is not a worthy DB program for a multiple user environment. I was going to
have my own financial stuff stored within Access, and then allow my wife
some capacity to record some things, but with the issues above, it's not
stable enough to have something like that setup. Instead, I am still using
Excel for all of my financial planning and tracking stuff, which that has
gotten to be so large, I have already broken that 1 file down into 4 files
and it's about to get split out to a 5th file with the data grouped in the
following categories:

Economy (This contains information about the economy including economic
historic data, which impacts inflation, thus also impacts the spending side
of the equation)

Debt

History (Our own financial history)

Investments (This isn't actually broken out yet, but it soon will be)

Planning and Summary (This file pretty much contains everything else that
doesn't fit into any of the above categories and currently also contains the
investments)


Issue 4: Listbox properties doesn't update until it has lost focus when
it's allowed to have multiple selections (Moderate)

This issue caused a lot of problems for me as well. Without addressing this
issue, records wouldn't be updated properly. To get around this issue, I
had to create a class module and put in a set of codes to more or less be
able to record all of the various behaviors, just so as I can get an
accurate picture of what's taking place with the listbox. All of the things
done to the listbox is retained, but it's properties are either not updated
or if they are, the properties are only returning their old values, similar
to what happens when a DB transaction is taken place by recording, but prior
to those transactions to being committed, they are only in a temporary
storage, but once committed, the tables are updated with the recorded data
from the transaction.

Of course, this meant that I had to learn every single behavior of the
listbox from using both methods, mouse and keyboard. Not only that, but I
found in few circumstances with the right combination of the keyboard and/or
mouse actions on the listbox, the listbox itself freezes up and does nothing
else, which the only way to get away from that issue has been to back out of
the form, and then go back into the form.

Sincerely,

 

[Guest]

Neither Access nor any other database application is perfect; however,
reading your post I get the sense that rather than learning how to use Access
correctly, you have your own preconceived notions about how it should be and
are trying to force fit it to your way of thinking. Some notes on you
issuses follow:

I have exerted a lot of energy in the past testing things out in Access
2000/2002/2003, and here's the issues that I have faced.


Issue 1: Error checking isn't user friendly (Very Critical)

The biggest issue that is the very reason why I have had so many other
issues in Access is the fact that Access is not very user friendly when it
comes to error checking, especially for mouse users. Most times, you want
the validation rules to run, but there are a minor few possible reasons why
you may want to bypass the validation rules such as user clicking on the
"Help" command button (if there is one on the form), resetting the form, or
even backing out of the form as 3 possible reasons why to bypass the
validation rules. However, when the user goes to click on a command button
on the form, all validation rules on the active control are processed, and
if any of those validation rules causes the control to be in error, the
Enter Event doesn't even take place on the command button cause the focus is
forced to stay on the active control rather than to transfer to the command
button.

Access, being part of Office, caters to the casual user. Validation Rules
are there so that a non developer can apply a rudimentary level of error
checking. Most professional developers do not use Validation rules. They
code their own to make the user friendly.

To get around this issue, I had to mimic the CausesValidation Property and
Validation Event as has been setup in VB6, which meant in order to get
around this issue, I had to UNBIND every single form and control else this
wasn't going to work. It took me a total of 3 full months to do the R&D to
get around this issue. Therefore, I ask, "Does Access 2007 have the
CausesValidation Property and Validation Event, that is comparable to VB6's
property and event?"

Using unbound forms is a typical mistake made when a user doesn't fully
understand how to use them. If you understand the event sequence, the
behaviours of the events, and how to properly address the issues.

Issue 2: EditMode within DAO programming loses it's value for no apparent
reason (Critical)

Initially, I was using DAO to get around the issue above as in order to get
data to the any of the BE databases with all forms and controls unbound, can
only use ADO or DAO programming language to manipulate the data. However,
there were instances that I found, when the code initially put the recordset
into Edit Mode, even with the variable set at the module level of the form,
before it even reaches the Update Method, the EditMode property no longer
has it's enum value that it suppose to have. This more or less has rendered
DAO programming useless.

I have not encountered this issue with the edit mode, perhaps because I
don't have to do as much coding to manipulate the data. This problem sound
like a byproduct of using unbound forms.

Issue 3: ADO programming doesn't allow for DynamicCursor Keyset against a
Jet Engine (Critical)

This one is actually documented, so I ask the question, how is one suppose
to setup a recordset using a Jet Engine to be able to see changes within it
when changes do take place? Without this capacity, it has rendered ADO
programming useless. Does Access 2007 address this issue?

Here, I agree with you. I don't much care for ADO and I only deal with it
if I have to work on someone else's application.

There is a 4th issue as well, but it's the above 3 issues that has more or
less turned me against from using Access as with those issues above, Access
is not a worthy DB program for a multiple user environment. I was going to
have my own financial stuff stored within Access, and then allow my wife
some capacity to record some things, but with the issues above, it's not
stable enough to have something like that setup. Instead, I am still using
Excel for all of my financial planning and tracking stuff, which that has
gotten to be so large, I have already broken that 1 file down into 4 files
and it's about to get split out to a 5th file with the data grouped in the
following categories:

Access is a worthy multi user environment when properly implemented by a
professional.

 

[Ronald Dodge]

Let me clearify up one thing as you seem to have misunderstood me when I
mentioned validation rules. Yes, I fully understand the order of the
sequence of the events, and secondly, I have done a lot of things with
various programs.

I get the sense you think I'm refering to the Validation rules that one sets
up in the controls or on the fields within the tables in the BE DB. Forget
about those fields (Validation Rule and Validation Text on both Controls and
Fields within Tables). I instead would use any of the events that I can
properly use. The events that I have used are, Enter, GotFocus, KeyDown,
BeforeUpdate, AfterUpdate, Exit, LostFocus, and in few instances, MouseDown
or MouseMove. I use KeyDown instead of KeyPress cause as a developer, I
have more control via the KeyDown Event than I do with the KeyPress Event,
as the KeyDown picks up on things that the KeyPress doesn't, and the things
that the KeyPress does pick up, I can get just as easily with the KeyDown
Event.

Also, before you start accusing people that they don't know how a program
works, try to think it through to see what they are getting at. I think the
reason why you said that I have my own perception is cause of the fact that
I am a stickler on data validation, but at the same time, I'm also a
stickler on user friendliness, which I can't get both with BOUND
FORMS/CONTROLS. I have gone over this several times in the past about this
very issue on the newsgroups, and others finally realized what I was getting
at and agreed with me, it's not possible to do within Access for bound
forms/controls of what I'm after. Cause of the lack of having the
CausesValidation Property and Validation Event, I have had to create my own
custom centralized error checking code, which only works for unbound
forms/controls. Well it could be done for bound forms/controls, but still
run into the user friendliness issue that the centralize code address, thus
why I say it only works for unbound forms/controls.

Here's a link to an example that I'm talking about:
http://www.thescarms.com/vbasic/VB6Features.asp

Within this example, you see a dialog box with 5 different command buttons.
All 5 of the command buttons has the property, "CausesValidation". All of
them except for the one that shows as "Quit" has this property set to "True"
while the one for "Quit" is set to "False". The textbox itself has the
"Validation" event.

If you click on any command button other than for "Quit", the Validation
event on the textbox is triggered without the textbox losing control, which
is more or less the same how Access works.

Now, if you click on the "Quit" command button, this is where VB6 differs
from Access. Access would still go through the validation process via the
set of events that's on the textbox control, but in VB6, cause the
"CausesValidation" property on the "Quit" command button is set to "False",
there is no validation ran against the contents in the textbox, thus if
there's no string in it, VB6 will close out the program, but if that was
done in Access, the command button would never get the focus cause it would
have errored out and the focus would still be stuck on the textbox.

You right in the sense that the other issues that I'm having is as a result
of this issue that I just went through the illustration. With me having to
unbind all forms/controls in order to attempt to restore the user
friendliness of the forms while still keeping the stringent data validation
and to be ran only at the times when it's proper to be ran, these other
issues has surfaced. However, that doesn't mean that these issues doesn't
exist in Access itself.

If MS fixes these issues among others, then I may lay claim about MS Access
being multi-user worthy, but until such thing takes place, I will not lay
that claim.

Another big thing that I don't like about MS Access is the size limitation
of DB files, thus I had to setup multiple BE DBs (about 8 of them) to get
around this issue.

 

['69 Camaro]

Hi, Ronald.

Most times, you want
the validation rules to run, but there are a minor few possible reasons
why
you may want to bypass the validation rules such as user clicking on the
"Help" command button (if there is one on the form

My validation rules don't run when I invoke online help. Please show your
code and describe what happens, and perhaps someone can help you on this
problem.

resetting the form

Use Me.Undo to reset the form.

or
even backing out of the form

However, when the user goes to click on a command button
on the form, all validation rules on the active control are processed, and
if any of those validation rules causes the control to be in error, the
Enter Event doesn't even take place on the command button cause the focus
is
forced to stay on the active control rather than to transfer to the
command
button.

Have you tried putting your validation code in the active control's
OnBeforeUpdate( ) event? You can cancel the update, use a MsgBox to tell
the user about the problem, and the focus will remain on the active control
whenever the value in the active control doesn't meet your criteria, such as
when the user tries to leave a required text box empty.

To get around this issue, I had to mimic the CausesValidation Property and
Validation Event as has been setup in VB6, which meant in order to get
around this issue, I had to UNBIND every single form and control else this
wasn't going to work.

Wow. That would be the hard way to do it. Hitting that <ESC> key or
writing VBA code to validate the data, and canceling the update if any
problems are encountered, works for me.

It took me a total of 3 full months to do the R&D to
get around this issue.

Wow! You really should have posted some questions in the Access newsgroups.
We would have gotten you straightened out that same day, probably within an
hour or two of your post.

Initially, I was using DAO to get around the issue above as in order to
get
data to the any of the BE databases with all forms and controls unbound

Suit yourself if you want to use unbound forms, but that's the beauty of
bound forms in a RAD environment: the developer doesn't have to do a bunch
of programming to provide data validation, maintain data integrity, prevent
record locking, et cetera.

can
only use ADO or DAO programming language to manipulate the data.

Well, database developers use a powerful programming language to manipulate
data. It's fast and reliable. It's called SQL. It's not a common need to
use DAO or ADO Recordset cursors, unless the data manipulation is
exceptionally complex. I get the impression you're using cursors to do all
your updates, not queries or bound forms.

However,
there were instances that I found, when the code initially put the
recordset
into Edit Mode, even with the variable set at the module level of the
form,
before it even reaches the Update Method, the EditMode property no longer
has it's enum value that it suppose to have. This more or less has
rendered
DAO programming useless.

Why use a Recordset to edit data? Why aren't you using a query or a bound
form? If you used either of these, there would be no reason to check
whether the record was currently being edited, or the record was being
added, et cetera. If you use optimistic locking and there's a record lock
with a bound form, Access will let you know. Likewise with update queries.
If there's a problem updating a record, Jet will let you know.

I get the impression that you are a programmer who is experienced in another
field but has migrated to databases, where the solutions you come up with
use code instead of the database engine's functionality. Using cursors and
unbound forms are typical symptoms of a programmer with previous experience
on non-data applications. If this is the case, then I recommend that you
either get training or find an experienced Access developer to show you the
techniques we use for reliable RAD in Access.

Issue 3: ADO programming doesn't allow for DynamicCursor Keyset against a
Jet Engine (Critical)

This one is actually documented, so I ask the question, how is one suppose
to setup a recordset using a Jet Engine to be able to see changes within
it
when changes do take place?

It's somewhat rare that an update query is too complex to use SQL and needs
VBA to customize the query. If you're using cursors for record
adds/updates/deletes for more than about 5% of your queries, then you are
probably doing this the hard way.

Without this capacity, it has rendered ADO
programming useless.

DAO, the preferred data access library for Jet, allows one to see changes in
the Recordset. The DAO library has been available for every version of
Access since at least 2.0 (and possibly 1.0 and 1.1 too, but I can't verify
that). If you're using ADO on a Jet database, it's because ADO provides
some functionality you need that DAO doesn't. dbSeeChanges isn't one of
them.

Does Access 2007 address this issue?

Access 2007 provides bound fields, and allows queries instead of cursors for
updating data, and also has dbSeeChanges in the DAO library. The ADO 2.8
library doesn't have dbSeeChanges, so it's not the library you're going to
be using if you absolutely must use a cursor to update records.

it's the above 3 issues that has more or
less turned me against from using Access as with those issues above,
Access
is not a worthy DB program for a multiple user environment.

Access is a multiuser database, so it works great in a multiuser
environment, provided the database application was not designed for a single
user environment instead, which is a very common mistake with inexperienced
developers.

I was going to
have my own financial stuff stored within Access, and then allow my wife
some capacity to record some things, but with the issues above, it's not
stable enough to have something like that setup.

First, don't store data in an Access database if it needs to be secured.
Second, if you find that Access isn't stable, then either Access isn't
installed correctly, or hasn't had all of the service packs applied, or the
developer hasn't designed the database application correctly. What
instability issues or errors are you experiencing?

Instead, I am still using
Excel for all of my financial planning and tracking stuff, which that has
gotten to be so large, I have already broken that 1 file down into 4 files
and it's about to get split out to a 5th file

Wow! That's doing it the hard way. How do you prevent duplicate entries?
How do you prevent insertion anomolies? How do you prevent deletion
anomolies? How do you prevent redundancy between the multiple files? How
do you do data validation? Answer to all of the above: you don't. I'd
recommend storing that data in a relational database, not a bunch of
spreadsheets.

Issue 4: Listbox properties doesn't update until it has lost focus

That's how this control is designed to work.

records wouldn't be updated properly. To get around this issue, I
had to create a class module

Of course, this meant that I had to learn every single behavior of the
listbox from using both methods, mouse and keyboard.

It's not a bad idea to learn the tool you are using, but you seem to write a
lot of unnecessary code to "get around issues." Have you tried a timer
event, so that you can read the list box's properties with VBA code almost
immediately after the list box has lost focus?

Not only that, but I
found in few circumstances with the right combination of the keyboard
and/or
mouse actions on the listbox, the listbox itself freezes up and does
nothing
else, which the only way to get away from that issue has been to back out
of
the form, and then go back into the form.

I've never seen this behavior in any version of Access, so I can't comment
on it.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[Guest]

Good comments, Gunny.
You and I disagree on only one point - Validation Rules.
I find them restricitve and prefer to do my own validation.
My prejudice is not set in concrete. If I knew ways to control the
behaviour of the application when a rule is violated, I could be convinced to
experiment a bit more.

 

[Guest]

Let me clearify up one thing as you seem to have misunderstood me when I
mentioned validation rules. Yes, I fully understand the order of the
sequence of the events, and secondly, I have done a lot of things with
various programs.

I get the sense you think I'm refering to the Validation rules that one sets
up in the controls or on the fields within the tables in the BE DB. Forget

Sure, how could I not get that sense when you use exactly that terminology?

about those fields (Validation Rule and Validation Text on both Controls and
Fields within Tables). I instead would use any of the events that I can
properly use. The events that I have used are, Enter, GotFocus, KeyDown,
BeforeUpdate, AfterUpdate, Exit, LostFocus, and in few instances, MouseDown
or MouseMove. I use KeyDown instead of KeyPress cause as a developer, I
have more control via the KeyDown Event than I do with the KeyPress Event,
as the KeyDown picks up on things that the KeyPress doesn't, and the things
that the KeyPress does pick up, I can get just as easily with the KeyDown
Event.

Also, before you start accusing people that they don't know how a program
works, try to think it through to see what they are getting at. I think the
reason why you said that I have my own perception is cause of the fact that
I am a stickler on data validation, but at the same time, I'm also a
stickler on user friendliness, which I can't get both with BOUND
FORMS/CONTROLS. I have gone over this several times in the past about this
very issue on the newsgroups, and others finally realized what I was getting

Sorry, you will not convince me of this. I spent 2 years using only unbound
forms and controls in Access before I realized I was beating myself up.

at and agreed with me, it's not possible to do within Access for bound
forms/controls of what I'm after. Cause of the lack of having the
CausesValidation Property and Validation Event, I have had to create my own
custom centralized error checking code, which only works for unbound
forms/controls. Well it could be done for bound forms/controls, but still
run into the user friendliness issue that the centralize code address, thus
why I say it only works for unbound forms/controls.

Sorry, you are wrong. You just don't know how to use the tool.

Here's a link to an example that I'm talking about:
http://www.thescarms.com/vbasic/VB6Features.asp

Within this example, you see a dialog box with 5 different command buttons.
All 5 of the command buttons has the property, "CausesValidation". All of
them except for the one that shows as "Quit" has this property set to "True"
while the one for "Quit" is set to "False". The textbox itself has the
"Validation" event.

If you click on any command button other than for "Quit", the Validation
event on the textbox is triggered without the textbox losing control, which
is more or less the same how Access works.

Then you are not using the event processing correctly. If you have event
driven validation either at the control or form level and want to exit the
form without the validation errors, you have to program for that.

As to the CausesValidation property in VB, there is a difference in Access
that I thinks would cause a major revision in that when you close a form in
Access, it attempts to update the current record before the form closes. If
you were to disallow that, then either bad data could be written, or Database
level errors could occur.

Now, if you click on the "Quit" command button, this is where VB6 differs
from Access. Access would still go through the validation process via the
set of events that's on the textbox control, but in VB6, cause the
"CausesValidation" property on the "Quit" command button is set to "False",
there is no validation ran against the contents in the textbox, thus if
there's no string in it, VB6 will close out the program, but if that was
done in Access, the command button would never get the focus cause it would
have errored out and the focus would still be stuck on the textbox.

You right in the sense that the other issues that I'm having is as a result
of this issue that I just went through the illustration. With me having to
unbind all forms/controls in order to attempt to restore the user
friendliness of the forms while still keeping the stringent data validation
and to be ran only at the times when it's proper to be ran, these other
issues has surfaced. However, that doesn't mean that these issues doesn't
exist in Access itself.

If MS fixes these issues among others, then I may lay claim about MS Access
being multi-user worthy, but until such thing takes place, I will not lay
that claim.

Another big thing that I don't like about MS Access is the size limitation
of DB files, thus I had to setup multiple BE DBs (about 8 of them) to get
around this issue.

Upsize to SQL Server. If you have 8 X 2GB of personal financial data, you
are one rich SOB or your database is not well normalized.

 

[Ronald Dodge]

See inline statements.

--
Ronald R. Dodge, Jr.
Production Statistician/Programmer
Master MOUS 2000

Hi, Ronald.

How are you invoking the online help? Remember, even though I may know the
different ways, I have users that don't know very much about computers and
only know what they see.

My validation rules don't run when I invoke online help. Please show your
code and describe what happens, and perhaps someone can help you on this
problem.


Use Me.Undo to reset the form.

Same deal here with backing out of the form. Yes, I knew about this, but I
have users that don't.

Have you tried putting your validation code in the active control's
OnBeforeUpdate( ) event? You can cancel the update, use a MsgBox to tell
the user about the problem, and the focus will remain on the active control
whenever the value in the active control doesn't meet your criteria, such as
when the user tries to leave a required text box empty.

You missed my point here. To illustrate my point, setup the following
example:

A text box
A command button

Within each of the events below on the textbox, put the code:

Msgbox "<Name of Event> event was triggered", 48

Such as

Private Sub tbxNAME_BeforeUpdate(Cancel as Integer)
Msgbox "BeforeUpdate event was triggered.", 48
End Sub

List of textbox events to put this in:

BeforeUpdate
AfterUpdate
Exit

I also use the Change Event as part of my validation process, but in this
example, the Change event won't be triggered. However, also put this same
code in the "Enter" event of the command button.

Within the Exit Event, set the Cancel property to "True" for the
illustrative purpose.

After you have done that, click into the textbox, then click on the command
button.

As you will notice, all of the events in the text box was gone through and
cause the cancel property was set to true in the Exit Event, the command
button never even got the focus, thus the messagebox doesn't even come up
for the command button.

Wow. That would be the hard way to do it. Hitting that <ESC> key or
writing VBA code to validate the data, and canceling the update if any
problems are encountered, works for me.


Wow! You really should have posted some questions in the Access newsgroups.
We would have gotten you straightened out that same day, probably within an
hour or two of your post.


Suit yourself if you want to use unbound forms, but that's the beauty of
bound forms in a RAD environment: the developer doesn't have to do a bunch
of programming to provide data validation, maintain data integrity, prevent
record locking, et cetera.


Well, database developers use a powerful programming language to manipulate
data. It's fast and reliable. It's called SQL. It's not a common need to
use DAO or ADO Recordset cursors, unless the data manipulation is
exceptionally complex. I get the impression you're using cursors to do all
your updates, not queries or bound forms.

I also use SQL quite extensively. However, there are cases that I need to
use other means on the recordset, and not only that, but one also needs to
know if the data behind the scene has changed, even if from a different
source, so that's the main reason why I need the dynamic cursor keyset to be
able to have the recordset to be able to see the changes (dbSeeChanges).

Why use a Recordset to edit data? Why aren't you using a query or a bound
form? If you used either of these, there would be no reason to check
whether the record was currently being edited, or the record was being
added, et cetera. If you use optimistic locking and there's a record lock
with a bound form, Access will let you know. Likewise with update queries.
If there's a problem updating a record, Jet will let you know.

Well I already mentioned as to why with regards to bound forms. I would
love to use bound forms, but given the user friendliness issues, this option
was blown out of the water. I also know how the different locking methods
works, which I use record locking though optimistic or pessimistic is
dependent on the situation.

I get the impression that you are a programmer who is experienced in another
field but has migrated to databases, where the solutions you come up with
use code instead of the database engine's functionality. Using cursors and
unbound forms are typical symptoms of a programmer with previous experience
on non-data applications. If this is the case, then I recommend that you
either get training or find an experienced Access developer to show you the
techniques we use for reliable RAD in Access.

Well I have used VBA quite extensively, but I also had a lot of exposure to
databases including taking tables to NF5 level, which many disagree with me
taking tables to that level, but there are cases for such things. Most DB
programmers will only go to the strict NF3 level, but for me, I take it all
the way out to NF5 level.

It's somewhat rare that an update query is too complex to use SQL and needs
VBA to customize the query. If you're using cursors for record
adds/updates/deletes for more than about 5% of your queries, then you are
probably doing this the hard way.


DAO, the preferred data access library for Jet, allows one to see changes in
the Recordset. The DAO library has been available for every version of
Access since at least 2.0 (and possibly 1.0 and 1.1 too, but I can't verify
that). If you're using ADO on a Jet database, it's because ADO provides
some functionality you need that DAO doesn't. dbSeeChanges isn't one of
them.


Access 2007 provides bound fields, and allows queries instead of cursors for
updating data, and also has dbSeeChanges in the DAO library. The ADO 2.8
library doesn't have dbSeeChanges, so it's not the library you're going to
be using if you absolutely must use a cursor to update records.

These things has been in Access long before 2007, so I don't think you see
the same issues that I see.

Access is a multiuser database, so it works great in a multiuser
environment, provided the database application was not designed for a single
user environment instead, which is a very common mistake with inexperienced
developers.

This is the very reason why I instantly start out developing for multiple
user environment, but given the issues that I have ran into, which I have
already stated in this thread mostly, but the size limitation of the DB
files is another one that I don't like, but got around that one as well.

First, don't store data in an Access database if it needs to be secured.
Second, if you find that Access isn't stable, then either Access isn't
installed correctly, or hasn't had all of the service packs applied, or the
developer hasn't designed the database application correctly. What
instability issues or errors are you experiencing?

When I mentioned about it being unstable, I was really refering more so to
the fact that the issues that I been having to deal with, which makes it not
so user friendly. As far as the application itself is considered, it
doens't crash or anything of that nature. It's just that I'm a stickler on
both user friendliness and strict data validations, but given how Access is,
I can only have one or the other, thus why I still won't lay claim to it
being multi-user worthy. Now, if MS puts in the CausesValidation and
Validation Event within Access, then that may change my claim, as that seems
to be the root of 90% of my issues in Access. That doesn't mean a lot of
these other issues isn't in Access, but I would be able to bypass them and
it would greatly reduce my development time. I probably would be able to
develop about 10 times faster, as in that cause, I wouldn't have to worry
all that much about the communication aspect. All I would have to do is
develop the form and put in validation checks within the events. I also
would be able to more utilize the form level events as they were meant to be
used rather than this long way round about way cause of this issue.

Wow! That's doing it the hard way. How do you prevent duplicate entries?
How do you prevent insertion anomolies? How do you prevent deletion
anomolies? How do you prevent redundancy between the multiple files? How
do you do data validation? Answer to all of the above: you don't. I'd
recommend storing that data in a relational database, not a bunch of
spreadsheets.

The way it's done in spreadsheet is so vastly different as compared to how
it would be done in a DB evironment. It is the long hard way, yes, but
given the issues, I won't put it in Access, at least not until at the very
least, the root issue is fixed.

That's how this control is designed to work.



It's not a bad idea to learn the tool you are using, but you seem to write a
lot of unnecessary code to "get around issues." Have you tried a timer
event, so that you can read the list box's properties with VBA code almost
immediately after the list box has lost focus?

I need to have the capacity to be able to read the data before the listbox
loses the focus. By the time it loses the focus, all of the needed stuff
has already been done in most cases. I have other things in most cases
being modified on the form such as enabling and disabling command buttons.
If a command button is disabled and needs to be enabled, can't do that while
the control has the focus, thus can't click on the command button or even
activate it via the command button's accellerated character (if it has one)
without having to go through's the listbox's KeyDown Event. This issue is
actually separate from the main problem.

There's also one other reason. In the production environment, in one case,
wanted to be able to drag and drop the different work orders within the
listbox, and here's what I found and documented. Bear in mind, I am using a
Class Module to store and manipulate the listbox, which the class is
initiated at the time the form is loaded.



List Box Drag and Drop Feature



Operations by Mouse



Left mouse button is used for the following things:



Just clicking on selects an item and deselects all other items. However,
this is only true, if clicking on an item that has not been highlighted.



Click on and hold down on an item does one of 2 things depending on if the
item is highlighted or not.



If item is highlighted, the selected items can be dragged up or
down within it's boundaries.



If item is not highlighted, can selected a range of items in one
go.



Can use the "Shift" key with the mouse to select a range of items



Can use the "Ctrl" key to select/deselect individual items without impacting
other items.



Operations by Keyboard



Arrow Key up and down selects only a single item



Shift Arrow up and down selects a range of items starting with the first
item that the shift key was pressed on



Ctrl Arrow Up goes to the first item in the list while Ctrl Arrow Down goes
to the last item in the list with all other items deselected.



Alt + Arrow Up/Down drags the selected items up/down the list while kept
within it's boundaries.



Known Issues



ITEMS DOESN'T SEEM TO DRAG PROPERLY



This issue is known cause of the many tests that has been done, but given
the nature of this issue not occurring on a regular basis, it's cause has
yet to be determined. At this time, if it causes an item to not be
selecting properly, you will need to back out of the form, and then go back
into the form.



SHIFT KEY DOESN'T SEEM TO BE WORKING WITH KEYBOARD METHOD



If you select multiple items via "Ctrl" key and the mouse, then hold down
the Shift key and use either the up arrow or the down arrow, it will work
once, but try it a second time in a row, it fails. Even in one case in
particular, it has caused the item to not highlight properly until you cause
another form or application to come to the front of the screen and hide the
form that the list box is on, which then when you bring the form back up
that has the listbox on it, it then shows the items all selected as
expected.



Steps to recreate this behavior

Create a test form

Add a Listbox to it

Set the RowSourceType as Value List

Set Multi Select to Extended

Set RowSource =
Test1;Test2;Test3;Test4;Test5;Test6;Test7;Test8;Test9;Test10;Test11;Test12

Set ColumnCount = 1

Now close out the Property dialog box, if you have it open.

Goto Form (Run-time) mode

Click and hold the Left mouse button down on "Test4" item

As holding down the left mouse button, drag the mouse to "Test7" item

Left up on the mouse left button

Press and hold the "Ctrl" key (either one, doesn't matter)

Now single click and left up with the left mouse button on "Test8" item



Lift up on the "Ctrl" key


Hold down the "Shift" key (either one, doesn't matter)

Press and release the "Up Arrow" key.

 

[Ronald Dodge]

Rather than repeating the inline stuff, I just started up a new clean reply.

When I said validation rules, I didn't mean the fields that you thought of,
but rather validation rules as in rules that I have setup for the data to be
met, which are done via events, not the Validation Rule/Text fields.

It's obvious that you don't see the issue that I have, and nor will you see
the fact that even though I know how to do all three things (reset, help,
and back out) without command buttons within Access, I do have users that
barely even know how to operate a computer and only will do things of what
they see, which is one of the things that I hear from way too many big shot
IT people saying "Oh, it's only training." Even though you would think it's
really easy, I have faced resistance from the very individuals that doesn't
know much about computer systems to such a degree, I had to go just the
opposite of what the IT people had said as training isn't going to knock out
that resistance. As far as supervisors and superintendents are concerned,
who is going to fire a machinist for not knowing much about computer systems
when their primary responsibility is to run the machine, and get the job
done. They are only required to to report their production, which doesn't
take much, but yet, you would be surprised by how much they seem to question
things at times, so while I admire the fact that it wouldn't seem to be
necessary for these features in Access, it's this very reason why I see such
need.

As far as going to SQL Server, those 8 different BE DBs are in regards to at
work, but given what I faced there, it's obvoiusly the SQL Server isn't
going to happen and I don't have that call either. I would in a heart beat
for a business type environment, but not for a home type environment. For
home, I would only have 1, maybe 2 BE DB(s).

Normalization: Hrmmm, what can I say. I take my stuff all the way through
the NF5 level, which many disagree with me taking it to that extent, but I
do see of circumstances as to why to take it all the way through the NF5
level rather than stopping at the strict NF3 level. Also, remember, when
you are combining certain things, you not only have to be able to take it up
the line to the grouped level, but you also must be able to take it back
down to the individual level, thus where NF4 and NF5 comes into play. This
web site explains it better and with examples.
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm8.html

 

[Ronald Dodge]

One other thing that I failed to mention about. You stated that it would
require a major revision to the application. I don't think it would cause
just as I would have had it in the code with bound forms/controls, I would
have it coded in the command button to handle the data properly such as
proper cleanup code to prevent such issues that you raise. Even with
ADO/DAO programming, one has to go through this clean up process.

 

['69 Camaro]

Hi, Klatuu.

You and I disagree on only one point - Validation Rules.

VBA is much more flexible than using Validation Rules, but I prefer letting
the database engine do the work whenever possible, so if a CHECK constraint
or a Validation Rule on a table or column can get the job done, that's my
first choice. But when I can't get these CHECK constraints and Validation
Rules to do everything I want, I write code in the event procedures to
validate the data input.

If I knew ways to control the
behaviour of the application when a rule is violated

About the only control you have with a Validation Rule is the Validation
Text you use to tell the user what's going on. Other than that, your error
handlers in the appropriate event procedures would need to handle what
happens when the Validation Rule is violated, which means "On Error Resume
Next" probably isn't going to be enough.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

['69 Camaro]

Hi, Ronald.

How are you invoking the online help?

Usually by pressing the <F1> key. If your users are encountering Validation
Rules being exercised when the control loses focus, then remember that the
developer is responsible for handling this with VBA code.

Same deal here with backing out of the form. Yes, I knew about this, but
I
have users that don't.

Either place a button on the form, "Undo," and put Me.Undo in the click
event procedure and train the users to use this button, or train the users

You missed my point here. To illustrate my point

Within the Exit Event, set the Cancel property to "True" for the
illustrative purpose.

Place the validation code and set Cancel to TRUE in the control's
OnBeforeUpdate( ) event, not the OnExit( ) event.

I also use SQL quite extensively. However, there are cases that I need to
use other means on the recordset, and not only that, but one also needs to
know if the data behind the scene has changed

If you're using unbound forms, then you need to do extra coding to prevent
record locks. I'll suggest that you use bound forms to lessen your own
workload. If you're using cursors in procedures in standard modules, then
if you use optimistic locking and record-level locking, Jet will attempt to
lock the record only when updating the current record. Unless you have
heavy transactions, or a slow network, or a database application that hasn't
been designed properly, it will be rare that you have record locks that will
prevent a record from being written to. In the case of heavy transactions,
then Jet probably isn't the back end engine you should be using.

I would
love to use bound forms, but given the user friendliness issues, this
option
was blown out of the water.

If you think that bound forms aren't user friendly, then you need to have an
experienced Access developer show you that "You ain't seen nuthin' yet!"
Access does have occasional processes on bound forms that aren't user
friendly, but these can almost always be coded around. To avoid unbound
forms because the defaults aren't user-friendly is like throwing the baby
out with the bath water. You miss out on a lot of built-in functionality by
not using bound forms.

Well I have used VBA quite extensively, but I also had a lot of exposure
to
databases including taking tables to NF5 level

I can't think of any times when SQL and/or bound forms on a properly
normalized relational database can't accommodate up to 5NF. Perhaps you can
think of plenty of examples. If you only have one example or a few
examples, then I would recommend that you use bound forms and SQL to
manipulate the data for all the other cases.

Most DB
programmers will only go to the strict NF3 level, but for me, I take it
all
the way out to NF5 level.

If you need 5NF, then you need 5NF. Access can handle 5NF with bound forms
and SQL, so you shouldn't be avoiding this in Access.

These things has been in Access long before 2007, so I don't think you see
the same issues that I see.

I don't put the same stumbling blocks in front of me as you do. Access has
a steep learning curve so it takes time and effort to learn the best
practices for getting any particular job done. Look at other experienced
Access developers' applications. You'll find out what others have done when
faced with the same or similar problems as you have encountered.

But my point is that DAO provides dbSeeChanges, so you shouldn't be using
ADO if you need this functionality. My other point is that you normally
don't need to write code to check the state of the data set if you're using
either SQL or bound forms to update records.

This is the very reason why I instantly start out developing for multiple
user environment

Do you follow the tips in Tom Wickerath's article, "Implementing a
Successful Multiuser Access/JET Application"? You'll find a link to his
article on the following Web page:

http://www.Access.QBuilt.com/html/articles.html

but given the issues that I have ran into, which I have
already stated in this thread mostly, but the size limitation of the DB
files is another one that I don't like, but got around that one as well.

Access is intended for small databases. If 2 GB isn't enough room to store
your data, then I advise you to use a different database engine than Jet.
SQL Server 2005 Express and Oracle 10g Express are both free databases with
the robustness of their enterprise level database counterparts, and can hold
up to 4 GB. If 4 GB isn't big enough, then I'll suggest that you use a
client/server database that can hold your data.

It's just that I'm a stickler on
both user friendliness and strict data validations

I am, too. I won't put up with a software product that shows me the
developers were striving towards a designation of "poor workmanship" instead
of "perfection."

but given how Access is,
I can only have one or the other, thus why I still won't lay claim to it
being multi-user worthy.

You can have both, but you've already decided that Access doesn't meet your
standards. You've never seen what an experienced Access developer can do
with Access. It's a huge difference in quality, stability, expandibility,
and maintainability compared to a beginning- or intermediate-level
developer's database application.

Now, if MS puts in the CausesValidation and
Validation Event within Access, then that may change my claim, as that
seems
to be the root of 90% of my issues in Access.

These aren't in Access 2007 and I wouldn't hold my breath if I were you
about them being included in future versions.

as that seems
to be the root of 90% of my issues in Access.

No. Your issues stem from your refusal to use bound forms.

it would greatly reduce my development time. I probably would be able to
develop about 10 times faster, as in that cause, I wouldn't have to worry
all that much about the communication aspect.

I'm sure you're correct. It would be easier to maintain, and less code
means fewer chances of mistakes.

All I would have to do is
develop the form and put in validation checks within the events.

That's what the rest of us do. Using Access's RAD environment, an
experienced Access developer can reduce development time three to five time
(or more) than when developing a front end in other common technologies.

The way it's done in spreadsheet is so vastly different as compared to how
it would be done in a DB evironment. It is the long hard way, yes, but
given the issues, I won't put it in Access, at least not until at the very
least, the root issue is fixed.

Chopping off your nose to spite your face, huh? ;-)

I need to have the capacity to be able to read the data before the listbox
loses the focus.

By design, you can't read what's been selected in the list box until after
the list box control has lost focus.

By the time it loses the focus, all of the needed stuff
has already been done in most cases. I have other things in most cases
being modified on the form such as enabling and disabling command buttons.

Then do those things in the form's OnTimer( ) event, which can start as soon
as the focus is lost from the list box control.

If a command button is disabled and needs to be enabled, can't do that
while
the control has the focus, thus can't click on the command button or even
activate it via the command button's accellerated character

The button is going to be disabled until you run code in another event that
enables it. That event doesn't have to be in _any_ of the listbox's events.

ITEMS DOESN'T SEEM TO DRAG PROPERLY

if it causes an item to not be
selecting properly, you will need to back out of the form, and then go
back
into the form.

I haven't experienced this problem. Perhaps your class has something to do
with it. To rule this out, don't use code that instantiates an object of
your class, and just use the list box control "natively." If it still acts
the same way, you know that it isn't your class.

SHIFT KEY DOESN'T SEEM TO BE WORKING WITH KEYBOARD METHOD
If you select multiple items via "Ctrl" key and the mouse, then hold down
the Shift key and use either the up arrow or the down arrow, it will work
once, but try it a second time in a row, it fails.

This works as designed. When you hold down the <SHIFT> key while you are on
a selected item and then select the up/down arrow key, you are selecting or
deselecting the next item in the list depending upon which state it was in
before your arrow key moved you there. But remember that using the <SHIFT>
key means that you are starting a new range of selected items, so moving up
one item with the arrow key changes the selection state -- and now you have
a discontinuous group in the range, so the range stops at the last selected
item. What you need to do is select your "range" with the <SHIFT> key

Even in one case in
particular, it has caused the item to not highlight properly until you
cause
another form or application to come to the front of the screen and hide
the
form that the list box is on, which then when you bring the form back up
that has the listbox on it, it then shows the items all selected as
expected.

I haven't seen this behavior, but I'm not using a user-defined class to
validate the data in the list box either, so perhaps your class is
interfering.

Steps to recreate this behavior

This works as designed. When you hold down the <SHIFT> key while you are on
the "Test 8" selection and then select the up arrow key, you are deselecting
the next higher item in the list "Test 7" (because it was selected before
you moved to it with the up arrow key). This results in a discontinuous
range (an item selected adjacent to another item that's not selected within
the previous range "movement"), so the range can't continue upward.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

 

[John Nurick]

Upsize to SQL Server. If you have 8 X 2GB of personal financial data, you
are one rich SOB or your database is not well normalized.

<ROFL>

 

[John Nurick]

I need to have the capacity to be able to read the data before the listbox
loses the focus.

On the rare occasions I need this, I use a continuous subform formatted
to look like a listbox, and use the events of the subform and its
controls.

 

[Ronald Dodge]

When you state put a button on the form, do you mean a command button? If
so, I have done that, but if the previous control doesn't even meet the
validation rules as set in the events of that previous control, this command
button not even once recieves the focus, so that's where I'm coming in with
why I'm stating the need for "CausesValidation" property and "Validation"
event. BTW, it doesn't matter which of the 2 validation events you put it
in the validation rules as far as this point is concerned. You are right,
such validations should be put into the BeforeUpdate event, but my point is
that it doesn't matter rather if it's in the BeforeUpdate or the Exit event,
if there's something set in either of these 2 events that causes the Cancel
variable to take on the value of "True", then the command button never
recieves the focus, which when a user is attempting to back out of a screen,
resetting the screen or attempting to invoke help via a command button, and
if the control that has the focus returns as not a valid entry, then what
they are attempting to do never takes place, cause the command button never
got the chance to recieve the focus. A command button must be allowed the
chance to receive the focus before it can do anything, including the Enter
event, which is before the GotFocus event, and that just simply doesn't take
place when the previous control's validation isn't met as that previous
control retains the focus.

 

[Albert D.Kallal]

Issue 1: Error checking isn't user friendly (Very Critical)

The problem is much your design approach. You always have two choices when
choosing a tool
to develop software:


change your designs to fit the tool
or
change your tool.

I would be quite pretentious of me to post in a VB6 newsgroup,and complain
that
you don't have inheritance or all of the OO features. Or, how about
something
as
simple as bound data forms that we have in ms-access? VB6 is NOT going
to get bound forms! It is just not going to happen. As much as you
would like the universe to move to "your" view of things, a major change in
the event model and change to ms-access is not likely.

To be fair, it would be nice to be able to turn of validation. However, you
also have to turn off the before update event. And, even further, you might
even have to turn off a for more events. So, *just* turning off validation
would not work, since we do have the before update event also (this is
likely were *most* of your validation code was/goes anyway..right?).

Not much you can do here:


Either you have to change your designs, or change your tool.

If you conclude that you can't get the level of user friendly with bound
controls, then you simply have to dump ms-access.

Now that you spend the time (or perhaps a better word wasted the time)
moving to un-bound controls, then you really receive no benefits of using
ms-access. VB6 and other tools are *optimized* with wizards etc to
help make up for the "lack" of bound controls. If you extensively use
un-bound forms and controls in ms-access, you loose all of the advantages
from a cost point of view, but receive NONE of the tools that other systems
have to make up for the lack of bound form. It is the worst of both worlds,
and you are spending money that your company could give to the poor, or
for use in helping other people (or, just simply not throwing $$ down the
drain).

However, it is fair to ask what kind of workarounds, and what approaches do
work in ms-access.

I have two suggestions that will work quite well in ms-access.

Suggestion #1

Keep bound controls, and move the validation code to the forms before
update event. This of course means that you loose a field by field
validation.
However, the simple solution in that case is to use the controls before
update event and have that validation code in there also. BUT DO NOT
USE THE CANCEL feature of the before update.. This means that a user
receives a message that the control is required (or not setup to whatever
value etc etc etc) but is ALLOWED to continue to move on in the form.

In fact, from a user friendly point of view, this approach is BETTER then
forcing the user to STAY on control, or use some quit button on the form!
You see, in your "quit" link example, that ONLY works if the users hits the
quit command. What happens if they need to go back a few fields and edit
something but are now are STUCK on the validation for the current control.
This just simply causes HUGE user frustration. If you want a debate on
user friendlness, then you DO NOT want to force a user to be stick on that
control, and ONLY give them the option to quite. Hence, move the *final*
valuation that prevents a record save, or form exit of the form to the forms
before update event.

Further, if the user does NOT ENTER INTO THAT REQUIRED FIELD/control,
, then you STILL MUST HAVE form's level validation code!!!. In other words,
if you have some control on the form that is required, or MUST meet some
special
value, but the user DOES NOT EDIT that control, THEN YOU MUST still have
that forms
level validation code anyway!! So, my point here that even when you used
control level
validation (I am talking about before update + cancel feature of a control),
you STILL had to write a forms level validation routine anyway to catch
those
controls/fields that were not edited by the user!!!

In conclusion, you at *always* had to write a forms level validation
routine in the forms before update event routine for user friendly code.
So, for each control, give the user a error message/warning..but
simply do not use the cancel feature of that control. Simply reduce user
frustration, and allow the user to continue to work their way through the
form. As I said, 9 out of 10 users prefer this, and they do NOT
like get being stuck on a particular control, but can continue to move to,
and edit
other controls on the screen. With the VB6 approach, you can't move to other
controls but must ONLY be faced with a cancel, or quit that disables the
validation.

The above approach I outline is not only MORE user friendly since they don't
get stick
in the middle of a form, it ALSO LESS CODE then what you wind up with in
VB6, or your wrong
choice of un-bound controls. This approach is friendly, and works very well
with bound forms +
controls.

So, In above, we are STILL giving the user a message when they enter some
value in a control
that is incorrect, but they are allowed to continue on to the next
control...and
users prefer this. And, thus our *final* validation is in the forms before
update catches this stuff (and as mentioned, you *always* had to write that
code anyway!).


Suggestion #2
Move your quite command, or other commands that "must skip" the
validation to a toolbar, or a menu bar. Again, this is more "widows" like,
interface, and the added benefit is that menu commands DO NOT TRIGGER
the before update event, or even any of
the last focus events. I used this approach in place of #1, and also that of
validation in VB6. So, I have used menu bars to get around the fact that we
don't have the ability to turn off the before update events, or validation
code (by the way, we need to turn off BOTH events to allow the equivalent
features of VB6 -- and, in thinking about this, perhaps a few more vents.
So,
turning on the events in ms-access is FAR MORE complex problem then the
very simple validation that you have in vb6).

Again, of course, if you are not willing to modify you designs to the above,
then I can't help you. However, in no way can I see that above
two choices is ANY LESS user-friendly then what you done (expect that you
wasted huge $$ moving to un-bound controls).

You can't design your appcation with one architecture in mind, and then hope
that FoxPro, or ms-access, or power-builder will work the way you want (they
don't by the way...YOU have to change your design...not the other way
around). Learning to design within in the bounds of the tool you are
using is the difference between developers that understand the subtleties of
developing..and those that don't...

As a number of others stated, you can very well reach a *VERY* high degree
of validation AND ALSO reach a level of user friendly nesss. As mentioned,
the above two suggestions are of many possible approaches. I too also have
some VERY high degree of demands for checking and validations,but also that
of
user friendly at the same time..

the EditMode property no longer
has it's enum value that it suppose to have. This more or less has
rendered
DAO programming useless.

I not seen this error pointed out. You might want to elaborate this with a
new question/post

Issue 3: ADO programming doesn't allow for DynamicCursor Keyset against a
Jet Engine (Critical)

ADO, and JET are only pieces used by ms-access, or VB.net, or VB6. Are you
saying the above ONLY applies to ms-access, or are you talking that the
general use of ADO and JET has this problem? (this would thus not be a
problem special to ms-access, but to ado in general, and presumably, then
also
this problem would exist with .net also???)

Issue 4: Listbox properties doesn't update until it has lost focus when
it's allowed to have multiple selections (Moderate)

Hum, that don't sound right. Try using the after update event, and use the
itemsselect collection...

Place the following code in the after update event of a multi-select list
box....


MsgBox Me.MyListBoxName.ItemsSelected.Count

Try the above. ItemsSelected should be updated correctly...

 

[Ronald Dodge]

Thank you for a much more constructive and specific response than what most
others has given. I can tell you have given this through some thorough
thought process on this rather than just coming out stating that I'm doing
it all wrong. You went beyond that and went to understand my dilemma and
then to help me out with other work arounds that I may not have necessarily
thought of.

1 & 2) Validations

I will need to reevaluate doing it this way as there are things at times
that I still like to have done after the entry of a field level has been
done, but will with a high probability take the trade off. Just as you
mentioned about the reasons for form level validations, I also have form
level validations for those same reasons. I just like to have all errors
caught ASAP. Where a lot of this comes from, I have been in all 3
positions, transcriber (data entry) from when I worked at the IRS,
developer, and user. These last 2 are as of the current position, even
though most of the work I do is development, but that's only cause I have
automated my primary job to the point that my primary job only takes at
most, 10% of my total time. Given my primary job, I am still held
responsible to the extent reasonably possible, so I also have to use
measures to make sure it's reasonably as accurate as it can be. For a
while, we did use a manufacturing DB program to get away from paper logs.
However, cause of the issues with it, plant by plant, the use of it was
dropping, which then my plant was the last plant to drop it. The reason why
my plant was the last one to drop it, I had work arounds in place to address
a lot of the issues. However, a lot of the others didn't want to use the
work arounds for one reason or another. Of course, my work arounds was more
so for the manufacturing side, and most of our plants are more so geared
towards assembly.

Anyhow, from the time of notice to the time when we were dropping the
manufacturing program, I had only 3 weeks to get something else in place
that would record all of the needed data and not only that, but also report
into our main DB program. Our IT department wanted the operators to use the
text version of the screens, which the supervisors and I all saw too many
issues with that, which still exists today, and that was in June of 2001,
when it was the last month to use the manufacturing DB program. The cost of
DB program was way too high to use for just one plant, thus didn't make
sense to renew the licenses on it. At that time, I would have liked to use
Access, but due to my limited knowledge of Access (I knew of the basics, but
that was about it as I knew very little of the programming side in it), and
I had only 3 weeks time tops to get something in place, debugged, and have
all operators trained, I did create a program within Excel, though I spent
12 hours per day for 5 days per week over those first 2 weeks and 2 days,
which then trained operators over the last remaining 3 days. I forewarned
my bosses that this program that I created in Excel would only be intended
as an intermediate program, not a permanent solution cause of all of the
different issues that I knew of in Excel, which obviously still exists today
in Excel. Of course, that's no fault of Excel cause Excel isn't really
intended for that sort of stuff, but it's all that I really had to go with
given the level of knowledge and the amount of time at that point of time.
Here it is, going on 5 years since put into place, and we are still using
it. I haven't done much to the program either since then, other than
monitoring the data, but that's still way better than reverting back to the
paper long days and manually putting that data into our main DB program.
But then our IT department hasn't done much either as it's hands been tied
by senior management being rather stingy on the funds. In this time period,
we have gone through 2 different sales of the division.

Anyhow, while I had plans on developing an Access program in the place of
Excel, I wasn't about to release a version without meeting certain
criterias. This response has renewed that possibility.

Even if going with what you suggested doesn't work out, at least using the
toolbar and toolbar buttons as the way to get around the limitations should
do the trick as you also stated.

3) ADO doesn't allow for Dynamic Cursor Keyset against Jet Engine

Sorry if I caused any confusion on this as when I see databases primarily
with a Jet engine, I seen mostly extensions of MDB/MDE, even of those that
aren't ran via MS Access. Yes, this is a general problem of ADO and Jet as
it has also been documented.

4) Listbox properties not updating until after it has lost focus

Yes, I have attempted to use the AfterUpdate event. As I attempted to use
the AfterUpdate event to do certain things, it came to my realization that
the properties of the listbox wasn't updating. I also used messageboxes to
troubleshoot it, and cause it isn't updating until it loses focus, it's the
very thing that caused me to go from using Centralized Standard Module to a
Class Module to address this issue.

As for the other listbox selection issue that causes the listbox to freeze
up, I have not only attempted with the class module, but also without the
class module and still get the same behavior. As a matter of fact, I did it
without the class module first cause I had to learn the specific behavior of
the listbox quite thoroughly [with it's selection mode set to "Extended"] in
order to even properly address it via a class module. I have to store in
the class module what items are selected, if any, on the listbox as it takes
place. Knowing users, I had to test for any combination of keyboard and
mouse usage, and that's how I found the freezing up patterns. Sad thing
about it, on one of the two lockup patterns, it doesn't happen often enough
to be able to find the specific issue, but yet, it still happens often
enough to know there is obvoiusly something going on.

 

[Ronald Dodge]

As I been thinking about this throughout the day between what Albert has
said and knowing how Access works, it appears initially, but again, I
haven't fully decided, I will most likely after all be able to bind my forms
and controls in at least a pretty good majority of the cases, if not all of
the cases, but I still will need the centralized custom error checking code
that I have created for certain circumstances. Even though this will still
mean some extra work, but not near as time consuming as it would be without
the bound forms/controls. Even with this need in those circumstances, based
on the initial thought process, it appears that I will be able to use the
best of both sides. The customized error checking code is only for the
field level, not the form level, which it was never intended to be used for
the form level. Biggest thing is to avoid setting the Cancel variable to
"True" unless you truly want that control/field verified in 100% of the
different types of situations and want the user to be sure to put in an
approved value 100% of the time.

Of course, before I use Office 2007, I will have to do some major upgrading
of my system at home, and as far as systems at work is concerned, even for
the size of company that we are (international), I really don't see Office
2007 coming into the company for 3 years minimally as most systems only have
512MB of RAM on them, not the 1GB of RAM minimally recommended for Office
2007. Computer systems on the production floor only has 256MB of RAM at
most, which means Office 2007 will run very slow for them.

 

[Larry Linson]

As I been thinking . . ., I will most likely
after all be able to bind my forms and
controls . . . but I still will need the
centralized custom error checking code
that I have created for certain circumstances. . .
The customized error checking code is
only for the field level, not the form level,
which it was never intended to be used for
the form level.

This almost reads as if you did not realize there is a BeforeUpdate event
for the Control, as well as the BeforeUpdate event for the Form.

Biggest thing is to avoid setting the
Cancel variable to "True" unless you
truly want that control/field verified in
100% of the different types of situations
and want the user to be sure to put in an
approved value 100% of the time.

Generally, the reason you run validation is to enforce the user NOT entering
an invalid value. Error checking code can, of course, pop up a MsgBox to
allow the user to override the check, if that is appropriate.

Of course, before I use Office 2007, I will
have to do some major upgrading of my system
at home, and as far as systems at work . . .

There will have to be a lot of upgrading and a lot of learning for the
combination of Windows Vista and Office 2007 in a lot of venues. It is
interesting that one article I read indicated that it was a Microsoft goal
to "drive hardware revenues" for hardware manufacturers (which seems an
unusual action for a software company).

I agree with Gunny and others that you might have saved yourself a good deal
of work, and grief, if you'd posted here much sooner. Keep posting and I
hope that by doing so we can help you save yourself some unnecessary work
and grief in the future. It is _key_ to understand "The Access Way" and work
with it, instead of having to work against it to make it do something
differently (but not necessarily better). It turns out that we are able to
take advantage of the ease of development it can provide and still produce
"user-friendly" applications for our users.

Sometimes a large-company environment such as yours provides the most
challenge; other times, it is the small and modest establishment that really
puts us to a trial. Best of luck with the continued development of your
application.

Larry Linson
Microsoft Access MVP

 

[Ronald Dodge]

Well I did realize this, but if you noticed, it has been the Cancel variable
being set to True for the field/control when validations aren't met, that's
brought on the issues. That's what drove me to create my own customized
centralized error checking which has the more or less the same effect as the
CausesValidation property and Validation event. Even though the role that
this code will play has been reduced, it still proves those 3 months I spent
has still been worthy. That's cause these 2 items still has to determine
rather if that next control should get the focus or the invalid
control/field should keep the focus as the Cancel variable within the
BeforeUpdate will always be set to "False" unless it truly does meet the
requirements as I stated in my last post. This in return will give me more
flexibility as a developer to determine when the user should be stuck in the
field versus when not to be stuck in the field, while at the same time
allowing for all of the validations to take place in the proper places as
was originally planned before I ran into this issue. Given this custom code
will remain in the package for it's use, there is no reason to have the
Cancel Variable set to True for the BeforeUpdate event on any of the
fields/controls.

Here's one such reason why I thought of havings something like this in place
rather than going through a bunch of different forms, like what Greyhound
has done on their web page in the past, which I'm assuming they had to
address a similar type issue in order to work their way down the list. I'm
not sure how their web page is now, but it was setup that way years ago.

Take a form that has Nations, States, Counties, and Municipals. In it, you
would have the user select the nation first, then a list of states pops up,
if such nation has states, otherwise, Counties (if nation has them) and
Municipals will be updated. However, before the user can even move onto the
next control, the nation must be valid, but yet, by the same token, you
don't want the user to be stuck for those other types of actions taken
either. Each of these fields would be setup with a combo box. Of course,
this is just one example.

 

[Albert D.Kallal]

Great stuff.

And, I do appreciate you feedback. Especially the part about me trying to be
constructive!!

I am of the camp to be "hard" in pointing out how ms-access can be used, or
not used.
I was also quick to point out that if you need some of your control +
verification, you might in fact want to dump ms-access.

And it would be MOST pretentious of me to criticize what YOU NEED to
accomplish. It not like you have a bad attitude, or are complaining!!!

You (like all of us) are working VERY HARD to accomplish you goals and
needs. And, you kindly asked for input here, and even made suggestions. (for
example, I made a note about being able to turn off verification like VB6
was able to do. I might even throw it in a suggestion for the next version
of ms-access). So, don't let a few people here throw any bath water on some
of your ideas and requests...since they DO MAKE sense. If your suggestions
were complete nuts, then I would tell you. You made sense, you defined what
you need to do, and simply want to use ms-access to accomplish this goal.
This sounds all good to me!!

Ok, now, lets chomp on some code here, and few more got-ya's:

I *really* need to write out how I verify these difficult forms with a
article, but I am just too busy.

I have to run, but in a few hours, I will post my "verification" code I use
in a form here:

My goal is:

a) not have to re-write code over and over for required fields +
custom message
b) have a "common" general approach to this problem that works for
all forms
c) the design is *extensible* and can handle weird special cases
(since that always happens).