ANSI sequence in concencated vba

[Wayne-I-M]

Hi

I have 2 tables
Groups and Clients

Groups = GroupID, etc, etc
Clients = ClientID, GroupID, BookRef, etc,etc

GroupID and ClientID are auto numbers and BookRef is text.

I have a form that sets the value of the BookRef like this

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then

Me.ClientID = Nz(DMax("[ClientID]", "tblClients", "[GroupID] = " &
Me.GroupID)) + 1

Me.BookRef = Me.GroupID & "-" & Me.ClientID & "-" &
Right(Str(Year([Forms]![frmGroups]![TripStart])), 2)

End If
End Sub

Trip start is a date field on the main form - frmGroups

This works fine and you get a BookRef like this (incremented 1 for each new
client in any given group)
GroupID - ClientID - Year(last 2 digits)

BUT
I hav had some comments from users that this "looks" like a "date" eg.
if you have a group 10 and then book 9 people into this on a event that is
taking pace during 2009 you will get
10-9-09
Now this does not matter to the admin staff but it can confused clients. So
I was thinking how to increment the ClientID within the BookRef so it would
look like this
10-I-09
10-J-09
10-K-09
etc.

I have looked at

Me.BookRef = Me.GroupID & "-" & Chr(Asc(Nz(DMax("[ClientID]", "tblClients",
"[GroupID] =
" & Me.GroupID),"@")) + 1) & "-" &
Right(Str(Year([Forms]![frmGroups]![TripStart])), 2)

So added this
Chr(Asc(Nz(DMax("[ClientID]", "tblClients", "[GroupID] =
" & Me.GroupID),"@")) + 1)
But it's still not getting there - been trying for quite a while,

Any ideas would be really helpfull

Many thanks

 

[Tom van Stiphout]

On Mon, 3 Dec 2007 14:36:00 -0800, Wayne-I-M

Or use tildes rather than dashes: 10~9~09 does not look like a date.
Or take out the separators altogether.

I'm not thrilled about the ASCII route: what if the ClientID is
greater than 26 = Z?

BTW, I would never display ID values to end users. They are for
internal purposes. If you show them, invariably users will want
changes, and then you have a RI problem.

-Tom.

 

[Wayne-I-M]

Hi Tom

Thans for answering

I'm not thrilled about the ASCII route: what if the ClientID is
greater than 26 = Z?

In the last 25 years there has never been a group larger than 15 - but
you're right it may happen. I will look at putting a limit of the groups
size - just in case.

This is a "booking reference" not an ID value. We must give people a
reference to identify "thier" booking - in case of problems. Using this
method just means the reference has some meaning rather than just a set of
digits.

I take you point about the "tides" (never hear that english word before- am
learning all the time)

I would still like a method incrementing text though - ??
--
Wayne
Manchester, England.

On Mon, 3 Dec 2007 14:36:00 -0800, Wayne-I-M

Or use tildes rather than dashes: 10~9~09 does not look like a date.
Or take out the separators altogether.

I'm not thrilled about the ASCII route: what if the ClientID is
greater than 26 = Z?

BTW, I would never display ID values to end users. They are for
internal purposes. If you show them, invariably users will want
changes, and then you have a RI problem.

-Tom.

Hi

I have 2 tables
Groups and Clients

Groups = GroupID, etc, etc
Clients = ClientID, GroupID, BookRef, etc,etc

GroupID and ClientID are auto numbers and BookRef is text.

I have a form that sets the value of the BookRef like this

Private Sub Form_BeforeInsert(Cancel As Integer)
If Me.NewRecord Then

Me.ClientID = Nz(DMax("[ClientID]", "tblClients", "[GroupID] = " &
Me.GroupID)) + 1

Me.BookRef = Me.GroupID & "-" & Me.ClientID & "-" &
Right(Str(Year([Forms]![frmGroups]![TripStart])), 2)

End If
End Sub

Trip start is a date field on the main form - frmGroups

This works fine and you get a BookRef like this (incremented 1 for each new
client in any given group)
GroupID - ClientID - Year(last 2 digits)

BUT
I hav had some comments from users that this "looks" like a "date" eg.
if you have a group 10 and then book 9 people into this on a event that is
taking pace during 2009 you will get
10-9-09
Now this does not matter to the admin staff but it can confused clients. So
I was thinking how to increment the ClientID within the BookRef so it would
look like this
10-I-09
10-J-09
10-K-09
etc.

I have looked at

Me.BookRef = Me.GroupID & "-" & Chr(Asc(Nz(DMax("[ClientID]", "tblClients",
"[GroupID] =
" & Me.GroupID),"@")) + 1) & "-" &
Right(Str(Year([Forms]![frmGroups]![TripStart])), 2)

So added this
Chr(Asc(Nz(DMax("[ClientID]", "tblClients", "[GroupID] =
" & Me.GroupID),"@")) + 1)
But it's still not getting there - been trying for quite a while,

Any ideas would be really helpfull

Many thanks

 

[Tom van Stiphout]

On Mon, 3 Dec 2007 21:36:00 -0800, Wayne-I-M

If you don't assign meaning to the letters, you could look into a
topic called "base-64 encoding".

I wrote "tilde". It's the name of the ~ character (at least in the
USA). See Accessories > System Tools > Character Map.

-Tom.