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
27 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 34

Accepted Solution

by:
PatHartman earned 500 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
 

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
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 
LVL 34

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

747 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now