If not saved, do not create new record?

I have a cmd button that opens an invoice form in add mode, and allocates an 
invoice number via autonumbering.
If the invoice creation is aborted for some reason (i.e. if the form is 
closed without the Save button having been clicked) the skeleton of the 
invoice is still added and the number is used and I have a meaningless record 
in tblInvoices.
Is there some way I can prevent this?
If it means that I have to add another cmd button "Cancel" that undoes 
things, rather than users just hitting the close button, that's fine...
Many thanks
CW
0
Utf
12/3/2007 4:31:02 PM
access 16762 articles. 3 followers. Follow

2 Replies
1106 Views

Similar Articles

[PageSpeed] 26

On Mon, 3 Dec 2007 08:31:02 -0800, CW <CW@discussions.microsoft.com> wrote:

>I have a cmd button that opens an invoice form in add mode, and allocates an 
>invoice number via autonumbering.

Autonumbers are NOT suitable for this purpose. The very instant you dirty the
record, a new autonumber is assigned and permanently "used up". You'll be left
with gaps in the numbering system; and for some reason auditors tend to get
freaked out by such things!

You're really better off using a Long Integer and assigning the value by VBA
code in your Form. This can be easy or moderately complicated depending on
whether it's a single user system or one with multiple users generating new
invoices concurrently.

>If the invoice creation is aborted for some reason (i.e. if the form is 
>closed without the Save button having been clicked) the skeleton of the 
>invoice is still added and the number is used and I have a meaningless record 
>in tblInvoices.

You added the save button yourself I take it?? That's not builtin.

>Is there some way I can prevent this?

Use the Form's BeforeUpdate event, which can be cancelled if some required
fields are null:

Private Sub Form_BeforeUpdate(Cancel as Integer)
Dim iAns as Integer
If IsNull(Me!CustomerID) Then
  Cancel = True
  iAns = MsgBox("No Customer selected; cancel invoice?", vbYesNo)
  If iAns = vbYes Then
     Me.Undo ' erase the record
  Else
     MsgBox "Fill in all required fields before closing the form", vbOKOnly
  End IF
End If
End Sub
     
>If it means that I have to add another cmd button "Cancel" that undoes 
>things, rather than users just hitting the close button, that's fine...

A Cancel button can just run Me.Undo, but as noted above that will NOT rescue
your autonumber value. It's too late.

             John W. Vinson [MVP]
0
John
12/3/2007 8:00:53 PM
John - I appreciate your detailed advice and words of warning. I will try to 
redesign this as you have suggested.
Many thanks
CW

"John W. Vinson" wrote:

> On Mon, 3 Dec 2007 08:31:02 -0800, CW <CW@discussions.microsoft.com> wrote:
> 
> >I have a cmd button that opens an invoice form in add mode, and allocates an 
> >invoice number via autonumbering.
> 
> Autonumbers are NOT suitable for this purpose. The very instant you dirty the
> record, a new autonumber is assigned and permanently "used up". You'll be left
> with gaps in the numbering system; and for some reason auditors tend to get
> freaked out by such things!
> 
> You're really better off using a Long Integer and assigning the value by VBA
> code in your Form. This can be easy or moderately complicated depending on
> whether it's a single user system or one with multiple users generating new
> invoices concurrently.
> 
> >If the invoice creation is aborted for some reason (i.e. if the form is 
> >closed without the Save button having been clicked) the skeleton of the 
> >invoice is still added and the number is used and I have a meaningless record 
> >in tblInvoices.
> 
> You added the save button yourself I take it?? That's not builtin.
> 
> >Is there some way I can prevent this?
> 
> Use the Form's BeforeUpdate event, which can be cancelled if some required
> fields are null:
> 
> Private Sub Form_BeforeUpdate(Cancel as Integer)
> Dim iAns as Integer
> If IsNull(Me!CustomerID) Then
>   Cancel = True
>   iAns = MsgBox("No Customer selected; cancel invoice?", vbYesNo)
>   If iAns = vbYes Then
>      Me.Undo ' erase the record
>   Else
>      MsgBox "Fill in all required fields before closing the form", vbOKOnly
>   End IF
> End If
> End Sub
>      
> >If it means that I have to add another cmd button "Cancel" that undoes 
> >things, rather than users just hitting the close button, that's fine...
> 
> A Cancel button can just run Me.Undo, but as noted above that will NOT rescue
> your autonumber value. It's too late.
> 
>              John W. Vinson [MVP]
> 
0
Utf
12/4/2007 12:28:01 AM
Reply:

