Form records out of order

  • Thread starter Thread starter Jason Kneip
  • Start date Start date
J

Jason Kneip

I have created a simple database to track calls in my
office. I am fairly new at databases and using Access
2000. I have a call_logging form I use to input data that
is linked to my call_contact table. The form has record
navigation buttons at the bottom that I use to review
entries. My problem is that the records are out of
order. Here are how my records are out of order:

Record 34 corresponds with CaseID# 40
" 35 " " " 65
" 49 " " " 79
" 50 " " " 42

The caseid's are set to autonumber and my date is auto
inserted when the record is created. When I view
call_contact table it lists the records in order. Also,
my form won't display my first record (caseid # 1).

Why is this happening??
 
Try placing a query between the table and the form and sort as desired in
the query. To place the query between the table and the form, create a query
and add the table to it. Add the fields you need in the form to the bottom
part of the query and set the desired sort order. You can sort on more than
one field, they will be sorted as listed from left to right. In the form,
change its Record Source to the query instead of the table.
 
Hi,
Records are not stored in any particular order. That's just the way databases work.
If you want them displayed in a certain order you have to use a Where clause in your query.
So use a query as the record source for your form and have something like:
Order By [yourDateField],
or use the Query Designer and just set the Sort field to ascending for that field.

Also you should be aware that an AutoNumber is strictly to provide you with a
unique primary key, it will not always be sequential, there will be gaps.
This number should have no meaning to users.

The 'record number' you see in your form has nothing to do with the AutoNumber,
so often they will not match!
 
Hi,

Thanks for the response. I created a query from my
call_contact table and set the date and caseid to sort
ascending. My form pulls client information in from my
client_utility table and I have text boxes for problem &
resolution that goes to my call_contact table. I changed
all fields that were linked to my call_contact table to
point toward my call_contact_query and changed the Order
by to my [Date] field but they still show out of order.

Am I missing something? Also, why wouldn't my first
record display as well?

Thanks,
Jason
-----Original Message-----
Hi,
Records are not stored in any particular order. That's just the way databases work.
If you want them displayed in a certain order you have to
Click to expand...
use a Where clause in your query.
So use a query as the record source for your form and have something like:
Order By [yourDateField],
or use the Query Designer and just set the Sort field to ascending for that field.

Also you should be aware that an AutoNumber is strictly to provide you with a
unique primary key, it will not always be sequential, there will be gaps.
This number should have no meaning to users.

The 'record number' you see in your form has nothing to do with the AutoNumber,
so often they will not match!
--
HTH
Dan Artuso, Access MVP


"Jason Kneip" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
 
To sort the date field properly, its data type has to be Date/Time. If it is
text, it won't sort properly. By chance has a filter or sort order been
applied directly to the form? Check Filter and Order By on the Data tab of
the form's Properties sheet.

--
Wayne Morgan
Microsoft Access MVP


Jason Kneip said:
Hi,

Thanks for the response. I created a query from my
call_contact table and set the date and caseid to sort
ascending. My form pulls client information in from my
client_utility table and I have text boxes for problem &
resolution that goes to my call_contact table. I changed
all fields that were linked to my call_contact table to
point toward my call_contact_query and changed the Order
by to my [Date] field but they still show out of order.

Am I missing something? Also, why wouldn't my first
record display as well?

Thanks,
Jason
-----Original Message-----
Hi,
Records are not stored in any particular order. That's just the way databases work.
If you want them displayed in a certain order you have to
Click to expand...
use a Where clause in your query.
So use a query as the record source for your form and have something like:
Order By [yourDateField],
or use the Query Designer and just set the Sort field to ascending for that field.

Also you should be aware that an AutoNumber is strictly to provide you with a
unique primary key, it will not always be sequential, there will be gaps.
This number should have no meaning to users.

The 'record number' you see in your form has nothing to do with the AutoNumber,
so often they will not match!
--
HTH
Dan Artuso, Access MVP


"Jason Kneip" <[email protected]> wrote
Click to expand...
in message news:[email protected]...
.
Click to expand...
Click to expand...
 
I haven't applied a filter (don't know how to) and my
table and my date field is set to date/time for data type
with a default value of =date(). On my form properties
under the data tab I have [Date] listed for Order by.
-----Original Message-----
To sort the date field properly, its data type has to be Date/Time. If it is
text, it won't sort properly. By chance has a filter or sort order been
applied directly to the form? Check Filter and Order By on the Data tab of
the form's Properties sheet.

--
Wayne Morgan
Microsoft Access MVP


Hi,

Thanks for the response. I created a query from my
call_contact table and set the date and caseid to sort
ascending. My form pulls client information in from my
client_utility table and I have text boxes for problem &
resolution that goes to my call_contact table. I changed
all fields that were linked to my call_contact table to
point toward my call_contact_query and changed the Order
by to my [Date] field but they still show out of order.

Am I missing something? Also, why wouldn't my first
record display as well?

Thanks,
Jason
-----Original Message-----
Hi,
Records are not stored in any particular order. That's just the way databases work.
If you want them displayed in a certain order you have
Click to expand...
to
use a Where clause in your query.
So use a query as the record source for your form and have something like:
Order By [yourDateField],
or use the Query Designer and just set the Sort field to ascending for that field.

Also you should be aware that an AutoNumber is strictly to provide you with a
unique primary key, it will not always be sequential, there will be gaps.
This number should have no meaning to users.

The 'record number' you see in your form has nothing to do with the AutoNumber,
so often they will not match!
--
HTH
Dan Artuso, Access MVP


"Jason Kneip" <[email protected]>
Click to expand...
wrote
in message news:ab2c01c47964$f66dad90 [email protected]...
I have created a simple database to track calls in my
office. I am fairly new at databases and using Access
2000. I have a call_logging form I use to input data that
is linked to my call_contact table. The form has record
navigation buttons at the bottom that I use to review
entries. My problem is that the records are out of
order. Here are how my records are out of order:

Record 34 corresponds with CaseID# 40
" 35 " " " 65
" 49 " " " 79
" 50 " " " 42

The caseid's are set to autonumber and my date is auto
inserted when the record is created. When I view
call_contact table it lists the records in order. Also,
my form won't display my first record (caseid # 1).

Why is this happening??




.
Click to expand...
Click to expand...


.
Click to expand...
 
Jason Kneip said:
I haven't applied a filter (don't know how to) and my
table and my date field is set to date/time for data type
with a default value of =date(). On my form properties
under the data tab I have [Date] listed for Order by.
Click to expand...

The OrderBy property is useless unless you also have the property OrderByOn
set to True. OrderByOn is not shown in the property sheet and thus has to
be set in code when you open the form. It's a lot less trouble to just use
a query that specifies the sort order.
 
In addition to Rick's comment, Date is a reserved word. It is the current
date. You should change the name of the field to something else to avoid
possible unwanted results.
 
Back
Top