sorting on a subform

  • Thread starter Thread starter ash
  • Start date Start date
A

ash

Hi All,

I have a Transactions subform with many fields including
an autonumber field called TransactionID and a text field
called AccountNumber. I've entered data for
AccountNumber=000 to AccountNumber=300 in ascending
order. On the table, you can see that it's stored in
order by TransactionID. But, on the subform, the data
displayed is not in the order that I've entered them, for
example:

Entered: AccountNumber=000,001,002,003,004,005
AutoNumber: TransactionID=200,201,202,203,204,205
Data Displayed on the subform: 002,003,004,005,000,001

Even though I have the Order By property on the subform
to equal to TransactionID, it doesn't resolve the issue.
FYI: I'm using Windows XP.

Thanks for your help.
Ash
 
ash said:
Hi All,

I have a Transactions subform with many fields including
an autonumber field called TransactionID and a text field
called AccountNumber. I've entered data for
AccountNumber=000 to AccountNumber=300 in ascending
order. On the table, you can see that it's stored in
order by TransactionID. But, on the subform, the data
displayed is not in the order that I've entered them, for
example:

Entered: AccountNumber=000,001,002,003,004,005
AutoNumber: TransactionID=200,201,202,203,204,205
Data Displayed on the subform: 002,003,004,005,000,001

Even though I have the Order By property on the subform
to equal to TransactionID, it doesn't resolve the issue.
FYI: I'm using Windows XP.

Thanks for your help.
Ash
Click to expand...


Access, as with almost all relational databases, stores data pretty
randomly, so the order in which you entered records doesn't actually
mean much. One way in which you can make it so, is to store a
date/time stamp value in a Field in each record. then you can sort on
that Field to give the order in which the records were entered.

You imply that you have the TrandsactionID as an Autonumber. From the
values you show for that field, I think that's highly unlikely.
Besides, using Autonumber for something visible to users or to have any
functional meaning is a definite no-no. Autonumber will develope gaps,
become random and may go negative, depending on your database design
and use. Autonumber is only intended to give a unique identifier to
records and that is all.

You don't say what relationship you have set between the main Form's
(account?) Table and the Subform's (transaction?) Table. That
information might help.

hth

Hugh
 
Is your subform based on a query? If so, order the query by
TransactionID to see if that helps..

Dave
 
Back
Top