Results 1 to 8 of 8
  1. #1
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24

    VBA Code being completely ignored!

    I have written a couple of 'if' statements on a 'submit' button, but when i click the button most of the code seems to be ignored.

    I wrote it with a single IF statement, and it worked perfectly. I added some more statements and everything else is being ignored.
    I've closed the form and reopened it. I've closed access and reopened it. I've selected the specific button and checked its even procedure. Everything looks right, i dont understand why it's ignoring code.

    I've tried 3 different methods to get it to prompt for specific information.

    The only msgbox that pops up is the one referencing the Booking Date.




    Method 1:

    All fields set to 'Required: No"

    Code:
    '------------------------------------------------------------
    ' btnSubmit_Click
    '
    '------------------------------------------------------------
    Private Sub btnSubmit_Click()
    On Error GoTo btnSubmit_Click_Err
    
    
        ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
        ' <UserInterfaceMacro For="btnExit" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="CloseWindow"/></Statements></UserInterfaceMacro>
        On Error Resume Next
    
        If IsNull(Me.cboAccountName) Or Me.cboAccountName = "" Then
            MsgBox "Please select an account.", vbOKOnly, "Required Information"
                Me.cboAccountName.SetFocus
            Exit Sub
        End If
        
        If IsNull(Me.cboCustomerName) Or Me.cboCustomerName = "" Then
            MsgBox "Please select a customer.", vbOKOnly, "Required Information"
                Me.cboCustomerName.SetFocus
            Exit Sub
        End If
        
        If IsNull(Me.cboVehicle) Or Me.cboVehicle = "" Then
            MsgBox "Please select a Vehicle.", vbOKOnly, "Required Information"
                Me.cboVehicle.SetFocus
            Exit Sub
        End If
        
        If IsNull(Me.stBookingDate) Or Me.stBookingDate = "" Then
            MsgBox "You need to insert a booking date!", vbOKOnly, "Required Information"
                Me.stBookingDate.SetFocus
                Exit Sub
    
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close acForm, "AddBooking", acSaveNoc
        If (MacroError <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
        End If
    
    
    
    
    btnSubmit_Click_Exit:
        Exit Sub
    
    
    btnSubmit_Click_Err:
        MsgBox Error$
        Resume btnSubmit_Click_Exit
    
    
    End Sub

    Method 2:

    All fields set to 'Required: No'

    Code:
    Private Sub btnSubmit_Click()On Error GoTo btnSubmit_Click_Err
    
    
        ' _AXL:<?xml version="1.0" encoding="UTF-16" standalone="no"?>
        ' <UserInterfaceMacro For="btnExit" xmlns="http://schemas.microsoft.com/office/accessservices/2009/11/application"><Statements><Action Name="CloseWindow"/></Statements></UserInterfaceMacro>
        On Error Resume Next
        
        
        If IsNull(Me.cboAccountName) Or Me.cboAccountName = "" Then
            MsgBox "Please select an account.", vbOKOnly, "Required Information"
                Me.cboAccountName.SetFocus
            Exit Sub
            
            Else
                 If IsNull(Me.cboCustomerName) Or Me.cboCustomerName = "" Then
                    MsgBox "Please select a customer.", vbOKOnly, "Required Information"
                    Me.cboCustomerName.SetFocus
                    Exit Sub
                 
                 
                     Else
                     If IsNull(Me.cboVehicle) Or Me.cboVehicle = "" Then
                         MsgBox "Please select a Vehicle.", vbOKOnly, "Required Information"
                         Me.cboVehicle.SetFocus
                         Exit Sub
                         
                         Else
                            If IsNull(Me.cboVehicle) Or Me.cboVehicle = "" Then
                                MsgBox "Please select a Vehicle.", vbOKOnly, "Required Information"
                                Me.cboVehicle.SetFocus
                                Exit Sub
                                
                                Else
                                    If IsNull(Me.stBookingDate) Or Me.stBookingDate = "" Then
                                        MsgBox "You need to insert a booking date!", vbOKOnly, "Required Information"
                                        Me.stBookingDate.SetFocus
                                        Exit Sub
                                    End If
                            End If
                    End If
                 End If
        End If
    
    
        
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close acForm, "AddBooking", acSaveNoc
        If (MacroError <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
        End If
    
    
    
    
    btnSubmit_Click_Exit:
        Exit Sub
    
    
    btnSubmit_Click_Err:
        MsgBox Error$
        Resume btnSubmit_Click_Exit
    
    
    End Sub
    Method 3:

    Using the above code and setting the desired fields to be Required via the Table settings.

  2. #2
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,389
    Remove the lines 'On Error Resume Next' - that line ignores any following error and your error routine will never be entered.
    Make sure you have
    Code:
    Option Compare Database
    Option Explicit
    at the top of the form code module (and EVERY OTHER module)

    Then see what the results are.

  3. #3
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    I did as you suggested, but the result is still the same.

    I leave the form completely blank, press 'Submit' then get the message box 'You need to insert a booking date!' as expected.
    If i put a booking date in, then hit 'Submit' again, it just saves the record and closes the form.

    For personal reference, what is 'Option Explicit' achieving?

    The entire code for my form is below.

    Code:
    Option Compare DatabaseOption Explicit
    
    
    Private Sub btnClear_Click()
    Me.Undo
    End Sub
    
    
    Private Sub cboAccountName_AfterUpdate()
        Dim SCustomerSource As String
        Dim SVehicleSource As String
            
        SCustomerSource = "SELECT [Customers].[Customer Number]," & _
                        " [Customers].[First Name], " & _
                        " [Customers].[Last Name], " & _
                        " [Customers].[Mobile Phone], " & _
                        " [Customers].[Landline], " & _
                        " [Customers].[Fax], " & _
                        " [Customers].[Email Address] " & _
                            "From Customers " & _
                            "WHERE [Account Number] = " & Me.cboAccountName.Value
                            
        SVehicleSource = "SELECT [Vehicles].[Vehicle Number]," & _
                        " [Vehicles].[Vehicle Make], " & _
                        " [Vehicles].[Vehicle Model], " & _
                        " [Vehicles].[Rego], " & _
                        " [Vehicles].[Build Date], " & _
                        " [Vehicles].[VIN], " & _
                        " [Vehicles].[Required Repairs] " & _
                            "From Vehicles " & _
                            "WHERE [Account Number] = " & Me.cboAccountName.Value
                                                
    
    
        Me.cboCustomerName.RowSource = SCustomerSource
        Me.cboCustomerName.Requery
        Me.cboVehicle.RowSource = SVehicleSource
        Me.cboVehicle.Requery
    End Sub
    
    
    Private Sub cboCustomerName_AfterUpdate()
    Me.stFirstName = Me.cboCustomerName.Column(1)
    Me.stLastName = Me.cboCustomerName.Column(2)
    Me.stMobilephone = Me.cboCustomerName.Column(3)
    Me.stLandLine = Me.cboCustomerName.Column(4)
    Me.stFax = Me.cboCustomerName.Column(5)
    Me.stEmailAddress = Me.cboCustomerName.Column(6)
        End Sub
        
    Private Sub cboVehicle_AfterUpdate()
    Me.stVehicleMake = Me.cboVehicle.Column(1)
    Me.stVehicleModel = Me.cboVehicle.Column(2)
    Me.stRego = Me.cboVehicle.Column(3)
    Me.stBuildDate = Me.cboVehicle.Column(4)
    Me.stVIN = Me.cboVehicle.Column(5)
    Me.stRequiredRepairs = Me.cboVehicle.Column(6)
    End Sub
    '------------------------------------------------------------
    ' btnExit_Click
    '
    '------------------------------------------------------------
    Private Sub btnExit_Click()
    On Error GoTo btnExit_Click_Err
    
    
        DoCmd.Close , ""
    
    
    
    
    btnExit_Click_Exit:
        Exit Sub
    
    
    btnExit_Click_Err:
        MsgBox Error$
        Resume btnExit_Click_Exit
    
    
    End Sub
    
    
    
    
    '------------------------------------------------------------
    ' btnSubmit_Click
    '
    '------------------------------------------------------------
    Private Sub btnSubmit_Click()
    On Error GoTo btnSubmit_Click_Err
        
           If IsNull(Me.cboAccountName) Or Me.cboAccountName = "" Then
            MsgBox "Please select an account.", vbOKOnly, "Required Information"
                Me.cboAccountName.SetFocus
            Exit Sub
        End If
        
        If IsNull(Me.cboCustomerName) Or Me.cboCustomerName = "" Then
            MsgBox "Please select a customer.", vbOKOnly, "Required Information"
                Me.cboCustomerName.SetFocus
            Exit Sub
        End If
        
        If IsNull(Me.cboVehicle) Or Me.cboVehicle = "" Then
            MsgBox "Please select a Vehicle.", vbOKOnly, "Required Information"
                Me.cboVehicle.SetFocus
            Exit Sub
        End If
        
        If IsNull(Me.stBookingDate) Or Me.stBookingDate = "" Then
            MsgBox "You need to insert a booking date!", vbOKOnly, "Required Information"
                Me.stBookingDate.SetFocus
                Exit Sub
        End If
    
    
        
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close acForm, "AddBooking", acSaveNo
        If (MacroError <> 0) Then
            Beep
            MsgBox MacroError.Description, vbOKOnly, ""
        End If
    
    
    
    
    btnSubmit_Click_Exit:
        Exit Sub
    
    
    btnSubmit_Click_Err:
        MsgBox Error$
        Resume btnSubmit_Click_Exit
    
    
    End Sub

  4. #4
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    Wow.

    I figure out the problem - I'm so dumb.

    The 'Account Number','Customer Number' and 'Vehicle Number' fields had a Default Value of 0.
    It wasn't skipping the code at all, it just had a value so there was no error.

    I removed the Default Value and now it works perfectly lol.

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Also, if this
    Code:
    If IsNull(Me.cboAccountName) Or Me.cboAccountName = "" Then
    is NOT true, what do you think happens to all the validation code that comes after that and before the closing End If?

    I might have suggested this to you before, as some of the terms look familiar. If so, ignore the rest as you probably decided not to implement the suggestion.
    If you put Reqd as a tag property on all these controls you can write
    Code:
    Dim ctl As Control
    
    For each ctl in Me.Controls
     If ctl.Type = acCombo Or ctl.Type = acTextbox And ctl.Tag = "Reqd" Then
      If IsNullEmpty(ctl) Then 
       Msgbox "Please enter a value for " & ctl.Controls(0).Caption
       Exit Sub
      End If
    Next
    It's quite simple to build a list of all the fields that are missing required data in one execution of the code rather than one at a time as you are doing. In other words, if fld1 is blank, user gets message and fills it in, but fld2 is blank - repeat, repeat... whereas we can say
    Please fill in
    - name
    - address
    - phone
    - etc.
    all at once. I didn't include that as I'm not even sure you are interested in the above anyway. I just might be confusing you with some other poster.
    The above uses the attached label caption as the description of the field with the missing data. If it's not attached, you'd have to use the control name or stick with what you're doing, but "Please enter data for txtFName" probably would confuse users.

    You'd also need
    Code:
    Public Function IsNullEmpty (ctl As Control)
    IsNullEmpty = False
    If IsNull(ctl) Or ctl = "" Then IsNullEmpty = True
    End Function
    in a standard module so that you can call it from anywhere in the project to test any control for being Null or containing an empty string ("")
    Much shorter and simpler than 10 or 20 If's, no?
    Last edited by Micron; 12-20-2017 at 11:09 PM. Reason: forgot to add function
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  6. #6
    Zombai is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Dec 2017
    Posts
    24
    I don't think it was me that you mentioned this to.

    I'm not the best with VBA, i've only just really started to learn it, so my 'agricultural' way is the best way i knew how to do it.

    I can see how your method is beneficial, however i didn't need that many checks to be done, so i feel the effort to follow your method isn't as necessary.

    I will definitely keep it in mind if i ever do something similar.

    Thanks all for the help

  7. #7
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    We all have different thresholds for something like this. Quite understandably, one's comfort level surely has to be a factor. My limit would be 2 such If blocks since it's so easy for me. I have also found that 2 becomes 4 or 5 becomes 7 (etc) quite easily, then it becomes "do I continue on that path or start over?" Easier to continue no doubt, but the code becomes cumbersome to read.
    I'm lazy or find repetition tedious (probably both). I wouldn't even write
    Me.cboCustomerName over and over. It would be (assuming I wasn't already using this variable)
    Dim ctl As Control
    Set ctl = Me.cboCustomerName

    Me.stFirstName = ctl.Column(1)
    etc
    etc

    You might think that this is just a matter of typing, but it's not. Each iteration of your code requires Access to evaluate what comes after Me. when it is all the same thing. By defining the control as an object, it only has to figure out what is in column 1, whereas your way, it has to evaluate the object first - every time. I suppose with today's processing power, you won't notice any difference with small db's but large and complex evaluations can slow things down, especially if you're going over a large network of users. You should try to get in a frame of mind that looks for ways to avoid such repetition as you evolve, because it's a great driving force for learning new things.
    At least you should be able to easily make use of the function to check for Null or "". I've used it a lot.
    Good luck!
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  8. #8
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just a comment:

    You should not use spaces in object names. That means ALL object: field names, table names, form names, query names, report names.

    Only the developer should see "under the covers" (ie be in design view), so names can be shortened (as in FName) or use the underscore (Vehicle_Model) or camel back (VehicleModel).
    And spaces in names adds complexity. If you have spaces in object names, you need to enclose the name in brackets ([First Name]). Access treats spaces as a delimiter.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. I have lost the developer tab completely
    By ntambomvu in forum Access
    Replies: 5
    Last Post: 10-24-2017, 01:37 AM
  2. Please help - Completely Stuck!
    By topcat10 in forum Access
    Replies: 1
    Last Post: 07-18-2015, 09:28 AM
  3. Completely confused! Help please
    By AndyC121 in forum Access
    Replies: 3
    Last Post: 04-27-2015, 06:33 AM
  4. Database not completely normalized
    By cuddles in forum Access
    Replies: 5
    Last Post: 06-10-2014, 05:24 PM
  5. Completely new to Access
    By khlmbrg in forum Access
    Replies: 2
    Last Post: 09-22-2011, 08:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums