Results 1 to 8 of 8
  1. #1
    Tim777 is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2012
    Posts
    18

    Simple Error Handler

    Ok, I suck at error handling. This should be very simple.



    I am working with 2 forms frmRepairs and frmCalendar. When a user has the calendar open he/she can select a repair on the calendar and then click on a command button (cmdEditRepair) which opens the selected record in the repair form. All this works fine.

    The error occurs when the user clicks on cmdEditRepair when no repair record is selected. A invalid use of Null error occurs.
    I tried writing an "On Error Goto" routine but I couldn't get it to work. I simply want a message box to pop up and force them to select a repair.
    I have the message box working but of course the error still occurs.

    I would really appreciate any help!


    Private Sub cmdEditRepair_Click()
    Dim rs As Object
    Dim lngBookmark As Long
    Dim Record As String
    Dim Cancel As Integer

    'test to see if a record is selected
    If IsNull(Me.lstEvents) Then MsgBox "Please select a repair to edit", vbInformation _

    'set a variable to the current record
    lngBookmark = Me.lstEvents

    'open the repair form and close the calendar
    DoCmd.OpenForm "frmRepairs"
    DoCmd.Close acForm, "frmCalendar"

    'take it to the selected record
    Set rs = Forms!frmRepairs.RecordsetClone
    rs.FindFirst "RepairNumb = " & lngBookmark

    Set rs = Nothing

    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Private Sub cmdEditRepair_Click()

    ...

    If IsNull(Me.lstEvents) Then

    MsgBox "Please select a repair to edit", vbInformation

    Else

    ...

    End If

    End Sub
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Tim777 is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2012
    Posts
    18
    Ok, the error handling seems to work fine now but my code no longer works. When you select a record to edit it opens the form but goes to the first record instead of the record selected. What did I screw up?

    Private Sub cmdEditRepair_Click()
    Dim rs As Object
    Dim lngBookmark As Long
    Dim Record As String
    Dim Cancel As Integer

    'test to see if a record is selected
    If IsNull(Me.lstEvents) Then
    MsgBox "Please select a repair to edit", vbInformation
    Else
    lngBookmark = Me.lstEvents 'set a variable to the current record
    DoCmd.OpenForm "frmRepairs" 'open the new form
    DoCmd.Close acForm, "frmCalendar" 'close the current form
    Set rs = Forms!frmRepairs.RecordsetClone
    rs.FindFirst "RepairNumb = " & lngBookmark 'take it to the selected record (THIS NO LONGER WORKS)
    End If

    'Set rs = Nothing (DO I NEED THIS LINE?)

    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
    Code:
    Private Sub cmdEditRepair_Click()
       Dim rs As DAO.Recordset
       Dim lngBookmark As Long
       Dim Record As String    '<< not needed
       'Dim Cancel As Integer   '<< not needed
    
       'test to see if a record is selected
       If IsNull(Me.lstEvents) Then
          MsgBox "Please select a repair to edit", vbInformation
          Exit Sub
       End If
    
       'set a variable to the current record
       lngBookmark = Me.lstEvents
    
       'open the repair form and close the calendar
       DoCmd.OpenForm "frmRepairs"
       DoCmd.Close acForm, "frmCalendar"
    
       'take it to the selected record
       Set rs = Forms!frmRepairs.RecordsetClone
       rs.FindFirst "RepairNumb = " & lngBookmark
    
       Set rs = Nothing
    
    End Sub
    The things I added/changed are in blue.

    The single line If() function is usually not considered good programing. It was used back when memory was very expensive and bytes were counted.


    Here is another way to write your sub:
    Code:
    Private Sub cmdEditRepair_Click()
    
    'test to see if a record is selected
       If IsNull(Me.lstEvents) Then
          MsgBox "Please select a repair to edit", vbInformation
          Exit Sub
       End If
    
       'open the repair form and close the calendar
       DoCmd.OpenForm "frmRepairs", , , "RepairNumb = " & Me.lstEvents
       DoCmd.Close acForm, "frmCalendar"
    
    End Sub
    Last edited by ssanfu; 11-16-2012 at 04:29 PM. Reason: spelling

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    My solution is not to find record after opening form but to open filtered to the desired record:

    DoCmd.OpenForm "frmRepairs", , , "RepairNumb=" & Me.lstEvents
    DoCmd.Close acForm "frmCalendar"

    Then none of the recordset code is needed.

    Assume RepairNumb is number type.

    The Set rs = Nothing is not required because the recordset variable will clear when the procedure ends, although many programmers like to explicitely clear these type of variables. However, it should be before the End If because it is initially Set within the Else.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    Tim777 is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2012
    Posts
    18
    I went with ssnafu's second example and it works great although I don't really understand what I did wrong. June7's example makes sense to me however.
    I understand logic but syntax and structure are still elusive to me.

    Thank you guys so much for your help!!! Maybe one day I be proficient enough to help others. Right now, I know enough VBA to be dangerous.

  7. #7
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Not really much difference between the two versions.

    Mine uses Else branching structure and therefore doesn't need the Exit Sub line because it doesn't matter which code happens, the sub will end after the code is accomplished as there is no additional code after the End If.

    Basically:

    If condition is met Then

    do this

    Else

    do this

    End If
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Tim777

    There is a free utility that you should obtain and use. It will add error handling to any procedure on a button click.
    It has several other features that you will use a lot if you create many programs(vba).

    http://www.mztools.com/v3/download.aspx

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

Similar Threads

  1. Help With Simple Error on Dlookup and Insert To
    By asdaf3814 in forum Programming
    Replies: 9
    Last Post: 08-09-2012, 10:50 AM
  2. Global Error Handler Issue
    By usmcgrunt in forum Programming
    Replies: 1
    Last Post: 11-02-2011, 08:26 PM
  3. Silly error on a simple function call - help
    By allenjasonbrown@gmail.com in forum Programming
    Replies: 5
    Last Post: 06-10-2011, 01:23 PM
  4. Replies: 0
    Last Post: 10-21-2010, 08:24 AM
  5. (simple) Expressions give error message
    By P.Hofman in forum Forms
    Replies: 3
    Last Post: 01-21-2010, 01:57 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