Solved

Error Handling

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

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…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

760 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

19 Experts available now in Live!

Get 1:1 Help Now