Solved

Error Handling

Posted on 2006-11-24
5
273 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

920 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

15 Experts available now in Live!

Get 1:1 Help Now