Auto Number - Start over each year

Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields.  In 
the bound form, frmProvider, I would like txtProcessProviderNumber to 
increment by 1
on each new record each new record entered during the Year.  The year will 
be entered manually by the user via combo box in text field.  Once the 
Process year changes to a new year I would want the numbers to start all over 
at 1.  How can I do this.  Any help would be greatly appreciated.
-- 
Gary i n Michigan, USA
0
Utf
1/17/2008 1:29:01 AM
access.formscoding 7493 articles. 0 followers. Follow

4 Replies
1286 Views

Similar Articles

[PageSpeed] 11

GaryS wrote:

>Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields.  In 
>the bound form, frmProvider, I would like txtProcessProviderNumber to 
>increment by 1
>on each new record each new record entered during the Year.  The year will 
>be entered manually by the user via combo box in text field.  Once the 
>Process year changes to a new year I would want the numbers to start all over 
>at 1.


The AutoNumber field type does not even guarantee sequential
numbers, much less starting over for any reason.

To do this kind of thing, you must use a form to
view/edit/add records.  Then you can put a little code in
the form's BeforeUpdate event to do it:

If Me.NewRecord Then
	txtProcessProviderNumber=Nz(DMax("roviderProcessNumber", _
			"tblProvider","ProcessYear=" & Me.txtProcessYear),0)+1
End If

-- 
Marsh
MVP [MS Access]
0
Marshall
1/17/2008 4:16:10 AM
Hi Marshall,

I placed the code in the beforeupdate event as you said, but it errors 
telling me that I have a Type mismatch in the criteria expression. When I 
open the debugger I see that cboProcessYear=2008, but 
txtProcessProviderNumber=0.

ProcessYear is a text field, and ProcessProviderNumber is a Number field.  
Does the code need some adjustment to accomdate the two data types?

Thanks for your help.
-- 
Gary in Michigan, USA


"Marshall Barton" wrote:

> GaryS wrote:
> 
> >Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields.  In 
> >the bound form, frmProvider, I would like txtProcessProviderNumber to 
> >increment by 1
> >on each new record each new record entered during the Year.  The year will 
> >be entered manually by the user via combo box in text field.  Once the 
> >Process year changes to a new year I would want the numbers to start all over 
> >at 1.
> 
> 
> The AutoNumber field type does not even guarantee sequential
> numbers, much less starting over for any reason.
> 
> To do this kind of thing, you must use a form to
> view/edit/add records.  Then you can put a little code in
> the form's BeforeUpdate event to do it:
> 
> If Me.NewRecord Then
> 	txtProcessProviderNumber=Nz(DMax("roviderProcessNumber", _
> 			"tblProvider","ProcessYear=" & Me.txtProcessYear),0)+1
> End If
> 
> -- 
> Marsh
> MVP [MS Access]
> 
0
Utf
1/18/2008 1:34:01 AM
Oops!  Here is the code I have so far, only slightly modified.

If Me.NewRecord Then
    txtProviderProcessNumber = Nz(DMax("providerProcessNumber", _
            "tblProviderStatus", "ProcessYear=" & Me.cboProcessYear), 0) + 1
       
End If
-- 
Gary in Michigan, USA


"GaryS" wrote:

> Hi Marshall,
> 
> I placed the code in the beforeupdate event as you said, but it errors 
> telling me that I have a Type mismatch in the criteria expression. When I 
> open the debugger I see that cboProcessYear=2008, but 
> txtProcessProviderNumber=0.
> 
> ProcessYear is a text field, and ProcessProviderNumber is a Number field.  
> Does the code need some adjustment to accomdate the two data types?
> 
> Thanks for your help.
> -- 
> Gary in Michigan, USA
> 
> 
> "Marshall Barton" wrote:
> 
> > GaryS wrote:
> > 
> > >Hi - In tblProvider I have ProcessYear and ProviderProcessNumber fields.  In 
> > >the bound form, frmProvider, I would like txtProcessProviderNumber to 
> > >increment by 1
> > >on each new record each new record entered during the Year.  The year will 
> > >be entered manually by the user via combo box in text field.  Once the 
> > >Process year changes to a new year I would want the numbers to start all over 
> > >at 1.
> > 
> > 
> > The AutoNumber field type does not even guarantee sequential
> > numbers, much less starting over for any reason.
> > 
> > To do this kind of thing, you must use a form to
> > view/edit/add records.  Then you can put a little code in
> > the form's BeforeUpdate event to do it:
> > 
> > If Me.NewRecord Then
> > 	txtProcessProviderNumber=Nz(DMax("roviderProcessNumber", _
> > 			"tblProvider","ProcessYear=" & Me.txtProcessYear),0)+1
> > End If
> > 
> > -- 
> > Marsh
> > MVP [MS Access]
> > 
0
Utf
1/18/2008 1:36:01 AM
GaryS wrote:

