Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

MS Access 2010 - Stop Table Update

Posted on 2015-01-13
5
Medium Priority
?
431 Views
Last Modified: 2015-01-14
Hi Experts,
During the addition of a new record I check for entry of data on a form in a non-key field.  The User doesn't always access the field but needs to be forced to access / put data in the field.  If the data is not supplied then I need to stop the writing of the record.  I can detect the that the field doesn't contain data and issue a message but how do I stop the record being written without valid data?  I'm currently doing the checking in the BeforeUpdate Event.

What I believe I need to know is how to stop the addition (writing) of a record or the updating of an existing record that is incomplete but has a valid Primary Key.

Thanks.
Bob C.
0
Comment
Question by:Bob_Collison
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:Simon
ID: 40547920
You could disallow nulls and zero length in the field in the table definition - then there is no way for the record to be udpated without a value in the field.

Or you can check that field in the form.beforeupdate event and cancel the update and set focus to the field.

pseudocode:

Form_BeforeUpdate (cancel as integer)
if isnull(me.txtField) then
  me.txtfield setfocus
  msgbox "Enter a value before saving"
  cancel = true
  exit sub
end if
'Otherwise continue with the update
end sub
0
 
LVL 39

Expert Comment

by:PatHartman
ID: 40547953
No points please.

I want to emphasize what simon said.  Simon says:)  use the FORM's BeforeUpdate event. NOT the control's BeforeUpdate event.  You can't ever rely on the BeforeUpdate event of a control to ensure some value is entered because as you have discovered, if the user doesn't go there or doesn't dirty the control, your code doesn't run.  

In practice, you will find that using the Form level event for everything is more convenient.  The only validation I do in controls is duplicate checking and then only if I want the user to stop and not proceed until he fixes the problem.

Sorry simon, I just couldn't resist:)
0
 

Author Comment

by:Bob_Collison
ID: 40548047
Hi Experts,

Thanks for the quick response.

Sorry I wasn't explicit that the Before Update Event was at the Form Object level (not the Control level).

My code is very similar to the Pseudo Code above with the exception of the 'Cancel' Statement.  I haven't used it in the past.

Am I correct that the 'Cancel' will totally stop the update from occurring?

Here is my code with the 'Cancel' added.  Is it correct?

Private Sub Form_BeforeUpdate(Cancel As Integer)
On Error GoTo Err_Form_BeforeUpdate
'MsgBox "Step 000-Form Before Update Event."
STEP_005:
'MsgBox "Step 005-Check For Organization Data."
If IsNull([PRIMARY_PERIOD_ORG_KEY]) Or [PRIMARY_PERIOD_ORG_KEY] = " " Then
   [PRIMARY_PERIOD_ORG_KEY].SetFocus
   MsgBox "E###-Organization doesn't exist for Period."
   cancel = true 'Added per your suggestion.
   GoTo STEP_900
End If
STEP_900:
'MsgBox "Step 900-Shutdown."
Exit_Form_BeforeUpdate:
    Exit Sub
Err_Form_BeforeUpdate:
   MsgBox Err.DESCRIPTION & " (" & Err.Number & ")"
   Resume Exit_Form_BeforeUpdate
End Sub

Thanks.
Bob C.
0
 
LVL 39

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 40548093
Cancel = True cancels the update event and prevents the record from being saved.  It is still dirty so the user won't be able to exit the form without either fixing the error or backing out the change using the Esc key.

I changed the code slightly.  Using the Len() function will take care of both null and ZLS at the same time.  I also added the "Me." qualifier to the control names.  This is more efficient since it tells Access where the variable name is defined so Access doesn't have to search it's libraries.  And finally, it looks like your code has waterfall GoTo's which I guess is a style but I don't recommend it.  I find it cleaner to simply exit the sub once the first error is found.  The users can't process more than one error message at a time so unless you are highlighting controls on the form to identify all the errors at once, it is better to just exit as soon as you find one.  With the waterfall GoTo's you always have to worry about your position in the code module and adding code or moving code will force you to have to redo all the GoTo's.

If Len(Me.[PRIMARY_PERIOD_ORG_KEY]) = 0 Then
    Me.[PRIMARY_PERIOD_ORG_KEY].SetFocus
    MsgBox "E###-Organization doesn't exist for Period."
    cancel = true 'Added per your suggestion.
    Exit Sub
    'GoTo STEP_900
 End If

Open in new window

0
 

Author Closing Comment

by:Bob_Collison
ID: 40548905
This is an excellent, simple solution!
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question