Record Order On Form Gets Scrambled with SQL

Record Order On Form Gets Scrambled with SQL

Post by Larry Stin » Sun, 07 Sep 2003 02:28:18


Now that all my client's data is on a SQL server (and my Access 2000
front-end applications were relinked to the SQL tables), one of the forms
starts displaying records out of order after working with it for a while.

The front-end form is a simple credit card reconciliation form (it is a
continuous form). The form displays all credit card charges submitted via
the employees' expense accounts with the employees' names, the date of the
charge, a description of the charge, the amount and a checkbox to clear the
charges when they are billed from the credit card company. When the user
first opens the form, the records are displayed properly (i.e. sorted by
employee's last name, first name and then date). As the user works through
the billing from the credit card company and checks the reconciled checkbox,
she suddenly sees records for employees who should have appeared on earlier
pages on the page she is working on. For eaxample, she may have worked
through the H's and is now working on the I's when she will see charges for
an employee with a last name that begins with B further down the page. If
she closes the form and then reopens it, all records are again in proper
order. But, again, after she works with the data for a while, records start
appearing out of order. The form may display up to 500 - 600 records at a
time

This weird behavior only began after the data was moved from Access 2000 to
SQL server 2000, so it must be some SQL issue.

Does anyone have any idea what's going on here and how to fix it?

Thanks in advance for any help!
 
 
 

Record Order On Form Gets Scrambled with SQL

Post by John Nuric » Sun, 07 Sep 2003 05:37:30

Hi Larry,

Make sure that the recordsource of the form in question is a query or
view that explicitly returns the records in the desired order (with
something like
ORDER BY Employees.LastName, Employees.FirstName, Charges.ChargeDate
)?


On Fri, 5 Sep 2003 13:28:18 -0400, "Larry Stinson" < XXXX@XXXXX.COM >



John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

 
 
 

Record Order On Form Gets Scrambled with SQL

Post by Larry Stin » Mon, 08 Sep 2003 00:48:35

Hi John,

It is. In fact, the underlying query was sorted, but the form's OrderBy and
OrderByOn properties were now set. In me initial effort at eliminating this
behavior, I also set these form properties as well. But it did not help.

Thanks for the suggestion anyway.



the
the
through
checkbox,
earlier
for
start
to
 
 
 

Record Order On Form Gets Scrambled with SQL

Post by John Nuric » Mon, 08 Sep 2003 02:45:56

I guess you're right: it's an SQL Server thing<g>. This isn't an area I
know much about. Perhaps it's necessary to alter the kind of recordset
the query returns, or to requery the form after an update.

On Sat, 6 Sep 2003 11:48:35 -0400, "Larry Stinson" < XXXX@XXXXX.COM >






John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
 
 

Record Order On Form Gets Scrambled with SQL

Post by sjs032 » Mon, 15 Sep 2003 04:59:32


"Larry Stinson" < XXXX@XXXXX.COM >
: XXXX@XXXXX.COM ...
the
checkbox,
earlier
for
start
to