DblClick Event

[johnlute]

I'm trying to revise a double click event from a database that I
didn't design. I'm having trouble understanding the logic of the
author. Here's what I have:

Private Sub RDno_DblClick(Cancel As Integer)
Dim WeirdNum As String
Dim WN1 As String
Dim WN2 As Integer
Dim MyNumber As Integer
Dim OKVar As Double
Dim dbs As Database, rst As Recordset

WeirdNum = DLookup("[MaxOfRDno]", "highestrdnum")
WN1 = Left(WeirdNum, 1)
WN2 = Right(WeirdNum, 3)
OKVar = WN2 + 1
If WN2 = 999 Then
MyNumber = Asc(WN1)
WN1 = Chr(MyNumber + 1)
OKVar = 0
End If
[RDno] = WN1 & Format(OKVar, "000")
[YR] = Format(Now, "yy")
'Save the Record so no one else can use it
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

This generates [RDno] values that increment:
A000
A001
A002...
B000
B001
B002...
C000
C001
C002...

We've reached Z999 and it now has nowhere to go. I'm trying to change
it so that it will increment like this:
Z999
AA000
AA001
AA002...
BB000
BB001
BB002...
CC000
CC001
CC002...

If I can do this then I'll eventually get to ZZ999 with nowhere to go.

How can I resolve the first increment challenge of going from "single-
letter, triple-number" to "double-letter, triple-number"?

Would it be possible to code this to go on "forever"? For example,
could the letters continuously increment to triple then quadruple,
etc. with the triple-number simply incrementing 000-999?

Also, while compiling the debugger points to:
Dim dbs As Database, rst As Recordset

and states: User-defined type not defined

I've checked my references and don't see any missing. Any ideas why
this is tripping?

Thanks for any help you might have!

 

[Tom van Stiphout]

On Wed, 28 Oct 2009 06:14:51 -0700 (PDT), johnlute

For 2 letters you shouldn't go AA - BB - ZZ because that only gives
you 26 combinations. Rather go AA - AB ... AZ - BA ... BZ etc which
gives you 26-squared combinations.

This gives you the letter(s):
WN1 = Left(WeirdNum,Len(WeirdNum)-3)

To increment it, grab the last number, and increment it like it is
done currently, and if that results in a Z, work your way back in the
string and increment those character(s) as well.

It's a bit too much to bang this out in a few minutes, but a
professional developer should have this done for you in less than an
hour.

-Tom.
Microsoft Access MVP

 

[Dorian]

Why are you even doing this? If you need a unique key, there are much better
ways to achieve it.
Obviously to do what you want, you have to recognize when the number hits
Z999 and then increment to AA01. From that point on you have to recognize
there are two leading alpha characters and only increment the last two
numbers. This is a simple matter of some IF....ELSE tests.
-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

 

[johnlute]

Hi, Dorian.

Why are you even doing this? If you need a unique key, there are much better
ways to achieve it.

That's why I posted here You guys are much smarter than I!

Obviously to do what you want, you have to recognize when the number hits
Z999 and then increment to AA01. From that point on you have to recognize
there are two leading alpha characters and only increment the last two
numbers. This is a simple matter of some IF....ELSE tests.

Isn't that what the code currently does...?

-- Dorian
"Give someone a fish and they eat for a day; teach someone to fish and they
eat for a lifetime".

I'm trying to revise a double click event from a database that I
didn't design. I'm having trouble understanding the logic of the
author. Here's what I have:

Private Sub RDno_DblClick(Cancel As Integer)
Dim WeirdNum As String
Dim WN1 As String
Dim WN2 As Integer
Dim MyNumber As Integer
Dim OKVar As Double
Dim dbs As Database, rst As Recordset

  WeirdNum = DLookup("[MaxOfRDno]", "highestrdnum")
  WN1 = Left(WeirdNum, 1)
  WN2 = Right(WeirdNum, 3)
  OKVar = WN2 + 1
  If WN2 = 999 Then
     MyNumber = Asc(WN1)
     WN1 = Chr(MyNumber + 1)
     OKVar = 0
  End If
  [RDno] = WN1 & Format(OKVar, "000")
  [YR] = Format(Now, "yy")
  'Save the Record so no one else can use it
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

This generates [RDno] values that increment:
A000
A001
A002...
B000
B001
B002...
C000
C001
C002...

We've reached Z999 and it now has nowhere to go. I'm trying to change
it so that it will increment like this:
Z999
AA000
AA001
AA002...
BB000
BB001
BB002...
CC000
CC001
CC002...

If I can do this then I'll eventually get to ZZ999 with nowhere to go.

How can I resolve the first increment challenge of going from "single-
letter, triple-number" to "double-letter, triple-number"?

Would it be possible to code this to go on "forever"? For example,
could the letters continuously increment to triple then quadruple,
etc. with the triple-number simply incrementing 000-999?

Also, while compiling the debugger points to:
Dim dbs As Database, rst As Recordset

and states: User-defined type not defined

I've checked my references and don't see any missing. Any ideas why
this is tripping?

Thanks for any help you might have!
.- Hide quoted text -

- Show quoted text -

 

[johnlute]

Hi, Tom!

For 2 letters you shouldn't go AA - BB - ZZ because that only gives
you 26 combinations. Rather go AA - AB ... AZ - BA ... BZ etc which
gives you 26-squared combinations.

You are correct! In some haste I typed the wrong concept. My notes
actually have it your way. Brain cramps!

This gives you the letter(s):
WN1 = Left(WeirdNum,Len(WeirdNum)-3)

I tried that and also tweaked a few other things. Incidentally, I'm
working on a database that was being run by multiple users over a WAN!
It's NOT split and had become corrupted. I'm amazed that I was able to
export out of it what I could - they had no backups!!!

Anyway, here's what I've got:
Private Sub RDno_DblClick(Cancel As Integer)
Dim WeirdNum As String
Dim WN1 As String
Dim WN2 As Integer
Dim MyNumber As Integer
Dim OKVar As Double
'Dim dbs As Database, rst As Recordset

WeirdNum = DLookup("[MaxOfRDno]", "qryHighestRDno")
WN1 = Left(WeirdNum, Len(WeirdNum) - 3)
WN2 = Right(WeirdNum, 3)
OKVar = WN2 + 1
If WN2 = 999 Then
MyNumber = Asc(WN1)
WN1 = Chr(MyNumber + 1)
OKVar = 0
End If
[RDno] = WN1 & Format(OKVar, "000")
[YR] = Format(Now, "yy")
'Save the Record so no one else can use it
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

The highest number in the database is currently Z999. When I run the
code it results in the new record "[000" without the quotes.

Any ideas why?

Thanks!

To increment it, grab the last number, and increment it like it is
done currently, and if that results in a Z, work your way back in the
string and increment those character(s) as well.

It's a bit too much to bang this out in a few minutes, but a
professional developer should have this done for you in less than an
hour.

-Tom.
Microsoft Access MVP

I'm trying to revise a double click event from a database that I
didn't design. I'm having trouble understanding the logic of the
author. Here's what I have:

Private Sub RDno_DblClick(Cancel As Integer)
Dim WeirdNum As String
Dim WN1 As String
Dim WN2 As Integer
Dim MyNumber As Integer
Dim OKVar As Double
Dim dbs As Database, rst As Recordset

 WeirdNum = DLookup("[MaxOfRDno]", "highestrdnum")
 WN1 = Left(WeirdNum, 1)
 WN2 = Right(WeirdNum, 3)
 OKVar = WN2 + 1
 If WN2 = 999 Then
    MyNumber = Asc(WN1)
    WN1 = Chr(MyNumber + 1)
    OKVar = 0
 End If
 [RDno] = WN1 & Format(OKVar, "000")
 [YR] = Format(Now, "yy")
 'Save the Record so no one else can use it
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

This generates [RDno] values that increment:
A000
A001
A002...
B000
B001
B002...
C000
C001
C002...

We've reached Z999 and it now has nowhere to go. I'm trying to change
it so that it will increment like this:
Z999
AA000
AA001
AA002...
BB000
BB001
BB002...
CC000
CC001
CC002...

If I can do this then I'll eventually get to ZZ999 with nowhere to go.

How can I resolve the first increment challenge of going from "single-
letter, triple-number" to "double-letter, triple-number"?

Would it be possible to code this to go on "forever"? For example,
could the letters continuously increment to triple then quadruple,
etc. with the triple-number simply incrementing 000-999?

Also, while compiling the debugger points to:
Dim dbs As Database, rst As Recordset

and states: User-defined type not defined

I've checked my references and don't see any missing. Any ideas why
this is tripping?

Thanks for any help you might have!- Hide quoted text -

- Show quoted text -

 

[Tom van Stiphout]

On Wed, 28 Oct 2009 14:15:58 -0700 (PDT), johnlute

You need to learn how to use the debugger. Set a breakpoint and step
through your code. Inspect the values. It will tell you a lot.

Additionally use the Immediate window. For example I entered:
?asc("Z"), asc("[")
90 91

Get it?

-Tom.
Microsoft Access MVP

Hi, Tom!

For 2 letters you shouldn't go AA - BB - ZZ because that only gives
you 26 combinations. Rather go AA - AB ... AZ - BA ... BZ etc which
gives you 26-squared combinations.

You are correct! In some haste I typed the wrong concept. My notes
actually have it your way. Brain cramps!

This gives you the letter(s):
WN1 = Left(WeirdNum,Len(WeirdNum)-3)

I tried that and also tweaked a few other things. Incidentally, I'm
working on a database that was being run by multiple users over a WAN!
It's NOT split and had become corrupted. I'm amazed that I was able to
export out of it what I could - they had no backups!!!

Anyway, here's what I've got:
Private Sub RDno_DblClick(Cancel As Integer)
Dim WeirdNum As String
Dim WN1 As String
Dim WN2 As Integer
Dim MyNumber As Integer
Dim OKVar As Double
'Dim dbs As Database, rst As Recordset