Similar Artilces:

new to outlook
Hi, I am using Outlook 2003 and a new user. We had a limited number of files or email to be stored in outlook. I am creating a Folders under Inbox so I will just transfer manually what I have receive email from my colleagues. Is there a way telling Outlook to transfer automatically to Folders I have assigned? Do I need a VBA code to accomlish it? Any suggestion or advise is much appreciated. thanks You can transfer automatically through the Rules function in Outlook. Inbox | Tools | Rules and Alerts. New Rule. Regards Judy Gleeson MVP Outlook "R...

Can you make excel auto save your workbook?
I need Excel to automaticly save my work every 2 or 3 minutes if that is possible. Can it do that? Hi what Excel version are you using? -- Regards Frank Kabel Frankfurt, Germany "Rhiannon" <Rhiannon@discussions.microsoft.com> schrieb im Newsbeitrag news:FE6930C8-D9D4-46D0-825E-0C25A02C1B71@microsoft.com... > I need Excel to automaticly save my work every 2 or 3 minutes if that is > possible. Can it do that? Excel 2003 "Frank Kabel" wrote: > Hi > what Excel version are you using? > > -- > Regards > Frank Kabel > Frankfurt, Germany &g...

Need help in accessing a sharepoint site that I created with the AllTemplates.exe
SBS2008 with WSS 3.0 I downloaded the AllTemplates.exe from Microsoft and installed the Helpdesk template. I can acess the Helpdesk site from SBS2008 and add things to it but I can't access it from a workstation. Can anyone help me out? Thanks You mean you can only access the site from the Server in local , right? maybe we need more information such as error message. how about doing "ping" or "tracert" result. can you access a normal web site (not sharepoint site) from the workstation ? do you get an authentication window ? "john doe" wr...

My mail merged document won't save the mailing info.
I have created a postcard in publisher 2002 and performed a mail merge. After Step 3 of 4 in the mail merge wizard, my addresses appear in the publisher file. Step 4 of 4 asks me to print. I want to save the file to upload to OfficeMax's website. So, I have tried both saving the file and also Pack and go..., however both options, when I save then reopen the file, my merged addresses do not appear. All I have is the templated post card or a blank file with the letters MSCF at the top. Help! Erin This can only be done with Publisher 2003 unless you choose to print your mailm...

Creating a Campaign Response via Follow-up
When you create a new Task/Phone Call or other activity via the Follow-up tab, the new Activity is associated with the entity you are looking at. (so following up a Contact with a Phone Call will create a Phone Call which is associated with the Contact) This is not the case for the Campaign Response activity - when you create a Campaign Response via the Follow-up tab, the Customer field (ie regarding) is left blank and hence not associated with anyone. Presumably this is a bug - anyone know if there is a hotfix for this? Kind Regards, Paul. ...

Outlook 2000 password saving probem
I have outlook 2000 and the software will not save the passwords I give it. A window always pops up asking me for a paaword whenever I try to connect to my ISP for my pop3. Also it always asked me for a password when I send an e-mail out even thought I have checked the save password box. Anyone know a fix for this problem? Plkease help. >-----Original Message----- >I have outlook 2000 and the software will not save the >passwords I give it. A window always pops up asking me >for a paaword whenever I try to connect to my ISP for my >pop3. Also it always asked me ...

Creating a summary sheet
I have a spreadsheet that is about 200 rows down and 60 columns accross. I want to create a second summary tab that will pull over only certain columns from the "detail" sheet. As I update the detail sheet I want to also update the summary sheet - so far no problem, but if I add a new row in the detai sheet - how can I also add the same row in the summary sheet and only bring over columns I want on the summary sheet (the columns are not next to each other) Paste link does not insert the row in the summary sheet unless I drag the formulas - which I do not want to do. Is th...

