Solved

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

Posted on 2016-11-04
10
40 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 119

Expert Comment

by:Rey Obrero
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 34

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
 

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 119

Accepted Solution

by:
Rey Obrero earned 250 total points
ID: 41874979
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 34

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 34

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

708 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now