Prevent sequential number being used if record not saved

I have an invoice form that when opened, uses the following code to allocate 
the next invoice number (InvNo):

Private Sub Form_BeforeInsert(Cancel As Integer)
    Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
    Me.Dirty = False
End Sub

Trouble is, if I then decide for some reason not to carry on, even if I 
click on the Close button rightaway without having entered anything anywhere, 
the invoice number is still used and appears in the Invoices table (without 
any values in any of the fields, of course).

How can I prevent this? Ideally I would like to be able to cancel at any 
point before hitting the Save button, even if I have populated a few 
controls, and the InvNo would not be used.

Looking forward to your help, thanks
CW
0
Utf
12/6/2007 7:40:07 PM
access 16762 articles. 2 followers. Follow

7 Replies
737 Views

Similar Articles

[PageSpeed] 34

What data type is on that [InvNo] field in the underlying table?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CW" <CW@discussions.microsoft.com> wrote in message 
news:69BD1139-4988-4DB4-A593-1BA27C487964@microsoft.com...
>I have an invoice form that when opened, uses the following code to 
>allocate
> the next invoice number (InvNo):
>
> Private Sub Form_BeforeInsert(Cancel As Integer)
>    Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
>    Me.Dirty = False
> End Sub
>
> Trouble is, if I then decide for some reason not to carry on, even if I
> click on the Close button rightaway without having entered anything 
> anywhere,
> the invoice number is still used and appears in the Invoices table 
> (without
> any values in any of the fields, of course).
>
> How can I prevent this? Ideally I would like to be able to cancel at any
> point before hitting the Save button, even if I have populated a few
> controls, and the InvNo would not be used.
>
> Looking forward to your help, thanks
> CW 


0
Jeff
12/6/2007 8:52:51 PM
Jeff, it's a Number (Long Integer). FYI, I used to have an autonumber but 
John Vinson recently pointed out to me how unreliable that would be, so I 
have changed it
Thanks
CW

"Jeff Boyce" wrote:

> What data type is on that [InvNo] field in the underlying table?
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "CW" <CW@discussions.microsoft.com> wrote in message 
> news:69BD1139-4988-4DB4-A593-1BA27C487964@microsoft.com...
> >I have an invoice form that when opened, uses the following code to 
> >allocate
> > the next invoice number (InvNo):
> >
> > Private Sub Form_BeforeInsert(Cancel As Integer)
> >    Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
> >    Me.Dirty = False
> > End Sub
> >
> > Trouble is, if I then decide for some reason not to carry on, even if I
> > click on the Close button rightaway without having entered anything 
> > anywhere,
> > the invoice number is still used and appears in the Invoices table 
> > (without
> > any values in any of the fields, of course).
> >
> > How can I prevent this? Ideally I would like to be able to cancel at any
> > point before hitting the Save button, even if I have populated a few
> > controls, and the InvNo would not be used.
> >
> > Looking forward to your help, thanks
> > CW 
> 
> 
> 
0
Utf
12/6/2007 9:13:01 PM
Perhaps what you are seeing is because you are using the BeforeInsert event 
rather than the BeforeUpdate event?

Regards

Jeff Boyce
Microsoft Office/Access MVP

"CW" <CW@discussions.microsoft.com> wrote in message 
news:5CCC9168-45D8-46EA-9C63-58D0C36B4BCF@microsoft.com...
> Jeff, it's a Number (Long Integer). FYI, I used to have an autonumber but
> John Vinson recently pointed out to me how unreliable that would be, so I
> have changed it
> Thanks
> CW
>
> "Jeff Boyce" wrote:
>
>> What data type is on that [InvNo] field in the underlying table?
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "CW" <CW@discussions.microsoft.com> wrote in message
>> news:69BD1139-4988-4DB4-A593-1BA27C487964@microsoft.com...
>> >I have an invoice form that when opened, uses the following code to
>> >allocate
>> > the next invoice number (InvNo):
>> >
>> > Private Sub Form_BeforeInsert(Cancel As Integer)
>> >    Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
>> >    Me.Dirty = False
>> > End Sub
>> >
>> > Trouble is, if I then decide for some reason not to carry on, even if I
>> > click on the Close button rightaway without having entered anything
>> > anywhere,
>> > the invoice number is still used and appears in the Invoices table
>> > (without
>> > any values in any of the fields, of course).
>> >
>> > How can I prevent this? Ideally I would like to be able to cancel at 
>> > any
>> > point before hitting the Save button, even if I have populated a few
>> > controls, and the InvNo would not be used.
>> >
>> > Looking forward to your help, thanks
>> > CW
>>
>>
>> 


0
Jeff
12/6/2007 11:47:00 PM
=?Utf-8?B?Q1c=?= <CW@discussions.microsoft.com> wrote in
news:69BD1139-4988-4DB4-A593-1BA27C487964@microsoft.com: 

> I have an invoice form that when opened, uses the following code
> to allocate the next invoice number (InvNo):
> 
> Private Sub Form_BeforeInsert(Cancel As Integer)
>     Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
>     Me.Dirty = False
> End Sub
> 
> Trouble is, if I then decide for some reason not to carry on, even
> if I click on the Close button rightaway without having entered
> anything anywhere, the invoice number is still used and appears in
> the Invoices table (without any values in any of the fields, of
> course). 
> 
> How can I prevent this? Ideally I would like to be able to cancel
> at any point before hitting the Save button, even if I have
> populated a few controls, and the InvNo would not be used.
> 
> Looking forward to your help, thanks
> CW

me.dirty = False forces a save to the record, remove the statement.
It may also be appearing elsewhere and causing the problem so check 
other sub procedures in the form module.. 

-- 
Bob Quintal

PA is y I've altered my email address.

-- 
Posted via a free Usenet account from http://www.teranews.com

0
Bob
12/7/2007 1:27:50 AM
CW wrote:
> I have an invoice form that when opened, uses the following code to
> allocate the next invoice number (InvNo):
>
> Private Sub Form_BeforeInsert(Cancel As Integer)
>    Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
>    Me.Dirty = False
> End Sub
>
> Trouble is, if I then decide for some reason not to carry on, even if
> I click on the Close button rightaway without having entered anything
> anywhere, the invoice number is still used and appears in the
> Invoices table (without any values in any of the fields, of course).
>
> How can I prevent this? Ideally I would like to be able to cancel at
> any point before hitting the Save button, even if I have populated a
> few controls, and the InvNo would not be used.
>
> Looking forward to your help, thanks
> CW

CW,
    "Me.Dirty = False" saves the new record
-- 
Harvey Thompson 


0
Harvey
12/7/2007 2:15:24 AM
Many thanks to all for your assistance.
I resolved it by moving the code to the InvNo control, rather than the form, 
and by changing the event to BeforeUpdate.
Working nicely now
Thanks again!
CW 

"Jeff Boyce" wrote:

> Perhaps what you are seeing is because you are using the BeforeInsert event 
> rather than the BeforeUpdate event?
> 
> Regards
> 
> Jeff Boyce
> Microsoft Office/Access MVP
> 
> "CW" <CW@discussions.microsoft.com> wrote in message 
> news:5CCC9168-45D8-46EA-9C63-58D0C36B4BCF@microsoft.com...
> > Jeff, it's a Number (Long Integer). FYI, I used to have an autonumber but
> > John Vinson recently pointed out to me how unreliable that would be, so I
> > have changed it
> > Thanks
> > CW
> >
> > "Jeff Boyce" wrote:
> >
> >> What data type is on that [InvNo] field in the underlying table?
> >>
> >> Regards
> >>
> >> Jeff Boyce
> >> Microsoft Office/Access MVP
> >>
> >> "CW" <CW@discussions.microsoft.com> wrote in message
> >> news:69BD1139-4988-4DB4-A593-1BA27C487964@microsoft.com...
> >> >I have an invoice form that when opened, uses the following code to
> >> >allocate
> >> > the next invoice number (InvNo):
> >> >
> >> > Private Sub Form_BeforeInsert(Cancel As Integer)
> >> >    Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
> >> >    Me.Dirty = False
> >> > End Sub
> >> >
> >> > Trouble is, if I then decide for some reason not to carry on, even if I
> >> > click on the Close button rightaway without having entered anything
> >> > anywhere,
> >> > the invoice number is still used and appears in the Invoices table
> >> > (without
> >> > any values in any of the fields, of course).
> >> >
> >> > How can I prevent this? Ideally I would like to be able to cancel at 
> >> > any
> >> > point before hitting the Save button, even if I have populated a few
> >> > controls, and the InvNo would not be used.
> >> >
> >> > Looking forward to your help, thanks
> >> > CW
> >>
> >>
> >> 
> 
> 
> 
0
Utf
12/7/2007 2:20:00 PM
>    Me.Dirty = False

That line saves it to the table.

I suggest that you bind the form to a local table, and
only copy the record to the database when you are
finished.

Alternatively, you can use an unbound form, and only
copy the record to the database when you are finished.

Alternatively, take out the Me.Dirty = false line, and
leave the record unsaved as long as you can.

(david)

