VBA - Crashing infrequently

Re: Macro - Crashing infrequently

vbaInet said:
You might also want to drop the spaces in the field name (if at all it's there).
Click to expand...

Right... thats a new one... (never had that suggested) I will do some major edits to it as the name field does have spaces in it, infact quite a few fields do. I'll underscore them instead.
 
Re: Macro - Crashing infrequently

Up to you whether you include an underscore or not. You can also write it like DateEntered. Most developers use this style.
 
Re: Macro - Crashing infrequently

vbaInet said:
Up to you whether you include an underscore or not. You can also write it like DateEntered. Most developers use this style.
Click to expand...

Thanks. I have some planned downtime on Wednesday to make these changes. I want to get a fully understandable grasp of Error Handling and impliment that at the same time.
 
Re: Macro - Crashing infrequently

Good! Let us know how things progress.
 
Re: Macro - Crashing infrequently

Thanks... I am still trying to get my head around the error handling but the links you provided me are good.

Tried a few examples but couldn't get it to work. I will though.
 
Re: Macro - Crashing infrequently

You couldn't get it to throw an error message? That should be easy to do ;)

What's the problem there?
 
Re: Macro - Crashing infrequently

Hi, I got my Error handler in the code without braking it. now I just need to test breaking it (any idea?)

Code: 
Private Sub SearchFor_Change()
On Error GoTo Err_SearchFor          ' Initialize error handling.
[B]SEARCH CODE HERE[/B]
Exit_SearchFor:                           ' Label to resume after error.
     Exit Sub                             ' Exit before error handler.
Err_SearchFor:                            ' Label to jump to on error.
     MsgBox Err.Number & Err.Description  ' Place error handling here.
     Resume Exit_SearchFor                ' Pick up again and quit.
End Sub

So yeah. I just need to test breaking it.
 
Re: Macro - Crashing infrequently

Any code will do. Division by Zero comes to mind.
 
Re: Macro - Crashing infrequently

I have already sent a link about Error Handling.

The attached is a Simple Database that uses it.
 

Attachments

Re: Macro - Crashing infrequently

Cheers guys, will check out both Sample DB's when I can (Zip blocked at work)
 
Re: Macro - Crashing infrequently

Houston.... we have an error code.

Error code 2110 Microsoft Access can't move the focus to the control SearachResults.

So it looks like it is having trouble updating the search results listbox.
 
Re: Macro - Crashing infrequently

Where's the block of code that relates to this error?
 
Re: Macro - Crashing infrequently

I only used a basic Error Handling so have no more details regarding the error than this.

The only code is in the SearchFor box. the search function works as follows

it has 3 boxes.

Text Box - Searchfor (users type the search criteria on here)
Text Box - SrchText (hidden text box used by the code)
List Box - SearchResults (used to display the results)

only VBA code is in the Searchfor text box which is as follows

Code: 
Private Sub SearchFor_Change()
On Error GoTo Err_SearchFor 
    Dim vSearchString As String
    vSearchString = SearchFor.Text
    SrchText.Value = vSearchString
    Me.SearchResults.Requery
 
    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
            Me.SearchResults = Me.SearchResults.ItemData(1)
            Me.SearchResults.SetFocus
            DoCmd.Requery
            Me.SearchFor = vSearchString
            Me.SearchFor.SetFocus
            Me.SearchFor.SelStart = Me.SearchFor.SelLength
 
        Exit Sub
    End If
    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus
    DoCmd.Requery
    Me.SearchFor.SetFocus
    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If
Exit_SearchFor:                           
     Exit Sub                             
Err_SearchFor:                            
     MsgBox Err.Number & Err.Description  
     Resume Exit_SearchFor                
End Sub

I'd imagine the line it is breaking on is
Code: 
Me.SearchResults.SetFocus

But the fact it only breaks rarely confuses me.
 
Re: Macro - Crashing infrequently

Remove all the SetFocus code lines. You're in the Change event of the control already so it already has focus.
 
Re: Macro - Crashing infrequently

Even thought I am in the change event for SearchFor and the code is setting the focus for SearchResults A totally different box?

This isn't my code of course, but it looks as if the Setfocus code is used to swap between the Searchfor (text box) and the SearchResults (list box)

I added the code with the code notes, please note the code isn't mine but modified from one of the guides from here

Code: 
Private Sub SearchFor_Change()
On Error GoTo Err_SearchFor          [COLOR=green]' Initialize error handling.[/COLOR]
[COLOR=green]'Create a string (text) variable[/COLOR]
    Dim vSearchString As String
[COLOR=green]'Populate the string variable with the text entered in the Text Box SearchFor[/COLOR]
    vSearchString = SearchFor.Text
[COLOR=green]'Pass the value contained in the string variable to the hidden text box SrchText,
'that is used as the sear4ch criteria for the Query QRY_SearchAll[/COLOR]
    SrchText.Value = vSearchString
[COLOR=green]'Requery the List Box to show the latest results for the text entered in Text Box SearchFor[/COLOR]
    Me.SearchResults.Requery

[COLOR=green]'Tests for a trailing space and exits the sub routine at this point
'so as to preserve the trailing space, which would be lost if focus was shifted from Text Box SearchFor
[/COLOR]    If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
     [COLOR=green]   'Set the focus on the first item in the list box
[/COLOR]            Me.SearchResults = Me.SearchResults.ItemData(1)
            Me.SearchResults.SetFocus
       [COLOR=green] 'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box[/COLOR]
            DoCmd.Requery
[COLOR=green]        'Returns the cursor to the the end of the text in Text Box SearchFor,
        'and restores trailing space lost when focus is shifted to the list box
[/COLOR]            Me.SearchFor = vSearchString
            Me.SearchFor.SetFocus
            Me.SearchFor.SelStart = Me.SearchFor.SelLength
            
        Exit Sub
    End If
[COLOR=green]'Set the focus on the first item in the list box
[/COLOR]    Me.SearchResults = Me.SearchResults.ItemData(1)
    Me.SearchResults.SetFocus
[COLOR=green]'Requery the form to refresh the content of any unbound text box that might be feeding off the record source of  the List Box
[/COLOR]    DoCmd.Requery
[COLOR=green]'Returns the cursor to the the end of the text in Text Box SearchFor[/COLOR]
    Me.SearchFor.SetFocus
    If Not IsNull(Len(Me.SearchFor)) Then
        Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If
Exit_SearchFor:                          [COLOR=green] ' Label to resume after error.
[/COLOR]     Exit Sub                            [COLOR=green] ' Exit before error handler.
[/COLOR]Err_SearchFor:                          [COLOR=green]  ' Label to jump to on error.[/COLOR]
     MsgBox Err.Number & Err.Description  [COLOR=green]' Place error handling here.[/COLOR]
     Resume Exit_SearchFor              [COLOR=green]  ' Pick up again and quit.[/COLOR]
End Sub
 
Last edited:
Re: Macro - Crashing infrequently

Correct! You don't need to set focus to any control to get the value.
 
Re: Macro - Crashing infrequently

Hi,

Cheers. I will try to remove ALL focus code.

See above, I added the notes from the person who wrote the code
 
Re: Macro - Crashing infrequently

Just poor coding!

By the way, have you noticed this in your Control Panel?? See attachment.
attachment.php
 

Attachments

  • YourThread_MyName.png
    YourThread_MyName.png
    17.3 KB · Views: 122
Re: Macro - Crashing infrequently

Okay, brilliant, the code works perfectly without the setfocus.

Will try implimenting that to users and test the system to see if any problems occur
 
You must log in or register to reply here.

Similar threads

murray83
Dynamically search multiple fields 2 list boxes ?
Replies
7
Views
1,956
murray83
murray83
V
Typing really fast run time error
Replies
9
Views
951
JHB
J
V
Combo box selection disrupting search box functionality
Replies
2
Views
877
isladogs
isladogs
V
Filter datasheet for specific column
Replies
8
Views
1,117
vent
V
S
turning single freetext on change search to a multiple criteria search
Replies
1
Views
689
Uncle Gizmo
Uncle Gizmo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom