• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 451
  • Last Modified:

MS Access 2010 - Stop Table Update

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
Bob Collison
Asked:
Bob Collison
  • 2
  • 2
1 Solution
 
SimonCommented:
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
 
PatHartmanCommented:
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
 
Bob CollisonSystem ArchitectAuthor Commented:
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
 
PatHartmanCommented:
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
 
Bob CollisonSystem ArchitectAuthor Commented:
This is an excellent, simple solution!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now