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
Solved

Error Handling

Posted on 2006-11-24
5
275 Views
Last Modified: 2008-02-01
I am filtering data on a form

Using the following code

Private Sub txtMember_Change()

    txtMemCriteria = txtMember.Text & "*"
    Me.RecordSource = "qryLHOFilter"
    DoCmd.GoToControl "txtMember"
    If Not IsNull(Me.txtMember) Then Me.txtMember.SelStart = Len(Me.txtMember)
   
   
End Sub

it is working but when I enter text like x where no records start with x I am getting the following error message

Run Time Error 2185
You can't referenece a property or method for a control unless the
control has the focus.

I want it to give a message there are no records for that selection and change the text criteria back to null
0
Comment
Question by:brogrimes
  • 3
  • 2
5 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 18008178
If qryLHOFilter's Where clause is based on the text criteria, try using DCount to determine if any records exist before proceding with the rest of your code:

Private Sub txtMember_Change()

    txtMemCriteria = txtMember.Text & "*"

   '** Check for records and exit sub if none arefound
    If DCount("*","qryLHOFilter") = 0 then          
       msgbox "No records found"
       me.txtMember = Null
       me.txtMemCriteria = Null
       exit sub
    end if

    Me.RecordSource = "qryLHOFilter"
    DoCmd.GoToControl "txtMember"
    If Not IsNull(Me.txtMember) Then Me.txtMember.SelStart = Len(Me.txtMember)
   
End Sub
0
 

Author Comment

by:brogrimes
ID: 18008354
Thanks, that is working to some degree, my fault in the explanation

The message is appearing when i enter "ax" but when that happens I want the record sourse to retrive all the records again so the user can start again.

What is happening is when I enter 'a' it is filtering all the a's

When I enter 'x' I get the message, I click OK and the records from the 'a' selection are still there. I changed the code a little for the control to get the focus

Private Sub txtMember_Change()

 txtMemCriteria = txtMember.Text & "*"

   '** Check for records and exit sub if none arefound
    If DCount("*", "qryLHOFilter") = 0 Then
       MsgBox "No records found"
       DoCmd.GoToControl "txtMember"
        If Not IsNull(Me.txtMember) Then Me.txtMember.SelStart = Len(Me.txtMember)
       
       'Me.txtMember = "Null"
       'Me.txtMemCriteria = "Null"
       Exit Sub
    End If

    Me.RecordSource = "qryLHOFilter"
    DoCmd.GoToControl "txtMember"
    If Not IsNull(Me.txtMember) Then Me.txtMember.SelStart = Len(Me.txtMember)

End Sub

This brings me to the control but it does not take out the 'ax' and the cursor appears between the 'a' and the 'x'

Thanks
0
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 18008977
Is this what you want to do?
- Clear txtMember
- Reset the record source so that all records show (txtMemCriteria = "*")
- ensure that the focus is on txtMember

Can you post the SQL to qryLHOFilter?

Try this:

Private Sub txtMember_Change()

 txtMemCriteria = txtMember.Text & "*"

   '** Check for records and exit sub if none are found
    If DCount("*", "qryLHOFilter") = 0 Then
        MsgBox "No records found"      
         Me.txtMember = ""             '** Clear txtMember
         Me.txtMemCriteria = "*"      '** Use the * wildcard to get all records
          'If Not IsNull(Me.txtMember) Then Me.txtMember.SelStart = Len(Me.txtMember)
          'Exit Sub        '** remove this line to reset filter and set focus              
    End If

    Me.RecordSource = "qryLHOFilter"
    DoCmd.GoToControl "txtMember"
    If  NZ(Me.txtMember,"") <> "" Then Me.txtMember.SelStart = Len(Me.txtMember)    '** Nz will also check for empty strings

End Sub
0
 

Author Comment

by:brogrimes
ID: 18009203
Thanks alot

My application is really starting lo look good, appreciate it.
0
 
LVL 61

Expert Comment

by:mbizup
ID: 18009221
Glad I could help ;-)  
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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