What is the right code on Form

tjm5508
tjm5508 used Ask the Experts™
on
I have a form that I want a msg box to pop up before the user adds the record to the table.  If they are satisfied, the can hit "OK" and the record will be written to the table.  If not, they can select "Cancel" and they will see the form with their data and be able to modifiy it as necessary.  

I found this code on this site and modified the msg box to say what I want it to say.  Everything works ok except for the "Cancel" Button.  

here is the code I am usinig in the BeforeUpdate event:

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If (MsgBox("Please check to make sure you have entered data in each of the boxes", vbOKCancel, "DATA VERIFICATION") = vbCancel) Then
        Cancel = True
        AccSubCategory.Undo
    End If
End Sub

When I hit "Cancel"  I get a debug error which points me to the "AccSubCategory" line.  I know nothing about coding, but know I need to replace "AccSubCategory" with are reference to my form?   I have tried everything I can think of and nothing works.  Any help on what I need to replace "AccSubCategory" with would be greatly appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
you can remove that line  "AccSubCategory.Undo" and proceed to editing the data

Top Expert 2016

Commented:
if you want to undo everything you did for the current record, replace "AccSubCategory.Undo" with  Me.Undo  
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Actually, the Undo method will undo any change to the control or object to which it is linked, so based on your comment, I think you should just delete that row from your code.

If you actually want to undo a control or forms changes I would normally refer to it as:

me.undo

or

me.controlName.undo

Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hello all...  thanks for your replies.  I tried the me.undo and the me.controlneam.undo, but they both returned compile errors... ?   I also tried just deleting the undo line altogether.  When I remove the undo line, the OK button still works fine.  When I now select "Cancel" it takes me back to the input form with the data I typed still in the form, however it also give mes a "You Can't go to the specified record" error.  After saying "OK" to the error message I am left on the form and can then proceed as normal.  Is there a way to get rid of the "You can't go to the specified record?"  While I understand it, it will be a bit cumbersome for others using the DB to understand what is happening.  
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Tim,

How many controls do you have on this form?  How many are required?

Instead of just asking them to verify this message, why don't you check to make sure each of the required fields has a value, and if not, cancel the update and go to that control.  This way, you can be certain that they have filled in all of the required fields


Private Sub Form_BeforeUpdate(Cancel as Integer)

    Dim strMsg as string
    Dim ctrl as control

    if LEN(me.txt_Field1 & "") = 0 then
        strMsg = "Field1 is a required field!"
        set ctrl = me.txt_Field1
    Elseif me.cbo_Field2 = NULL then
        strMsg = "Field2 is a required field!"
        set ctrl = me.cbo_Field2
    end if

    if len(strMsg) > 0 then
        msgbox strMsg
        ctrl.setfocus
        Cancel = True
    End if

End Sub

Open in new window

Author

Commented:
So I gave that a try. Keep in mind I an a novice at this and not sure exactly what to do.  I relplaced the me.txt_Field1 with me.name (see below), however I get a compile error.  It says it is a mismatch error on the set ctrl = me.name.   I am also including a hollowed out copy of the DB for you to see.



Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim strMsg As String
    Dim ctrl As Control
 
    If Len(Me.Name & "") = 0 Then
        strMsg = "Name is a required field!"
        Set ctrl = Me.Name
    ElseIf Me.Area = Null Then
        strMsg = "Area is a required field!"
        Set ctrl = Me.Area
    End If
 
    If Len(strMsg) > 0 Then
        MsgBox strMsg
        ctrl.SetFocus
        Cancel = True
    End If
WAR.mdb
Top Expert 2016

Commented:
using  "me.name"  will give the name of the form.. is this what you want?

"Name" is a reserved word and usage as name of control/fields must be avoided

if you are referring to a control with name  "Name", enclosed it with [ ]


Private Sub Form_BeforeUpdate(Cancel As Integer)

    Dim strMsg As String
    Dim ctrl As Control
 
    If Len(Me.[Name] & "") = 0 Then
        strMsg = "Name is a required field!"
        Set ctrl = Me.[Name]
    ElseIf Me.Area = Null Then
        strMsg = "Area is a required field!"
        Set ctrl = Me.Area
    End If
 
    If Len(strMsg) > 0 Then
        MsgBox strMsg
        ctrl.SetFocus
        Cancel = True
    End If

Author

Commented:
When I tried the code with the [Name] it still gives me a compile error.  If I change name to the next field on the form... .  i.e. first checked field is Area, 2nd is SOW... it will say AREA is null and is required, but if SOW is null, it does not prompt.... so once there is data in AREA it does not matter about SOW it allows the record to be saved regardles.  Also, after it says that the Area field is required and you say ok, I still get another messasge box that says "You can not go to the specified record"
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Tim,

Let me make a suggestion.  You need to adopt a naming convention for the controls on your forms and reports.  By default, Access will name your bound controls with the name of the field they are bound to.  This can be confusing when you are writing code that you want to refer to a control (textbox, listbox, combobox, checkbox) on your forms.  For that reason, most developers change the name of the controls on their forms so that they can identify the type of control just by looking at their code.

As an example, I have no idea whether your Area field is visible in a textbox, listbox, or combo box.  But if you added a prefix ("txt_", "lst_", or "cbo_") to the beginning of the name, you can now tell what type of control you are working with, and as you get more experienced, you will know what properties and events are associated with those controls.

Here is a link to one of the many naming conventions:

http://www.mvps.org/access/general/gen0012.htm

You need to add additional lines to the If/ElseIf/End syntax to address each of the controls you want to check.  Something like:

    If Len(Me.Area & "") = 0 Then
        strMsg = "Area is a required field!"
        Set ctrl = Me.Area
    Elseif Len(me.SOW & "") = 0 then
        strMsg = "SOW is a required field"
        set ctrl = me.SOW
    Elseif Len(me.ThirdControlName & "") = 0 then
        strMsg = "ThirdControlName is a required field"
        set ctrl = me.ThirdControlName
    End If

You may have noticed that I use the syntax

LEN(me.ControlName & "") = 0

to test whether there is anyting in the textbox, rather than:

 me.Area = NULL

This is because nothing is = NULL.  In VBA, you can test to see whether a controls value is NULL by using the IsNULL(me.Area) function, which will return a true/false value depending on the value of me.Area.  However, if you make an entry into a textbox, then delete it, the value is no longer NULL, but a zero length string.  And the IsNull( ) function will not return a True response if the field contains a zero length string.  So, the easiest way to check whether a controls value is either NULL or a zero length string is to use the LEN( ) function.  Unfortunately, the LEN( ) function requires a string value and will return an error if you pass it a NULL value.  To overcome this, you must concatenate an empty string ("") to the controls value to ensure you sent it a string.

HTH

Author

Commented:
Thanks!  I'll bookmark the site and start adding to my knowledge base.  Now that I understand that, and it works, is there anyway to to stop the second message box from popping up?  After I say ok to the message box that says the box can not be empty, a second box pops up that says I can not go to the specified record. This means if the users does not populate 3 fields they will get 6 message boxes... one for the empty field and one for can't go for each of the 3 fields.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Copy your entire proceedure and post it back.  Still not sure why you are getting the "cannot go the specified record" message.

Author

Commented:
Here is the code.  I get the box saying the field can not be empty and then as soon as I hit ok, i get the 2nd box that say I can not go to the specified record.  When I say ok to that, it takes me back to the form.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
    Dim ctrl As Control

    If Len(Me.Area & "") = 0 Then
        strMsg = "Area is a required field!"
        Set ctrl = Me.Area
    ElseIf Len(Me.SOW & "") = 0 Then
        strMsg = "SOW is a required field"
        Set ctrl = Me.SOW
    ElseIf Len(Me.xComments & "") = 0 Then
        strMsg = "Comments is a required field"
        Set ctrl = Me.xComments
    End If
   
        If Len(strMsg) > 0 Then
        MsgBox strMsg
        ctrl.SetFocus
        Cancel = True
    End If

End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim strMsg As String
    Dim ctrl As Control

    If Len(Me.Area & "") = 0 Then
        strMsg = "Area is a required field!"
        Set ctrl = Me.Area
    ElseIf Len(Me.SOW & "") = 0 Then
        strMsg = "SOW is a required field"
        Set ctrl = Me.SOW
    ElseIf Len(Me.xComments & "") = 0 Then
        strMsg = "Comments is a required field"
        Set ctrl = Me.xComments
    End If
    
        If Len(strMsg) > 0 Then
        MsgBox strMsg
        ctrl.SetFocus
        Cancel = True
    End If

End Sub

Open in new window

Author

Commented:
I think the reason I am getting the Can't Go to specified record is that I am already in a new record.... do we need something in the code to show that?
Top Expert 2016

Commented:
test this




Private Sub Form_BeforeUpdate(Cancel As Integer) 
        Dim strMsg As String, strCtl, sCtl
        Dim ctrl As Control
        strMsg = "The following fields are required..."
        If Len(Me.Area & "") = 0 Then
            strCtl = strCtl + Me.Area.Name & ","
            sCtl = sCtl + Me.Area.Name & vbCrLf
        End If
        If Len(Me.SOW & "") = 0 Then
            strCtl = strCtl + Me.SOW.Name & ","
            sCtl = sCtl + Me.SOW.Name & vbCrLf
        End If
        If Len(Me.xComments & "") = 0 Then
            strCtl = strCtl + Me.xComments.Name
            sCtl = sCtl + Me.xComments.Name
        End If
     
'        Cancel = True

        If Len(strCtl) > 0 Then
            MsgBox strMsg & vbCrLf & sCtl
            If InStr(strCtl, ",") > 0 Then
                Me(Split(strCtl, ",")(0)).SetFocus
                Else
                Me(strCtl).SetFocus
            End If
        End If
        Cancel = True
End Sub

Open in new window

Author

Commented:
Wow!  Putting the check of all three areas in one box was cool... .however it still generates a "You can't go to the sepecified record" error and when all the fields have data, it gives the error and won't let me move to another record...
Top Expert 2016

Commented:
which button are you clicking?
Top Expert 2016

Commented:
test this



WAR.mdb

Author

Commented:
When the user fills out the form, there are two command buttons the user can click.  One button allows a user to add a new record and the other exits the DB.  I have attached the stripped out DB with the code in it so you can see.
xx.mdb
Top Expert 2016

Commented:
cross posting, try the db i uploaded.

Author

Commented:
OK... so that works really well!  However, when you moved the code from Before Update and moved it into the On Load... it works, but the disable mousewheel code no longer works.  When I tried moving the code to the Before Update it does not work, but the disable mousewheel works again.....  so close to having everything work.... but just not quite there.
Top Expert 2016
Commented:
i disable the mousewheel, just enable it.
why do you want the codes in the beforeupdate event?
and why will you place it on the on load event?

leave the codes under the function DataCompleted, that will be your validation.

Author

Commented:
Outstanding!  thanks!  This works great.  Took me a minute to figure out how to re - disable the mouse.  I was not hung up on where to place the code... but when i looked in your copy of the DB it showed your code in the on load but the first line still reads Before load..... in any case it is all good now!  Thanks so much, I really appreciate all the help and understanding.

Author

Commented:
Outstanding solution.  Thanks for taking the extra time needed for this newbie to get it......  great job!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial