Database Deleting Record Problem

Just launched a new database.  Using an Autonumber field to generate document 
numbers (sequence is not important - just need a unique identifier).  There 
are a number of fields that are required fields based on an option group 
selection.  Problem; If all required fields (they are required fields 
programmatically) are not completed and you attempt to exit the form, a 
message box appears asking " Do you want to save changes".  If you say "Yes" 
the form closes, does not save, and the record is deleted - but users are not 
aware of this deletion - and they think the record just created is just fine. 
 

Question 1.  I thought access saved automatically when you moved focus from 
a field or record.  Why does a message box appear asking if I want to save 
changes?  Is there a conflict between the required fields and automatic 
saving in access?

Question 2.  How do I prevent deletion of records?.

Any help is appreciated.

Thanks

-- 
Pin
0
Utf
10/19/2007 2:53:01 PM
access 16762 articles. 3 followers. Follow

2 Replies
697 Views

Similar Articles

[PageSpeed] 38

A1
The message indicates that the record cannot be saved in its current state. 
It is not deleted, because it never was saved.

By definition, you've told Access NOT to save the record if a required field 
is Null. So in this case, it cannot perform the default behavior of saving 
the record for you.

You're lucky you get a message at all. Years ago, you didn't. Even today, if 
you have code in a button that closes the form, the record is silently lost 
(no message at all) if it cannot be saved:
    http://allenbrowne.com/bug-01.html

A2.
To prevent the deletion of records, set your form's AllowDeletions property 
to No.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Pin" <city@thedeeplake.com> wrote in message
news:1DB92C52-E48B-4768-9F6E-EB2751701BA5@microsoft.com...
> Just launched a new database.  Using an Autonumber field to generate 
> document
> numbers (sequence is not important - just need a unique identifier). 
> There
> are a number of fields that are required fields based on an option group
> selection.  Problem; If all required fields (they are required fields
> programmatically) are not completed and you attempt to exit the form, a
> message box appears asking " Do you want to save changes".  If you say 
> "Yes"
> the form closes, does not save, and the record is deleted - but users are 
> not
> aware of this deletion - and they think the record just created is just 
> fine.
>
>
> Question 1.  I thought access saved automatically when you moved focus 
> from
> a field or record.  Why does a message box appear asking if I want to save
> changes?  Is there a conflict between the required fields and automatic
> saving in access?
>
> Question 2.  How do I prevent deletion of records?.
>
> Any help is appreciated.
>
> Thanks
>
> -- 
> Pin 

0
Allen
10/19/2007 3:05:29 PM
What command is being used to close the form? That message sounds a lot like 
the prompt asking if you want to save changes *to the form* not the record. 
2 entirely different things, and certainly not what your users would think 
they are being asked. As a matter of fact, I don't think Access ever asks 
whether u want to save record changes. It always assumes you do and will if 
it can. So, unless that message is something added by your own code, I 
suspect it's referrung to form changes, not record changes. I suggest you 
don't let it ask the question in the first place since its so misleading and 
the answer would probably always be "No".

DoCmd.Close acForm, strFormName, acCloseSave

acCloseSave is a vb constant and can have one of 3 values:
    acSaveNo
    acSavePrompt (default)
    acSaveYes

remember, this pertains to changes in the *form*, not the data.  I rarely 
have this set to anything other than acSaveNo.
In addition to having Form.AllowDesignChanges set to False (i.e., DesignView 
only)

Changes to a form design would include a simple change to the recordsource 
of a control, something that is done a lot during runtime. If that change 
gets saved, that's how the form will open next time. That is rarely 
desirable.

HTH,




"Pin" <city@thedeeplake.com> wrote in message 
news:1DB92C52-E48B-4768-9F6E-EB2751701BA5@microsoft.com...
> Just launched a new database.  Using an Autonumber field to generate 
> document
> numbers (sequence is not important - just need a unique identifier). 
> There
> are a number of fields that are required fields based on an option group
> selection.  Problem; If all required fields (they are required fields
> programmatically) are not completed and you attempt to exit the form, a
> message box appears asking " Do you want to save changes".  If you say 
> "Yes"
> the form closes, does not save, and the record is deleted - but users are 
> not
> aware of this deletion - and they think the record just created is just 
> fine.
>
>
> Question 1.  I thought access saved automatically when you moved focus 
> from
> a field or record.  Why does a message box appear asking if I want to save
> changes?  Is there a conflict between the required fields and automatic
> saving in access?
>
> Question 2.  How do I prevent deletion of records?.
>
> Any help is appreciated.
>
> Thanks
>
> -- 
> Pin 


