Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-access > #10976 > unrolled thread

AfterUpdate Code causes Access failure ("Microsoft Access has stopped working")

Back to article view | Back to comp.databases.ms-access

Contents

  AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") magmike <magmike7@yahoo.com> - 2015-08-08 23:04 -0700
    Re: AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") Ulrich Möller <knobbi38@arcor.de> - 2015-08-10 13:48 +0200
      Re: AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") "Ron Paii" <None@none.com> - 2015-08-10 07:11 -0500
        Re: AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") Ulrich Möller <knobbi38@arcor.de> - 2015-08-10 14:39 +0200
          Re: AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") "Ron Paii" <None@none.com> - 2015-08-12 08:02 -0500
            Re: AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") Ulrich Möller <knobbi38@arcor.de> - 2015-08-12 18:47 +0200
    Re: AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") magmike <magmike7@yahoo.com> - 2015-08-11 07:54 -0700
    Re: AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") magmike <magmike7@yahoo.com> - 2015-08-11 09:32 -0700
      Re: AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") Ulrich Möller <knobbi38@arcor.de> - 2015-08-12 01:54 +0200
    Re: AfterUpdate Code causes Access failure ("Microsoft Access has stopped working") Patrick Finucane <patrickfinucanetx@gmail.com> - 2015-08-11 12:30 -0700

#10976 — AfterUpdate Code causes Access failure ("Microsoft Access has stopped working")

I know this might sound strange, but when I first open my database (ProspecForm opens), if I choose to use the CompanyFind dropdown/combo box on the form to to choose the record ProspectForm will populate with, it causes "Microsoft Access has stopped working". However, if I first cycle through a record or two using the navigation buttons, and then use the CompanyFind dropdown/combo box, it is fine the rest of the time the database is open. 

Here is the offending code: 

Private Sub CompanyFind_AfterUpdate() 
    Dim rs As Object 
If Me.CompanyFind.Column(3) > 0 Then 
    Set rs = Me.Recordset.Clone 
    rs.FindFirst "[ID] = " & Str(Nz(Me![CompanyFind].Column(3), 0)) 
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark 
    Else: 
    MyCompanySearch 
End If 
End Sub 



Any ideas? 

Thanks in advance for your help, 
magmike

[toc] | [next] | [standalone]

#10979

Am 09.08.2015 um 08:04 schrieb magmike:
> I know this might sound strange, but when I first open my database (ProspecForm opens), if I choose to use the CompanyFind dropdown/combo box on the form to to choose the record ProspectForm will populate with, it causes "Microsoft Access has stopped working". However, if I first cycle through a record or two using the navigation buttons, and then use the CompanyFind dropdown/combo box, it is fine the rest of the time the database is open.
>
> Here is the offending code:
>
> Private Sub CompanyFind_AfterUpdate()
>      Dim rs As Object
> If Me.CompanyFind.Column(3) > 0 Then
>      Set rs = Me.Recordset.Clone
>      rs.FindFirst "[ID] = " & Str(Nz(Me![CompanyFind].Column(3), 0))
>      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>      Else:
>      MyCompanySearch
> End If
> End Sub
>
>
>
> Any ideas?
>
> Thanks in advance for your help,
> magmike
Maybe you have some timing issues. The records in the combobox will be 
loaded asynchronously to the rest of the form. I don't know how many 
records will be loaded into your combo so it might took a little while.

Try to force loading the recordset from the combo in the CompanyFind 
enter_event with:
lngReccordCounts = CompanyFind.ListCount

and second, a modification of the original code to a more common usage:

Private Sub CompanyFind_AfterUpdate()
   Dim rs As DAO.Recordset
   
   If nz(Me.CompanyFind.Column(3),0) > 0 Then
     Set rs = Me.Recordset.Clone
     rs.FindFirst "[ID] = " & Cstr(Nz(Me![CompanyFind].Column(3), "0"))
     
     if rs.NoMatch Then
       MyComanySearch
     else
       Me.Bookmark = rs.Bookmark
     End If
   end if
   set rs = Nothing
End Sub

At first you should try it without the code in the enter event and if it doesn't work for you, add the given code for the enter event and try it again.

Ulrich

[toc] | [prev] | [next] | [standalone]

#10980

"Ulrich Möller" <knobbi38@arcor.de> wrote in message 
news:mqa2um$map$1@dont-email.me...
> Am 09.08.2015 um 08:04 schrieb magmike:
>> I know this might sound strange, but when I first open my database 
>> (ProspecForm opens), if I choose to use the CompanyFind dropdown/combo 
>> box on the form to to choose the record ProspectForm will populate with, 
>> it causes "Microsoft Access has stopped working". However, if I first 
>> cycle through a record or two using the navigation buttons, and then use 
>> the CompanyFind dropdown/combo box, it is fine the rest of the time the 
>> database is open.
>>
>> Here is the offending code:
>>
>> Private Sub CompanyFind_AfterUpdate()
>>      Dim rs As Object
>> If Me.CompanyFind.Column(3) > 0 Then
>>      Set rs = Me.Recordset.Clone
>>      rs.FindFirst "[ID] = " & Str(Nz(Me![CompanyFind].Column(3), 0))
>>      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>>      Else:
>>      MyCompanySearch
>> End If
>> End Sub
>>
>>
>>
>> Any ideas?
>>
>> Thanks in advance for your help,
>> magmike
> Maybe you have some timing issues. The records in the combobox will be 
> loaded asynchronously to the rest of the form. I don't know how many 
> records will be loaded into your combo so it might took a little while.
>
> Try to force loading the recordset from the combo in the CompanyFind 
> enter_event with:
> lngReccordCounts = CompanyFind.ListCount
>
> and second, a modification of the original code to a more common usage:
>
> Private Sub CompanyFind_AfterUpdate()
>   Dim rs As DAO.Recordset
>   If nz(Me.CompanyFind.Column(3),0) > 0 Then
>     Set rs = Me.Recordset.Clone
>     rs.FindFirst "[ID] = " & Cstr(Nz(Me![CompanyFind].Column(3), "0"))
>     if rs.NoMatch Then
>       MyComanySearch
>     else
>       Me.Bookmark = rs.Bookmark
>     End If
>   end if
>   set rs = Nothing
> End Sub
>
> At first you should try it without the code in the enter event and if it 
> doesn't work for you, add the given code for the enter event and try it 
> again.
>
> Ulrich
>

Ulrich fix, if itworks "If nz(Me.CompanyFind.Column(3),0) > 0 Then", 
indicates that the control is null which makes the "If" statement without 
"NZ" invalid. VBA will ignore the statement and continue to the next line. 
The error will occur on the Recordset.Clone or the rs.FindFirst. You should 
be using "On Error" code in you functions to catch these errors. Question, 
does the Access crash happen when running under full Access or only on 
Runtime? Without error checking, full Access should halt with an un-handled 
exception error, Runtime fill crash. 

[toc] | [prev] | [next] | [standalone]

#10981