"CW" <CW@discussions.microsoft.com> wrote in message 
news:69BD1139-4988-4DB4-A593-1BA27C487964@microsoft.com...
>I have an invoice form that when opened, uses the following code to 
>allocate
> the next invoice number (InvNo):
>
> Private Sub Form_BeforeInsert(Cancel As Integer)
>    Me.InvNo = Nz(DMax("[InvNo]", "[tblInvoices]")) + 1
>    Me.Dirty = False
> End Sub
>
> Trouble is, if I then decide for some reason not to carry on, even if I
> click on the Close button rightaway without having entered anything 
> anywhere,
> the invoice number is still used and appears in the Invoices table 
> (without
> any values in any of the fields, of course).
>
> How can I prevent this? Ideally I would like to be able to cancel at any
> point before hitting the Save button, even if I have populated a few
> controls, and the InvNo would not be used.
>
> Looking forward to your help, thanks
> CW 


0
david
12/9/2007 11:45:13 PM
Reply:

Similar Artilces:

Conflicting information about using /3GB switch on Exchange 2003 Server?
We are running Exchange 2003 Enterprise on a Windows 2003 Server standard platform. The server has 4 GB of physical memory. I have found conflicting information on Microsoft's site, regarding whether using the /3 GB switch on my server. Some of the documentation says to only use it with Windows 2000 Advanced Server or Windows 2003 Enterprise Server, and that using it on Windows 2003 Standard can cause the OS to become unstable, while other documentation says that the /3GB switch is safe to use across all versions of Windows 2003 Server. Which is it? You can use it on either the ...

how do I exit working offline I use outlook
mike <mike@discussions.microsoft.com> wrote: <nothing> Ask in the body of the message, not the subject. Click File and uncheck Work Offline. -- Brian Tillman ...

How can i find full card number..
I have problem some amexcard card sales.. Because, I have receipt with 6digit auth number and 14 times amex sales.. But, my card processor(cynergy data) and amex says, They can't see the any transaction their hos computer.. so, retry again sale thru offline sale with full card number and auth code.. But' I can see just only last 4 digit card number because set up the option marked securit c/c number.. anybody here please tell me how can i find out full card number on my computer.. And, another question How can got the rms software approve code even not through amex and c/c card com...

Stem and Leaf Diagrams using Excel
Is there a way do do stem and leaf diagrams using excel and if so how do I do this. "Linda" <ljsinsc@aol.com> schrieb im Newsbeitrag news:009e01c39026$035decf0$a401280a@phx.gbl... > Is there a way do do stem and leaf diagrams using excel > and if so how do I do this. Hello Linda, I'm not familiar at all with this kind of Diagram, but if I had this homework to do, I would try at first, for a Data in A1, determine - the leave : using the function =RIGHT(A1,1) - the stem : through the function =LEFT(A1,LEN(A1)-1) in a second step I would sort together thes...

what are the 5 scenarious Microsoft corporation placed in MSDN In what areas polymorphism concept is used
Hai, what are the 5 scenarious Microsoft corporation placed in MSDN In what areas polymorphism concept is used ( In Real Time what are the situations Polymorphism concept used). Thank you, Bye What exactly do you mean? What scenarios? Where in MSDN? Why here? Is this a school exam? ------- Ajay Kalra ajaykalra@yahoo.com Polymorphism is at the heart of C++ so anytime OOP is used it's almost always a factor. Tom "jagadeesh" <jagadeeshbabu.mca@gmail.com> wrote in message news:1142596513.757918.162640@e56g2000cwe.googlegroups.com... > Hai, > > > what ...

Everchanging number of rows
Hi, Where am I going wrong on this piece of code below What I am trying to achieve in simple terms is this: Range("A2").Select ActiveCell.FormulaR1C1 = "1" Selection.Copy Range("A2:A10000").Select ActiveSheet.Paste But I need it to size with the amount of rows the rest of the data uses My try: Range("A2").Select ActiveCell.FormulaR1C1 = "1" Selection.Copy Range("A2:A & Range("Count")").Select ActiveSheet.Paste I have already created a Range called ‘Count’ that adds...

Numbers format
I have a document containg numbers (digits) which I want to be in Arabic format, but also at the end of my document are some references which are in english and their numbers should be in english too... can anyone help me how to do that... because if I go to the Options in the Tools menu and change the way numbers should look from there, the effect will be applied to the whole document... thank you... Since you don't need to use the numbers as numbers (to do arithmetic), you can go to Insert Symbol, choose the Unicode range (upper right dropdown) for the forms that _aren'...

