Inserting Incremental number

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!

-- 
AnnieB
Basic Babe in the Woods

0
Utf
10/12/2007 8:14:00 AM
access 16762 articles. 2 followers. Follow

3 Replies
718 Views

Similar Articles

[PageSpeed] 37

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-
--

"AnnieB" <AnnieB@discussions.microsoft.com> wrote in message
news:AD99482D-90C7-4D37-B2BE-44052C546295@microsoft.com...
> 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!
>
> -- 
> AnnieB
> Basic Babe in the Woods
>


0
Larry
10/13/2007 1:56:16 AM
On Fri, 12 Oct 2007 01:14:00 -0700, AnnieB
<AnnieB@discussions.microsoft.com> wrote:

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!
0
Tom
10/13/2007 4:30:14 AM
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



"Larry Daugherty" wrote:

> 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-
> --
> 
> "AnnieB" <AnnieB@discussions.microsoft.com> wrote in message
> news:AD99482D-90C7-4D37-B2BE-44052C546295@microsoft.com...
> > 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!
> >
> > -- 
> > AnnieB
> > Basic Babe in the Woods
> >
> 
> 
> 
0
Utf
10/13/2007 6:11:01 AM
Reply:

Similar Artilces:

format cell to show zero if not a positive number
How can I format a column of figures to show that if the result is zero or negative amount it will just show a zero or be left blank. I am attempting to show the difference between figures that have been invoiced to figures that have been added and thus need to be invoiced. Use a custom format like #,##0.00_);; or #,##0.00_);"0";"0" HTH, Bernie MS Excel MVP "TechnoGram" <mumzee3@hotmail.com> wrote in message news:4183a06c-5eb1-4699-9efd-fd8a4bdd4e07@d62g2000hsf.googlegroups.com... > How can I format a column of figures to show that if the result i...

Translate any english number into English word
I am a beginner. it may be a silly question. In excel, suppose in one cell a number is appearing as 9. I want to write in another cell as "Nine". How it will be possible? SANTANU Hi, See here http://support.microsoft.com/default.aspx?scid=kb;en-us;213360 'How to convert a numeric value into English words in Excel' "SANTANU" wrote: > I am a beginner. it may be a silly question. > > In excel, suppose in one cell a number is appearing as 9. I want to write in > another cell as "Nine". > > How i...

Looking for cells containing numbers
Hi, in a column with cells containing either text or numbers I have to exclude those with text in an arithmetic formula as is runs through the whole column, hence avoiding an error as a result when reading text cells. A cloumn holding an intermediate result of the reading as "0" or "1" or something will be just fine. How to? Cheers, Thorkild You might be thinking of something like this ? Assuming the col with text/numbers is col A, A1 down Put in B1: =IF(ISTEXT(A1),0,A1) Copy B1 down Then use col B instead of col A for downstream calcs -- Rgds Max xl 97 --- GMT+8, ...

How do I insert Roman Numerals?
New to Word 2007 would someone please explain to me how to insert Roman Numerlas in my document? "ckj" <ckj@discussions.microsoft.com> wrote in message news:6CF0C580-BA6E-463D-B4FD-E6A6FDD9E814@microsoft.com... > New to Word 2007 would someone please explain to me how to insert Roman > Numerlas in my document? Just type them... I II III IV V VI VII VIII IX X XI etc etc. Type a number e.g. 21, select it and run the following macro Dim oRng As Range Set oRng = Selection.Range oRng.Fields.Add oRng, 34, oRng.Text & " \*Roman"...

Showing a date field as a number
Hi. I was wondering if there was any way that I could have a column showing the date and time (20/12/2007 06:00:00) and another column show the date and time as a number, (29548). This would be done in a select query, but I am not sure how? Is it possible? Regards AJ AJ, Use format... mm/dd/yy hh:nn:ss for 20/12/2007 06:00:00 Use format... #.0000 (with Decimal Places = to suit your needs) for 39436.2500 Use format... # (with Decimal Places = 0) for 39436 -- hth Al Campagna Microsoft Access MVP http://home.comcast.net/~cccs...

Insert trigger
Looking for some advice on SQL 2005. I have a table that will usually be populated by an SSIS package. I want to set the "loaddate" column to the current time after a record is inserted. Should i do this via trigger or should i just build a step in the SSIS package to update the column after the file loads? If trigger is the way to go, what is the syntax to create the after insert trigger? Thanks in advance. You can create a default constraint on the table set to CURRENT_TIMESTAMP. That will handle the automatic date assignment without any need for coding. -...