>Oops!  Here is the code I have so far, only slightly modified.
>
>If Me.NewRecord Then
>    txtProviderProcessNumber = Nz(DMax("providerProcessNumber", _
>            "tblProviderStatus", "ProcessYear=" & Me.cboProcessYear), 0) + 1       
>End If


Sorry, I assumed that the year field was a number.  For a
text field, use:

 . . . , "ProcessYear='" & Me.cboProcessYear & "' "), 0) + 1

-- 
Marsh
MVP [MS Access]
0
Marshall
1/18/2008 1:54:47 AM
Reply:

Similar Artilces:

limit number of rows 7 colloms in a worksheet
is there a way to limit or set the number of rows & collums in a worksheet ? thanks david --- Message posted from http://www.ExcelForum.com/ "davidbrowne17" <davidbrowne17.ya1sm@excelforum-nospam.com> wrote in message news:davidbrowne17.ya1sm@excelforum-nospam.com... > is there a way to limit or set the number of rows & collums in a > worksheet ? No. All worksheets have 256 columns by 65536 rows. You can hide unused rows/columns. But why bother? Hi David, Put this in the ThisWorkbook code module. Adjust to suit the area. Private Sub Workbook_Open() Wo...

NUMBER FORMAT #9
CREATE A NEW NUMBER FORMAT SO THAT THE SELECTED DATES WILL APPEAR ONLY AS THE FULL NAME OF THE DAYS OF THE WEEK. ...

Why are my numbers disappearing in excel yet it totals them?
I have a spreadsheet that I have filled out the individual cells with number. These cells are totaling correctly, however when I open the spreadsheet the individual number I entered are showing blank.... I moved my mouse around in the spreadhsheet and all of a sudden the numbers appeared and then disappeared. Check the font color. The default is black. Also check the cell color. Default is "no fill". If the font color was changed to white, you would only see the content after you select the cell. Remember to Click Yes, if this post helps! "Donna S...

Auto Reply #13
Can someone please let me know how to set up an Auto Reply on my email...I will be away from my office on vacation and I would like to set up an auto reply to advise anyone trying to contact me that I will be away. I am currently using Outlook 2000. Please help if you can.....You have no idea how long I've been trying to figure this out Linda You can use Outlook Rules Wizard or one of the third-party automatic rules processing addins. See http://www.slipstick.com/rules/index.htm and http://www.slipstick.com/addins/index.htm . -- Sue Mosher, Outlook MVP Author of Microsoft Outl...

Auto forwarding email
Is there a way I can have outlook automatically forward email that comes into my inbox and have it sent to a different email address. Any help would be great. Alan.... ...

No account number shown in printed checks
Using Money 2007 Deluxe. Printed a series of checks from MS Mmoney this morning, but the account numbers didn't print on the checks. Each of the payees has an account number entered in the appropriate place, in the "Go to Payees" detail listing. Can't figure out what's going on here! Thanks for any assistance. Dave On Sat, 17 Mar 2007 08:37:03 -0700, Dave M. <DaveM@discussions.microsoft.com> wrote: >Using Money 2007 Deluxe. > >Printed a series of checks from MS Mmoney this morning, but the account >numbers didn't print on the checks. Each of...

How to remove dashes and slashes form a sequence of numbers & lett
Hi I have a sequence of numbers in column D and I require to extract just the numbers and letters to column E. D 190/0-01 31-0014 pp7/44-1 uf-744-5 E 190001 310014 pp7441 uf7445 Any pointers would be much appreciated. Kind Regards Celticshadow Put this in E1: =3DSUBSTITUTE(SUBSTITUTE(D1,"/",""),"-","") and copy down as required. Hope this helps. Pete On Oct 14, 11:34=A0am, Celticshadow <Celticsha...@discussions.microsoft.com> wrote: > Hi > > I have a sequence of numbers in column D and I require to extract just th= e > ...

