Solved

MS Access how to check if two fields IsNull and Code Layout VbYesNo MsgBox

Posted on 2016-11-04
10
63 Views
Last Modified: 2016-11-06
Ok I have a form that I will use to add Serial Numbers to a table called "ProdSerNmbrs". The form is called "sbfrmProdSerNmbrs" I have two fields
ProductID
SerialNumber

 I need to check and see if either IsNull. This all happens OnClick of a Close button. If one or either IsNull I need it to Pop up a MsgBox VbYesNo. If the User Picks No Then go back to the form.  If the user picks yes then Me.Undo and then close the form.

I can't figure this out because I have to many Else's.  Here is just something I have been working with but can't get it to come out right. It may be even more messed up after trying over and over. thanks for the help.

On Error GoTo Command13_Click_Err

    'Call the macro to check for empty records
    If IsNull(Me.SerialNumber) = True Then
      GoTo DirtyCheck
     Else
   'Forms!frmSkusEntry!SerialNumber.Requery
        GoTo Command13_Click_Exit
     End If
DirtyCheck:
If Me.Dirty = True Then
        msgbox "Not All Fields Were Completely Filled Out! Do you want to Still Exit Without Saving?", vbYesNo
        If vbNo Then
      Exit Sub
        Else
        Me.Undo
      Else
    If Me.Dirty = False Then
    GoTo Command13_Click_Exit
End If
End If
Command13_Click_Exit:
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSavePrompt
    Exit Sub

Command13_Click_Err:
    msgbox Error$
    Resume Command13_Click_Exit

End Sub

Open in new window

0
Comment
Question by:Dustin Stanley
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41874673
You could do your checking  and just exit sub if it meets the criteria...so the close action is canceled....
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41874725
it is best doing the checking in the beforeupdate event of the form

see this similar thread

https://www.experts-exchange.com/questions/28734660/how-to-use-the-BeforeUpdate-event-of-a-bound-form-in-ACCESS-2010.html#a41017235
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 41874743
I believe we've had this discussion in your other threads.

Validation of this nature belongs in the Form's BeforeUpdate event NOT in the click event of a button which the user might not even click.

The Form's BeforeUpdate event ALWAYS runs before a record is saved.  It is the LAST event that runs prior to the save.  If you discover a validation error, you set the Cancel argument to true, display a message and exit the procedure.  If you want to give the user a way to bail completely, you can do that but that logic path has to also undo any pending updates.

Cancel = True  '--- cancels the save
Me.Undo         '--- backs out ALL pending changes to the form and allows the user to exit gracefully without applying changes.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:Dustin Stanley
ID: 41874971
Ok this is the code I have setup now and it works if the user is trying to add a new record. But if they click the Close button it pops up the MsgBox of whichever IsNull and then immedietly closes and the user can't correct the issue.

What I need on this form is for the user to be able to do:

Add Record
Not Delete Them
Non Editable Once Saved

This is a continous Form.

I believe we've had this discussion in your other threads.
Yes I honestly spaced it but will keep it noted for now on. If I ask another question in the future that relates just say
"HEY IDIOT I TOLD YOU!" Thanks for the help!

user a way to bail completely
Yes I would love to have that! I just can't figure the right sequence of code out.



Private Sub btnClosefrm_Click()
'------------------------------------------------------------
' CONTROL FOR CLOSE BUTTON
'------------------------------------------------------------
On Error GoTo Command13_Click_Err

 GoTo Command13_Click_Exit
Command13_Click_Exit:
DoCmd.Close ObjectType:=acForm, ObjectName:=Me.Name, Save:=acSavePrompt
    Exit Sub

Command13_Click_Err:
    msgbox Error$
    Resume Command13_Click_Exit

End Sub


Private Sub Form_BeforeUpdate(Cancel As Integer)
     If IsNull(Me.ProductID) = True Then
       msgbox "Not All Fields Were Completely Filled Out! ProdConSku Is Blank?"
      SerialNumber.SetFocus
      Cancel = True
End If
     If IsNull(Me.SerialNumber) = True Then
       msgbox "Not All Fields Were Completely Filled Out! Serial Number Is Blank?"
      ProductID.SetFocus
      Cancel = True
 End If
End Sub

Open in new window

0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 250 total points
ID: 41874979
0
 
LVL 35

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
ID: 41874982
The problem is caused because of an error (?) in the way the close event works when a pending update fails.  Here are code snippits from the three events you need to use to handle the problem.

In the BeforeUpdate event, you set the variable (I suggest using a hidden field on the form for reasons that are a little complicated to explain) to False because you don't yet know if the update will be successful
In the AfterUpdate event, the record has been successfully saved so you can set the variable to True to allow the form to close.
In the Unload event (you can't cancel the close event) you test the variable and give the user the option of discarding the changes or trying to fix them.

Private Sub Form_BeforeUpdate(Cancel As Integer)
   On Error GoTo Err_Proc    
   Forms!frmLogin!chkAllowClose = False
....
End Sub

Private Sub Form_AfterUpdate()
    Forms!frmLogin!chkAllowClose = True
......
End Sub

Private Sub Form_Unload(Cancel As Integer)
   On Error GoTo Err_Proc

    If Forms!frmLogin!chkAllowClose = True Then
        Exit Sub
    Else
        If MsgBox("An error has been detected that is preventing the changed record from being saved.  Press Yes to return to fix it.  Press No to cancel the update and exit without saving.", vbYesNo) = vbYes Then
            Cancel = True
            Exit Sub
        Else
            Me.Undo
            MsgBox "Update has been cancelled.", vbOKOnly
            Exit Sub
        End If
    End If
Exit_Proc:
   On Error GoTo 0
   Exit Sub

Err_Proc:

    Select Case Err.Number
        Case Else
            MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Form_Unload of VBA Document Form_frmClients"
    End Select
End Sub

Open in new window

0
 

Author Comment

by:Dustin Stanley
ID: 41875724
Ok I have most fo my code finished and it is working 99%. Thank you for helping.  The last 1% is when you fill out a record row and you have not clicked on another record row. The record is still not saved. The pencil is still there. There for my variable has not changed back to True. How can I change my invisible variable back to true while it is still unsaved. Or make it go through the beforeupdate procedure again. This is for my Close button to function properly.
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41875788
Thank you!
0
 
LVL 35

Expert Comment

by:PatHartman
ID: 41876093
Your close button has to check if the form is dirty and if it is, save the record using:

Docmd.RunCommand acCmdSaveRecord


or

Me.Dirty = False

I always recommend that if you use the Me.Dirty hack that you add a comment that it saves the record because logically, it looks like it is undoing the update.
0
 

Author Comment

by:Dustin Stanley
ID: 41876222
Good Deal. Thank you Pat!
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

806 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