How do i insert new record in between Records

[Benjamins via AccessMonster.com]

Hi,

I have a user who has enter the following in a subform.

Description Amount
========= ======
Inv0001 100.00
Inv0003 100.00
Inv0004 100.00
Inv0005 100.00
Inv0006 100.00
Inv0007 100.00
Inv0008 100.00
Inv0009 100.00
Inv0010 100.00
Tax 50.00

Later he found out that he had miss out Inv0002 and have to insert it in
between Inv0001 and Inv0003

The entry is sort by an ID which is an autonumber thus i not able to change
it. I need to do a coding to allow the user to insert a new line between
records.

How can i insert the record?

 

[Jack Leach]

There really is no way to insert a record "between" other records. You will
make a new record, Inv0002, and it goes into a table and is not sorted in any
way. Then you need to query the table with a sort order to display them in
the order you require. If you do not have a sequence number to sort them in
the order you want then, quite simply, you won't be able to. You may try
sorting by the Description field but I think with text your Inv0010 will show
up before Inv0001.

You should have a sequence field (of number datatype to properly sort), and
after you add Inv0002 (editing the sequence field as well with the desired
placement), requery the subform (Me.SubformControlName.Requery) and the
records will be placed accordingly.

As far as tables and data goes, there's no order. It's all done in a query.
So there's no putting records "between"

hth
--
Jack Leach
www.tristatemachine.com

"I haven''t failed, I''ve found ten thousand ways that don''t work."
-Thomas Edison (1847-1931)

 

[Marshall Barton]

There really is no way to insert a record "between" other records. You will
make a new record, Inv0002, and it goes into a table and is not sorted in any
way. Then you need to query the table with a sort order to display them in
the order you require. If you do not have a sequence number to sort them in
the order you want then, quite simply, you won't be able to. You may try
sorting by the Description field but I think with text your Inv0010 will show
up before Inv0001.

You should have a sequence field (of number datatype to properly sort), and
after you add Inv0002 (editing the sequence field as well with the desired
placement), requery the subform (Me.SubformControlName.Requery) and the
records will be placed accordingly.

Inv0001 will sort before Inv0010 because the leading zeros
are in there.

 

[Marshall Barton]

I have a user who has enter the following in a subform.

Description Amount
========= ======
Inv0001 100.00
Inv0003 100.00
Inv0004 100.00
Inv0005 100.00
Inv0006 100.00
Inv0007 100.00
Inv0008 100.00
Inv0009 100.00
Inv0010 100.00
Tax 50.00

Later he found out that he had miss out Inv0002 and have to insert it in
between Inv0001 and Inv0003

The entry is sort by an ID which is an autonumber thus i not able to change
it. I need to do a coding to allow the user to insert a new line between
records.

What Jack says about table sorting is how you should think
about the "order" of records. To reiterate, a table is just
a bag of records with no order. Sorting can only be done in
a query.

If your Description field always has "Inv" as the first
three characters, then they are redundant and should be
omitted. You can always display the number with the "Inv"
by using a simple expression in a report text box. Even
that should be unnecessary as it's not an overwhelmingly
complex thing for users to know that 0010 is just as
informative as Inv0010. If the field did not have the "Inv"
in it, then it could be the sequence number that Jack
recommends.

 

[vanderghast]

Append the record. Sort or Requery the recordset (assuming the recordset is
open with an ORDER BY clause in its supporting SQL statement).

You should not bother yourself about the position of the 'record' in the
table, there is no such thing in a table (in fact, there is not even real
"table" either, but that is another story).
In a recordset, though, that is another 'beast', and while the initial order
by clause would 'break' if you change the value making the order or if you
append records to it, the new records appearing at the end of the recordset,
resorting or requerying the recordset should restablish the ordering.


Vanderghast, Access MVP

 

[Benjamins via AccessMonster.com]

Hi,

The printout has to be printed printed as the order of input thus i can only
sort by the ID.

I think of a method but not sure how the coding goes

Here is my idea:
This process will be done on key down on the recrod location that wanted to
insert into.
1. It will add a new blank record.
2. the record below will copy the record above it excludeing those above the
cursor location.
3. null the fields of the cursor location

There really is no way to insert a record "between" other records. You will
make a new record, Inv0002, and it goes into a table and is not sorted in any
way. Then you need to query the table with a sort order to display them in
the order you require. If you do not have a sequence number to sort them in
the order you want then, quite simply, you won't be able to. You may try
sorting by the Description field but I think with text your Inv0010 will show
up before Inv0001.

You should have a sequence field (of number datatype to properly sort), and
after you add Inv0002 (editing the sequence field as well with the desired
placement), requery the subform (Me.SubformControlName.Requery) and the
records will be placed accordingly.

As far as tables and data goes, there's no order. It's all done in a query.
So there's no putting records "between"

hth

Hi,

[quoted text clipped - 21 lines]

How can i insert the record?

 

[Marshall Barton]

The printout has to be printed printed as the order of input thus i can only
sort by the ID.

I think of a method but not sure how the coding goes

Here is my idea:
This process will be done on key down on the recrod location that wanted to
insert into.
1. It will add a new blank record.
2. the record below will copy the record above it excludeing those above the
cursor location.
3. null the fields of the cursor location

Sorry, I have no idea what you are trying to describe.

If you want to sort the records in the order they are
entered, regardless of the Inv### field, then you should add
a field to the table and set the field's DefaultValue
property to =Now()

 

