Spurious blank records being added

Hi

I have a form Customers and a subform Bookings. The subforms Allow
Additions property is set to false and Data Entry to false.
I put a button on the Customers form, New Booking, which changes the
subforms Allow Additions property to true and Data Entry to true. If
the user now closes the form without adding any booking details, a
blank record gets created.
In order to try to prevent this, I set one of the fields in the
Booking table to required. Validation > 0. Validation message "Booking
not added".
That works fine, i.e. if the user backs out they get a message
"Booking not added". However, when I now go to enter data on the
booking form the 'Booking not added' message comes out before I start
typing.

All I actually want is to prevent spurious blank records being
created.

This must be a regular thing. What is the correct procedure?

Stapes

0
Stapes
9/28/2007 10:37:47 AM
access 16762 articles. 3 followers. Follow

1 Replies
587 Views

Similar Articles

[PageSpeed] 15

It would appear that something is attempting to save the subform's current 
record as soon as the user enters the subform.  Is there anything in your 
code for doing this?  It could be in the code behind the button for changing 
the subform's properties, in the subform's current event procedure or 
elsewhere in the parent form or subform's modules.

Another thing to be aware of is that if you assign a value to a control in 
the subform as a default, say for instance the current date is assigned with:

    BookingDate = VBA.Date()

then this will 'Dirty' the subform so closing it would attempt to save the 
record.  This would account for the original problem, but not for the fact 
that your validation rule is kicking in as soon as you enter the subform.  If 
you are assigning a value as a default use the DefaultValue property.  This 
is always a string expression regardless of the data type, so its prudent to 
wrap the value in quotes:

    BookingDate.DefaultValue = """" & VBA.Date() & """"

BTW don't use the # date delimiter character in this context.  On systems 
with non-US format, or otherwise internationally ambiguous system date 
settings it will do things like changing 4 July to 7 April!

I doubt that it’s a factor in the behaviour you are experiencing, but I 
wouldn't have thought there's any real necessity to set the DataEntry 
property of the subform to True.  Why not simply move to a new record after 
setting the AllowAdditions property to True?  Set focus to the subform 
control and then use the GotoRecord method of the DoCmd object:

    Me.BookingsSubformControl.Form.AllowAdditions = True
    Me.BookingsSubformControl.SetFocus
    DoCmd.GoToRecord Record:=acNewRec

Ken Sheridan
Stafford, England

"Stapes" wrote:

> Hi
> 
> I have a form Customers and a subform Bookings. The subforms Allow
> Additions property is set to false and Data Entry to false.
> I put a button on the Customers form, New Booking, which changes the
> subforms Allow Additions property to true and Data Entry to true. If
> the user now closes the form without adding any booking details, a
> blank record gets created.
> In order to try to prevent this, I set one of the fields in the
> Booking table to required. Validation > 0. Validation message "Booking
> not added".
> That works fine, i.e. if the user backs out they get a message
> "Booking not added". However, when I now go to enter data on the
> booking form the 'Booking not added' message comes out before I start
> typing.
> 
> All I actually want is to prevent spurious blank records being
> created.
> 
> This must be a regular thing. What is the correct procedure?
> 
> Stapes
> 
> 

0
Utf
9/28/2007 5:04:00 PM
Reply:

Similar Artilces:

Publisher does not suppress blank fields with Catalog Merge...please help
As I originally posted, I need help finding out why Publisher is not suppressing blank lines when I merge to a new (or existing) document. [I tried to reply to the existing post, but google just gave me an error.] The person who replied just referred me to the help file (F1). Well, actually, I have tried the Help file, Microsoft's knowledge base, and newsgroups. That's why I posted here. I cannot find any information as to why it is not suppressing blank fields, and inserting extra lines. Some real constructive help would really be appreciated. I am posting here as a last resort...

Merge records function
Is it possible to reuse the record merge function in CRM. Fx to call the function with a valid Account GUID with a URL-call to mergerecords ? I want this to open the merge windows with the GUID as the master record. ...

