Record Source Problem

[Mary Fetsch]

In my Access 2000 project, I have a form where the record source is blank in
the form properties. In the Form_Load event, I set the record source equal
to the following SQL statement that joins 2 tables.

Select * from TRIP T
Inner join CROP_YEAR CY
On T.YEAR_CODE = CY.YEAR_CODE
Where ((T.YEAR_CODE LIKE '04-05')
And (T.HAULER_CODE LIKE 'P'))
Order by START_DATE desc, GROVE_CODE, TRIP_CODE

In VB, I'm setting the form's unique table to Trip. I'm not selecting any
data in the Crop Year table - I'm just using its Start Date in the Order By
statement. There will be times when there's no Trip data that matches the
criteria in the record source. When Trip data is found, it will always have
matching data in the Crop Year table.

If there's no Trip data that matches the criteria in the record source, I've
had some problems, so I set the following record source, which won't return
any records:

Select * from TRIP Where 1 = 0

When this is my record source, I can add as many new Trip records as I want,
even though they don't match the record source criteria.

When there's Trip data that matches the criteria in the record source, I
have no problems getting the correct data when I open the form. But when I
add a new record that matches the criteria in the record source, I get the
following message after the Form Before_Update event fires.

"The data was added to the database but the data won't be displayed in the
form because it doesn't satisfy the criteria in the underlying record
source."

The Form After_Update event doesn't fire, and the new record doesn't appear
on the screen. (However, the new record is in the Trip table.) If I then
close and reopen the form, I see the new record on the screen.

I don't know why this is happening. Is it some sort of timing issue? Does
it have anything to do with the fact that this is a project (.adp) rather
than a .mdb? I'll appreciate any help anyone can give me on this.

 

[Sylvain Lafontaine]

First, you should learn how to use the Profiler on the SQL-Server. This
will tell you exactly what Access is trying to do and why the optimistic
updating of Access doesn't work in your case.

Second, make sure that both Trip and Crop_Year have a primary key defined.
Also, make a liberal use of the refresh function (F5) of Access for the
Tables and the Queries displays.

Third, replace Select * with something like Select T.*, CY.*. Take a look
for Bit fields and TimeStamp fields; as they are often troublesome with ADP
(particularly the Bit fields).

Fourth, set the Resync Command to your own updating procedure; as the Unique
Table may not be sufficient in your case. A very, very good trick is to
write a stored procedure with the wrong number of arguments and use it as
your Resync Command. Access will try to use it, see that the number of
arguments doesn't match the number of field for the primary key in your
Unique Table and will then revert to the default updating policy of ADO;
which is simply to return the same values used to update the record. Not
only this trick can go around your problem but it will make your code faster
at the same thing because a round trip to the server will be eliminated.

Finally, it will be a good idea that you forget about this idead of making
an heavy use of UPPER CASE.

S. L.