Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 282
  • Last Modified:

Error Handling

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
brogrimes
Asked:
brogrimes
  • 3
  • 2
1 Solution
 
mbizupCommented:
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
 
brogrimesAuthor Commented:
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
 
mbizupCommented:
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
 
brogrimesAuthor Commented:
Thanks alot

My application is really starting lo look good, appreciate it.
0
 
mbizupCommented:
Glad I could help ;-)  
0

Featured Post

Industry Leaders: 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!

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now