Control if a form updates table

I would like to know how best to accomplish this data entry form requirement.

I have a form with a number of fields which is linked to a table.  When the 
operator opens the form i would like all the fields to be blank and the user 
can tab through the fields as necessary.  I have two command buttons.  One 
allows to exit the form (all set with that one) with no saving of the form 
data, while the second button opens a message box which asks the operator if 
they wish to submit the information on the form (within the fields) Y/N and 
the message box has all the data from the fields listed for confirmatin.  

What i need help with is how do i NOT allow the fields to update the table 
if the operator tabs past the last field in the form and it arrives back to 
the first field?  Anotherwords i want the form to only cycle through the 
fields without updating the table.  The table update is left for once the 
command button event is intitated.  If the operator selects yes i wish the 
form data to be added to the table and closes the form.  Also, how do i clear 
all the fields if the operator decides not to accept the information once it 
arrives in the message box?  That is he/she selects No on the message box 
message.

Thanks
DaveM
0
Utf
1/27/2010 1:53:01 AM
access 16762 articles. 2 followers. Follow

3 Replies
720 Views

Similar Articles

[PageSpeed] 51

The only way to block updates is the cancel the BeforeUpdate event of the 
*form* (not controls.) Access fires that event regardless of what triggered 
the update (e.g. closing the form, tabbing past the last control, moving 
record, closing Access, toolbar/menu/ribbon, pressing Shift+Enter, ...) The 
event doesn't fire if nothing was updated.

Your 'cancel and close' button's Click event procedure would contain code 
like this:
    If Me.Dirty Then Me.Undo
    DoCmd.Close acForm, Me.Name

Your 'save and close' button's Click event procedure:
    If Me.Dirty Then Me.Dirty = False
    DoCmd.Close acForm, Me.Name
You'll need error handling too: if the save is cancelled, Access will notify 
you that the attempt to set the Dirty property to False didn't work.

The confirmation is then handled in Form_BeforeUpdate, like this:
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If MsgBox("Save?", vbOkCancel, "Confirm") <> vbOk Then
        Cancel = True
    End If
End Sub

-- 
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.


"DaveM" <DaveM@discussions.microsoft.com> wrote in message 
news:53B8CF8F-B969-4FFA-964F-6A6CF3BA57A0@microsoft.com...
> I would like to know how best to accomplish this data entry form 
> requirement.
>
> I have a form with a number of fields which is linked to a table.  When 
> the
> operator opens the form i would like all the fields to be blank and the 
> user
> can tab through the fields as necessary.  I have two command buttons.  One
> allows to exit the form (all set with that one) with no saving of the form
> data, while the second button opens a message box which asks the operator
> if they wish to submit the information on the form (within the fields) Y/N 
> and
> the message box has all the data from the fields listed for confirmatin.
>
> What i need help with is how do i NOT allow the fields to update the table
> if the operator tabs past the last field in the form and it arrives back 
> to
> the first field?  Anotherwords i want the form to only cycle through the
> fields without updating the table.  The table update is left for once the
> command button event is intitated.  If the operator selects yes i wish the
> form data to be added to the table and closes the form.  Also, how do i
> clear all the fields if the operator decides not to accept the information
> once it arrives in the message box?  That is he/she selects No on the
> message box message.
>
> Thanks
> DaveM 

0
Allen
1/27/2010 2:31:49 AM
Thank you Allen.  Before i go to work on this would it be easier to fire an 
Update Query that would append the table with these fields/data if the 
operator chooses to accept the information?  If not that's fine and i'll 
proceed.  If so, what would the "save and close" button code look like?  The 
one item i want to note is that one field in the table is an "Auto Number" 
field which is not visible in the form and whatever proceedure i use to get 
the data into the table will need to consider the auto num field.  This may 
not be a issue but just wanted to point it out.

Thanks Allen, your help is very appreciated.

DaveM

"Allen Browne" wrote:

> The only way to block updates is the cancel the BeforeUpdate event of the 
> *form* (not controls.) Access fires that event regardless of what triggered 
> the update (e.g. closing the form, tabbing past the last control, moving 
> record, closing Access, toolbar/menu/ribbon, pressing Shift+Enter, ...) The 
> event doesn't fire if nothing was updated.
> 
> Your 'cancel and close' button's Click event procedure would contain code 
> like this:
>     If Me.Dirty Then Me.Undo
>     DoCmd.Close acForm, Me.Name
> 
> Your 'save and close' button's Click event procedure:
>     If Me.Dirty Then Me.Dirty = False
>     DoCmd.Close acForm, Me.Name
> You'll need error handling too: if the save is cancelled, Access will notify 
> you that the attempt to set the Dirty property to False didn't work.
> 
> The confirmation is then handled in Form_BeforeUpdate, like this:
> Private Sub Form_BeforeUpdate(Cancel As Integer)
>     If MsgBox("Save?", vbOkCancel, "Confirm") <> vbOk Then
>         Cancel = True
>     End If
> End Sub
> 
> -- 
> 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.
> 
> 
> "DaveM" <DaveM@discussions.microsoft.com> wrote in message 
> news:53B8CF8F-B969-4FFA-964F-6A6CF3BA57A0@microsoft.com...
> > I would like to know how best to accomplish this data entry form 
> > requirement.
> >
> > I have a form with a number of fields which is linked to a table.  When 
> > the
> > operator opens the form i would like all the fields to be blank and the 
> > user
> > can tab through the fields as necessary.  I have two command buttons.  One
> > allows to exit the form (all set with that one) with no saving of the form
> > data, while the second button opens a message box which asks the operator
> > if they wish to submit the information on the form (within the fields) Y/N 
> > and
> > the message box has all the data from the fields listed for confirmatin.
> >
> > What i need help with is how do i NOT allow the fields to update the table
> > if the operator tabs past the last field in the form and it arrives back 
> > to
> > the first field?  Anotherwords i want the form to only cycle through the
> > fields without updating the table.  The table update is left for once the
> > command button event is intitated.  If the operator selects yes i wish the
> > form data to be added to the table and closes the form.  Also, how do i
> > clear all the fields if the operator decides not to accept the information
> > once it arrives in the message box?  That is he/she selects No on the
> > message box message.
> >
> > Thanks
> > DaveM 
> 
> .
> 
0
Utf
1/27/2010 5:59:02 PM
a) It will be *much* easier to work with the bound form events than to build 
action queries to insert/delete/edit the data in an unbound form.

b) The save'n'close button will have just the 2 lines posted previously, 
plus error handling. If error handling is new, here's an example:
    http://allenbrowne.com/ser-23a.html
(You don't have to use the logging that page: the first "simplest" example 
at the top will do.)

c) You don't need to worry about the autonumber. Access will assign a number 
without you needing to do anything.

-- 
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.


"DaveM" <DaveM@discussions.microsoft.com> wrote in message 
news:2DE82360-3C6A-49D5-80FA-3DD435A3C7B1@microsoft.com...
> Thank you Allen.  Before i go to work on this would it be easier to fire 
> an
> Update Query that would append the table with these fields/data if the
> operator chooses to accept the information?  If not that's fine and i'll
> proceed.  If so, what would the "save and close" button code look like? 
> The
> one item i want to note is that one field in the table is an "Auto Number"
> field which is not visible in the form and whatever proceedure i use to 
> get
> the data into the table will need to consider the auto num field.  This 
> may
> not be a issue but just wanted to point it out.
>
> Thanks Allen, your help is very appreciated.
>
> DaveM
>
> "Allen Browne" wrote:
>
>> The only way to block updates is the cancel the BeforeUpdate event of the
>> *form* (not controls.) Access fires that event regardless of what 
>> triggered
>> the update (e.g. closing the form, tabbing past the last control, moving
>> record, closing Access, toolbar/menu/ribbon, pressing Shift+Enter, ...) 
>> The
>> event doesn't fire if nothing was updated.
>>
>> Your 'cancel and close' button's Click event procedure would contain code
>> like this:
>>     If Me.Dirty Then Me.Undo
>>     DoCmd.Close acForm, Me.Name
>>
>> Your 'save and close' button's Click event procedure:
>>     If Me.Dirty Then Me.Dirty = False
>>     DoCmd.Close acForm, Me.Name
>> You'll need error handling too: if the save is cancelled, Access will 
>> notify
>> you that the attempt to set the Dirty property to False didn't work.
>>
>> The confirmation is then handled in Form_BeforeUpdate, like this:
>> Private Sub Form_BeforeUpdate(Cancel As Integer)
>>     If MsgBox("Save?", vbOkCancel, "Confirm") <> vbOk Then
>>         Cancel = True
>>     End If
>> End Sub
>>
>> -- 
>> 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.
>>
>>
>> "DaveM" <DaveM@discussions.microsoft.com> wrote in message
>> news:53B8CF8F-B969-4FFA-964F-6A6CF3BA57A0@microsoft.com...
>> > I would like to know how best to accomplish this data entry form
>> > requirement.
>> >
>> > I have a form with a number of fields which is linked to a table.  When
>> > the
>> > operator opens the form i would like all the fields to be blank and the
>> > user
>> > can tab through the fields as necessary.  I have two command buttons. 
>> > One
>> > allows to exit the form (all set with that one) with no saving of the 
>> > form
>> > data, while the second button opens a message box which asks the 
>> > operator
>> > if they wish to submit the information on the form (within the fields) 
>> > Y/N
>> > and
>> > the message box has all the data from the fields listed for 
>> > confirmatin.
>> >
>> > What i need help with is how do i NOT allow the fields to update the 
>> > table
>> > if the operator tabs past the last field in the form and it arrives 
>> > back
>> > to
>> > the first field?  Anotherwords i want the form to only cycle through 
>> > the
>> > fields without updating the table.  The table update is left for once 
>> > the
>> > command button event is intitated.  If the operator selects yes i wish 
>> > the
>> > form data to be added to the table and closes the form.  Also, how do i
>> > clear all the fields if the operator decides not to accept the 
>> > information
>> > once it arrives in the message box?  That is he/she selects No on the
>> > message box message.
>> >
>> > Thanks
>> > DaveM
>>
>> .
>> 
0
Allen
1/28/2010 4:55:34 AM
Reply:

Similar Artilces:

Using TAB key in a table
Hello folks I am sure that this would of been asked before but I cannot find any reference to it. Please can you tell me wether it is possible to use the TAB key within a single cell in a table. (not to advance to the next cell, but just to be able to line up text in another cell.) Maybe it is a key combination ctrl/tab for example. Your help is and always has been very much appreciated. Thanking you in anticipation. -- Big Rick Create a text box the size of the cell. -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msauer.mvps.org/ news://msnews.microsoft.com "Big Ri...

Split Form Size
I've created a split form but it I can't get it to save the size I resize it to. The height is off the bottom of the screen. Can someone enlighten me as to how I can size these forms to my liking? Thanks, James DoCmd.MoveSize works from the top and left of the Access window. The following example changes the active window height, but leaves its width unchanged: DoCmd.MoveSize , , , 2000 -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "JamesJ" <jjy@adelphia_darwin.net> wrote in message news:ud%236QegLIHA.3400...

RE: Control
Can some one point me right direction to achive this following. Need to dispaly a data in a table format. some of the column data may have to display a image/icon based on a value from the table (1 :- Red color icon, 2:-yellow color icon...) Once the data is loaded and displayed on the format , able to search particular row by column index and update the values including the images(change different image). I tried with Listview control, with the report view I am not able to search row and update the columns with the List vew I am not able to show the image/icon. do not want to use ...

cursor missing from form textboxes
I believe that I know 98% of the little intricacies of Publisher, but I'm stumped on this one. I created a website with a contact page and none of the standard textboxes will show a cursor to indicate with field you are in (I've even made the site live to double check). If you tab between textboxes, the tabbing works, as does typing, there is just no visual cursor to indicate where you are. If you click on the first textbox and type, all is good you can tab to the next box and type, and it's fine. I'd like to have the visual cursor show and can't get it. I also have ...

Appending multiple tables
I have taken on a new role where I have control of 7 databases on different sites. I want to create master tables with data from each database. Is there a way I can append all the tables into the master table at one go rather than appending them one by one? Many thanks GLS ...

bound control or inbound control
Hi Katherine, There are differences between using bound versus unbound controls. Where on the form do you want to use an unbound control? Jeanette Cunningham "Katherine" <Katherine@discussions.microsoft.com> wrote in message news:23BBC192-0A74-4B9F-9513-5E5923A76658@microsoft.com... > ...

Lock Position Of User Form In VBA
Can Someone Help? Ron De Bruin and Jorge Rodrigues gave sone ideas on how to disable th "X" close button on a user form. However I now need to ensure that the User Form cant be moved down b holding on the program bar and sliding down, as this would revea critical data shown below. Is there a way to lock the position of the User Form? Thanks Celtic_Avenge -- Celtic_Avenge ----------------------------------------------------------------------- Celtic_Avenger's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=1410 View this thread: http://www.excelforu...

Money 2003 adds financial desktop shortcuts when running Money Updates
Why does Money add desktop shortcuts to two (2) financial institutions while I'm running account updates w/ "Money Updates" selected? I would like to disable this feature (w/out turning off Money Updates)? Thanks, Roger In microsoft.public.money, Roger Beniot wrote: >Why does Money add desktop shortcuts to two (2) financial institutions >while I'm running account updates w/ "Money Updates" selected? > >I would like to disable this feature (w/out turning off Money Updates)? Check Tools->Options->General->TurnOffSponsorshipsAndShoppingLinks ...