-1026 JET_errRecordTooBig, Record larger than maximum size
I administer a single Windows 2003 domain, Native Mode only W3k servers and use Exchange 2003. I am seeing this error in all DC Directory Service Event Logs. Event Type: Error Event Source: NTDS SDPROP Event Category: Internal Processing Event ID: 2008 Date: 28/07/2004 Time: 6:38:04 PM User: NT AUTHORITY\ANONYMOUS LOGON Computer: TLSAD1 Description: Internal error: The security descriptor propagation task encountered an error while processing the following object. The propagation of security descriptors may not be possible until the problem is corrected. Object: CN=internal 29176...

Blank Quantities in Store Quantity Tab
I added items via Headquarters and left store quantity blank. Through store ops I did a purchase order to accept items to the store. Then I ran a 501 - request full item count. Now, in the HQ on the store quantity table, no stores are listed. Does anyone know how to fix this? Also, what am I doing wrong!? Thanks for the help in advance. diana This is a multi-part message in MIME format. ------=_NextPart_000_0070_01CAD7D2.5CD027D0 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: quoted-printable Diana, I don't see where you did a 250 wor...

Adding stages to a Sales Process
Hi, I want to add two new stages to a current sales process. Has anyone done this before? Any issues arise with previous opportunties, reporting, etc? Thanks, Sinead. -- Sinead O''''Shaughnessy previous opportunities will not see the new stages ============================== John O'Donnell Microsoft CRM MVP http://www.crowecrm.com "Sinead" <Sinead@discussions.microsoft.com> wrote in message news:8EC75D1E-06C5-4A6E-813D-1B5816DE87B6@microsoft.com... > Hi, > I want to add two new stages to a current sales process. Has anyone done > this bef...

Counting rows of blanks across certain columns
I have a survey whose answers were recorded in Excel. The answers for a particular question extend from Q6 to Z505. I need to count the people who did not answer the question (that is, the people, entered in rows 6 to 505 that left columns Q through Z blank). I'm not sure how to do this. Can someone offer a suggestion? Hi You can use a formula like this in Column AA =IF(COUNTA(Q6:Z6)=0,1,"") copy down till AA505 You can use a simple Sum formula now to count the empty ones =SUM(AA6:AA505) -- Regards Ron de Bruin http://www.rondebruin.nl "crossingboston" <...

How to have one year value added to another cell
I have a cell where I date foramtted fill in the date. How to have on another cell the date + one year automatically filled? So, A1 02-04-06 auto: B1 02-04-07 Bart Excel 2003 =DATE(YEAR(A1)+1,MONTH(A1),DAY(A1)) Bob Umlas Excel MVP "AA Arens" <bartvandongen@gmail.com> wrote in message news:1164983094.802613.212520@l12g2000cwl.googlegroups.com... > I have a cell where I date foramtted fill in the date. > How to have on another cell the date + one year automatically filled? > > So, > > A1 02-04-06 > > auto: > B1 02-04-07 > > > Bart >...

Adding sums in rows and columns and colouring cells with conditions
Hi all, I have a long table where i need to add and colour individual cells based on 'days home' and 'days away'. The table is uploaded here https://www.yousendit.com/download/MnFqaUNBUzhoMlYzZUE9PQ In order to automate some of this i'd like to automate some, but preferably all aspects of this, such as: 1. automatically colour the cell orange for "home" and yellow for "away" 2. add separately in the home and away columns the number of days for each 3. add the rows for the alternate lines (ie days only, rates only) I want to do this without yet another...

NTBackup is blank
Hi all, I have been using NTBackup for my Exchange 2003 SP1 and wanted to test a restore of the database. I have had no errors or issues with the backups at all. I noticed as a browse my catalogs that they are all blank, no database information is displayed under the backup jobs. Just the folders in the left columns and the right column says no data available (a breakout of the stores is listed). My databse is 50GB and the *.bkf file reflects it however the file itself appears blank. Can this be? So, I go to restore the file and I get an error that the database could not be located wi...

Previous Record
How do I access a field from the previous record in a form To programmatically read the value from the previous record, regardless of how the form is sorted or filtered, use the RecordsetClone of the form. This kind of thing: Function GetPreviousValue(frm As Form, strField As String) As Variant On Error GoTo Err_Handler 'Purpose: Return the value from the previous row of the form. 'Keywords: PriorRow PreviousValue Dim rs As DAO.Recordset Set rs = frm.RecordsetClone rs.Bookmark = frm.Bookmark rs.MovePrevious GetPreviousValue = rs(strField) Set rs...

substraction of sequential records
I need to calculate moving ranges in either a report or query. Currently my query returns TestTime (ascending) and TestResult. The moving range is the absolute difference between two consecutive samples. TestTime TestResult MovingRange 8am 20 -- 9am 24 4 10am 21 3 11am 26 5 Once all the moving ranges are calculated, I need to report the average of the moving ranges. ...

Adding text and date problem
I have text in column 1, a date in column 2 and in column 3 I wish to show the text followed by the date. I have tried both Concatenate and & but the the result is text followed by the underlying number representing the date. I cannot seem to format this column to show the date as dd/mm/yy. Any suggestions? Regards ="Today is: "&TEXT(A1,"mm/dd/yyyy") -- Kind regards, Niek Otten Microsoft MVP - Excel "newman" <aa111@despammed.com> wrote in message news:464C1646.AC6A42E0@despammed.com... |I have text in column 1, a date in column 2 and in column...

adding values of cells
I am trying to use a formula or function that will look at a cells say column A check for a condition, if true, then add the value of another cell that is on the same row to another cell. Yes I know that may be difficult to understand so let me give you an example. A B C D 1 2 dvd 25.23 3 cd 12.25 4 dvd 25.23 5 cd 12.25 6 bat 19.25 ok so what i want to do is look at column A, and look for any cells that have dvd and add the corresponding value from column D to cell B1. So in this case we can see the cell A2 and cell A4 is dvd so I want to add cells D2 and D4 and that...

Adding and subtracting from a column.
I have a stock list with a "quantity" column. What I would like to do is add the new stock to the existing quantity column or subtract requisitioned items from the quantity column. I guess it is a "self totaling" column I need to make. As a separate exercise I would like to make a new column for orders. Insert a quantity on that column for any of the items ( rows) I want to order and then print it out... BUT I only want to print the rows that have quantities in that column ( i.e. the actual items I am ordering ). This column is not linked to the above column. Could an...

Adding items (hours) into invoices
Hello, In my M07 Home and Business, I get an error while entering an invoice as soon as I add hours and press enter. The program says that there's an error: "This operation can not be performed" When I select more help, I get a help page that say:Details Id: obres:34 Bron: 16.0 When I search on this I see that it is an error that's related to homepage settings, but that does not work for me. Any suggestions? Thanks, Eric ...

email account will not show up when added: says deleted
my main email account will not show up in the "accounts" section of Outlook 2003 even though I have added it and the "test" button shows it is set up correctly. It is listed in the accounts pane of add/change email accounts but when I try to set it as the default account I get a message it is not found and probably was deleted. I have deleted and re added it several times with the same result. Any solution ? Alec McNaughton <anonymous@discussions.microsoft.com> wrote: > my main email account will not show up in the "accounts" > section of Outl...

Default Public View has records but columns are blank
A custom entity has a default list view but the entries in the list do not show any values. There are a number of lines in the list with an icon only. If I try to delete one the message says select an entry before attempting operation. I cannot select the lijne either. Have created many views before and never seen this. Other views are ok? Hi Steve, Try publishing the entity and see if this resolves the problem. HTH, Niths "Steve" wrote: > A custom entity has a default list view but the entries in the list do not > show any values. There are a number of lines in the...

Field with running count of records
Hi, I have a query with fields such as date, Item Number and Quantity. I want to create an additional field called Count that will act like an autonumber. It will assign a value of 1 for the first record, 2 for the second number etc. Can someone tell me how to do this? Thanks, -- Chuck W There are tons of posts on how to create a "ranking" query. You must have a field or fields that uniquely identify the sort order. If you provided significant table and field names as well as your desired sort order, someone could create the SQL for you if you can't search for and fi...

Adding 2nd Y-axis to Excel2003 chart
I am making a simple line chart with two types of column data. I want to represent them with separate Y-axes. However, when I select the two columns of data and go through the Chart wizard the secondary Y-axis option is inaccessible. Excel 2003 help is no help as it tells me to go to the Format Menu, click Selected Data Series then click the Axis tab, but there is no such option under my format menu. How do I select the secondary Y-axis option? Hi, The chart wizard does not give you the option to place the selected data on 2 axes. You will have to do it once the chart is cre...

Access Denied error while edit some of the GPOs in Windows 2003 AD
Hi We are unable to edit some of the GPOs (Default Domain Policy, etc) and getting Access Denied error. We checked the permission of SYSVOL folder and found Administrators (Domain), System and Authenticated Users have full control share permissions. Full access has been provided to Administrators, creator owner & System and read & execute permission has been provided to Authenticated users in Security tab. Can anyone help me to resolve the issue and also any doc is available to check the correct permissions with SYSVOL. Thanks in advance for help Regards Lal -...

Advance filter not filtering unique records only
Hi, I have a user who has four large lists, he wants to combine them into two master lists; but hide or delete duplicates. I showed him how to use Advance Filter and he said it worked perfectly for combination; but the second worksheet did not filter only unique records. He assured me he followed the directions and click "Unique Records" in the Advance filter dialog box; but it didn't work. Can you guys give me a few more avenues he can pursue: (I suggested misspelled entries, and double-checking his work.) Thanks Kim I agree with you. My bet is that there is a typo or even ...

synchronized combo boxes moving between records problem
Sorry if this has been answered, I couldn't find the exact problem elsewhere! I'm only a beginner at this but when i select the right ID from the first combo box, i got it to come up with the correct related IDs in the second combo box , like it's meant to! but when I move to the next record (a new one, or the last one, or anything), the second combo box doesn't retain the record. As in, as you scroll through each record, none of the second combo box 'records' are kept, they are all blank until you click on them, then they come up with your choices, UNLESS you ...

Adding a password to a reminder?
This is a family PC which several of us use. Outlook 2002, XP ProSP2. There have been a couple of occasions when one of us have set a reminder in Calendar, but it has popped up when another user is at the machine. Teenagers being what they are, they don't appreciate the importance to someone else, and its been 'dismissed'. The Mum misses a deadline cos no-one reminded her.... I realise the answer to this lies in our helping one another out - and I'm creasonably confident that will happen - but I wonder if as a failsafe I can prevent an item being dismissed in some way...

Seeking an alternative to blank ("") cell in IF arguments
I usually find it convenient to use "" in formulas such as the following: =IF(B12>0,B12/C4,"") The problem of course is that an error will be returned if the cell left blank with "" is used in subsequent calculations. Is there an alternative to using zero instead of "" that still makes it obvious that the cell is intentionally left unpopulated but that does not result in #VALUE# errors? KG wrote: > I usually find it convenient to use "" in formulas such as the following: > > =IF(B12>0,B12/C4,"") > > The pr...

My code stops after the record saves....form never closes
Users are going into to update a number, and they want to close the form before the record gets saved, thus losing their changes. I created an Exit button, which should save the record change and close the form. Here's the code: If Me.Dirty Then RunCommand acCmdSaveRecord End If Forms![frmRMSHours]![Act Hours] = Me![Text9] DoCmd.Close If the record is dirty, they press Exit and the record saves. They have to press Exit again and the form closes. If the record is not dirty, the form will close when pressing Exit. What am I doing wrong? To save ...