inserting hrs and minutes
I have a cell in my time card that displays total weekly time -ex- "40:15" is there a way to make it more like this...40hrs,15mins -- Message posted from http://www.ExcelForum.com Use a custom format hh"hrs",mm"mins" -- Regards, Peo Sjoblom "-Brian-H- >" <<Brian-H-.110wgs@excelforum-nospam.com> wrote in message news:Brian-H-.110wgs@excelforum-nospam.com... > I have a cell in my time card that displays total weekly time -ex- > "40:15" is there a way to make it more like this...40hrs,15mins ? > > > ...

insert an interactive excel file into word web page
I'm trying to insert a excel file into a word document with text, and then save it was as a web page, but I want to keep the excel part interactive. Any ideas? ...

Insert | File > Attachmnet-Button Drop Down ;What is the difference between Insert and Insert As Attachmnet
re: "Outlook2003, File-Insert-Options" On making new-email with Attachment-File(s), ** File Menu | Insert | File >>> (Brows and select File to insert ) then we can see the button "Insert", and write side Drop Down Arrow lower-right side of Dialog Box; If it clicked, we can see three options as follows: ** Insert Insert as text Insert as Attachment I can not recognize/understand the difference between "Insert" and "Insert as Attachment" *** What is the difference between Insert and Insert As Attachment ? I would appreciate y...

Finding labor total for multimple employees in 15 min. increments.
Is this possible to do in excel? I would like to see my total labor for multiple employees in 15 minut increments. What I would like to be able to do is to enter in my employees' hourl rate (once) and when my employees clock in/clock out (daily). Fro this I would like to know what my total labor cost is every 15 mins o each day. Currently my spread sheet shows me what my sales are every 15 mins an I would like to know what my total labor for all employees is every 1 mins. From this I can chart labor and sales on the same graph. Please help me! THANK YOU -- Message posted from http:...

What about routing numbers when printing checks
I had bought a pack of versacheck refills, which are compatable with money, and was looking forward to printing my checks at home. Well after setup, i went to print a few checks. The checks orinted fine and within margins, but i notice that there arent any routing numbers, my name or address, or anything on it to make this a valid and redeemable check. Am i missing something?? I have MS money 2003 and am runnning XP. On Fri, 29 Aug 2003 01:33:55 -0700, "MGidish" <MGidish@cox-internet.com> wrote: >I had bought a pack of versacheck refills, which are >compatable ...

Inserting Hyperlinks in a Protected Sheet
Hi I run Excel 2000 and I have a protected worksheet that I share wit users in my organisation. I want to allow the insertion of a hyperlin to a specific file type within a specified directory on our server. 3 Questions: 1.Protection on disables the insert hyperlink command. Can this b overcome with worksheet activate code? 2.Can I limit the types of files (preferably by requiring the file t meet a mask format eg "z-*.xls")? 3.Can I limit the directory that can be linked, by referring to pathname stored in a cell on the active sheet? Would appreciate your suggestions. Thanks S...

Colour numbers within my formulae
I have been playing around with the '&' function, allowing me to mix text with calculations and cell references. I have the following at the moment: ="Of the "&TEXT(J954,"�#,0.00")&" made this year, I have managed to save "&TEXT(100/J954*J955, "#,#0.0")&"%" Which gives me a cell displaying the following: Of the �0.00 made this year, I have managed to save 0.0% What I would like to do is show the numbers (�0.00; 0.0%) in the colour blue. I don't want to add conditional formatting, just apply a colour so t...

Insert with a where condition
Hi, sql 2005 I have an insert statement that is ignoring the where condition. That is, I want to insert records when they do not already exist in the destination table. INSERT INTO dbo.tblmnuGroupPerm ( gId ,mtfID ,... ) SELECT @gID ,mtfID ,... FROM dbo.locmnuTabFunction AS ltf WHERE ltf.mtfID NOT IN ( SELECT gp.mtfID FROM dbo.tblmnuGroupPerm AS gp WHERE gp.gId=@gID AND gp.Deleted=0 ) Any ideas or recommendations appreciated :-) Many thanks, Jonathan It's OK... <oops "redFace">I did not correctly se...

Insert
I want to overtype in a Publisher text box. I find I can only insert. The "insert" key doesn't do anything. ...

Auto Sum unknown number of rows
Im using odbc to import data from a database. Imported is a list of products in column A and then various columns containing sales figures. Because of the import from the database, I do not know how many rows of products will appear, this week we sell 500 different products, next week 550. I need to add totals to the columns directly under the last product. I've tried creating the totals in an empty template and then inserting the imported data so the total figures move down and appear at the bottom. However the imported data moves the total cells to the right instead of down. Tried ...