why won't word 2007 release control of a .rtf file after exiting w
I AM HAVING TROUBLE WITH ACCESS TO RICH TEXT FILES BY PROGRAMS OTHER THAN WORD------ AFTER THE FILE OPEN IN WORD IS CLOSED AND EVEN AFTER WORD HAS BEEN SHUT DOWN (EXITED) ---------- I GET THE ERROR MESSAGE THAT THE FILE IS "IN USE" BY WORD WHEN (FOR EXAMPLE) AN ATTEMPT IS MADE TO DELETE IT ---- SOMETIMES WHEN RE-OPENING THE FILE AFTER RE-STARTING WORD THE FILE CANNOT BE SAVED BECAUSE IT IS IN "READ ONLY" MODE ------- AFTER MUCH FIDDLING AROUND I FINALLY MANAGED TO DELETE THE PROBLEM FILE ONLY TO HAVE THE SAME PROBLEM RETURN WITH A DIFFERNT FILE ----- WORD 200...

MSCRM login and the Users Control Panel
Is anyone aware of any way that the settings stored under Control Panel - Users - Password Management might interfere with the process of Single Sign On into MSCRM? It seemed to have somehow cached someones login and prevented logging in as the logged in user, until cached credentials had been "deleted". However I have never seen this mentioned as being relevant. I am not too sure what the architecture model is there, but it appears to be a single domain. If this area has any relevance, it would be useful to know....thanks ...

AD not updating Exchange
I have an Exchange 2000 server with 3 5.5 servers, all in the same organisation but in different sites. 2-way ADC has been configured and site connectors/replicators set up. The 5.5 servers aren't receiving address lists or mailbox info from the 2000 server, and the 2000 server is logging the following errors: MSExchangeSRS Event ID 109 The directory replication agent (DRA) attempted to open mail with name /o=ORG/ou=OU/cn=Configuration/cn=Servers/cn=SERVERNAME/cn=Microsoft DSA:SERVERNAME but received error 2147746077. Messages could not be sent. Make sure that the mail service on this M...

Adding a control variable
Hello all, Under VC++ 7.1, I've added a checkbox to a dialog. Using the dialog editor, I am not trying to add a control variable for this check box. However, when I right-click on the check box and select "Add Variable", the resulting wizard dialog does not allow me to select "Control Variable" (it is grayed out). What do I need to do to get a control variable for this check box added to my class? Thanks, Dave I've never seen that before - are you able to duplicate this problem with other projects too? -- Regards, Nish [VC++ MVP] "Dave" <bett...

Spam control - MS Outlook
Hi! I am using MS Outlook 2000 and use 2 profiles. I recently installed a firewall package (Fsecure = Telia S�ker Surf), including Spam control. After install, MSO hangs and I cannot change to the other profile (if I not removed MSO by ctrl/alt/del) Somebody want to comment this? /Sven-Erik Intergrated AV/Spam is the cause of frequent issues in Outlook Is the FSecure you installed compatible with this old version of Outlook? "Storfille" <storfille@ebrev.net> wrote in message news:b1Ixm.11987$U5.164765@newsb.telia.net... > Hi! > > I am using MS Outlook 200...

Updating info from a form with query
I have a form with a user entered default value, which I have moved to a called form. This form then is to update a table. The problem is that it only updates when I actually change the data myself. What action must I take programmably to have the table info entered? I had a button on the form which I have save the form, but it gives me a "The command or action 'SaveRecord' isn't available now." I have even tried to set the focus to the controls which have the info to be saved to the table. Can anyone help? Thanks, Kurt -- learning by playing answered in &...

updating macros
i have a few macros that i have made for a blank workbook, i want to keep the workbook as a template, so when i save it, i change the filename. now whenever i run the macros it comes up with that it needs to run the debugger. how do i automatically update the macros so they reference to the current filename? Failing that, is there an easier way to run it so that it activates the current file rather than it having to be named? i am using the following macro: Sub copyam() Workbooks.Open Filename:= _ "\\Alc-server\Users\Midshires\My Documents\Wages\Blank Sheets and Lists\Blank Wa...

