Upsizing Head Scratcher - Datasheet Subform

[Guest]

2003 ADP – SQL Server 200

I have a datasheet subform that has a stored procedure as the recordset.
Because the sp has to select on multiple tables for description purposes I
have set the Unique Table and Resysnc properties.

As soon as I change a single character in the datasheet (e.g. field name
Quantity which is bound to that UniqueTable ) the field AfterUpdate event
fires followed immediately by the form’s BeforeUpdate Event and so on. Again
I have only changed a single character! I haven’t even tabbed out of the
field or clicked elsewhere.

Other strange things occur beyond there but I will start with this odd
behavior!

What would cause these events to fire on a single keystroke? The subform
KeyPreview property is set to no. As is the main form.

I have checked the Link Master and the Linked child fields and all seems
good there.

Any ideas? Thanks for your help!

 

[Sylvain Lafontaine]

This problem can happen sometimes with some screen capture or keyboard
logging programs because they change the focus of the control on the screen
each time.

Look also for anything else that could affect the focus; like a fast timer
to control the wheel of the mouse and the likes. This will affect the
datasheet subform even if the program/timer/whatever else is not directly
attached to it.

Your best bet would be to make some tests with a near empty ADP project:
begin with a datasheet directly linked to a table and with only the
BeforeUpdate event attached to it. Use the Debug.Print command to trace the
event in the immediate window (less trouble then using the MsgBox command).

 

[Guest]

Thanks for your input. I took your suggestion of starting with a single
table as the recordsource and worked it up from there. It was a nasty thing
to trace down and I have 30 years experience! I was just about ready to
write it off to an ADP bug and thought about redesigning the process.
Clearer minds prevailed however. As always, it was a combination of things
however the main problem was my misuse of the resync property.

Do you have any good sources that fully outline when and how to use the
Resync in an ADP? I have seen conflicting comments regarding this.

Thanks again!

 

[Sylvain Lafontaine]

How did you do this with the Resync property?

The way I use it is to create a sp with the primary key as its single
parameter (personally, I never use composite primary keys) and set to the
resync property to:

MySP_Resync ?

I also take a look with the SQL-Profiler to make that everything is OK (ie,
the sp is really called after an update) and that there are no missing
fields (the profiler will shows the requests for the missing fields if there
are some).

 

[Guest]

Like you I am not a fan of composite primary keys. Most, if not all, of my
primary keys are single column Identity fields. I was putting the entire
Select statement back into the Resync property with = ? instead of =
@variable . I will try your syntax suggested below. Obviously it's much
cleaner.

Thanks again!

 

[Guest]

Your last reply lead me down a path I hadn’t found before…

Do I have this right?

RecordSource = MySp – stored procedure that selects on the multiple tables
required….
UniqueTable = the single table I want to update
Resync = MySp_Resync ? – stored procedure that selects on the single table
(same as Unique) using the primary key?

If so, I had the resync property all wrong! I was repeating the original
select from the sp which seemed silly to me. I am sure I found that advice
online somewhere. But if the above is correct it would make sense given what
is to be accomplished.

For all of you that may come down this ADP path… Get to know Profiler. It
is your friend.

Thanks again,
RJ

 

[Sylvain Lafontaine]

Resync = MySp_Resync ? - stored procedure that selects on the single table

(same as Unique) using the primary key?

Not necessarily: the resync SP can returns fields from more than a single
table or even be identical to the record source SP.

Usually, the record source SP might return more than one records and for the
case that it returns only a single record, the retrieval of this record
might be based on a complex set of Where (and Join) statements.

Beeing based on the primary key of the current edited record, the retrieval
of the data by the resync SP should be fast and is used to make sure that
after the update, the displayed values are the same as the values in the
database. In most cases, the displayed values should be same as the values
who have just been written; however, there is the possibility that some
trigger or other event on the database have modified them, hence the
verification. Personally, I would have liked to have the option of *not*
refreshing the values right after they have just been written to the dbb.

 

[Guest]

Personally, I would have liked to have the option of *not*

refreshing the values right after they have just been written to the dbb.

Funny thing is that I was about to ask if there was a way around the refresh!

Thanks again for your help!

 

[Sylvain Lafontaine]

Well, if you want to, you can set a dummy resync command that doesn't exist
or don't have the right number of parameters. This will have the effect of
calling the default refresh option of ADO which is exactly to do nothing and
returns the same values without making a round trip to the server.

However, it seems that using this method can now have give some
troubles/bugs with ADP since the latest service packs. Personally, I don't
use it anymore.