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
41 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 2
9 Comments
 
LVL 37

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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 37

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

734 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