My columns are numbers too!!!
My comulmns are not letters anymore....they are numbers. What did I do, and how can I change it back? Richard Go to Tools / Options / General and uncheck R1/C1 reference style Andy. "Richard G" <anonymous@discussions.microsoft.com> wrote in message news:73f501c4020b$28d0b9c0$a101280a@phx.gbl... > My comulmns are not letters anymore....they are numbers. > What did I do, and how can I change it back? Andy, Thanks. That was driving me crazy! Richard >-----Original Message----- >Richard > >Go to Tools / Options / General and uncheck R1/C1 reference ...

how to insert data in a table
Hi Exprets; I am creating an access database in which I want to insert data in already created table. Kindly help. Regards, Vikky Vikky <love.excel@gmail.com> wrote in news:1194124711.012302.269990 @e34g2000pro.googlegroups.com: > Hi Exprets; > > I am creating an access database in which I want to insert data in > already created table. > > Kindly help. > > Regards, > > Vikky > Data from where? Do you want to import it from excel, from a text file, copy it from another table or type it in manually? -- Bob Quintal PA is y I've altere...

How do you insert page numbers larger than 1000?
I have my purchase orders set up as a Publisher document. When our organization upgraded from Publisher 2000 to Publisher 2002, the new version set parameters on the page numbers. This was one of those things that worked just fine in the previous version... Does anyone know how to turn it off or change it? Hi mregen (mregen@discussions.microsoft.com), in the newsgroups you posted: || I have my purchase orders set up as a Publisher document. When our || organization upgraded from Publisher 2000 to Publisher 2002, the new || version set parameters on the page numbers. This was one of those...

insert downloads into power point
i downloaded an application called "BioDigital Simulator" of an animated cleft lip/palate surgery and need it put into my power point presentation, but can't fiugre out how to do so.... very frustrating... What kind of file is this application? Is it a video? If so, what kind? MPEG? AVI? MOV? Or is it an EXE file? Something else? Which version of PPT are you using? -- Echo [MS PPT MVP] http://www.echosvoice.com What's new in PPT 2010? http://www.echosvoice.com/2010.htm Fixing PowerPoint Annoyances http://tinyurl.com/36grcd PowerPoint 2007 Complete Makeover...

Custom Number Format #9
I'm trying to set a cell format to either $ or % determined by the value input by the selection of one of two lists dependent on a $ or % selection criteria in another cell. If the value input is within the range of -1 to 1, I need it to display in a percentage format. If it is less than -1 or greater than 1, it needs to display as a currency format (or comma w/ 2 decimals). I prefer using data validation lists on this sheet instead of other controls due to their larger size. The version is Excel 2000. Any assistance is appreciated. You could use a helper cell to show the f...

want to convert number to word (eg) 1 as one
I would like to convert automaticaly the numericals into word format fo example In excel If I type 100.00 ( it should type automatically (one hundre only). Is it available in excel. If anyone knows pls reply to my e-mail wintersc@vsnl.ne -- Message posted from http://www.ExcelForum.com It is not available by default, but this is an often-asked question in this forum. You may want to do a search and see if you find your answer. Otherwise, are you looking for a formula or a VBA macro? If you're looking to put the text in a different cell from the number, then a formula will work. If ...

Insert a blank row
Hi, I need some help to insert a blank row in a range where column A has a series of dates. There will be several of the same dates and I need to both sort the dates and then insert the blank row at the end of each sequence. In the blank row I need to total figures that will be in columns B through to G. Thanks, Jim S Hi Jim maybe an easier solution 1. Sort your data (use 'Data - Sort', e.g., column A ascending) 2. Use 'Data - Subtotals' This will insert a row after each date and calculate subtotals automatically for you HTH Frank anon wrote: > Hi, > I need some...

Week number from date
Hi all, I have the code below in a text box which is returning the week number from text box 118 on a report, it is returning week 4 when the date in the text box is 18th January 2010 when actually it should read week 3, can anyone tell me what is wrong? Thanks =DatePart("ww",[text118]) Hi Blake Check the options for designating the first week of year. Regards Kevin Constant Value Year Starts with Week vbUseSystem 0 Use the system setting. vbFirstJan1 1 The week in which January 1 falls (the default setting). v...

how do i increase the number of digits displayed
I am a person dealing with large numericals with digits more than 20 in number(for eg. visa no:s and phone card no:s)..when I type in a no: such as 12345678912345678912 and enter the no: it is displayed as 1.23457E-27 how do i enter such large no:s and have them displayed as such i.e without the aforementioned formatting Excel goes to 15 max so format as text or 'at the start of the number -- Don Guillett SalesAid Software donaldb@281.com "ameen" <ameen@discussions.microsoft.com> wrote in message news:36BF725E-CFDB-42A1-8F25-9020C7C82681@microsoft.com... > I am ...