Am 10.08.2015 um 14:11 schrieb Ron Paii:
>
>
> "Ulrich Möller" <knobbi38@arcor.de> wrote in message 
> news:mqa2um$map$1@dont-email.me...
>> Am 09.08.2015 um 08:04 schrieb magmike:
>>> I know this might sound strange, but when I first open my database 
>>> (ProspecForm opens), if I choose to use the CompanyFind 
>>> dropdown/combo box on the form to to choose the record ProspectForm 
>>> will populate with, it causes "Microsoft Access has stopped 
>>> working". However, if I first cycle through a record or two using 
>>> the navigation buttons, and then use the CompanyFind dropdown/combo 
>>> box, it is fine the rest of the time the database is open.
>>>
>>> Here is the offending code:
>>>
>>> Private Sub CompanyFind_AfterUpdate()
>>>      Dim rs As Object
>>> If Me.CompanyFind.Column(3) > 0 Then
>>>      Set rs = Me.Recordset.Clone
>>>      rs.FindFirst "[ID] = " & Str(Nz(Me![CompanyFind].Column(3), 0))
>>>      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>>>      Else:
>>>      MyCompanySearch
>>> End If
>>> End Sub
>>>
>>>
>>>
>>> Any ideas?
>>>
>>> Thanks in advance for your help,
>>> magmike
>> Maybe you have some timing issues. The records in the combobox will 
>> be loaded asynchronously to the rest of the form. I don't know how 
>> many records will be loaded into your combo so it might took a little 
>> while.
>>
>> Try to force loading the recordset from the combo in the CompanyFind 
>> enter_event with:
>> lngReccordCounts = CompanyFind.ListCount
>>
>> and second, a modification of the original code to a more common usage:
>>
>> Private Sub CompanyFind_AfterUpdate()
>>   Dim rs As DAO.Recordset
>>   If nz(Me.CompanyFind.Column(3),0) > 0 Then
>>     Set rs = Me.Recordset.Clone
>>     rs.FindFirst "[ID] = " & Cstr(Nz(Me![CompanyFind].Column(3), "0"))
>>     if rs.NoMatch Then
>>       MyComanySearch
>>     else
>>       Me.Bookmark = rs.Bookmark
>>     End If
>>   end if
>>   set rs = Nothing
>> End Sub
>>
>> At first you should try it without the code in the enter event and if 
>> it doesn't work for you, add the given code for the enter event and 
>> try it again.
>>
>> Ulrich
>>
>
> Ulrich fix, if itworks "If nz(Me.CompanyFind.Column(3),0) > 0 Then", 
> indicates that the control is null which makes the "If" statement 
> without "NZ" invalid. VBA will ignore the statement and continue to 
> the next line. 
No, without the nz(..) the comparison will be see if NULL > 0 then ...  
which will always evaluate to FALSE and don't throw any error, because 
it is a valid statement! It is only for better reading and a cleaner 
style, how you want.
> The error will occur on the Recordset.Clone or the rs.FindFirst. You 
> should be using "On Error" code in you functions to catch these 
> errors. Question, does the Access crash happen when running under full 
> Access or only on Runtime? Without error checking, full Access should 
> halt with an un-handled exception error, Runtime fill crash.
The real think is the invalid if then else statement where magmike try 
to assign the bookmark, but this may although only a formatting issue in 
my newsreader. Anyway, the code should be rewritten in a more convenient 
style.

Ulrich

[toc] | [prev] | [next] | [standalone]

#10990

