A - Z in record

[Design by Sue]

I am creating a database to keep track of car trips. Each trip can simply be
from point A to point B and then the return trip of point B to point A.
Other times there will be additional stops on the trip.

I have created a form with a subform. The main form records the complete
trip and assigns a trip number to it. The subform records each leg of the
trip. On this subfomr I need to assign a letter (A,B,C, etc.) to each leg of
the trip. When the user goes to the next record on the main form, the
subform has to reset to A.

I know this should be easy, but there is SOOOO much to learn about Access!!!!

 

[Design by Sue]

I can't believe I haven't gotten a reply - usually someone responds within a
few hours. Did I stump you? Lord knows I'm stumped. I reread my post and
it makes sense to me but perhaps I'm not wording it correctly. If anyone has
any thoughts on this please let me know. I'm stopped because I can't do any
more work on this til I get it worked out.

As always - I really appreciate this board - this is a first!

 

[Brendan Reynolds]

Something like ...

In the current event of the main form, if .NewRecord, stick the letter "A"
into a hidden text box (or maybe the Tag property of the form if you're not
using it for anything else). If not .NewRecord, you'll have to count the
number of related records to determine what the starting letter should be.

Then in the AfterInsert event of the subform, something like ...

Me!LegField = Me.Parent.Tag
Me.Parent.Tag = Chr$(Asc(Me.Parent.Tag)+1))

I'm assuming here that there will never be more than 26 legs in one journey.
If there is, you'll end up with a leg "[", as Chr$(Asc("Z")+1) = "[".

 

[Design by Sue]

Thank you for your reply - I figured if I bumped this up someone with the
answer would see it.

Something like ...

In the current event of the main form, if .NewRecord, stick the letter "A"
into a hidden text box (or maybe the Tag property of the form if you're not
using it for anything else). If not .NewRecord, you'll have to count the
number of related records to determine what the starting letter should be.

Then in the AfterInsert event of the subform, something like ...

Me!LegField = Me.Parent.Tag
Me.Parent.Tag = Chr$(Asc(Me.Parent.Tag)+1))

I'm assuming here that there will never be more than 26 legs in one journey.
If there is, you'll end up with a leg "[", as Chr$(Asc("Z")+1) = "[".

--
Brendan Reynolds (MVP)

I can't believe I haven't gotten a reply - usually someone responds within
a
few hours. Did I stump you? Lord knows I'm stumped. I reread my post
and
it makes sense to me but perhaps I'm not wording it correctly. If anyone
has
any thoughts on this please let me know. I'm stopped because I can't do
any
more work on this til I get it worked out.

As always - I really appreciate this board - this is a first!