conditional number formats
Does anyone know how to link the number format of a cell on the value of another cell by using the custom number format tool and not VBA? For example: if cell A1 = "GBP" then value in cell B1 will be displayed as either "� <value>" or "GBP <value>" So if the user then enters "EUR" in cell A1, then B1 will automatically change to either "� <value>" or "EUR <value>". Thanks. --- Message posted from http://www.ExcelForum.com/ You would need VBA for that. -- Regards, Peo Sjoblom "I_need_help >&...

I can not use Word as editor
Hello, Since today it's impossible to select word as email editor in Outlook. I have only RTF, HTML and Text. It's Outlook XP and Office 97. This worked until today. On more strange thing, is that i can see some of the messages but not all, but the partial view(it's a translation) shows all the text. Can someone give me a light ? Thanks Frederic Did you just upgrade Outlook? Outlook and Word have to be running the same version for this functionality to work. Thanks! "Fr�d�ric SCHENCKEL" <f_schenckel@cab-technologies.fr> wrote in message news:Oy3nIP1YE...

Prevent the sales from Tendering when another Item is Scan at POS
Hello, We have serveral items that our Retail does have price assign to them. The cashier will scan the next item. They will not notice the last item they did not enter a price. Now the 2nd item is the price for the 1st item. Then the cashier will tender out the sales without noticing they have an incorrect total for this transaction. Then the store manager have to performed a post void for this transaction, which hold up the customer awaiting to check out. Have anyone else encounter this and know a way to limited sale from being total out of an acceptable sales amount...

even or odd records
How do I list records with only the even part numbers. On Fri, 21 Dec 2007 15:33:47 -0600, Jeff Klein wrote: > How do I list records with only the even part numbers. In a Query? EvenNumbers:[FieldName] Mod 2 = 0 The result will be -1 (True) if the value is an even number The result will be 0 (False) if the value is an odd number or... OddNumbers:[FieldName] Mod 2 = 1 The result will be -1 (True) if the value is an odd number Thwe result will be 0 (False) if the value is an even number -- Fred Please respond only to this newsgroup. I do not reply to personal e-mail Jeff Klein wrote...

Auto Save
Does Excel 2000 still have the autosave add-in option? The only thing I can find is the auto-recovery under tools and options. My old version had an option that I had set to prompt me every 10 minutes if I wanted to save the current file. Please reply any help via e-mail. Thanks... ...

preventing date from changing
I have an If condition that sets the date to "today()" true or "today() + 1" if false. My question is how do I prevent the date from changing? If the date is set for today, then tomorrow I don't want to "update". How can this be done? thanks You could copy the cell, and paste as values, before you close the file. (Edit>Paste Special, Values) Or, you could enter today's date in a cell in the row (Ctrl+; ), then refer to that cell in the formula. For example, instead of: =IF(A2="Local",TODAY(),TODAY()+1) enter: =IF(A2="Local...

outlook feature to prevent forgetting attachments
Often people forget to send attachments with emails. Very often they will write something along the lines of "I have attached the minutes from our meeting" and then forget to atcually attach a file. Is there a way therefore that outlook can check the text of an email for words like attach, attached, attachment and query the user before sending if there does not appear to be an attachment? ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree"...

Number of days since a date
Hi folks, Is there a simple way to calculate in a cell the number of days *since* a date in a different cell? For example - Last used: [12/6/05] Days ago: [ 15] It seems so simple, but I can't get my head around Excel's treatment of dates. Thanks, Jim Put, in cell c2, the formula =a2-b2 where b2 is the later date. C2 will need to be formatted to number. Regards. Bill Ridgeway Computer Solutions "Jim M" <jim@deteste_vraiment.le.spam> wrote in message news:270620051216296712%jim@deteste_vraiment.le.spam... > Hi folks, > > Is there a simple way to...

prevent user from deleting a tab prevent running macro from menu
how can one prevent a user from 1. deleting a specific tab in a workbook? 2. prevent a user from running a macro from the tools menu (where user can only run a macro from a command button)? thanks! #1. Protect the workbook's structure. In xl2003 menus: tools|protection|protect workbook|check structure #2. Make the sub private: Sub Testme() becomes Private Sub testme() This will stop the user from seeing that subroutine--but if they know the name, they can still type it and run it. joemeshuggah wrote: > > how can one prevent a user from...

Stock count of serial numbered items
I have recurring issues with serial-numbered items and stock count entry. For instance, in my stock count I have a serial-numbered item with a captured quantity of 1. My counted quantity is zero. Therefore, I should have a variance of -1. I go into the Stock Count Serial Number Entry window and change the Count Status to Not Found for my one serial number. When I click on OK, I get "The variance or counted quantity for this serial number does not equal the variance or counted quantity for the item - Continue, Override, Cancel". If I click on Override, I get "The calc...

Help for how to associate names to phone numbers
In an excel sheet I have built a small database with phone numbers in column A and names in column B. Below this database and in the column A, there are phone numbers without names on its contiguous B cells. I'm looking for a formula (to be written in these column B adjacent cells) that will show the names relative to those phone numbers, as stated on the database. Thanks in advance Jaime-Oviedo-Spain -- Saludos J.G.Benedet Take a look at HELP for LOOKUP -- Don Guillett SalesAid Software Granite Shoals, TX donaldb@281.com "Jaime" <jgoben@inetcom.es> wrote in message ...

Using publisher 2007, how do I reformat a PUB flyer to jpg format
I just started using PUB 2007 and have this question -- pls help. Mny thx, Gil Rosoff, San Mateo, CA File, save as, Files of type, scroll down to the .jpg options. -- Mary Sauer http://msauer.mvps.org/ "Gil Rosoff" <Gil Rosoff@discussions.microsoft.com> wrote in message news:B70E8398-1BD2-44A2-AF3E-20DB8D7A646C@microsoft.com... >I just started using PUB 2007 and have this question -- pls help. > Mny thx, Gil Rosoff, San Mateo, CA Why use a .jpg? Why not a .pdf? -- JoAnn Paules MVP Microsoft [Publisher] Tech Editor for "Microsoft Publisher 2007 For Dum...

Excel defaulting to 3 decimal places when using the gen. format
I have a problem when entering numerical values in cells that have been formated to general. Somehow the cells seem to be formatted using 3 decimal places automatically even though I don't want decimal places. Entering 123 gives me a .123. Entering 1234 gives me 1.234. I believe that I may have set up a macro to automatically default all values to 3 decimal places. It is now affecting all my excel files. I can't call up the macro and delete it. Attempting to reformat the cells doesn't help. It automatically affects any new workbooks that I create. Can anyone suggest what I...

Re: Access violation error
> I decided to check the size of the GTrayIcon class using sizeof. It returns > me 112 bytes. > > But, if I do sizeof in the constructor of GTrayIcon, it gives me 512 bytes! > From this, only 2 conclusions are possible: > > 1) The 'new' only allocate 112 bytes of memory, while the class really needs > 512. Thus, when assigning my member variable, I end up overflowing outside > of the allocate memory block. (If that's the case, why is it doing such > behavior and how do I fix it?). > > 2) Or, it has nothing to do with it. Since the heap is alread...

"if" function ~ odd/even numbers
i need to do this if cell A1 is odd then i want to take the negative, if A1 is even the i want the positive =if(a1="odd", -a1, +a1) how do i specify "odd" -- Message posted from http://www.ExcelForum.com Hi you can try =IF(MOD(A1,2),"odd","even") or use the functions ISEVEN and ISODD respectively. e.g. =IF(ISODD(A1),"odd","even") HTH Frank > i need to do this > > if cell A1 is odd then i want to take the negative, if A1 is even then > i want the positive > > =if(a1="odd", -a1, +a1) > > how do ...

how to anylalize lottery numbers in excel
is this possible as i am still getting to grips with the workings of excel While it's nearly always a worthless exercise, you can look in the archives to see what others have done: http://groups.google.com/advanced_group_search?q=group:*excel* In article <950920D6-E230-43A6-85BA-BEFEA80B1025@microsoft.com>, "mydogdylan" <mydogdylan@discussions.microsoft.com> wrote: > is this possible as i am still getting to grips with the workings of excel You could enter all the details of each draw into excel, but bassed o the fact that each draw is created from a n...

A unique document number could not be found. Please check setup
has anyone come across this message Is this when creating a new transaction? It's possible that you've 'run out' of numbers....what does the module setup say? If you have something like ABC99999, it may not be able to go to ABC100000 without manual intervention. -- Victoria Yudin Dynamics GP MVP Flexible Solutions - home of GP Reports http://www.flex-solutions.com/gpreports.html "rcr" <rcr@discussions.microsoft.com> wrote in message news:642E314C-DFEF-48C9-BD37-EB047270430E@microsoft.com... > has anyone come across this message Victoria has one ...

Use DLookUp?
I need a certain check boxes on a form to be checked if the corresponding data is present in a table. I.E. if the applicant type is a corporation, then the corporation checkbox will be checked. I will need to pull this information from applicationinfo table and then the applicanttype field. How do I go about doing this? If statements and dlookup? Thanks, Chris just 'bind' the checkbox to something like (control source property of the check box): = DCount("*", "applicationinfo", "applicanttype=FORMS!FormNameHere!applicanttype ) since a 0 wil...