Releasing memory after creating OCX control
Hi, I try to create an instance of an activex control in my MFC dialog based app. Though my application works with out any error, if i check the memory after calling "delete pMyControl" it keeps on occupying the same memory it occupied when the control was alive. Could anyone help me where I am missing to clear the memory? Thank you. My Code here ---------------m_pxWmp= new CWMPPlayer4();if( m_pxWmp->Create("replay", WS_CHILD | WS_BORDER | WS_VISIBLE , CRect(100, 100,320,240), this, 2)){ m_replay = true; m_pxWmp->SetUrl("C:\\video.avi"); m_pxWmp->Set...

Access 2007: Record update timestamps
Hi all, I've been fighting with this one and can't find any information on it. I have a feeling it was easier to do this with Access 2003, but maybe not. If anyone can help me out, I'd appreciate it. In Access 2007, I have a form where if I edit any (or specific) fields I want it to stamp the current date and time in a separate field. So, for example: I have a "Notes" field. If I edit this field, I want it to stamp the time in a "DateModified" field at the bottom of the form. I have tried to use onDirty, AfterUpdate, BeforeUpdate with expressions as well as V...

Automatically updating
How can I get numbers in one column (ie Year-to-date) to automatically update when I add new numbers in another column (ie Month-to-date)? Hi do you have ...........A...........B...............C............M........N 1.......Item......Jan.............Feb..........Dec.....YTD if so the formula in N2 would be =SUM(B2:M2) or do you have .........A...........B..............C 1.....Item.......Mth Value...YTD Value if so the formula in C2 would be =B2+C2 but you'll have to choose tools / options / calculation and tick the Iterations box and set the maximum value to 1 Cheers JulieD "...

Uninstall Update Rollup 4
I need to uninstall update rollup 4 which the announcement says can be done, but there are no instructions for doing so. Can anyone point me in the direction of uninstall directions? thanks, Ken Compter wrote: > I need to uninstall update rollup 4 which the announcement says can be done, > but there are no instructions for doing so. Can anyone point me in the > direction of uninstall directions? > > thanks, Control Panel - Add/Remove programs Pete, found it. Thanks, "Ken Compter" wrote: > I need to uninstall update rollup 4 which the announcement s...

stock control system
trying to see up a 'basic stock control sytem' for a warehouse that contains building materials. -- Thank you. You can use multiple ways of doing, there's 3 ways to come to m head... 1) Use a worksheet as database an save all the information on the cell of that worksheet using some macros to access it and handle. 2) You might want to use Access instead of Excel, I think would b easier to handle the database portion and you still have the Macro available. 3) You can use VB with an Access to create an application -- fanay ------------------------------------------------------...

what control can display characters from txt files in a dialog?
I want to read lines from txt files and display them on a dialog.I wonder which control can be put into the dialog to hold the characters from txt? Something like :- while(m_file.ReadString(tmpstr)) { str += tmpstr; } m_edit.SetWindowText(str); -- Regards, Nish [VC++ MVP] "liao_xf" <sclxf@sina.com> wrote in message news:001401c3652f$177b9830$a001280a@phx.gbl... > Thanks first! > I know that Edit control can receive and edit input. > and I know the filestdio can read lines from txt. > but can you tell me how to attatch the lines to the edit? > Thanks!...

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

Creating dialog controls at run-time
I've been researching how to do this, but whenever I try what other people do, I get very bad results. But my challenge doesn't end there. The number of controls I want to create is only known at run-time. The problems I face are manifold: 1. The code most people use fails badly. Not only is the font unconventionally big, but when I click the checkbox I create, the whole dialog disappears. If that's not enough, the pointer to the control is NULL when the dialog's destructor is called, so I can't delete the instance, and get a memory leak message when the program ends: CADi...

MDI support in ATL composite control
Hi, I have components created in ATL. The components are ATL composite controls. Entire work has been done till now using ATL, however I now need to give MDI type of work area (based o doc-view architecture) within the control. I have class derived from CMDIFrameWnd. However when I try to initialize instance of this class by calling "Create" method of CFrameWnd (base class in CMDIFrameWnd), I get error and creation fails. Tracing the code, I found that base class create method calls "AfxGetInstanceHandle" to get handle to the DLL. Now since this is ATL Composite control, ...

Microsoft Access Table Record Limitation
Hi, is there a limit to how may rows an access table can have before it performs poorly? "mark" <user@msgroups.net/> wrote in message news:OGxT6vdgKHA.2596@TK2MSFTNGP04.phx.gbl... > Hi, is there a limit to how may rows an access table can have before it > performs poorly? > > --- > frmsrcurl: http://msgroups.net/microsoft.public.access/ That's depends upon the type and quality of indexes, as well as other factors. I have a database front-end that connects to a 1.1 GB backend with 6 million records. By using the primary key, I can pull 1 record ...