Prevent form/record update

[Shinobi]

Hi all

I'm in dire need of some help to come up with a solution for something I've been struggling with for some time now.

I have a data entry form for users on which they can create, edit & delete records. Essentially I don't want to allow the form to append any newly entered data to the table (i.e. save, update) if the name field is empty. Sounds simple. I have tried numerous vba solutions using the BeforeUpdate event, declaring a global variable which cancels the update. But nothing I do seems to get the desired result.

The problem stems from the fact that there are multiple ways that the form would usually force an update e.g. closing it, navigating to another record etc.. and some custom buttons which I have, namely 'Save & Close' and 'New'. All of which when activated attempt to save and therefore conflict with anything I put in the BeforeUpdate event and vice versa. What I think I need to be able to do is, stop the update event from another sub-routine. For example: (The red text is where I need a line of code that stops the form from updating)

Public Sub cmdSave_Click()

If Me.FirstName = "" Or IsNull(Me.FirstName) Or Me.Surname = "" Or IsNull(Me.Surname) Then

    If MsgBox("Can't save record, no name given. Continue without saving?", vbYesNo, "Warning") = vbYes Then
    [COLOR=Red]Update Cancel = True[/COLOR]
    DoCmd.OpenForm "frmMenu", , , , , , Me.OpenArgs
    DoCmd.Close acForm, "frmCourses"
    Else
    Exit Sub
    End If

Else
    DoCmd.RunCommand acCmdSaveRecord
    DoCmd.OpenForm "frmMenu", , , , , , Me.OpenArgs
    DoCmd.Close acForm, "frmCourses"
End If
End Sub

I hope I haven't made a complete ballsup of that explanation, and that some extraordinarily clever person can come and tell me how to fix it!! Thansk in advance for taking a look.

 

[RainLover]

Write your code in the Form's Before Update event.

This event always fires before saving.

 

[Shinobi]

Thanks for your reply RainLover. However as mentioned in my post, I have tried using various methods in the BeforeUpdate event. I will illustrate the problem using an example with the following code in the BeforeUpdate event of the form:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.CourseName = "" Or IsNull(Me.CourseName) Then
    If MsgBox("No Course Name entered, continue without saving?", vbYesNo, "Unable to save") = vbYes Then
    DoCmd.CancelEvent
    End If
End If
End Sub

Public Sub cmdSave_Click()
    DoCmd.OpenForm "frmMenu", , , , , , Me.OpenArgs
    DoCmd.Close acForm, "frmCourses"
End Sub

1. User creates a new record and enteres some info but leaves the 'Name' field blank
2. User presses save/close button
3. The BeforeUpdate event fires and brings up the message box.
4. The user clicks no (This should mean the the form stays open and doesn't save)
5. The problem however; the OnClick event for the save/close button still fires therefore closing the form and automatically saving it because the event has fired seperately/after the BeforeUpdate event.

So how can I tell the BeforeUpdate event to effectifvely ignore the last routine run?

 

[jdraw]

see if this offers anything new
http://www.blueclaw-db.com/access_event_programming/beforeupdate.htm

I agree with Rain that the Beforeupdate is the event involved.


edit: also I think all you need in the code is
Cancel=True

not the Docmd......

 

[Shinobi]

I've thrown together a diagram to show exactly how I'd expect/hope my form to act under the three possible circumstances. Saves you trying to make sense of my poor explanation skills.

 

[Shinobi]

Thanks for coming in on the thread jdraw. I don't dissagree that the BeforeUpdate is part of how to do this but simply that anything I've tried with it hasn't worked.

Ok I had a look at the link, but both the examples on that page are using BeforeUpdate events of those specific fields. I'm trying to stop the whole record from being updated.

 

[RainLover]

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Len(Me.CourseName) = 0 Then

    MsgBox "No Course Name entered"
 
    Cancel = True

End If
 
End Sub

See if this works. If it does then build around it.

 

[Shinobi]

Hi Rain.

Yea, it works as much as stopping the record from being saved. But the code of the button that initiated the update still runs...

If the Save & Close button was clicked it still closes and goes to the main menu.

Edit: I have stoped this by putting an error handler on in the sub
[If the New button was clicked it comes up with runtime error 2105 - couldn't go to specified record]

 

[RainLover]

What code are you using for New?

There is no need to have a save button. Leaving the record automatically saves.

A Close Button will save and close without the need to use any code for saving.

 

[Shinobi]

This is the code for both, my new record and save/close buttons. The save/close button is more of a return to menu button, but the 'save' is there to give users the benefit of the doubt since they would be unaware and wory whether it's saved.

Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click

    DoCmd.GoToRecord , , acNewRec
    Me.CourseName.SetFocus

Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
Resume Exit_cmdNew_Click
    
End Sub

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

    DoCmd.OpenForm "frmMenu", , , , , , Me.OpenArgs
    DoCmd.Close acForm, "frmCourses"
    
Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
Resume Exit_cmdSave_Click
End Sub

 

[RainLover]

If you opened the Form from the Menu then when you close the Form the Menu will be showing.

Why do you have open Args for a Menu.

The only thing you need is "DoCmd.Close"

Why do you use this "Me.CourseName.SetFocus"

Doesn't your Tab Order cover this.

 

[Shinobi]

The cursor does not automatically apear unless I set the focus despite the field being the first tabstop.

The menu is not open in the background so I need to open it when this one is closed. As such I need to specify the form to be closed.

If it was:
DoCmd.Close
DoCmd.OpenForm "frmMenu"
Then the menu would not open because the code can no longer run because the close was carried out first.

It it was:
DoCmd.OpenForm "frmMenu"
DoCmd.Close
Then the menu that was just opened would close and the courses form would still be open.

The OpenArgs are for a deeply intricate and probably poorly designed (but hey, im not an expert) security feature which restricts buttons, records etc depending on which user logged in.

But that's all inconsequential to this matter anyway.

 

[RainLover]

If it was:
DoCmd.Close
DoCmd.OpenForm "frmMenu"
Then the menu would not open because the code can no longer run because the close was carried out first.

The code finishes at the End Sub not at CloseForm.

Can you test this. I am pretty sure I am correct.

 

[Shinobi]

Ye, you're right. It did work actally, but I also checked before posting. Must have not done it right the first time.

Anyway, on another note: I've come up with sometthings that seems to be doing the trick for the 'save/close' or 'menu' button.

I've declared the following variable which is then used to determin the update:

Dim CancelUpdate As Boolean

Then in the BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)[INDENT]If CancelUpdate = True Then
Cancel = True
End If
[/INDENT]End Sub

The variable is then set within each of the buttons onclick events. Followed by quite a mish mash of stuff. Seems to work though. Only problem is if you've just created a new record without completing the name field and then click yes on the msg box to continue without saving it throws the "cant go to specified record" error.

Private Sub cmdNew_Click()
'On Error GoTo Err_cmdNew_Click

If Me.CourseName = "" Or IsNull(Me.CourseName) Then
CancelUpdate = True
End If

    If CancelUpdate = True Then
        If MsgBox("No course name entered, continue without saving?", vbYesNo, "Can't save record") = vbYes Then
        CancelUpdate = True
        DoCmd.GoToRecord , , acNewRec
        Exit Sub
        Else
        Exit Sub
        End If
    Else
    DoCmd.GoToRecord , , acNewRec
    End If

'Exit_cmdNew_Click:
'Exit Sub
'
'Err_cmdNew_Click:
'Resume Exit_cmdNew_Click
    
End Sub

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

If Me.CourseName = "" Or IsNull(Me.CourseName) Then
CancelUpdate = True
End If

    If CancelUpdate = True Then
        If MsgBox("No course name entered, continue without saving?", vbYesNo, "Can't save record") = vbYes Then
        CancelUpdate = True
        DoCmd.Close
        DoCmd.OpenForm "frmMenu"
        Exit Sub
        Else
        Exit Sub
        End If
    Else
    DoCmd.Close
    DoCmd.OpenForm "frmMenu"
    End If
    
Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
Resume Exit_cmdSave_Click
End Sub

 

[RainLover]

I think you write too much code.

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

If Len(Me.CourseName) = 0 Then
        If MsgBox("No course name entered, continue without saving?", vbYesNo, "Can't save record") = vbYes Then
        CancelUpdate = True
     End If
End If
 
    DoCmd.Close
    DoCmd.OpenForm "frmMenu"

    
Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
Resume Exit_cmdSave_Click
End Sub

NOT TESTED

I think this does the same thing.

Also you should look for some better error trapping.

 

[Shinobi]

lol maybe

Morning anyway, or evening... whatever time it is over there. So I tried that, didnt work. Was close though. Below is the slightly edited version, which does the same thing. The emboldened code is what I had to change/add.

Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click

[B]If IsNull(Me.CourseName) Or Me.CourseName = "" Then[/B]
        If MsgBox("No course name entered, continue without saving?", vbYesNo, "Can't save record") = vbYes Then
        CancelUpdate = True
        [B]Else: Exit Sub[/B]
     End If
End If
 
    DoCmd.Close
    DoCmd.OpenForm "frmMenu"

    
Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
Resume Exit_cmdSave_Click
End Sub

This still leaves me with the problem when creating a new record though. It just wont navigate to a new record.

 

[Shinobi]

ZOMG! I've only gone and done it...

Just thought I'd try a little theory and it worked.

Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click

If IsNull(Me.CourseName) Or Me.CourseName = "" Then
        If MsgBox("No course name entered, continue without saving?", vbYesNo, "Can't save record") = vbYes Then
        CancelUpdate = True
        Else: Exit Sub
     End If
End If
    
   [B] Me.Undo[/B]
    DoCmd.GoToRecord , , acNewRec
    Me.CourseName.SetFocus


Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
Resume Exit_cmdNew_Click
End Sub

 

[RainLover]

Happy to hear.

Yea.

Well I guess you won't forget this.

Good Luck with the balance of the project.

I still think that this

If IsNull(Me.CourseName) Or Me.CourseName = "" Then

Can be replaced with

If Len(Me.CourseName) = 0 Then

 

[Shinobi]

Believe me I tried, nothing happens if I use that.

Happy to hear.

Yea.

Well I guess you won't forget this.

Good Luck with the balance of the project.

I still think that this


Can be replaced with

 

[RainLover]

You are right.

My code does not suit this situation.