Results 1 to 7 of 7
  1. #1
    wardw is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    May 2013
    Location
    Hines, Oregon
    Posts
    41

    Can't debug a Sub

    I'm having a problem with a form, where I've written some VBA code to ensure a field is filled in when other fields on the form are filled in. The problem is that if all fields are filled in properly, my MsgBox error still appears. I've checked the Sub's code exhaustively and can't find the source of the problem.



    I'd like to see just where the problem occurs in the code, so I'm trying to use the VBA (Microsoft Visual Basic 6.5) debugger. But when I try to run the Sub from the code window, it just beeps or displays a Macro dialog box.

    How can I debug a Sub when the "error" it causes is not a VBA error, but just some problem with my coding?

  2. #2
    dccjr's Avatar
    dccjr is offline Competent Performer
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Location
    Colorado Springs, CO
    Posts
    138
    Post your code, please.

  3. #3
    wardw is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    May 2013
    Location
    Hines, Oregon
    Posts
    41
    Some background: The form is for entering obituary data. On one of the form's tabs, a combo box (FirstPublicationName) lists newspapers the obituary appears in. Other fields are for recording the column (FirstPublicationColumn) and page no. (FirstPublicationPage) where the obit appears. If the user specifies column and/or page no., but no newspaper, I want an error message to appear.

    I'm now also receiving another error message when I try to save a record:

    The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.

    Here's the Sub's code. It's triggered by a Before Update form property:

    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
    'This procedure is triggered either by clicking one of the navigation arrows in the
    'bottom of the frame (previous record, next record, last record, new record),
    'or by typing a record number and pressing Enter. Its actions are the same
    'as those when the Save button is clicked in any of the form's tabs (e.g.,
    'the SaveRecord1_Click procedure): First check for errors, and then
    'save the record and display a confirmation message.
    
    
    If LenB([Gender] & "") = 0 Or LenB([LastName] & "") = 0 _
        Then GoTo Err_Form_BeforeUpdate
                                                  'If Gender or LastName fields are blank, go to error message.
    '-------------------
    If Me.FirstPublicationDate < Me.DeathDate _
        Then GoTo Err_FirstPublicationDate_Change
                                                  'If FirstPublicationDate is earlier than DeathDate,
                                                  'go to error message.
    '-------------------
    If LenB([FirstPublicationName] & "") = 0 _
        And LenB([FirstPublicationNameNote] & "") = 0 _
        And LenB([FirstPublicationColumn] & "") > 0 _
        Or LenB([FirstPublicationPage] & "") > 0 _
        Then GoTo Err_FirstObitPageOrColumn_Change
                                                   'If FirstPublicationName and FirstPublicationNameNote
                                                   'fields are blank, and FirstPublicationColumn
                                                   'or FirstPublicationPage field has content,
                                                   'go to error message.
    
    If LenB([SecondPublicationName] & "") = 0 _
        And LenB([SecondPublicationNameNote] & "") = 0 _
        And LenB([SecondPublicationColumn] & "") > 0 _
        Or LenB([SecondPublicationPage] & "") > 0 _
        Then GoTo Err_SecondObitPageOrColumn_Change
                                                     'If SecondPublicationName and SecondPublicationNameNote
                                                     'fields are blank, and SecondPublicationColumn or
                                                     'SecondPublicationPage field has content,
                                                     'go to error message.
    '-------------------
    If LenB([ThirdPublicationName] & "") = 0 _
        And LenB([ThirdPublicationNameNote] & "") = 0 _
        And LenB([ThirdPublicationColumn] & "") > 0 _
        Or LenB([ThirdPublicationPage] & "") > 0 _
        Then GoTo Err_ThirdObitPageOrColumn_Change
                                                     'If ThirdPublicationName and ThirdPublicationNameNote
                                                     'fields are blank, and ThirdPublicationColumn or
                                                     'ThirdPublicationPage field has content,
                                                     'go to error message.
        Cancel = True
    
        DoCmd.RunCommand acCmdSaveRecord
    
    '----------------------
    'Error Messages for Form_BeforeUpdate(Cancel As Integer)
    '----------------------
    Exit_Form_BeforeUpdate:
        Exit Sub
    
    Err_Form_BeforeUpdate:
        MsgBox "On the Identity tab, please specify both Gender and Last Name."
        If LenB([Gender] & "") = 0 Then
        Gender.SetFocus
        ElseIf LenB([Gender] & "") > 0 Then
        LastName.SetFocus
        End If
        Cancel = True
    
    '----------------------
    Exit_FirstPublicationDate_Change:
        Exit Sub
    
    Err_FirstPublicationDate_Change:
        MsgBox "Date of Death (on Death Age/Date tab) must be earlier" & vbCrLf & _
        "or the same as First Obituary Date (on Obituary tab)."
        DeathDate.SetFocus
        Cancel = True
    '----------------------
    Exit_FirstObitPageOrColumn_Change:
        Exit Sub
    
    Err_FirstObitPageOrColumn_Change:
        MsgBox "On the Obituary tab, there must be a First Obituary Publication" & vbCrLf & _
        "or note before there is a First Obituary Column or First Obituary Page."
        FirstPublicationName.SetFocus
        Cancel = True
    '----------------------
    Exit_SecondObitPageOrColumn_Change:
        Exit Sub
    
    Err_SecondObitPageOrColumn_Change:
        MsgBox "On the Obituary tab, there must be a Second Obituary Publication" & vbCrLf & _
        "or note before there is a Second Obituary Column or Page."
        SecondPublicationName.SetFocus
        Cancel = True
    '----------------------
    Exit_ThirdObitPageOrColumn_Change:
        Exit Sub
    
    Err_ThirdObitPageOrColumn_Change:
        MsgBox "On the Obituary tab, there must be a Third Obituary Publication" & vbCrLf & _
        "or note before there is a Third Obituary Column or Page."
        ThirdPublicationName.SetFocus
        Cancel = True
    '----------------------
    End Sub

  4. #4
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    so I'm trying to use the VBA (Microsoft Visual Basic 6.5) debugger.
    With an event in a form module, you have to call the sub from that form. Set a break point, then make a change and navigate to a new/different record. The code should stop where you set the break point, where you can single step through the rest of the subroutine.

    BTW, nice commenting of the code. It helped.


    The code for "Gender", "LastName" and "FirstPublicationDate" I understand. The gender and last name fields should to be filled in and there shouldn't be an obit notice at least until a person has died.

    But the rest of the code doesn't match what the comments say. I had to re-write/format the code to see what was happening. (Using the "GOTO" construct is really bad programming practice. It led to spaghetti code. The only time I use it is for true error handler code.

    So your comment for "First Publication", I see as:
    Code:
          'If FirstPublicationName and FirstPublicationNameNote fields are blank, 
          ' and 
          'FirstPublicationColumn or FirstPublicationPage field has content,
    then give an error message.
    But your code is missing parentheses for grouping. Due to operator precedence, the "And" clauses are grouped (evaluated) together, then the "Or" clause.

    Based on your comment in the code, I would add parentheses like this: (I broke this into two lines for clarity)
    Code:
    If (LenB([FirstPublicationName] & "") = 0 And LenB([FirstPublicationNameNote] & "") = 0) 
         And
       (LenB([FirstPublicationColumn] & "") > 0 Or LenB([FirstPublicationPage] & "") > 0) Then

    You also had an extra "Cancel = True" line.
    The line "DoCmd.RunCommand acCmdSaveRecord" is unnecessary because you are already saving the record. The Before Update event executes, the record is saved, then the after update event executes.


    Here is the code (untested) I modified:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
       'This procedure is triggered either by clicking one of the navigation arrows in the
       'bottom of the frame (previous record, next record, last record, new record),
       'or by typing a record number and pressing Enter. Its actions are the same
       'as those when the Save button is clicked in any of the form's tabs (e.g.,
       'the SaveRecord1_Click procedure): First check for errors, and then
       'save the record and display a confirmation message.
    
    
       'check if Gender or LastName fields are blank
       If LenB([Gender] & "") = 0 Or LenB([LastName] & "") = 0 Then
          '      GoTo Err_Form_BeforeUpdate
          MsgBox "On the Identity tab, please specify both Gender and Last Name."
          If LenB([Gender] & "") = 0 Then
             Gender.SetFocus
          ElseIf LenB([Gender] & "") > 0 Then
             LastName.SetFocus
          End If
          Cancel = True
    
          'check if FirstPublicationDate is earlier than DeathDate
       ElseIf Me.FirstPublicationDate < Me.DeathDate Then
    
          MsgBox "Date of Death (on Death Age/Date tab) must be earlier" & vbCrLf & _
                 "or the same as First Obituary Date (on Obituary tab)."
          DeathDate.SetFocus
          Cancel = True
    
          'check if FirstPublicationName and FirstPublicationNameNote fields are blank, and FirstPublicationColumn
          'or FirstPublicationPage field have content,
       ElseIf (LenB([FirstPublicationName] & "") = 0 And LenB([FirstPublicationNameNote] & "") = 0) And (LenB([FirstPublicationColumn] & "") > 0 Or LenB([FirstPublicationPage] & "") > 0) Then
          MsgBox "On the Obituary tab, there must be a First Obituary Publication" & vbCrLf & _
                 "or note before there is a First Obituary Column or First Obituary Page."
          FirstPublicationName.SetFocus
          Cancel = True
    
          'check if SecondPublicationName and SecondPublicationNameNote fields are blank, and SecondPublicationColumn or
          'SecondPublicationPage field have content,
       ElseIf (LenB([SecondPublicationName] & "") = 0 And LenB([SecondPublicationNameNote] & "") = 0) And (LenB([SecondPublicationColumn] & "") > 0 Or LenB([SecondPublicationPage] & "") > 0) Then
          MsgBox "On the Obituary tab, there must be a Second Obituary Publication" & vbCrLf & _
                 "or note before there is a Second Obituary Column or Page."
          SecondPublicationName.SetFocus
          Cancel = True
    
          'check if ThirdPublicationName and ThirdPublicationNameNote fields are blank, and ThirdPublicationColumn or
          'ThirdPublicationPage field have content,
       ElseIf (LenB([ThirdPublicationName] & "") = 0 And LenB([ThirdPublicationNameNote] & "") = 0) And (LenB([ThirdPublicationColumn] & "") > 0 Or LenB([ThirdPublicationPage] & "") > 0) Then
          MsgBox "On the Obituary tab, there must be a Third Obituary Publication" & vbCrLf & _
                 "or note before there is a Third Obituary Column or Page."
          ThirdPublicationName.SetFocus
          Cancel = True
       End If
    
    End Sub

  5. #5
    wardw is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    May 2013
    Location
    Hines, Oregon
    Posts
    41
    Thank you for your detailed response, ssanfu; a lot of work! The commenting is mainly for my benefit: If I leave the code alone for a few weeks and come back to it, I'm lost without copious notes.

    I implemented the changes you suggested: The parens make perfect sense; without them, the code is totally ambiguous. And I incorporated the MsgBoxes into the code instead of using GoTo. I thought I'd save code lines by having them in a separate location, but the code is clearer this way.

    Here's the corrected code, followed by another whine from me:
    Code:
    Private Sub Form_BeforeUpdate(Cancel As Integer)
        'This procedure is triggered either by clicking one of the navigation arrows in the
        'bottom of the frame (previous record, next record, last record, new record),
        'or by typing a record number and pressing Enter. Its actions are the same
        'as those when the Save button is clicked in any of the form's tabs (e.g.,
        'the SaveRecord1_Click procedure): First check for errors, and then
        'save the record and display a confirmation message.
        
        
        'If Gender or LastName fields are blank, display error message.
        If LenB([Gender] & "") = 0 Or LenB([LastName] & "") = 0 Then
            MsgBox "On the Identity tab, please specify both Gender and Last Name."
        'Put the cursor on the appropriate tab and field
            If LenB([Gender] & "") = 0 Then
                Gender.SetFocus
            ElseIf LenB([Gender] & "") > 0 Then
                LastName.SetFocus
            End If
            Cancel = True
        
        'If FirstPublicationDate is earlier than DeathDate, display error message.
        ElseIf Me.FirstPublicationDate < Me.DeathDate Then
            MsgBox "Date of Death (on Death Age/Date tab) must be earlier" & vbCrLf & _
                   "or the same as First Obituary Date (on Obituary tab)."
            'Put the cursor on the appropriate tab and field
            DeathDate.SetFocus
            Cancel = True
        
        'If FirstPublicationName and FirstPublicationNameNote
        'fields are blank, and FirstPublicationColumn
        'or FirstPublicationPage field has content,
        'display error message.
        ElseIf (LenB([FirstPublicationName] & "") = 0 _
            And LenB([FirstPublicationNameNote] & "") = 0) _
            And (LenB([FirstPublicationColumn] & "") > 0 _
            Or LenB([FirstPublicationPage] & "") > 0) Then
            MsgBox "On the Obituary tab, there must be a First Obituary Publication" & vbCrLf & _
                   "or note before there is a First Obituary Column or First Obituary Page."
            'Put the cursor on the appropriate tab and field
            FirstPublicationName.SetFocus
            Cancel = True
                                                  
        'If SecondPublicationName and SecondPublicationNameNote
        'fields are blank, and the SecondPublicationColumn
        'or SecondPublicationPage field has content,
        'display error message.
        ElseIf (LenB([SecondPublicationName] & "") = 0 _
            And LenB([SecondPublicationNameNote] & "") = 0) _
            And (LenB([SecondPublicationColumn] & "") > 0 _
            Or LenB([SecondPublicationPage] & "") > 0) Then
            MsgBox "On the Obituary tab, there must be a Second Obituary Publication" & vbCrLf & _
                   "or note before there is a Second Obituary Column or Second Obituary Page."
            'Put the cursor on the appropriate tab and field
            SecondPublicationName.SetFocus
            Cancel = True
                    
        'If ThirdPublicationName and ThirdPublicationNameNote
        'fields are blank, and the ThirdPublicationColumn
        'or ThirdPublicationPage field has content,
        'display error message.
        If (LenB([ThirdPublicationName] & "") = 0 _
            And LenB([ThirdPublicationNameNote] & "") = 0) _
            And (LenB([ThirdPublicationColumn] & "") > 0 _
            Or LenB([ThirdPublicationPage] & "") > 0) _
            Then GoTo Err_ThirdObitPageOrColumn_Change
            MsgBox "On the Obituary tab, there must be a Third Obituary Publication" & vbCrLf & _
                   "or note before there is a Third Obituary Column or Third Obituary Page."
            'Put the cursor on the appropriate tab and field
            ThirdPublicationName.SetFocus
            Cancel = True
        End If
    End Sub
    I set a breakpoint in the code and then tried a new form entry, but when I tried to move to another record (or click Save, for that matter) I'm still getting that error I had before:
    The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.

    I do have other Subs in the code, pertaining to undoing all changes to the form and to closing a form without saving the changes. If those are causing the error, let me know and I'll send the portion of the code with those Subs.

  6. #6
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    The commenting is mainly for my benefit: If I leave the code alone for a few weeks and come back to it, I'm lost without copious notes.
    Yep. That is what comments are for. But it is closer to 5 days for me.

    I set a breakpoint in the code and then tried a new form entry, but when I tried to move to another record (or click Save, for that matter) I'm still getting that error I had before:
    The expression On Click you entered as the event property setting produced the following error: Member already exists in an object module from which this object module derives.
    Look in the for, module to see if there somehow is another procedure with the same name.

    When in the form module, do you have as the first two lines (of every module):
    Code:
    Option Compare Database
    Option Explicit

    In the IDE, have you compiled the code? (DEBUG menu /Compile)



    Or, if you can, delete/change any sensitive data, compact and repair, zip it and post the dB. I'll take a look at it.

  7. #7
    wardw is offline Advanced Beginner
    Windows 7 64bit Access 2000
    Join Date
    May 2013
    Location
    Hines, Oregon
    Posts
    41
    Thanks, Steve; I'd never used Debug > Compile <DatabaseName> before. Now I'm using it all the time and find it very useful. So the "Member already exists in an object module" error doesn't appear now, after I fixed some of the code. I now have another issue, which I'll start a new thread about.

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

Similar Threads

  1. disable debug option
    By mrbabji in forum Access
    Replies: 1
    Last Post: 05-12-2013, 12:21 AM
  2. Form debug error
    By LOUM in forum Forms
    Replies: 5
    Last Post: 06-11-2012, 11:21 AM
  3. TempVars monitoring for debug
    By chris.williams in forum Programming
    Replies: 2
    Last Post: 11-18-2011, 03:27 PM
  4. Debug
    By gio25 in forum Programming
    Replies: 1
    Last Post: 02-08-2011, 08:30 AM
  5. Is There a Way to Debug or Step Through VB code?
    By jeffbase34 in forum Programming
    Replies: 1
    Last Post: 05-28-2009, 08:14 PM

Tags for this Thread

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