VBA - Crashing infrequently (1 Viewer)

[kacey8]

Hi,

I have a Macro running as a search function. 99.5% of the time it is working perfectly, however every so often it will throw a hizzy fit and crash the system,

I can't get a debug as the users who are doing it are running the DB in Access Runtime and of course it doesn't offer to debug into VBA. all they get is this

It happens when typing in so I assume it is related to the Macro which is set on update. the Macro is this

Private Sub SearchFor_Change()
    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
End Sub

It's from one of the guides on here (for ease of pasting I removed the notes but if you do need them please let me know.

Anyway... rather than trying to work out the error without having debug, is there a way to get the system to create a crash/log file for the users?

It happens so infrequently I have yet been unable to recreate it in full access.


Cheers,

Graeme

 

[vbaInet]

Re: Macro - Crashing infrequently

I've not scrutinised your code but first of all, there's no error handling hence the runtime error. And it's not a macro, it's VBA code.

But you still need to pin point the problem. Ask your users what steps they took before it crashed.

 

[kacey8]

Re: Macro - Crashing infrequently

I've not scrutinised your code but first of all, there's no error handling hence the runtime error. And it's not a macro, it's VBA code.

But you still need to pin point the problem. Ask your users what steps they took before it crashed.

Yes... yes it is... my mistake :/ and I do know the difference

The steps are the same, they go into the search form, begin typing and then crashes (random times, random intervals, sometimes it happens 4/5 times in a row but then works perfectly for the user

 

[vbaInet]

Re: Macro - Crashing infrequently

Where's your error handling Kacey?

What do they type that makes it crash? You need to get the string and try it on your dev environment.

 

[kacey8]

Re: Macro - Crashing infrequently

That'll be my fault, I've never used Error handling before VBA (only been using Access for a month or so)

And as for typing, well one example is at the top, it crashed when typing WA however it is random, any names can cause it.

 

[vbaInet]

Re: Macro - Crashing infrequently

This should give you some good starting point:

http://allenbrowne.com/ser-23a.html

As for the error itself, you need to tell us that. Which code line does it highlight when it errors? If you're running this in a full development environment it will take you to the problem code when you hit Debug.

 

[kacey8]

Re: Macro - Crashing infrequently

This should give you some good starting point:

http://allenbrowne.com/ser-23a.html

As for the error itself, you need to tell us that. Which code line does it highlight when it errors? If you're running this in a full development environment it will take you to the problem code when you hit Debug.

Absolutely, and highlight it in yellow, problem is I am unable to replicate the error in Access Dev.

I even sat for 3 hours typing into the search bar to recreate it and was unable too.

 

[kacey8]

Re: Macro - Crashing infrequently

so if I am using this.

1 Sub|Function SomeName()
2     On Error GoTo Err_SomeName          ' Initialize error handling.
3     ' Code to do something here.
4 Exit_SomeName:                          ' Label to resume after error.
5     Exit Sub|Function                   ' Exit before error handler.
6 Err_SomeName:                           ' Label to jump to on error.
7     MsgBox Err.Number & Err.Description ' Place error handling here.
8     Resume Exit_SomeName                ' Pick up again and quit.
9 End Sub|Function

Am I right in saying this is added to the VBA code for the text box?

So lines 1/2 go befopre my search code
Line 3 is my search code
and 4 onwards go after my search code?

 

[vbaInet]

Re: Macro - Crashing infrequently

Go to the user's machine and replicate the error. Take down the steps from start to finish (including steps from subsequent forms that lead to this form). If a user is willing to do this for you then great.

Correct! But you need to spend much more than just a few minutes understanding error handling. Also read the link embedded in the one I sent. Error handling should go into every function and every sub.

 

[RainLover]

Re: Macro - Crashing infrequently

And as for typing, well one example is at the top, it crashed when typing WA however it is random, any names can cause it.

Typing what?

If this is happening while writing code (Big boys write code, little boys type or play with their crayon) I would suggest that you need to do a decompile.

Is this your Database from the beginning or have you taken over from someone else. It would also be nice to know your Location and the Version you are using.

Check out my signature for an example.

Only having fun with the crayon etc talk.

VBA Sorry for jumping in. I just could not help myself. I will leave it up to you from here as I really am only going on a past experience which is not overly common.

 

[vbaInet]

Re: Macro - Crashing infrequently

VBA Sorry for jumping in. I just could not help myself. I will leave it up to you from here as I really am only going on a past experience which is not overly common.

No bother Rain! All suggestions are useful to the poster.

 

[RainLover]

Re: Macro - Crashing infrequently

This might help with your Error handling.

It is good, it is complicated and it is what I use.

 

[kacey8]

Re: Macro - Crashing infrequently

Typing what?

Hi Rain... Well if you read my first post you'd realise the error is occuring when typing into a "search" box...

As per the screenshot above and this new one bellow I will put it as simply as possible... Person typey where Blue box is and blue Error box appears

I hope this clarifies what you are asking...

The USER types where the search box is, when using the program and on random occurances the error box appears.

If this is happening while writing code (Big boys write code, little boys type or play with their crayon) I would suggest that you need to do a decompile.

As previously said this is not happening when writing code... this is happening when the USER is typing a name in a search box to search and located records in the DB.

oh and I left my crayons at home...

Is this your Database from the beginning or have you taken over from someone else. It would also be nice to know your Location and the Version you are using.

Check out my signature for an example.

Only having fun with the crayon etc talk.

VBA Sorry for jumping in. I just could not help myself. I will leave it up to you from here as I really am only going on a past experience which is not overly common.

Yes... it is my DB and was created by me only. my first ever Access DB incorporated as much as ONE WHOLE MONTH of Access experience.

as I am a Hardware Engineer by trade and work in support this is kind of out of my area of expertise.

Go to the user's machine and replicate the error. Take down the steps from start to finish (including steps from subsequent forms that lead to this form). If a user is willing to do this for you then great.

Correct! But you need to spend much more than just a few minutes understanding error handling. Also read the link embedded in the one I sent. Error handling should go into every function and every sub.

Yes.. I realise that and I am working through this.. very slowly this is kind of an extra project I took on. now the system is live with this error cropping up.

I have sat with the users and tracked the error several times, everything is completely random and the only constant is that it is set off when using the "Search by name" function above.

If they have the form open and come back to it, open the form for the first time or even close and open the form, these seem to have no bearing on this.

There seems to be no constant or pattern with how the error is occuring. I thought it was if one user was in the record they were searching for but it isn't even that, as the record hadn't even been selected yet.

 

[vbaInet]

Re: Macro - Crashing infrequently

If you have the error handling in place and output it to a file, it will at least tell you which function is causing the error and what the real error message is.

 

[kacey8]

Re: Macro - Crashing infrequently

If you have the error handling in place and output it to a file, it will at least tell you which function is causing the error and what the real error message is.

Thanks VBA and this is what I am working on at present, the moment I can work out and impliment error handling I can try to trace it.

I just didn't appreciate Rains sarcasm, Especially when it is evidented I am clearly new to the whole Access/VBA thing.

 

[vbaInet]

Re: Macro - Crashing infrequently

Thanks VBA and this is what I am working on at present, the moment I can work out and impliment error handling I can try to trace it.

It won't be a full trace but it would help to an extent. In case you missed the embedded link I was talking about, here it is:

http://www.fmsinc.com/tpapers/vbacode/Debug.asp

 

[RainLover]

Re: Macro - Crashing infrequently

I just didn't appreciate Rains sarcasm, Especially when it is evidented I am clearly new to the whole Access/VBA thing.

You shouldn't get ruffled by a bit of fun. Especially when I put one of those smiley things in there.

Your problem could be one of many possibilities. Such as incompatible data type. Using a Reserved word. Has it been compiled properly. I could go on.

I would suggest that before people loose interest, post a copy of the Database that has not been compiled. We need to see all your code. Some dummy data of about 4 records per table would be nice. This should pinpoint the problem.

One last thing before posting, make sure this copy does in fact fail. No use sending a good copy.

Finally and I don't think this question has been asked. Are you sharing the Front end. The back end must be shared because it holds the data. But every user must have their own front end copy on their own machine.

 

[RainLover]

Re: Macro - Crashing infrequently


I hope you are not using Name or Date as the name of the fields in the Table.

They are shown on the form which sometimes makes one think that they are in the Table.

There are reserved words.

 

[kacey8]

Re: Macro - Crashing infrequently

You shouldn't get ruffled by a bit of fun. Especially when I put one of those smiley things in there.

Hi I do appreciate that and wasn't hurt, I've been around long enough for little jokes not to bother me

To answer the questions.

I would suggest that before people loose interest, post a copy of the Database that has not been compiled. We need to see all your code. Some dummy data of about 4 records per table would be nice. This should pinpoint the problem.
One last thing before posting, make sure this copy does in fact fail. No use sending a good copy.

I will try, the problem is I have been unable to break the DB myself. Users do it no problem but It seems random and only in around 1 in every 1000 + searches, I spent four hours doing searches without breaking it.

Finally and I don't think this question has been asked. Are you sharing the Front end. The back end must be shared because it holds the data. But every user must have their own front end copy on their own machine.

Yes, each user have their own .accdr file (well actually I keep the .accdr in a private directory and each user have their own shortcut in a folder for them. First thing I checked was they were using their own files.

I hope you are not using Name or Date as the name of the fields in the Table.

They are shown on the form which sometimes makes one think that they are in the Table.
There are reserved words.

Deffinately not, I am using DATE ENTERED for the date which is not reserved apparently and Lead Name for the name field.

No reserved words are being used, someone pointed this out to me in a previous thread and I changed it all

 

[vbaInet]

Re: Macro - Crashing infrequently

Deffinately not, I am using DATE ENTERED for the date which is not reserved apparently and Lead Name for the name field.

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