Inserting Incremental number

[Guest]

Hi there!

I have a table of Assessors [TblAssessors] and a field [RegNo] which is
filled in later when trainees have successfully passed. This is later used on
a Certificate of Registration. As not all trainees will successfully
complete, I'd like to create a macro that will assign an incremental [RegNo]
on a button's click event on my data entry form "FrmAssessors".
Unfortunately, I have inherited this data and [RegNo] is in the format of
"QTA00101","QTA00102", etc. The prefix will always be "QTA" and at this stage
we do not envisage ever needing more than 5 digits to follow.
I seem to recall seeing a post somewhere on this site a while ago regarding
something similar but I can't locate it now.
I believe it dealt with creating a table to store the last used number and
when executed, the code looks up that table, increments the stored number by
1, writes it to the formfield and then stores the new number back in the
number storage table.
Have I got that right? Is it possible to do in this case with the QTA prefix?
Anyone have some code that might help?

Any help most gratefully received!

 

[Larry Daugherty]

For the prefix portion of your registration numbers you could create a
Global Constant in a standard module named something like RegPrefix.
You can always refer to that constant and concatenate it with the
incrementing count when requuired for display. Don't store it in the
same field as the incrementing portion.

The incrementing portion will be stored in
[TblAssessors].[RegNo]. You can place it in the text control of the
curently open form and it will be stored in the table for you.

The function you want is DMax(). The code behind your command button
will go something like the below. I'll leave it to you to doublecheck
the code by digging in Help if necessary.

me!txtRegNum = DMax("[RegNo]", "TblAssessors")+1

HTH

 

[Tom van Stiphout]

On Fri, 12 Oct 2007 01:14:00 -0700, AnnieB

That is one way to do it (google for 'TakeANumber'); another one is to
realize that your RegNo can be a plain old regular numeric autonumber.
Then when you DISPLAY that number, you prefix "QTA".
So your ControlSource could be:
= 'QTA' & [RegNo]

-Tom.

 

[Guest]

Thanks Larry,

I've got it working with your help, although I simplified it by removing the
QTA and putting that as a label in front of my [RegNo] formfield and the
[RegNo]mergefield in the Certificate.doc. Same end result for historic
consistency - easier to manage (for me!)

So - now I simply have this:

--------------------------------------
Private Sub AssignRegNo_Click()
Me!RegNo = Format(DMax("RegNo", "TblAssessors") + 1,"\00000")
End Sub
-------------------------------------
Lovely - thanks!
--
AnnieB
Basic Babe in the Woods

For the prefix portion of your registration numbers you could create a
Global Constant in a standard module named something like RegPrefix.
You can always refer to that constant and concatenate it with the
incrementing count when requuired for display. Don't store it in the
same field as the incrementing portion.

The incrementing portion will be stored in
[TblAssessors].[RegNo]. You can place it in the text control of the
curently open form and it will be stored in the table for you.

The function you want is DMax(). The code behind your command button
will go something like the below. I'll leave it to you to doublecheck
the code by digging in Help if necessary.

me!txtRegNum = DMax("[RegNo]", "TblAssessors")+1

HTH
--
-Larry-
--

Hi there!

I have a table of Assessors [TblAssessors] and a field [RegNo] which is
filled in later when trainees have successfully passed. This is later used on
a Certificate of Registration. As not all trainees will successfully
complete, I'd like to create a macro that will assign an incremental [RegNo]
on a button's click event on my data entry form "FrmAssessors".
Unfortunately, I have inherited this data and [RegNo] is in the format of
"QTA00101","QTA00102", etc. The prefix will always be "QTA" and at this stage
we do not envisage ever needing more than 5 digits to follow.
I seem to recall seeing a post somewhere on this site a while ago regarding
something similar but I can't locate it now.
I believe it dealt with creating a table to store the last used number and
when executed, the code looks up that table, increments the stored number by
1, writes it to the formfield and then stores the new number back in the
number storage table.
Have I got that right? Is it possible to do in this case with the QTA prefix?
Anyone have some code that might help?

Any help most gratefully received!

 

[Guest]

Hi Tom,

I had already started working on another solution (see previous reply), but
thanks for your input . It's great that you guys give of your time to help
out those of us who struggle!


--
AnnieB
Basic Babe in the Woods

On Fri, 12 Oct 2007 01:14:00 -0700, AnnieB

That is one way to do it (google for 'TakeANumber'); another one is to
realize that your RegNo can be a plain old regular numeric autonumber.
Then when you DISPLAY that number, you prefix "QTA".
So your ControlSource could be:
= 'QTA' & [RegNo]

-Tom.

Hi there!

I have a table of Assessors [TblAssessors] and a field [RegNo] which is
filled in later when trainees have successfully passed. This is later used on
a Certificate of Registration. As not all trainees will successfully
complete, I'd like to create a macro that will assign an incremental [RegNo]
on a button's click event on my data entry form "FrmAssessors".
Unfortunately, I have inherited this data and [RegNo] is in the format of
"QTA00101","QTA00102", etc. The prefix will always be "QTA" and at this stage
we do not envisage ever needing more than 5 digits to follow.
I seem to recall seeing a post somewhere on this site a while ago regarding
something similar but I can't locate it now.
I believe it dealt with creating a table to store the last used number and
when executed, the code looks up that table, increments the stored number by
1, writes it to the formfield and then stores the new number back in the
number storage table.
Have I got that right? Is it possible to do in this case with the QTA prefix?
Anyone have some code that might help?

Any help most gratefully received!