0
George
10/19/2007 4:28:36 PM
Reply:

Similar Artilces:

Nothing shows up in Database objects in Database Wizard
Hello, I am having touble with Visio 2003 once again. Though the db wizard, I created an excel spreadsheet (table) for data and am tryinig to link the chart back to this original database so that when I change something in the database it will change correspondingly. I am going back through the DB wizard, and can only get as far as selecting a database object to connect to. I have selected the original dada source, checked table, but nothing showes up under the database objects. Am I or my computer missing something? Please help! Thank You. Have you defined a name for the region ...

Access Database Conversion to Excel Database
I need to convert a downloaded database in Access format to an Excel format I can use on my desktop. Hi Greg One way Use Data>Import External Data in the menubar -- Regards Ron de Bruin (Win XP Pro SP-1 XL2000-2003) www.rondebruin.nl "greg" <anonymous@discussions.microsoft.com> wrote in message news:b0f301c3ec2b$2b12d830$a001280a@phx.gbl... > I need to convert a downloaded database in Access format > to an Excel format I can use on my desktop. ...

Huge database
Good afternoon, i have to make a huge database wich have to get data from excel file.That is not a big problem problem is that i have to get information for about 1000 families with 1000 elements for every family and this database have to storage this information at leaset one year. This make a huge pack of elements. Can Microsoft Access get so huge pack of data ? If yes i would be glad to read some ideas. Thanks If you are talking about 1000 columns in a table, Access can't do it. 255 is the upper limit. 1,000 * 1,000 is potentially a million records. However if the data is pr...

deleting or clearing recently used files in Publisher
Is there any way to delete the "recent file" list in MS Publisher 2003? I know that in MS Office 2000, for Word(which i am more famliar with) you can just go to the options tab and tell the program how many files to remember (under the general portion or the tab) I don't like that the next person that uses the program after me can see what file i was just working on and I can't figure out how to get that "OPEN" at the bottom of the left hand screen to not display the last files used. even in the most basic program... Microsoft Works..., there is a way to cle...

deleted items still show up
Hi, we've deleted all of our emails from our deleted items box and the recovered deleted items box and the exchange server still shows 167mb for a users box. Does anyone know what else we should be looking for? We're using Exchange 5.5 SP4. The clients are on Office XP. Thanks What is the mailbox size from Outlook's perspective? Right click the Outlook Today folder/Properties/Folder Size. On Thu, 13 May 2004 18:01:38 -0700, "Angus" <anonymous@discussions.microsoft.com> wrote: >Hi, we've deleted all of our emails from our deleted >items box a...

Outlook printout Problem
My computer installing windows XP ,IE6 and MS Outlook 2003. My problem outlook printout only header is printed out and not print out of content Are these messages in HTML? HTML messages are rendered and printed by Internet Explorer components, so one of these articles might help: http://support.microsoft.com/default.aspx?scid=kb;en-us;Q272518 Only the Text at the Top of the Web Page Is Printed http://support.microsoft.com/default.aspx?scid=kb;en-us;Q271583 Blank Page Is Printed in Internet Explorer 5.5 Hal -- Hal Hostetler, CPBE -- hhh@kvoa.com Senior Engineer/MIS -- MS MVP-Print/Imagin...

linking 2 databases in 1
Hi, I am running 2 hqs for 2 different purposes, now the company decided to use 1 hq for both purpose. How can i link the 2 databases into 1 database???? I tired to link them from SQL server enterprise manager-all task- export data from database A to B but some tables can not be exported and show me tables failure, is it possible to link them or im just trying something impossible? Advice me on that plZzzzZZzz -- Aliko ...

Macro to Delete Duplicate Cells
Hi, I've seen other questions regarding deleting duplicates but none of them quite fit what I need. I have values in column A. I want to delete the cell (shift up) of any and all exact duplicates, but keep the original. Thanks for your help! Try: Sub CleanA() Dim n As Long n = Cells(Rows.Count, "A").End(xlUp).Row For i = n To 2 Step -1 m = Application.WorksheetFunction.CountIf(Range("A1:A" & i), Range("A" & i).Value) If m > 1 Then Range("A" & i).Delete Shift:=xlUp End If Next End Sub ...