WeirdNum = DLookup("[MaxOfRDno]", "qryHighestRDno")
WN1 = Left(WeirdNum, Len(WeirdNum) - 3)
WN2 = Right(WeirdNum, 3)
OKVar = WN2 + 1
If WN2 = 999 Then
MyNumber = Asc(WN1)
WN1 = Chr(MyNumber + 1)
OKVar = 0
End If
[RDno] = WN1 & Format(OKVar, "000")
[YR] = Format(Now, "yy")
'Save the Record so no one else can use it
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

The highest number in the database is currently Z999. When I run the
code it results in the new record "[000" without the quotes.

Any ideas why?

Thanks!

To increment it, grab the last number, and increment it like it is
done currently, and if that results in a Z, work your way back in the
string and increment those character(s) as well.

It's a bit too much to bang this out in a few minutes, but a
professional developer should have this done for you in less than an
hour.

-Tom.
Microsoft Access MVP

I'm trying to revise a double click event from a database that I
didn't design. I'm having trouble understanding the logic of the
author. Here's what I have:

Private Sub RDno_DblClick(Cancel As Integer)
Dim WeirdNum As String
Dim WN1 As String
Dim WN2 As Integer
Dim MyNumber As Integer
Dim OKVar As Double
Dim dbs As Database, rst As Recordset

 WeirdNum = DLookup("[MaxOfRDno]", "highestrdnum")
 WN1 = Left(WeirdNum, 1)
 WN2 = Right(WeirdNum, 3)
 OKVar = WN2 + 1
 If WN2 = 999 Then
    MyNumber = Asc(WN1)
    WN1 = Chr(MyNumber + 1)
    OKVar = 0
 End If
 [RDno] = WN1 & Format(OKVar, "000")
 [YR] = Format(Now, "yy")
 'Save the Record so no one else can use it
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

End Sub

This generates [RDno] values that increment:
A000
A001
A002...
B000
B001
B002...
C000
C001
C002...

We've reached Z999 and it now has nowhere to go. I'm trying to change
it so that it will increment like this:
Z999
AA000
AA001
AA002...
BB000
BB001
BB002...
CC000
CC001
CC002...

If I can do this then I'll eventually get to ZZ999 with nowhere to go.

How can I resolve the first increment challenge of going from "single-
letter, triple-number" to "double-letter, triple-number"?

Would it be possible to code this to go on "forever"? For example,
could the letters continuously increment to triple then quadruple,
etc. with the triple-number simply incrementing 000-999?

Also, while compiling the debugger points to:
Dim dbs As Database, rst As Recordset

and states: User-defined type not defined

I've checked my references and don't see any missing. Any ideas why
this is tripping?

Thanks for any help you might have!- Hide quoted text -

- Show quoted text -

 

[johnlute]

Thanks, Tom.

You need to learn how to use the debugger.

I agree.

Set a breakpoint and step
through your code. Inspect the values. It will tell you a lot.

Additionally use the Immediate window. For example I entered:
?asc("Z"), asc("[")
 90            91

Get it?

Not at the moment but thanks for the direction.

 

[Tom van Stiphout]

On Thu, 29 Oct 2009 07:04:09 -0700 (PDT), johnlute

Perhaps I was a bit terse. Sorry about that.
You wrote that you were surprised that after Z, you got [.
I'm thinking my example points out that the ASCII value of [ comes
after the ASCII value of Z, so I'm not surprised if your code is
trying to increment the letter, after Z it simply continues on and [
is the next letter in the ASCII alphabet.

What your code should do is stop at Z, and introduce a second letter,
so you'd be at AA. Then go up to AZ and again realize this is the end
of a sequence and need to go to BA.

-Tom.
Microsoft Access MVP

Thanks, Tom.

You need to learn how to use the debugger.

I agree.

Set a breakpoint and step
through your code. Inspect the values. It will tell you a lot.

Additionally use the Immediate window. For example I entered:
?asc("Z"), asc("[")
 90            91

Get it?

Not at the moment but thanks for the direction.

 

[johnlute]

Thanks for the clarification, Tom.

I see your point now. Ultimately, this one is a bit beyond my grasp.
I'm sure I could eventually grasp it but it's not my database so I'm
inclined to tell the owners (who know a LOT less than I) that they
should seek more professional help as my time is selfishly needed for
my own projects.

I appreciate your help!

On Thu, 29 Oct 2009 07:04:09 -0700 (PDT), johnlute


Perhaps I was a bit terse. Sorry about that.
You wrote that you were surprised that after Z, you got [.
I'm thinking my example points out that the ASCII value of [ comes
after the ASCII value of Z, so I'm not surprised if your code is
trying to increment the letter, after Z it simply continues on and [
is the next letter in the ASCII alphabet.

What your code should do is stop at Z, and introduce a second letter,
so you'd be at AA. Then go up to AZ and again realize this is the end
of a sequence and need to go to BA.

-Tom.
Microsoft Access MVP

Thanks, Tom.

I agree.

Set a breakpoint and step
through your code. Inspect the values. It will tell you a lot.
Additionally use the Immediate window. For example I entered:
?asc("Z"), asc("[")
 90            91
Get it?

Not at the moment but thanks for the direction.- Hide quoted text -

- Show quoted text -