Record Order On Form Gets Scrambled with SQL

  • Thread starter Thread starter Larry Stinson
  • Start date Start date
L

Larry Stinson

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!
 
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
)?


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!
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
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.
John Nurick said:
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
)?


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!
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
 
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.

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.
John Nurick said:
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
)?


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!
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Larry Stinson said:
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!
Click to expand...
 
Back
Top