Use record number of subform to assign letters

[Access Newbie Nick]

I have a subform in which each record has a letter starting from A and
preceding alphabetically. I want to make it so that if one record in the
subform is deleted, the letters will automatically change to accomodate it
with no gaps.

For example

ID Record Number(in subform) Letter
34 1 A
35 2 B <-----
delete this record
36 3 C

then goes to

ID Record Number(in subform) Letter
34 1 A
36 2 B <-------
auto changed assigned letter

Any help given would be great!

Thanks

 

[Ken Snell \(MVP\)]

Assuming that you're using a query as the subform's RecordSource, add a
calculated field to the query that will give you the desired letter:

SELECT ID, [Record Number], Chr(63 + [Record Number]) AS Letter
FROM YourTableName
ORDER BY [RecordN Number];

 

[Access Newbie Nick]

Thanks for your reply,

However i think there are a couple of problems due because there is no
actual field with the name [Record Number]: I mean the number that is on the
navigation bar at the bottom of the subform. This value has to come from the
subform itself as the underlying query contains all records for each value in
the main form, also autonumber would create gaps.
Either that or letter the records beggining with A, beggining at A again at
every time a new [PO Main ID] (the linked field to the main form) value
arises in the underlying query.
The latter would be the most beneficial.
I am aware that probably i havent explained it well so any thing that you
may need to know please ask!

Thank you again Ken.

Assuming that you're using a query as the subform's RecordSource, add a
calculated field to the query that will give you the desired letter:

SELECT ID, [Record Number], Chr(63 + [Record Number]) AS Letter
FROM YourTableName
ORDER BY [RecordN Number];

--

Ken Snell
<MS ACCESS MVP>

I have a subform in which each record has a letter starting from A and
preceding alphabetically. I want to make it so that if one record in the
subform is deleted, the letters will automatically change to accomodate it
with no gaps.

For example

ID Record Number(in subform) Letter
34 1 A
35 2 B <-----
delete this record
36 3 C

then goes to

ID Record Number(in subform) Letter
34 1 A
36 2 B <-------
auto changed assigned letter

Any help given would be great!

Thanks

 

[Ken Snell \(MVP\)]

OK. One approach to doing this would be to leave the RecordSource query
unchanged, and instead change the ControlSource of that textbox that shows
the Letter to this expression:
=Chr([CurrentRecord] + 64)

This works fine for a Single Form view in my testing.

However, there is a bug in ACCESS 2003 if you use Continuous Forms view.
This bug does not properly handle the repainting of the records to show the
correct Letter information as you move through the records or scroll through
the form. So, if you're using a Continuous Forms view in your form (which
your description suggests that you are), then it will be necessary to use a
slightly complicated setup to show the correct Letter. In this case, we'll
need to use a "ranking" subquery to get the correct letter combination, so
your subform's RecordSource query would need to be changed

SELECT ID, [PO Main ID],
Chr((SELECT Count(*) FROM YourTableName T
WHERE T.[PO Main ID] = YourTableName.[PO Main ID]
AND T.ID <= YourTableName.ID) + 64) AS Letter
FROM YourTableName
ORDER BY [PO Main ID], [ID];

--

Ken Snell
<MS ACCESS MVP>

Thanks for your reply,

However i think there are a couple of problems due because there is no
actual field with the name [Record Number]: I mean the number that is on
the
navigation bar at the bottom of the subform. This value has to come from
the
subform itself as the underlying query contains all records for each value
in
the main form, also autonumber would create gaps.
Either that or letter the records beggining with A, beggining at A again
at
every time a new [PO Main ID] (the linked field to the main form) value
arises in the underlying query.
The latter would be the most beneficial.
I am aware that probably i havent explained it well so any thing that you
may need to know please ask!

Thank you again Ken.

Assuming that you're using a query as the subform's RecordSource, add a
calculated field to the query that will give you the desired letter:

SELECT ID, [Record Number], Chr(63 + [Record Number]) AS Letter
FROM YourTableName
ORDER BY [RecordN Number];

--

Ken Snell
<MS ACCESS MVP>


in
message news:[email protected]...

I have a subform in which each record has a letter starting from A and
preceding alphabetically. I want to make it so that if one record in
the
subform is deleted, the letters will automatically change to accomodate
it
with no gaps.

For example

ID Record Number(in subform) Letter
34 1 A
35 2 B <-----
delete this record
36 3 C

then goes to

ID Record Number(in subform) Letter
34 1 A
36 2 B
<-------
auto changed assigned letter

Any help given would be great!

Thanks

 

[Access Newbie Nick]

Thank you!!! It is in single form view so i used something similar to what
you suggested for that, but as i needed it to write onto the underlying table
and query i used it as a macro, it works ok but the only problem is it only
corrects the record that it currently shows. The letters usually go no
further than D or E so it is no big problem. It is useful to know that i can
use the expression [CurrentRecord] and Chr(blah blah) and i may be able to
put them to use in other places. Thanks again for ur help.