?
Solved

MS Access When Filter Removed Error occurs. Where to place the Error handler and what type of Error Handler?

Posted on 2016-10-26
9
Medium Priority
?
54 Views
Last Modified: 2016-10-26
On my form when I remove the filter or click the next record button I made (Which Removes the filter) Access errors and highlights in the forms OnCurrent

ImagePath = GetProductImageFilePath & Forms!frmSkusEntry!sbfrmProductImages.Form!ProductImageFileNm

Open in new window


you entered an expression that has an invalid reference to the property form/report

How can I fix this and where to place the Error Handler in the codes?

Option Compare Database
Option Explicit
Dim Operator As Integer
Dim varFilterID As Variant

Private Sub btnMPNDoesNotApply_Click()
SkuMPN.SetFocus
SkuMPN.Text = "Does Not Apply"
End Sub

Private Sub btnNextImage_Click()
     Me.sbfrmProductImages.SetFocus
        If Not Me.Recordset.EOF Then
      RunCommand acCmdRecordsGoToNext
      
      Else
 Exit Sub
        
Imageerr:
    MsgBox "At The End", vbInformation, "Company Says"
    Me!sbfrmProductImages.Form!ProductImageLocalPath.SetFocus 'goto first record
    RunCommand acCmdRecordsGoToFirst
End If

End Sub

Private Sub btnNextRecordSKU_Click()
 If Me.FilterOn = True Then
   DoCmd.ShowAllRecords
 Else
   Me.Recordset.MoveNext
 If Recordset.EOF Then
   MsgBox "At The End", vbInformation, "Company Says"
   Recordset.MoveFirst
 End If
End If
End Sub

Private Sub btnPreviousImage_Click()
     Me.sbfrmProductImages.SetFocus
        On Error GoTo Imageerr
        If Not Me.Recordset.BOF Then
      RunCommand acCmdRecordsGoToPrevious
      Else
 Exit Sub
        
Imageerr:
    MsgBox "At The Beginning", vbInformation, "Company Says"
    Me!sbfrmProductImages.Form!ProductImageLocalPath.SetFocus 'goto Last record
    RunCommand acCmdRecordsGoToLast
End If

End Sub

Private Sub btnPreviousRecordSKU_Click()
  If Me.FilterOn = True Then
    DoCmd.ShowAllRecords
 Else
   Me.Recordset.MovePrevious
  If Recordset.BOF Then
     MsgBox "At The Beginning", vbInformation, "Company Says"
   Recordset.MoveFirst
  End If
 End If
End Sub

Private Sub btnUPCDoesNotApply_Click()
UPC.SetFocus
UPC.Text = "Does Not Apply"
End Sub
Private Sub btnAddImage_Click()
    Forms!frmSkusEntry!sbfrmProductImages.Form.AddLocalImagePath
End Sub



Private Sub Form_BeforeInsert(Cancel As Integer)
Operator = "11"
Forms!frmSkusEntry!SKU = UniqueString(Operator)
End Sub

Private Sub Form_ApplyFilter(Cancel As Integer, ApplyType As Integer)

    ' Upon Applying the filter, save the ID of the current selection

    varFilterID = Me.SkuID

End Sub

Private Sub Form_Current()
    Dim rs As DAO.Recordset
    Dim strCriteria As String
    Set rs = Me.RecordsetClone
    
    Call SetFormAllowAdditions(Me!sbfrmProducts.Form, 4)
    
    
    


    
    ' If the filter is OFF, and we have a stored ID from the filter setting,

    ' use standard bookmark code to return to the record selected for the filter.

    If Me.FilterOn = False Then

        If Nz(varFilterID) <> "" Then

            strCriteria = "SkuID = " & varFilterID

            rs.FindFirst strCriteria

            If rs.NoMatch = False Then Me.Bookmark = rs.Bookmark

            ' Reset the stored filterID so that the code does not keep forcing this

            ' selection as the user navigates through the records.

            varFilterID = Null

        End If

    End If

    Set rs = Nothing
    
    Dim ImagePath As String
    
   
    ImagePath = GetProductImageFilePath & Forms!frmSkusEntry!sbfrmProductImages.Form!ProductImageFileNm

    If Len(Forms!frmSkusEntry!sbfrmProductImages.Form!ProductImageFileNm) > 0 And Len(Dir(ImagePath)) > 0 Then
        Image126.Picture = ImagePath
    Else
        Image126.Picture = ""
    End If
End Sub

Public Function GetProductImageFilePath() As String
    GetProductImageFilePath = GetDBPath & "images\"
End Function


    Public Function GetDBPath() As String
    GetDBPath = Replace(CurrentDb.TableDefs("Assemblies").Connect, ";DATABASE=", "")
'Remove db name
GetDBPath = Left(GetDBPath, InStrRev(GetDBPath, "\"))

End Function

Private Sub SkuNm_Change()
      If Len(SkuNm.Text) > 75 Then
   SkuNm.BackColor = vbYellow
Else
   SkuNm.BackColor = vbWhite
 End If
End Sub


Function UniqueString(ByVal parmLen) As String
    Const cAlphabet = "ABCDEFGHJKMNPQRSTUVWXYZ0123456789"
    Const AlphabetLen = 33
    Dim lngLoop As Long
    Dim lngOffset As Long
    Dim lngPosn As Long
    Dim GUID As String
    Dim GUID_Trailer As String
    
    Do
        GUID = GUID & Replace(Mid(CreateObject("scriptlet.typelib").GUID, 2, 36), "-", vbNullString)
    Loop Until Len(GUID) >= parmLen
    
    UniqueString = String(parmLen, "*")     'initialize output string
    Select Case Len(GUID)
        Case parmLen
            'convert entire GUID to string
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = "&h" & Mid(GUID, lngLoop, 2)
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
            Next
            
        Case Is > parmLen
            'use remaining byte values as increment, mod 256
            GUID_Trailer = Mid(GUID, (parmLen * 2) + 1)
            Do
                GUID_Trailer = GUID_Trailer & GUID_Trailer
            Loop Until Len(GUID_Trailer) >= ((parmLen * 2) + 6)
            
            GUID = Left(GUID, (parmLen * 2))
            
            lngPosn = 1
            For lngLoop = 1 To Len(GUID) Step 2
                lngOffset = CLng("&h" & Mid(GUID, lngLoop, 2)) + CLng("&h" & Mid(GUID_Trailer, lngPosn, 6))
                Mid(UniqueString, lngPosn, 1) = Mid(cAlphabet, (lngOffset Mod AlphabetLen) + 1, 1)
                lngPosn = lngPosn + 1
            Next
        
    End Select
End Function

Open in new window

0
Comment
Question by:Dustin Stanley
  • 7
  • 2
9 Comments
 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 total points
ID: 41861339
The referenced form must be open.  Is it?

A generic error handler -

First line of any proceedure =
OnError Go To ErrProc

After existing code in procedure:
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case Else
            Msgbox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
0
 

Author Comment

by:Dustin Stanley
ID: 41861348
The Form is opened. I click the filter button at the bottom or my Next record button I made and it errors.

It always highlights:
ImagePath = GetProductImageFilePath & Forms!frmSkusEntry!sbfrmProductImages.Form!ProductImageFileNm

Open in new window


which that code is for my images and combining the file name and database path together to display a photo.
0
 

Author Comment

by:Dustin Stanley
ID: 41861354
The filter is applied when I come in off a list form where I click on a list item and it matches SkuID to SkuID. I don't know if that helps any.
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:Dustin Stanley
ID: 41861390
Ok so this pops up the error but everything seems alright with this code but is there a way to just say let it error ignore it and let all this automatically go on in the background without the user knowing anything?


    On Error GoTo ErrProc
    Dim ImagePath As String
    
   
    ImagePath = GetProductImageFilePath & Forms!frmSkusEntry!sbfrmProductImages.Form!ProductImageFileNm

    If Len(Forms!frmSkusEntry!sbfrmProductImages.Form!ProductImageFileNm) > 0 And Len(Dir(ImagePath)) > 0 Then
        Image126.Picture = ImagePath
    Else
        Image126.Picture = ""
    End If
    
ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case Else
            MsgBox Err.Number & "--" & Err.Description
            Resume ExitProc
    End Select
End Sub

Open in new window

0
 
LVL 40

Expert Comment

by:PatHartman
ID: 41861391
I almost never use filters since my BE's are usually SQL Server.  I use queries with selection criteria that refers to selection fields on a form.  That way rather than retrieving ALL the rows and filtering locally, the server filters for me and only returns the requested rows.  It is much more efficient.

Did you try adding error handling?

When the error is irrelevant, you can simply ignore it if you add an error handler.  Just add additional cases to the select case.  Use Resume Next if you just want to ignore the error and continue.
0
 

Author Comment

by:Dustin Stanley
ID: 41861404
Yeah I tried the error handler. Please view the code above. Where would I place the On Error Resume Next in the code above. I know there are better options out there but as I am learning I get what I can. As they say beggars can't be choosers! I am trying to replace a horrible software we were using and never even knew what a database truly was until about 3 months ago. I just want to get his one right (As in Functional) and later upgrade and add to it. This DB is already over 1000 times better then our last one.
0
 

Author Comment

by:Dustin Stanley
ID: 41861411
I tried the On error Resume Next and it didn't work right but this worked great.

ExitProc:
    Exit Sub
ErrProc:
    Select Case Err.Number
        Case Else
            Resume ExitProc
    End Select
End Sub

Open in new window


The only thing is. When I press the buttons next or previous (The ones I made) It clears fine but never moves to the next or previous. and both buttons come down to this error as it is on the OnCurrent for the main form. So I don't know how to loop back to the next or previous action.
0
 

Author Comment

by:Dustin Stanley
ID: 41861484
I got it! I was using two different codes to remove a filter and stay on the same record. One in the OnCurrent event and one in the button. The Oncurrent was masking the button code in the cycle and messing with the bookmarking. So I removed the on Current event one and now works perfect!
0
 

Author Closing Comment

by:Dustin Stanley
ID: 41861485
THANKS PAT!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses

850 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