Solved

MS Access 2010 - Stop Table Update

Posted on 2015-01-13
5
328 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
  • 2
  • 2
5 Comments
 
LVL 18

Expert Comment

by:SimonAdept
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 34

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 34

Accepted Solution

by:
PatHartman earned 500 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now