[Benjamins via AccessMonster.com]

Hi,

For my example, the user had to put the Inv0002 in between Inv0001 and
Inv0003 as where Inv0002 is entered at a later timing.

Example for the Entry
1)The user entered the following
ID Description Amt
1 Inv0001 100
2 Inv0003 300
2 Inv0004 400

2)He later found out that he had left out Inv0002 thus pressing on a assigned
function key will allow the user to "Insert" one field so the user can enter
Inv0002 between Inv0001 and Inv0003
ID Description Amt
1 Inv0001 100
2
3 Inv0003 300
4 Inv0004 400

The printout has to be printed printed as the order of input thus i can only
sort by the ID.

[quoted text clipped - 8 lines]

cursor location.
3. null the fields of the cursor location

Sorry, I have no idea what you are trying to describe.

If you want to sort the records in the order they are
entered, regardless of the Inv### field, then you should add
a field to the table and set the field's DefaultValue
property to =Now()

 

[BruceM via AccessMonster.com]

There is no "between" in a table. You need to use a query for that, or a
report's sorting and grouping capabilities. Sort in whatever order you want.

If the idea is that invoice 0002 needs to be numbered "2", here are some ways
to number a list:
http://allenbrowne.com/ranking.html

Consider using a custom autonumber rather than requiring the user to input
invoice numbers manually. One possibility is here:

http://www.rogersaccesslibrary.com/download3.asp?SampleName=AutonumberProblem.mdb


For display purposes you can format the result "0000" and add a leading "Inv".

Hi,

For my example, the user had to put the Inv0002 in between Inv0001 and
Inv0003 as where Inv0002 is entered at a later timing.

Example for the Entry
1)The user entered the following
ID Description Amt
1 Inv0001 100
2 Inv0003 300
2 Inv0004 400

2)He later found out that he had left out Inv0002 thus pressing on a assigned
function key will allow the user to "Insert" one field so the user can enter
Inv0002 between Inv0001 and Inv0003
ID Description Amt
1 Inv0001 100
2
3 Inv0003 300
4 Inv0004 400
[quoted text clipped - 8 lines]

a field to the table and set the field's DefaultValue
property to =Now()

 

[vanderghast]

If you use a form (or a recordet), append the record AT THE END, as usual,
and once it is done, in the after update event of the form, sort the form so
that the new record will then find its VISUAL position where the sorting
expression expect it to be.

Again, there is no POSITION in a TABLE. There are pages of data. These pages
get splitted in two when they get overfilled and the data move with the
pages and in the end, the data may not be stored AT ALL as you VISUALLY see
it. A database is not a spreadsheet: the data is not necessary STORED as you
VISUALLY see it. Recordsets are something extra built over TABLEs, and
records in recordset have position, determined at the creation of the
recordset, with new records are appended at the end. You need to requery (or
close the recordset and re-open it) to re-establish a new position, in the
recodset, or to explicitly SORT on some criteria to 'recalculate' the
relative position of the records, in the recordset.


Vanderghast, Access MVP

Hi,

For my example, the user had to put the Inv0002 in between Inv0001 and
Inv0003 as where Inv0002 is entered at a later timing.

Example for the Entry
1)The user entered the following
ID Description Amt
1 Inv0001 100
2 Inv0003 300
2 Inv0004 400

2)He later found out that he had left out Inv0002 thus pressing on a
assigned
function key will allow the user to "Insert" one field so the user can
enter
Inv0002 between Inv0001 and Inv0003
ID Description Amt
1 Inv0001 100
2
3 Inv0003 300
4 Inv0004 400

The printout has to be printed printed as the order of input thus i can
only
sort by the ID.

[quoted text clipped - 8 lines]

cursor location.
3. null the fields of the cursor location

Sorry, I have no idea what you are trying to describe.

If you want to sort the records in the order they are
entered, regardless of the Inv### field, then you should add
a field to the table and set the field's DefaultValue
property to =Now()

 

[Marshall Barton]

You really need to come to terms with the fact that
databases (Access or any other) are not spreadsheets. You
can easily get the data to display the way you want *after*
the Inv0002 record has been created.

Trying to create a placeholder record with just an ID field
is a overly complex, roundabout way of doing things that can
lead to all kinds of potential ways of messing up the
information. Get rid of your function key stuff and just
let users enter the missing record in the normal way. The
form's AfterInsert or AfterUpdate event only needs to
Requery the form to have the records displayed the way you
want (which is not the order they were entered).
--
Marsh
MVP [MS Access]

For my example, the user had to put the Inv0002 in between Inv0001 and
Inv0003 as where Inv0002 is entered at a later timing.

Example for the Entry
1)The user entered the following
ID Description Amt
1 Inv0001 100
2 Inv0003 300
2 Inv0004 400

2)He later found out that he had left out Inv0002 thus pressing on a assigned
function key will allow the user to "Insert" one field so the user can enter
Inv0002 between Inv0001 and Inv0003
ID Description Amt
1 Inv0001 100
2
3 Inv0003 300
4 Inv0004 400

The printout has to be printed printed as the order of input thus i can only
sort by the ID.

[quoted text clipped - 8 lines]

cursor location.
3. null the fields of the cursor location

Sorry, I have no idea what you are trying to describe.

If you want to sort the records in the order they are
entered, regardless of the Inv### field, then you should add
a field to the table and set the field's DefaultValue
property to =Now()