Importing Current Database into CRM Database
Hi, I would just like to know if it is possible to tranfer my current database into the Adventure Works Database(and if so how) or is it used just for a few records for testing purposes. You help will be highly appreciated... there is a redeployment tool on support.microsoft.com/downloads under microsoft crm. This may help you. -- John O'Donnell Microsoft CRM MVP http://www.mscrmfaq.us "Rock619" <rodger@intervigil.com> wrote in message news:5728edd40161d2cca1c0e079cfd86665@localhost.talkaboutsoftware.com... > Hi, > > I would just like to know if it is pos...

Outlook 2003
Either I've found a bug, or I've got something really broken. With Outlook 2003 (latest patch installed) if I select to reply or forward by attaching the message, my signature isn't placed in the message. If however I select the option to include the text then the signature is attached. This is completely reproducable and I can't find anything about it online. So, anybody else see this? Anybody know how to fix it so that the signature is attached, even when I attach the message to a reply or forward? Thanks. Rob Yes, I'm having the same problem and haven'...

Delete records
I have a table called residency I create a query (named notdate) that uses the residency table and compares to another table. I want to take the results of the query and delete the records residency table. Do I need to create another table then create a delete query? I can not seem to get the delete query to work using the notdate query. Any thoughts and suggestions would be appreciated. Post the SQL for what you are trying. Chances are that you need to use the query as a sub-query in the Where clause of the table where you want to delete records. I could look something like ...

Timeout expired problem
Hi All, Using Windows 2008 enterprise version. I created a web site using .net 3.5 and ms sql 2008. Sometimes when the web site is idle it freezes afterwards when I clack on the menu and I am getting a Time Out error as follows. Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding. What do you suggest to resolve it? Thanks, Eric On Dec 18, 1:32=A0am, "Eric S" <xxx_noS...@Hotmail.com> wrote: > Hi All, > > Using Windows 2008 enterprise version. > > I created a web site using ...

Remote Web Workplace displays problems
-- Frustrated You might want to post this in a more appropriate group, this group is for Microsoft Exchange questions. I would also recommend you provide more information about the problem. -- Mark Fugatt Exchange MVP http://www.exchangetrainer.com http://www.msexchange.org "Pollywog" <Pollywog@discussions.microsoft.com> wrote in message news:BFF1D333-D482-49EF-859C-2D9A81F9DB68@microsoft.com... > > -- > Frustrated ...

how to start a database
hi guys, i want to start a database on excel 2000, i never had to do this before, and i know this can be done. i just dont know where to start from ! what i want to do is, it is a racing club, i want a listing of all pilot and a sheet where we can enter the number of the car(all pilot info need will be transfer) for final result of the race.(right now they enter the car no, name , city at every race at every registration, if he is register to 3 race they do the process 3 time.) like in access went we create the database and after the query, that what i want to do in excel. any tip or link...

Use query from another database
Hi In the code below is there a way that qryOrderHistory could be in a separate database. If so how would i refer to it in the code. Thanks Dim db As DAO.Database Dim qd As DAO.Querydef Dim rs As DAO.Recordset Set db = CurrentDb Set qd = db.QueryDefs("qryOrderHistory") qd.Parameters("prmCustNo") = lngCustNo Set rs = qd.OpenRecordset() If Not rs.Eof Then .... code to populate an excel spreadsheet Change your 'Set db = CurrentDb' line to something like the following .. Set db = DbEngine.OpenDatabase("full path and name of target MDB here") -- ...

Database Queries with database views, that base on many database t
I created 2 database views. Each of them uses 256 database tables. From every database table only one column is used in the select list of the view. Every database table has one column which is used for the join. Each database table has 5 rows. The database tables have not any foreign keys and indexes. Both views works fine. When I use the database views in a query like this: SELECT v1.*, v2.* FROM v1, v2 WHERE v1.id=v2.id I should get 5 rows with 512 columns in the resultset. But I get the following error from the SQL-Server (after about 5 minutes): Msg 8621, Level 17, State 2...

CMontCalCtrl multiselect mode problem
I have multiselect CMontCalCtrl and sometimes I need to select days across month boundary and a calendar page. I do this by calling SetSelRange() and everything works fine until I try to scroll months. When I switch the calendar page to the other month the selection is incorrect. It looks like the calendar doesn't refresh it - the month is different but the selected days stays the same on each page ( the same as on the first page ). Maybe I missed something. I call SetMaxSelCount(180),SetRange( a_few_months ), SetSelRange( a_few_months ) and it doesn't work. Whats wrong with it ? Alan...

