?
Solved

MS Access 2010 - Stop Table Update

Posted on 2015-01-13
5
Medium Priority
?
417 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 38

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 38

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

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

777 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