Skip Records in a Form
I have a form in my database based on a table. The table is appended with new reocrds everyday. My goal is to have the form present only the new records and allow a user to key data into the new record and be recorded in the same table. The issue I have is that the form is displaying all records not just the new. Does anyone know a way to open a form and display only those records that have not been updated by the user? Thanks in advance Add a column for UpdateDate and update this column whenever a record is updated via the form. Set up a query to read all the rows/columns ...

How can you show the new items in a refreshed document?
When refreshing a spreadsheet that is pulling information from a different database, is there a way to highlight the new lines? ...

Should be able to create recurring service activities
It would be nice to be able to create recurring service activities and appointments. We use the shared service calendar and other members of my team cannot see when i am carrying out the month end routine each month unless i manually create an appointment for it. It would be most helpful if you could create a recurring appointment or service activity within crm to save time each time you have the same activity. ---------------- 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&q...

How to open saved .DBX files?
I saved a folder of e-mails to CD from Outlook Express shortly before my last computer died. I desperately need to open the folder (saved as a .dbx file) by tomorrow, to get my husband (in Portugal watching the footie) the details of his hotel!!I am now running Xp and have both outlook and outlook express(office 2003) I tried open with, but selecting OE takes you into the OE files rather than selecting it as a prog.If I try open with outlook, it just appears as a folder attachment in an e-mail Can anybody help me open this wretched thing??? Ask in an Outlook Express forum. This is an...

moving users to new cluster server
I would like to start migrating all my users about 400 mailboxes from one server with 1 storage group and 5 databases to a cluster server with 2 storage groups and 3 databases each. Should I go with the move mailbox method? What is the best way to do this? On Thu, 8 Mar 2007 09:28:13 -0800, "mcp" <none@none.com> wrote: >I would like to start migrating all my users about 400 mailboxes from one >server with 1 storage group and 5 databases to a cluster server with 2 >storage groups and 3 databases each. Should I go with the move mailbox >method? What is the ...

how to create emails with sound when opening
how to create emails with sound when opening Buster <Buster@discussions.microsoft.com> wrote: > how to create emails with sound when opening http://www.howto-outlook.com/Howto/addingsound.htm -- Brian Tillman ...

