New Records

  • Thread starter Thread starter Bob Rice
  • Start date Start date
B

Bob Rice

I want to insert records into a table, not only just at the end of the
table. I need to keep the order of the records in the table and present
them to the user in that order. The user has to be able to insert records
into specific locations.
 
An Access table doesn't have any such thing as an "end of the table."
Records are simply dumped into a bucket. If you want them in some particular
order, you have to use a query and sort them on some field that will give
you the order you want.
 
I need to keep them in the same order that the user originally inputs them.
They are to be unsorted, and remain that way.

--
Bob Rice

Lynn Trapp said:
An Access table doesn't have any such thing as an "end of the table."
Records are simply dumped into a bucket. If you want them in some
particular
order, you have to use a query and sort them on some field that will give
you the order you want.
Click to expand...
 
I need to keep them in the same order that the user originally inputs
them.
They are to be unsorted, and remain that way.
Click to expand...

In any relational database system, Access included, records are NOT stored
in any kind of order. If the user puts the records in 1, 2, 3 order, there
is no guarantee that the database will store them in that order. Therefore,
you MUST use sorting in a query to get them in the order you want. There is
no way around it.
 
Bob,

Try date/time stamping the records. Then you can use that to sort in the
manner that Lynn described.

Jason
 
I wasn't as clear as I should have been. I have the following records in
the field, "Items" in a table:

Record 1 Wand
Record 2 Apple
Record 3 Car

I need to always show these records in the above order. The records are not
sorted, and I have no criteria on which to sort them.
A new record is added, "House". It is to be added and shown between
"Apple" and "Car", so that the data are presented:

Wand
Apple
House
Car

Thanks all.
 
Add an extra field, say, SortOrder of type long. In the table create an index
UserOrder based on the User & the SortOrder. Make it unique. This means that
the SortOrder is unique to each user but do NOT make the SortOrder itself
unique as user 1 will have a SortOrder of 1 as will user 2 but we do not want
user 1 to have two SortOrder 1.

In the BeforeUpdate event check that the user has not changed the sort
order. If s/he has you will have to revise the sort orders. Another way would
be to lock that specific field's box & have a button in, say, the header to
revise the sort order a bit like the TAB order in form design. Another way is
to let the punter take care of it (not so good). To change the order of one
s/he would have to renumber all below from the last backwards to get the
space then put in the new number. If s/he duplicates a number the system will
whinge and block the change (remember we made the User/Order unique). Anyway,
I'm sure you'll come up with something

Messy but it does work. I am using it in some of my databases where, for
example, I want to display adminintration positions in a particular order and
the combinations of positions varies. In the database to track my software
different software packages have Activation codes, Unlock codes, User
codes.......... (you know what I mean) & I want them to be in the right
order, for example you enter the CD code before the Activation Cade.

Bob Rice said:
I wasn't as clear as I should have been. I have the following records in
the field, "Items" in a table:

Record 1 Wand
Record 2 Apple
Record 3 Car

I need to always show these records in the above order. The records are not
sorted, and I have no criteria on which to sort them.
A new record is added, "House". It is to be added and shown between
"Apple" and "Car", so that the data are presented:

Wand
Apple
House
Car

Thanks all.
Click to expand...
 
Unless you follow an approach like Terry suggests, you can't do that in
Access. There just is no natural order to the way records are entered and or
displayed. You will need to add a field to create the ordering for you and
then you will need to develop a way to maintain that field.
--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm


Bob Rice said:
I wasn't as clear as I should have been. I have the following records in
the field, "Items" in a table:

Record 1 Wand
Record 2 Apple
Record 3 Car

I need to always show these records in the above order. The records are not
sorted, and I have no criteria on which to sort them.
A new record is added, "House". It is to be added and shown between
"Apple" and "Car", so that the data are presented:

Wand
Apple
House
Car

Thanks all.
Click to expand...
 
Back
Top