Table Defaults

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is it normal???

When adding a new record, via a form, as soon as the form goes dirty (user
keystroke) ADP/SQL Server goes out the underlying table to retrieve all
defaults defined for any column. It does this by calling the
sp_Mshelpcolumns procedure which is very costly.

For some tables it actually inhibits / interferes with the user entry. I
rarely use control default values however I assumed (or is that hope) that
table defaults would be applied at the server level and not the client level.
Is there anything I can do to eliminate/reduce this problem?

The form property has Fetch defaults as False. ADP 2003 (now SP2) Sql
Server 2k sp4.

I have looked online but have found little on the topic. Help me please!
 
On Tue, 6 Nov 2007 05:27:00 -0800, RJ <[email protected]>
wrote:

It appears you assume FetchDefaults prevents the server call. However,
the help file says "Returns or sets a Boolean indicating whether
Microsoft Access SHOWS default values for new rows on the specified
form before the row is saved."

I'm reading this such that the call is always made, but the results
are optionally shown on the form.

-Tom.
 
Yes, I understand what you are saying, but wouldn't that make the property
DISPLAY DEFAULTS????

This following definition is from Microsoft

http://www.microsoft.com/mspress/books/sampchap/5600b.aspx

Fetch Defaults Choose Yes (the default) to have the form fetch the default
values from the field definitions when you move to a new row. Set this
property to No to use only the Default Value settings you have specified for
controls.

My problem really has to do with the call to sp_Mshelpcolumns procedure is
getting in the way of user entry. As the user types and the form becomes
dirty, the keystrokes get lost / jumbled. Franklin becomes nklinFa. Not
exactly user friendly. On most forms it is not a problem but on those more
complex forms with an underlying table with many columns it becomes an issue.
One workaround is to redesign those lengthy tables but then that gets in the
way bound forms / User interface which is the benefit of using Access as a
front end.

I have tested against a table that has no defaults and it doesn't seem to
matter.

I think I know where this is headed eventually. aka .Net
 
Additionally from another MS page

Disable pre-fetching of default values if not required. By default, when you
display a form or datasheet, Access shows the default values in the new row
of the datasheet and the form controls for new records on forms. This
requires a round-trip to the server. You can disable the pre-fetching of
default values by setting the Fetch Defaults property to No.

http://office.microsoft.com/en-us/access/HP030870191033.aspx
 
In my test, I don't see the sp_MShelpcolumns called each a new record become
dirty; only for the first record created after the form is opened. More
important, this behavior doesn't seem to have any effect on the keystrokes;
ie. they don't get jumbled.

I suppose that you making some strange things in your code like playing with
the display.

Another possibility could be the Autocorrect option. If I were you, I would
deactivate all autocorrect options and see what this is giving.
 
Well your insight helped. On a funny note, I noticed that the user input was
actually coming out exactly backwards. Neat trick.

On Form Dirty I am enabling write / undo command buttons and disabling
close, delete, new command buttons. Additionaly I am disabling some toolbars
to prevent users from navigating away from the form until the form is saved.
In the MDB version of this exact code it worked well. In the ADP - Not so
good.

Obviously more testing is required to nail this down. As always , thanks
for the input.
 
I would say that your problem has more to do with shifting focus in the On
Form Dirty event than with a call to the sp_Mshelpcolumns procedure.
Instead of playing with the display of these buttons and the toolbars, maybe
you should limit yourself to simply refuse any invalid command with the
display of an error message to the user.
 
Yes I have solved my case. No it had nothing to do with the call to
sp_Mshelpcolumns. I still don't understand why it does this with Feth
Defaults = false but as long as it doesn't get in the way of the user...

The routines I use for enabling/disabling also hide/unhide tabs containing
subforms in certain cases. As it turned out, my particular problem was
having to do with these tabs. I was making a tab not visible that was
already not visible and viola backward user entry!!! It was never a problem
for an MDB but for ADP it was causing hair loss!

You have to laugh.

Thanks again,
RJ
 
Back
Top