create customize trendlines
How do you create customize trendlines (w/o doing any additional spreadsheet calculations)? For example, Excel gives you the option of plotting y=m(lnx)+b. I might like to plot y=m(logx)+b instead. Does anyone know any easy way to do this using the trendline option (I've seen graphing programs where you can edit existing curve fits or creat new ones.)? Thanks! ...

New filegroups for the rms database
Are there any plans to break up the database into smaller file groups? Our ..mdf file is well over 30 gigs and the reporting on it sucks. If I where to do this myself would the upgrades still work with muliple filegroups? thanks hi Darwin, there is no link in upgrade that how many physical file groups you have and how many multiple files you have created for your database the only it looks is the logical name of database. so when you perform the upgrade you need logical name of db. I hope you understand this clearly. "darwin" wrote: > Are there any plans to break up th...

New blank document at opening
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel How do I DISABLE the new blank document that shows up when I open Word? It also seems to appear from time to time when I haven't instructed Word to do so ... <br><br>Help? <br><br>johnoerter That's a matter of how the program is designed & is in compliance with Apple's OS X guidelines for software of its type. There's literally nothing you can do in Word without having a file open. The new blank document is not an issue because it will evaporate as soon as you op...

Selecting differents Records in a Table
Hi, I use MS Works DB and in Table, I am able to select Records that are NOT in sequence and print them afterward. How can I do the same thing, in Access MS Office 2007 Table's and NOT in Query or Report? Thanks -- Roger On Mon, 7 Jan 2008 15:26:03 -0800, Roger <Roger@discussions.microsoft.com> wrote: >How can I do the same thing, in Access MS Office 2007 Table's and NOT in >Query or Report? You can't. Access is *NOT* Works on steroids. It's a different program, with different conventions and different usage. Not using Queries in Access is sort of l...

Using Access to create an appointment and add attendees
Hi i am using Access to create an appointment in outlook. I have the code for creating the appointment but i am struggling to add attendees. This is what i have so far; DoCmd.RunCommand acCmdSaveRecord Dim objOutlook As Outlook.Application Dim objAppt As Outlook.AppointmentItem Dim objRecurPattern As Outlook.RecurrencePattern Set objOutlook = CreateObject("Outlook.Application") Set objAppt = objOutlook.CreateItem(olAppointmentItem) With objAppt .Start = Est_Finish_Date .Subject = Component ...

saving word docs to my mac desktop
Version: 2004 Operating System: Mac OS X 10.0 (Cheetah) Processor: Intel when i complete a new word doc on either my iMac or Macbook, then save it to the desktop on either machine and open up an email (via my Yahoo account) and attempt to attach the newly completed word doc to my email i keep getting a message saying that the word doc contains a virus. <br><br><i>know it does'nt contain a virus because i just completed and saved it 2 minutes earlier.</i>&#32;<br><br>can anyone please tell me what's happening here and what i need to do to fi...

earn cash while saving gas #2
Looking to supplement your income or even change careeers. distributors needed all over the world. Take a look at this website and start earning real money in no time. The tablets really work and the income will continue to grow. This company is new and is growing leaps and bounds. Get in now. www.ransue.myffi.biz ----== Posted via Newsfeeds.Com - Unlimited-Unrestricted-Secure Usenet News==---- http://www.newsfeeds.com The #1 Newsgroup Service in the World! 120,000+ Newsgroups ----= East and West-Coast Server Farms - Total Privacy via Encryption =---- ...

SBS08 new install observations, not like all my other installs
Well, setup the new domain and added the old workstations to the new domain, some strange observations: The SBS admin account, when joining computers to the domain via //connect, it would not give me permission to add this computer for other users like all my other SBS installs have, had to add each one as FOR MYSELF and then go back and add the users manually.... From the User wizard, if I make 5 users local admins on a machine, from the machine itself they are not added to the local administrators groups, even after 24 hours. GPO to force screen saver to ssbez and 5 minu...

Subform doesn't populate with full record details
I have a main page and 5 tab control pages carrying subforms with a variety of info about each transaction. I need to create an invoice form, which would consist of probably 3 separate subforms sitting on one new tab control page. The upper subform would show the name and address of the debtor. The middle one would show the details of our services being charged and the bottom one, the charges. I have created the upper one, as a separate form, with its source being the debtor table (name, account number and 4 address fields). I have used a combo so that the name can be looked up, and when...

Save before crash
Hi, I have got an Excel 2000 VBA application that sends stuff out in a batch job. Every now and again, the app breaks down and has to be restarted again. I sometimes lose a little bit of that or that. So how do I make Excel save my stuff before it crashes? Thanks Jack There is no workbook_beforecrash event that you can tie into. You could create a macro that saves when you want. You could save more often manually. Or you could use Jan Karel Pieterse's addin: AutoSafe. It doesn't overwrite the existing workbook when it saves. It saves to a user selectable folder. And when it&#...

Creating new company
I am trying to create a new company (I already have Fabricom installed). During the creation of the company - the Program crashes as "Create Company Information". I am running GP 10 on a standalone XP machine. I just need to create a couple more test companies, but it is becoming a challenge. The Fabricom works fine - but I would like a test company with a different set of data. "microsoft dynamics GP has encountered a problem and needs to close. We are sorry for the the inconveneince" Thanks Hi NFP, Have you registered your installation of GP? Have installed ...

Outlook 2002
Office XP sp3 (Outlook 2002) non Exchange I have a network user who cannot create an appointment in his Outlook Calendar. In Day/Week/Month view, you can type in an appointment, but within a few seconds of hitting Enter, the label disappears and nothing more appears to happen. The monthly calendar shows bold, indicating an appointment for the date, and trying to enter another appointment on the same time on the same day generates a warning that there is an existing appointment. However there is no visible sign of the appointment. Now this is not an exchange setup, but the user has a ro...