Solved

MS Access 2010 - Stop Table Update

Posted on 2015-01-13
5
355 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: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 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

911 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

24 Experts available now in Live!

Get 1:1 Help Now