CRM 3.0 KB927751 Update Roll Up Install Problem
Hi, I'm running CRM 3.0 Small Business edition on Windows Small Business Server 2003 R2 with SQL 2005. When I try to install the update roll up (KB927751) It unpack the files, but before the instalation starts, it comes up with an Installation Error saying: "Setup failed to update the database. The installation of this update has stopped." Does anybody have any clues as to what might be causing this? The rest of the CRM appears to work fine. Thanks in advance Tim Solved the problem in the end - It turns out that the DataMigration pack caused the issue. Uninstalling th...

Problems using excel as an oledb datasource
I have an application using Jet4.Oledb to select data from an excel spread sheet. My problem is that some of the field values contain the single quote char such as the brand name "L'Oreal" but when I get the data back it is returned as "Loreal". My sql statement is pretty basic: SELECT * FROM[Sheet1$] ORDER BY brand_name Everything else works fine, it just seems to not want to return a single quote as a literal part of the string. Any ideas how I can get around this? Thanks Lee ...

Deleting Old Messages on Local PC if their not on server.
Ok, So im running multipule computers and i have windows live mail set up to auto sync every 5min. I check my mail on my computer and then delete some permatatly off the server. When i go to my other computer that has the same email; all I want to do is hit sync and it should check my server mail and find that some have been deleted and so in turn remove them from my inbox. It however does not do this, insted leaving the whole list there. I relize that I can set up the computers to sync manualy and theres no problem but I dont want to miss any important mail if I forget to do t...

Impact of changing database column width?
Hi, We are a software product company and are planning to begin using the Contract Administration module. To do so, we will be switching our inventory items from Track: None to Track: Serial Numbers and we will be storing our software license keys in the Serial Number field which is column SERLTNUM in table SOP10201. Unfortunately, SERLTNUM is CHAR(21) and our license keys are a few characters wider than 21. We can get the keys down to 21 characters by removing three embedded hyphens, but there are several databases outside of GP where the license keys must still exist with...

Windows XP boot problem
Hi, I have a Del netbook which is loaded with Windows XP. I've not had need to use the netbook for about a fortnight and on trying to boot it up today I find that I cannot get past the boot screen ( the screen that has the MS Flag and the words "Microsoft Windows XP" and below these a scroll bar). The scroll bar is constantly scrolling from left to right, as it always does, but for reasons best known to the netbook, this is as far as it gets. The mouse is not yet visible Any suggestions as to what the problem(s) mey be would be much appreciated -- Cheers P...

Program stops (creating pivot table) when too many records
Hi all, I have a macro which creates a Pivot Table using the records I provide and it works fine up to probably 40,000 records. When the database becomes too big, the program stops at the line of code which creates the Pivot Table with an error message. 1) Pressing F5 simply gives the same error message. 2) Running the same macro from start with a reduced records works well again. How can I overcome this problem? Thanks in advance. On Mar 5, 7:57=A0am, ch <c...@discussions.microsoft.com> wrote: > Hi all, > > I have a macro which creates a Pivot Table u...

Set Database in Excel
In Microsoft article 183446 under method 2, it refers to using "Set Database" on the Data menu. I am using Excel 2002 (the article says it applies to Excel 2002), but Set Database does not appear in my Data menu. Is this an error? It's an error due, I think, to very old menu structures. The article says that the information applies to everything from version 5.0 (1995) through 2003, inclusive. But I believe that the Set Database menu item disappeared from the Data menu as of Excel 97. And one has long defined a name using the Insert menu, not the Formula menu. But my rapidl...

upgraded from MSSQL 2000 to MSSQL 2005 now database's users have
Hello. I updated the MSSQL 2000 server to MSSQL 2005 server now the database's users have no login names. I found a way to list orphaned users sp_change_users_login 'Report' but it only listed dbo and there are actually five listed with MSSQL Server Management Studio Express. I tried this sp_change_users_login 'update_one', 'RPS', 'RPS' but I get the following error the user name 'RPS' is absent or invalid. Any ideas? Try with Auto_Fix instead; see the example at the end of the following article: http://msdn.microsoft.com/en-us/...