Re: Access 2007 Technical Questions

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
to hit the <ESC> key twice.

You missed my point here. To illustrate my point
<SNIP>
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.

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.

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 spread*** 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
<SNIP>
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
first, then select the individual items with the <CTRL> key pressed, so as
to keep the previously selected items.

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.


"Ronald Dodge" <ronald.dodge@xxxxxxxxxxxxxx> wrote in message
news:O2MJ6fmhGHA.412@xxxxxxxxxxxxxxxxxxxxxxx
See inline statements.

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

"'69 Camaro" <ForwardZERO_SPAM.To.69Camaro@xxxxxxxxxxxxxxxxxxxxxx> wrote
in
message news:ujnLTRlhGHA.460@xxxxxxxxxxxxxxxxxxxxxxx
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


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.

resetting the form

Use Me.Undo to reset the form.

or
even backing out of the form


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

Hit the <ESC> key twice.

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.


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.

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.

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


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.

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.

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.

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


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.

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.


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?

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.


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.

The way it's done in spread*** 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.


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
<SNIP>
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?

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.


.