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

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

LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
 
Rey Obrero (Capricorn1)Connect With a Mentor Commented:
0
 
John TsioumprisSoftware & Systems EngineerCommented:
You could do your checking  and just exit sub if it meets the criteria...so the close action is canceled....
0
 
Rey Obrero (Capricorn1)Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
PatHartmanCommented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
PatHartmanConnect With a Mentor Commented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
Thank you!
0
 
PatHartmanCommented:
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
 
Dustin StanleyEntrepreneurAuthor Commented:
Good Deal. Thank you Pat!
0
All Courses

From novice to tech pro — start learning today.