"Ulrich Möller" <knobbi38@arcor.de> wrote in message 
news:mqa5v4$13t$1@dont-email.me...
> Am 10.08.2015 um 14:11 schrieb Ron Paii:
>>
>>
>> "Ulrich Möller" <knobbi38@arcor.de> wrote in message 
>> news:mqa2um$map$1@dont-email.me...
>>> Am 09.08.2015 um 08:04 schrieb magmike:
>>>> I know this might sound strange, but when I first open my database 
>>>> (ProspecForm opens), if I choose to use the CompanyFind dropdown/combo 
>>>> box on the form to to choose the record ProspectForm will populate 
>>>> with, it causes "Microsoft Access has stopped working". However, if I 
>>>> first cycle through a record or two using the navigation buttons, and 
>>>> then use the CompanyFind dropdown/combo box, it is fine the rest of the 
>>>> time the database is open.
>>>>
>>>> Here is the offending code:
>>>>
>>>> Private Sub CompanyFind_AfterUpdate()
>>>>      Dim rs As Object
>>>> If Me.CompanyFind.Column(3) > 0 Then
>>>>      Set rs = Me.Recordset.Clone
>>>>      rs.FindFirst "[ID] = " & Str(Nz(Me![CompanyFind].Column(3), 0))
>>>>      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>>>>      Else:
>>>>      MyCompanySearch
>>>> End If
>>>> End Sub
>>>>
>>>>
>>>>
>>>> Any ideas?
>>>>
>>>> Thanks in advance for your help,
>>>> magmike
>>> Maybe you have some timing issues. The records in the combobox will be 
>>> loaded asynchronously to the rest of the form. I don't know how many 
>>> records will be loaded into your combo so it might took a little while.
>>>
>>> Try to force loading the recordset from the combo in the CompanyFind 
>>> enter_event with:
>>> lngReccordCounts = CompanyFind.ListCount
>>>
>>> and second, a modification of the original code to a more common usage:
>>>
>>> Private Sub CompanyFind_AfterUpdate()
>>>   Dim rs As DAO.Recordset
>>>   If nz(Me.CompanyFind.Column(3),0) > 0 Then
>>>     Set rs = Me.Recordset.Clone
>>>     rs.FindFirst "[ID] = " & Cstr(Nz(Me![CompanyFind].Column(3), "0"))
>>>     if rs.NoMatch Then
>>>       MyComanySearch
>>>     else
>>>       Me.Bookmark = rs.Bookmark
>>>     End If
>>>   end if
>>>   set rs = Nothing
>>> End Sub
>>>
>>> At first you should try it without the code in the enter event and if it 
>>> doesn't work for you, add the given code for the enter event and try it 
>>> again.
>>>
>>> Ulrich
>>>
>>
>> Ulrich fix, if itworks "If nz(Me.CompanyFind.Column(3),0) > 0 Then", 
>> indicates that the control is null which makes the "If" statement without 
>> "NZ" invalid. VBA will ignore the statement and continue to the next 
>> line.
> No, without the nz(..) the comparison will be see if NULL > 0 then ... 
> which will always evaluate to FALSE and don't throw any error, because it 
> is a valid statement! It is only for better reading and a cleaner style, 
> how you want.

Any operation involving Null results in Null.
? Null > 0
outputs
Null

The following code

Public Sub TestNull()

    If Null = True Then
        Debug.Print "Null = True"
    Else
        Debug.Print "Null = False"
    End If

    If Null = False Then
        Debug.Print "Null = False"
    Else
        Debug.Print "Null = True"
    End If

End Sub

TestNull
Outputs
Null = False
Null = True

I wish VBA would instead raise an error. It would be easer to catch logic 
errors involving Null.

' PS The following changes should make the code more stable

Private Sub CompanyFind_AfterUpdate()
on error goto errCompanyFind_AfterUpdate
      Dim rs As Object
 If nz(Me.CompanyFind.Column(3),0) > 0 Then
     Set rs = Me.Recordset.Clone
     if rs.recordcount > 0 then ' I like to be paranoid when working on 
recordsets

          ' Str() indicates [ID] is text, so I added quotes to the search.
         ' Str() will output a leading space if number is positive.
         ' Str() will raise an error if you pass it a string that is not a 
number.

          rs.FindFirst "[ID] =' " & Str(Nz(Me![CompanyFind].Column(3), 0)) & 
"'"
          If Not rs.Nomatch Then
             Me.Bookmark = rs.Bookmark
      Else:
          MyCompanySearch
       endif
    else
    MyCompanySearch
 End If

doneCompanyFind_AfterUpdate:
    on error resume next
    set rs = nothing ' Never hurts to cleanup after yourself
    exit sub

errCompanyFind_AfterUpdate:
    debug.print err.description
    resume doneCompanyFind_AfterUpdate
End Sub 

[toc] | [prev] | [next] | [standalone]

#10994

Hi Ron,

Am 12.08.2015 um 15:02 schrieb Ron Paii:
> No, without the nz(..) the comparison will be see if NULL > 0 then ... 
> which will always evaluate to FALSE and don't throw any error, because 
> it is a valid statement! It is only for better reading and a cleaner 
> style, how you want.
>
> Any operation involving Null results in Null.
> ? Null > 0
> outputs
> Null
>
That's it and the if command will interpret it as FALSE.

> The following code
>
> Public Sub TestNull()
>
>    If Null = True Then
>        Debug.Print "Null = True"
>    Else
>        Debug.Print "Null = False"
>    End If
>
>    If Null = False Then
>        Debug.Print "Null = False"
>    Else
>        Debug.Print "Null = True"
>    End If
>
> End Sub
>
> TestNull
> Outputs
> Null = False
> Null = True
>
> I wish VBA would instead raise an error. It would be easer to catch 
> logic errors involving Null.
>
Maybe. I prefer extra handling of the special case that something 
evaluates to null and not to mention the tristate checkboxes!

> ' PS The following changes should make the code more stable
>
> Private Sub CompanyFind_AfterUpdate()
> on error goto errCompanyFind_AfterUpdate
>      Dim rs As Object
> If nz(Me.CompanyFind.Column(3),0) > 0 Then
>     Set rs = Me.Recordset.Clone
>     if rs.recordcount > 0 then ' I like to be paranoid when working on 
> recordsets
>
>          ' Str() indicates [ID] is text, so I added quotes to the search.
>         ' Str() will output a leading space if number is positive.
>         ' Str() will raise an error if you pass it a string that is 
> not a number.
>
>          rs.FindFirst "[ID] =' " & Str(Nz(Me![CompanyFind].Column(3), 
> 0)) & "'"
>          If Not rs.Nomatch Then
>             Me.Bookmark = rs.Bookmark
>      Else:
>          MyCompanySearch
>       endif
>    else
>    MyCompanySearch
> End If
>
> doneCompanyFind_AfterUpdate:
>    on error resume next
>    set rs = nothing ' Never hurts to cleanup after yourself
>    exit sub
>
> errCompanyFind_AfterUpdate:
>    debug.print err.description
>    resume doneCompanyFind_AfterUpdate
> End Sub

I agree with you, an explicit error handling is recommended.

Ulrich

[toc] | [prev] | [next] | [standalone]

#10982

On Sunday, August 9, 2015 at 1:04:35 AM UTC-5, magmike wrote:
> I know this might sound strange, but when I first open my database (ProspecForm opens), if I choose to use the CompanyFind dropdown/combo box on the form to to choose the record ProspectForm will populate with, it causes "Microsoft Access has stopped working". However, if I first cycle through a record or two using the navigation buttons, and then use the CompanyFind dropdown/combo box, it is fine the rest of the time the database is open. 
> 
> Here is the offending code: 
> 
> Private Sub CompanyFind_AfterUpdate() 
>     Dim rs As Object 
> If Me.CompanyFind.Column(3) > 0 Then 
>     Set rs = Me.Recordset.Clone 
>     rs.FindFirst "[ID] = " & Str(Nz(Me![CompanyFind].Column(3), 0)) 
>     If Not rs.EOF Then Me.Bookmark = rs.Bookmark 
>     Else: 
>     MyCompanySearch 
> End If 
> End Sub 
> 
> 
> 
> Any ideas? 
> 
> Thanks in advance for your help, 
> magmike

Yeehaw, Ulrich! That did it! Thank you for eliminating an annoyance I have been living with for years :)

[toc] | [prev] | [next] | [standalone]

#10983

On Sunday, August 9, 2015 at 1:04:35 AM UTC-5, magmike wrote:
> I know this might sound strange, but when I first open my database (ProspecForm opens), if I choose to use the CompanyFind dropdown/combo box on the form to to choose the record ProspectForm will populate with, it causes "Microsoft Access has stopped working". However, if I first cycle through a record or two using the navigation buttons, and then use the CompanyFind dropdown/combo box, it is fine the rest of the time the database is open. 
> 
> Here is the offending code: 
> 
> Private Sub CompanyFind_AfterUpdate() 
>     Dim rs As Object 
> If Me.CompanyFind.Column(3) > 0 Then 
>     Set rs = Me.Recordset.Clone 
>     rs.FindFirst "[ID] = " & Str(Nz(Me![CompanyFind].Column(3), 0)) 
>     If Not rs.EOF Then Me.Bookmark = rs.Bookmark 
>     Else: 
>     MyCompanySearch 
> End If 
> End Sub 
> 
> 
> 
> Any ideas? 
> 
> Thanks in advance for your help, 
> magmike

Ulrich,

The combobox is no longer causing a shutdown when used for the first time, however, if a value is entered that is not prepulated in the combobox, the MyCompanySearch function is no longer being invoke. Any ideas?

[toc] | [prev] | [next] | [standalone]

#10987

Am 11.08.2015 um 18:32 schrieb magmike:
> On Sunday, August 9, 2015 at 1:04:35 AM UTC-5, magmike wrote:
>> I know this might sound strange, but when I first open my database (ProspecForm opens), if I choose to use the CompanyFind dropdown/combo box on the form to to choose the record ProspectForm will populate with, it causes "Microsoft Access has stopped working". However, if I first cycle through a record or two using the navigation buttons, and then use the CompanyFind dropdown/combo box, it is fine the rest of the time the database is open.
>>
>> Here is the offending code:
>>
>> Private Sub CompanyFind_AfterUpdate()
>>      Dim rs As Object
>> If Me.CompanyFind.Column(3) > 0 Then
>>      Set rs = Me.Recordset.Clone
>>      rs.FindFirst "[ID] = " & Str(Nz(Me![CompanyFind].Column(3), 0))
>>      If Not rs.EOF Then Me.Bookmark = rs.Bookmark
>>      Else:
>>      MyCompanySearch
>> End If
>> End Sub
>>
>>
>>
>> Any ideas?
>>
>> Thanks in advance for your help,
>> magmike
> Ulrich,
>
> The combobox is no longer causing a shutdown when used for the first time, however, if a value is entered that is not prepulated in the combobox, the MyCompanySearch function is no longer being invoke. Any ideas?
That's right. The find expression uses the 4th column populated in the 
combobox and this can not be entered by the user. So the search is 
limited to the items already populated. If you want the user to be able 
to enter a free search with the same combobox you have to change your 
program design. It is possible, but you have to provide us with a little 
bit more informations, ex. the recordsource of the combobox and the  
fields which should be used for a free text search.

Ulrich

[toc] | [prev] | [next] | [standalone]

#10984

On Sunday, August 9, 2015 at 2:04:35 AM UTC-4, magmike wrote:
> I know this might sound strange, but when I first open my database (ProspecForm opens), if I choose to use the CompanyFind dropdown/combo box on the form to to choose the record ProspectForm will populate with, it causes "Microsoft Access has stopped working". However, if I first cycle through a record or two using the navigation buttons, and then use the CompanyFind dropdown/combo box, it is fine the rest of the time the database is open. 
> 
> Here is the offending code: 
> 
> Private Sub CompanyFind_AfterUpdate() 
>     Dim rs As Object 
> If Me.CompanyFind.Column(3) > 0 Then 
>     Set rs = Me.Recordset.Clone 
>     rs.FindFirst "[ID] = " & Str(Nz(Me![CompanyFind].Column(3), 0)) 
>     If Not rs.EOF Then Me.Bookmark = rs.Bookmark 
>     Else: 
>     MyCompanySearch 
> End If 
> End Sub 
> 
> 
> 
> Any ideas? 
> 
> Thanks in advance for your help, 
> magmike

You might want to use 
  If Not rs.NoMatch Then
instead of
  If Not rs.EOF

When in doubt, put the word STOP on a code line.  You can then step thru your code, see where it branches off to where you didn't expect it.  If STOP doesn't execute, you have an even bigger problem since it didn't fire.

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-access

csiph-web