Windows XP Start.wav / Start Navigation sound isn't working
Hello, and thank you for reading my post. For some time now, I noticed that my PC's "click sound", which normally appears while surfing in IE 6, wasn't working. I'd learned that I'd need to visit the Start Navigation sound in Control Panel-->Sounds and Audio Devices, and select Windows XP Start.wav...which I'd do, but it wouldn't stay in place as soon as I clicked on "Apply". Because of this issue, and others, too, I reinstalled a "clean" version of Windows XP SP2, and then upgraded it (&, by extension, IE 6) to SP3,...

Money cannot start
Can anybody help me? I cannot start Money. There is an error when I open Money Deluxe 2004: Runtime error! This application has requested the Runtime to terminate it in an unusual way. Please contact the application's support team for more information. In microsoft.public.money, Pham wrote: >Can anybody help me? I cannot start Money. There is an >error when I open Money Deluxe 2004: > Runtime error! > This application has requested the Runtime to terminate >it in an unusual way. Please contact the application's >support team for more information. > ...

Auto Repsonse for Incoming Email
Sorry to ask this question but I can't seem to locate an answer in the archives. I have a distribution account account called service@abc.com. Emails are distributed to 5 users in the service department. The manager of the department would like an auto response generated for all new email acknowledging receipt of the email and thanking the customer for their business. We are running a mixture of OL 97 and 2000 on an Exchange 5.5 server. I have tried setting up a rule to reply to any inbound email to the service account but, it is not replying to the sender. Any help would be...

Jump to start of next row teaser
Hi, can anyone assist in telling me if there is either :- a) a shortcut key b) a macro c) none of the above but another solution to jumping to the beginning of a new row on pressing a key. For example, after completeing cell m10 can you press enter (or any other key) and the cursor will automatically go to cell a11, then after you get to m11 it jumps to a12 and so on? Just to make things a little trickier, I know you can do this using the protect worksheet etc function, but I also have the data filter running which doesn't work when the protect sheet function is on. If there is a macr...

trying to start a service with VBScript on Win7
Hello: I'm using Windows 7 Home Premium. I have a service which is set to manual -- I only need to run it when I'm doing development and thus not take up resources. At any rate, none of the previous scripts I have, nor the samples from the ScriptCenter will start this script. When I try to make a batch file, from command line I get: [SC] StartService: OpenService FAILED 5: Access is denied. From searching on Google, I guess this is a permission problem but since the home premium flavor of Windows 7 doesn't come with GPEdit, I'm at a loss what to do. Is...

Publishing Auto installing with refresh
I installed Office Beta 2 Refresh. I originally had not installed Publisher. However, after installing the refresh, Publisher was automatically installed. Is the refresh supposed to do this? Consider you got yourself a bonus. At least now you will have the chance to evaluate a decent program. You should be ashamed of yourself not installing Publisher originally. -- ...

Conver General number to Currency
I have a column of numbers in Excel 2003 that are of General type. I need to insert a decimal point two positions in from the right of the existing number. When I do that however by using Format > Cells and changing it to Number with 2 decimal places, or to Currency format, it adds a .00 to the existing number instead of inserting a decimal point into the existing number, e.g., 999955 come out as 999955.00 or $999,955.00 when what I really want it to do is 9999.55 or even $9,999.55. Please help! Put 100 in an empty cell, select the numbers, do edit>paste special and select divide, ...

finding differing numbers.
How do I in a column of numbers some in duplication, how can i get a list off the entries which reflects these numbers but not in duplication. ie "numbers" 1, 1, 2, 3, 4, 5, 1, 5, 3, 5, 2, 1, 2. "result" 1, 2, 3, 4, 5 Thanks Chris Hi one way: - select your column - choose 'Data - Filter Advanced Filter' - choose a new range and 'unique entries' -- Regards Frank Kabel Frankfurt, Germany curleyc wrote: > How do I in a column of numbers some in duplication, how can i get a > list off the entries which reflects these numbers but not in > duplicat...

Last Record Numbering
I need to find a way to display in a form text box the highest numerical value entered in any one of four fields for the last record entered. For example: If Field_1 is 0001 Field_2 is 0008 Field_3 is 0005 Field_4 is 0004 When the form is opened, the textbox would display the number 0008. The textbox should display the highest of the four fields of the last record. -- Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/Forums.aspx/access-forms/200708/1 Already responded to in another newsgroup. It is rarely necessary to post and re-post the same question in multiple ...

