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>
"Access Newbie Nick" <AccessNewbieNick@[EMAIL PROTECTED]
> wrote in
message news:7A7FCBDE-806E-47CC-8334-D4E018BA916A@[EMAIL PROTECTED]
> 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.
>
> "Ken Snell (MVP)" wrote:
>
>> 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>
>>
>>
>> "Access Newbie Nick" <AccessNewbieNick@[EMAIL PROTECTED]
> wrote
>> in
>> message news:0E72520B-5B74-4712-A414-8A9D1BAD93E5@[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
>>
>>
>>
|
