Access InStr() Function Crash - Microsoft Access has Stopped Working!

Question

I've been reading on this forum a lot a bout this error but I haven't found a clear solution from it. A guy from the community said he did some substitute formula as it appears is an issue with the new 365 version.

To clear the waters, I did compact and repair, debug, I re did the entire db and the issue persist.

The formula works here and there but every so often it crashes without any insight why, here is my code:

This code is to enable/disable 2 dropdowns from my form depending on the selection of a multiple choice combo box. So in the code I displayed all possible selection scenarios. The code works but some times crashes the form :(.

If somebody can help me get a substitute function to use that would be awesome.

thanks!

'Enable scap and siap box when selected from the list'

Private Sub Review_Attributes_AfterUpdate()

  Me.Review\_Attributes.Requery 

  Me.SCAP\_Countries.Enabled = (InStr(1, Me.Review\_Attributes.Text, "SCAP") > 0) 

  Me.SIAP\_Type.Enabled = (InStr(1, Me.Review\_Attributes.Text, "SIAP") > 0) 

End Sub

The error comes as "Microsoft Access has stopped working"

Answer 1

Hey IMB. After two days of use with the shorted formula the issue of Access Crashing happened again. :(

Hi Ale,

So, probably it is some issue with the new 365 version.

How are "SCAP" and/or "SIAP" to find in the control  Me.Review_Attributes? At the beginning, at the end or somewhere in the text?

You could use a alternative function, something like:

Function Occurs(org_text As String, search As String) As Integer

Dim org_arr() As String

org_arr = Split(org_text, search)

Select Case UBound(org_arr)

Case -1, 0

Occurs = 0

Case Else

Occurs = Len(org_arr(0)) + 1

End Select

End Function

You can place this function in a general module.

I hope this solves your problem.

Imb.

Answer 2

I am a bit lost of how the code will enable or disable the 2 fields I need.

Hi Ale,

The "original" code in the AfterUpdate event was:

Private Sub Review_Attributes_AfterUpdate()

*Me.Review\_Attributes.Requery*

*Me.SCAP\_Countries.Enabled = (InStr(1, Me.Review\_Attributes.Text, "SCAP") > 0)*

*Me.SIAP\_Type.Enabled = (InStr(1, Me.Review\_Attributes.Text, "SIAP") > 0)*

End Sub

Now, replace the InStr function with the Occurs function (or whatever name you want to give it):

Private Sub Review_Attributes_AfterUpdate()

*Me.Review\_Attributes.Requery*

*Me.SCAP\_Countries.Enabled = (Occurs(Me.Review\_Attributes.Text, "SCAP") > 0)*

*Me.SIAP\_Type.Enabled = (Occurs(Me.Review\_Attributes.Text, "SIAP") > 0)*

End Sub

The code of the Occurs function should be placed in a general module.

Imb.

Answer 3

My guess would be along the lines of running needlessly redundant code.

IMB's alternative should provide the same results with less code.

Answer 4

If somebody can help me get a substitute function to use that would be awesome.

Hi cokiston,

I can't prevent your crash, it can have many causes.

But your code looks quite elaborated.

In my opinion the next code has the same functionality:

Private Sub Review_Attributes_AfterUpdate()
      Me.Review_Attributes.Requery
      Me.SCAP_Countries.Enabled = (InStr(1, Me.Review_Attributes.Text, "SCAP") > 0)
      Me.SIAP_Type.Enabled = (InStr(1, Me.Review_Attributes.Text, "SIAP") > 0)
End Sub

Imb.

Answer 5

Does your code compile?

No missing reference libraries?

Are you running an ACCDB or ACCDE?