removing spaces between the numbers
Hello all! When copying number from Navision account program to Excel, it copies numbers with spaces as text, but not as numerical values. Such value: 1 109 014,08 is copied as text. One possibility to set the number actually to a numerical value is to delete the spaces within the number, so after doing that it looks as 1109014,08. Then i can add, multiply, divide this number with ease. Is there a automatic possibility how remove those spaces? Is there an alterantive? Use Search and Replace - highlight all the cells, then Edit | Replace (or CTRL-H). In the Find box type a single spac...

Negative Number Formats
Can anyone help? I have Excel 2003 and despite setting=20 all regional options correctly and specifying number=20 formats as (99), (=A399) etc I still can't get Excel to=20 display them in parentheses. I seem to remember from=20 Excel XP that you have to install/uninstall=20 certain "foreign" language options but can't remember=20 what. Please e-mail if you have the key! Thanks I'd double check that windows setting: Windows Start button|settings|control panel|Regional Settings Currency Tab|Negative Number Format (that was the path I used in Win98.) Paul Handley wro...

[$-409] in Custom Number Formats
Hello All, I've seen [$-409] in some number formats and I was looking through the help files to see if I could find out what it means. I couldn't find anything on it. I've also seen [$-F800]. Does anyone know what these codes are for or where I can find info telling me what they are for and any others that can be used? Thanks for any help anyone can provide, Conan Kelly This was covered in the Daily Dose of Excel blog about a month ago: http://www.dailydoseofexcel.com/archives/2006/02/27/months-of-the-world/ - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Techn...

count number sunday+saturday in many week
I don't know what function can count number sunday+saturday in range of weeks. Please help me. Thanks Use the function Weekday(A1) to give the day of the week (Sunday=1, Saterday=7) If you haven't this function you have to activate the Add-Inn Analysis Toolpak as follows: Tools - Add-Inns - Analysis Toolpak To calculate the number of Sundays and Saterday: =CountIf(Range,1) + CountIf(Range,7) Anne "chu" <phuongchu@fptnet.com.vn> wrote in message news:%23MeeJn6PDHA.1612@TK2MSFTNGP11.phx.gbl... > I don't know what function can count number sunday+saturday in r...

When starting Outlook Error:Unable to open your default e-mail fol
I just installed Outlook 2003 and when i open it a box comes up saying Configuring Outlook with "Migrating Account Settings" highlighted in a list. Then an error pops up saying: Unable to open your default e-mail folders This error can occur if you attempt to run Outlook from a Terminal Server session when the OST file is in use by the console session. To access this instance of Outlook, exit the current Terminal Server session and start a new session by using the following Run command (click Start, then click Run): mstsc.exe /CONSOLE You must be an administrator on the computer ...

TTF Number already in use error
Working on a CBM Batch, I'm choosing a specific TTF document number for a telegraphic transfer payment. Receive an error - number alreay in use. The number has never been used and does not reflect in any enquiry or report on TTF document numbers. Dear, First checklinks on (CM Transactions) then check table CB100000, make sure that the number does not exist there, if it exist then it have been used before and deleted. Regards, -- Mohammad R. Daoud MCP, MCBMSP, MCTS, MCBMSS Software Development Manager +962 - 79 - 999 65 85 Great Package For Business Solutions daoudm@greatpbs.co...

Maximum/Minimum number of records on import
My database is set to import a CSV. Is there a way to not allow a CSV file to be imported, and bring up a message box if the CSV file has less than 30 records or more than 500 records? I think it involves something to do with Select Case, but I'm not quite sure where to take it from there. Thank you for your response. I already have the actual importing process working perfectly the way I want it. But I was wondering whether or no there was some type of code to stick in there, to tell it not to allow importing of a CSV file with less than 30 or more than 500. If there is a code that I ca...

Fax Numbers In Exchange 2003 OWA
Hi there, When a user is using Exchange 2003 OWA and wants to view another users Fax Number they cannot see it. All they can see are the users: Name, Alias, Company, Address, City, State, Postal Code, Country/Region, Title, Company, Department, Office, Phone and Mobile Phone details, no fax number. Can anyone please tell me how to make the fax number visible in Exchange 2003 OWA? thanks Ste ...

How to measure the number of emails sent to external domain?
Can we measure in total for each person or each DL the number of email sent to external people in a month? We are running Exchange 2003 SP2. Count in mail log. -- Ray MCSE+Internet, MCDBA, MCP "MLi" <lige888@gmail.com> wrote in message news:uca$f5xZHHA.3984@TK2MSFTNGP02.phx.gbl... > Can we measure in total for each person or each DL the number of email > sent to external people in a month? > > We are running Exchange 2003 SP2. > > ...