Adding a Record to a Form Out of Order

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

The Access database was created as an indices of
investigative cases, which we wanted to appear in
sequetential order by year. However, because a case
opened in 1998 was called 98-001, and a case opened in
2004 was called 04-001, the 2004 cases were appearing
first. Therefore, we added a "dummy" field to assign
numbers chronologically (i.e. 1 - 2000) so that the cases
would stay in the order they were entered.

However, here is the problem. A case was skipped and
needs to be added. If I insert it at the end of the new
records or through "insert a new record", it is assigned
a "dummy" number, which would be the most recent number
assigned, so the case number itself would be out of
order. I can't figure out how to add a record out of
order without having it automatically appear as the last
entered record, which would make it out of order. I know
I've done it before, but I cannot remember how. Please
help!! Thanks, Lisa
 
Hi Lisa,

Is this "dummy" field an autonumber field? Hopefully it is not since
autonumbers are not reliable for sequencing (and they're a pain to reorder).
If it is not an autonumber field then you can just write an update query to
find all record which have a value in the dummy field greater than or equal
to the number you need and increment the SeqCaseNum by 1. Then insert the
new record.

You should probably stop and reconsider your table design - the initial
problem is due to the fact that your case number field is being used to
store two values, year and case number (for the year). If you break this
into two fields, the original sorting problem goes away because you can sort
on the fields independantly but show them appended for display purposes. If
you were to do this, then the second field, the sequential case number
field, would not be an autonumber field. Instead it would need to be a
custom counter field which you increment using one of the custom counter
strategies (more on this later). You could still have an autonumber field,
but it would then be used strictly as a primary key making it easier to
create relationships between other tables.

tblCases
--------------
CaseID (Primary, autoNumber)
CaseYear *
SeqCaseNum *
.. . . other fields

* Part of a unique index

CaseNum (what your users are used to seeing) becomes a field that exists
only in queries and is a calculated field based on appending CaseYear with
SeqCaseNum.

Now, you still have the problem of squeezing in a record between two case
numbers (now looking only at the SeqCaseNum field for the given year). To do
this you would create an update query to find all records matching the
specified year and with SeqCaseNumber greater than or equal to the number
you need and increment the SeqCaseNum by 1. Then insert the new record.
 
Thanks for the detailed response. However, the bad news
is that the dummy field is an autonumber field. Now what?
-----Original Message-----
Hi Lisa,

Is this "dummy" field an autonumber field? Hopefully it is not since
autonumbers are not reliable for sequencing (and they're a pain to reorder).
If it is not an autonumber field then you can just write an update query to
find all record which have a value in the dummy field greater than or equal
to the number you need and increment the SeqCaseNum by 1. Then insert the
new record.

You should probably stop and reconsider your table design - the initial
problem is due to the fact that your case number field is being used to
store two values, year and case number (for the year). If you break this
into two fields, the original sorting problem goes away because you can sort
on the fields independantly but show them appended for display purposes. If
you were to do this, then the second field, the sequential case number
field, would not be an autonumber field. Instead it would need to be a
custom counter field which you increment using one of the custom counter
strategies (more on this later). You could still have an autonumber field,
but it would then be used strictly as a primary key making it easier to
create relationships between other tables.

tblCases
--------------
CaseID (Primary, autoNumber)
CaseYear *
SeqCaseNum *
.. . . other fields

* Part of a unique index

CaseNum (what your users are used to seeing) becomes a field that exists
only in queries and is a calculated field based on appending CaseYear with
SeqCaseNum.

Now, you still have the problem of squeezing in a record between two case
numbers (now looking only at the SeqCaseNum field for the given year). To do
this you would create an update query to find all records matching the
specified year and with SeqCaseNumber greater than or equal to the number
you need and increment the SeqCaseNum by 1. Then insert the new record.


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

The Access database was created as an indices of
investigative cases, which we wanted to appear in
sequetential order by year. However, because a case
opened in 1998 was called 98-001, and a case opened in
2004 was called 04-001, the 2004 cases were appearing
first. Therefore, we added a "dummy" field to assign
numbers chronologically (i.e. 1 - 2000) so that the cases
would stay in the order they were entered.

However, here is the problem. A case was skipped and
needs to be added. If I insert it at the end of the new
records or through "insert a new record", it is assigned
a "dummy" number, which would be the most recent number
assigned, so the case number itself would be out of
order. I can't figure out how to add a record out of
order without having it automatically appear as the last
entered record, which would make it out of order. I know
I've done it before, but I cannot remember how. Please
help!! Thanks, Lisa
Click to expand...


.
Click to expand...
 
My preference would be to correct the design :-). I say it with a smile but
I am actually serious because you may find that using a autonumber as a
sequencer is problematic.

If that is not possible then make a backup of the database. Then make copy
of the table. Delete all the rows from the original table. Compact the
database. Now using the copy add the new record. You may need to change the
autonum field in the copied table to a number field so you can resequence it
before you add the new record - unless you have other fields that you can
use for sequencing (case date?). Create an append query using the data from
the copied table and including all fields except the autonum field , ordered
by the appropriate sequencing field(s). Delete the copied table when you're
satisfied with the results.

--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

Thanks for the detailed response. However, the bad news
is that the dummy field is an autonumber field. Now what?
-----Original Message-----
Hi Lisa,

Is this "dummy" field an autonumber field? Hopefully it is not since
autonumbers are not reliable for sequencing (and they're a pain to
reorder). If it is not an autonumber field then you can just write
an update query to find all record which have a value in the dummy
field greater than or equal to the number you need and increment the
SeqCaseNum by 1. Then insert the
new record.

You should probably stop and reconsider your table design - the initial
problem is due to the fact that your case number field is being used to
store two values, year and case number (for the year). If you break this
into two fields, the original sorting problem goes away because you
can sort on the fields independantly but show them appended for
display purposes. If you were to do this, then the second field, the
sequential case number field, would not be an autonumber field.
Instead it would need to be a
custom counter field which you increment using one of the custom counter
strategies (more on this later). You could still have an autonumber
field, but it would then be used strictly as a primary key making it
easier to create relationships between other tables.

tblCases
--------------
CaseID (Primary, autoNumber)
CaseYear *
SeqCaseNum *
.. . . other fields

* Part of a unique index

CaseNum (what your users are used to seeing) becomes a field that
exists only in queries and is a calculated field based on appending
CaseYear with SeqCaseNum.

Now, you still have the problem of squeezing in a record between two
case numbers (now looking only at the SeqCaseNum field for the given year). To do
this you would create an update query to find all records matching the
specified year and with SeqCaseNumber greater than or equal to the
number you need and increment the SeqCaseNum by 1. Then insert the
new record.


--
Sandra Daigle [Microsoft Access MVP]
Please post all replies to the newsgroup.

The Access database was created as an indices of
investigative cases, which we wanted to appear in
sequetential order by year. However, because a case
opened in 1998 was called 98-001, and a case opened in
2004 was called 04-001, the 2004 cases were appearing
first. Therefore, we added a "dummy" field to assign
numbers chronologically (i.e. 1 - 2000) so that the cases
would stay in the order they were entered.

However, here is the problem. A case was skipped and
needs to be added. If I insert it at the end of the new
records or through "insert a new record", it is assigned
a "dummy" number, which would be the most recent number
assigned, so the case number itself would be out of
order. I can't figure out how to add a record out of
order without having it automatically appear as the last
entered record, which would make it out of order. I know
I've done it before, but I cannot remember how. Please
help!! Thanks, Lisa
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top