Module Error : Ok in 97, Error in 2000

[charcurley]

I have been given a database to go over because of an error that keeps occuring after the installation of Windows 2000. This was written in Access 97 and works fine in Access 97. When you open it in Access 2000 or 2002 an error occurs and I think that the Module below may be the culprit. Basically here is what is suppose to happen and what is happening:
You click into a field on a form. If the field is null another form pops up with only a listbox and OK and Cancel buttons. You make your selection(s) and click ok. In Access 97 the info you selected writes to the field in the Initial Form. But in 2000 it will not, you have to close the Initial Form before you can close out the FrmPhraseLookup. Before I spend more time on this issue is there anyone out there that might be able to look at the module and know what is causing this?
Thanks for any help.
Charlene


Function ShowChosen() As String

Dim varPhrase As Variant
Dim strtemp As String



'-- for each of the items in the ItemsSelected collection

For Each varPhrase In Forms![FrmPhraseLookup].LstPhrasePicks.ItemsSelected()

'-- If not the first item, put a carriage return line feed
'-- in front of the item
If Len(strtemp) <> 0 Then
If InStr(1, &quot;:;.&quot;, Mid$(strtemp, Len(strtemp), 1), 1) = 0 Then
strtemp = strtemp & &quot;, &quot;
Else
strtemp = strtemp & &quot; &quot;
End If
If Mid$(Forms![FrmPhraseLookup].LstPhrasePicks.Column(1, varPhrase), 1, 3) = &quot;and&quot; Then
strtemp = Mid$(strtemp, 1, Len(strtemp) - 2)
strtemp = strtemp & &quot; &quot;
End If
If Mid$(Forms![FrmPhraseLookup].LstPhrasePicks.Column(1, varPhrase), 1, 1) = &quot;-&quot; Then
If Mid$(strtemp, Len(strtemp) - 1, 1) = &quot;,&quot; Then
strtemp = Mid$(strtemp, 1, Len(strtemp) - 2)
End If
strtemp = strtemp & Chr(13) & Chr(10)
End If
End If

'-- Grab the second column over of the current item

strtemp = strtemp & Forms![FrmPhraseLookup].LstPhrasePicks.Column(1, varPhrase)

Next varPhrase

'-- Assign the final string pass it back
If Len(strtemp) > 0 Then
If Mid$(strtemp, Len(strtemp), 1) <> &quot;.&quot; Then
strtemp = strtemp & &quot;.&quot;
End If
strtemp = UCase(Mid$(strtemp, 1, 1)) + Mid$(strtemp, 2, Len(strtemp) - 1)
End If

ShowChosen = strtemp

End Function

 

[vbajock]

Have you applied all the service packs to 2k?

 

[charcurley]

I'll ask the person that set up the machine. I have not even been to the site. mdb file was emailed.

 

[charcurley]

I do have all the latest updates on my Access 2000 and 2002. Sorry if I misunderstood the question.

 

[beetee]

I suggest you make very sure the module is indeed what is at fault prior to pointing fingers.

Can you verify that the SQL produced by the two modules is different?

btw, I recently ported two large, complex 97 apps to 2002 (XP) and ran into very few difficulties

1) If you try to close some of the new pre-defined toolbars (e.g. Task Pane, Clipboard), Access crashes. Kablooeey.

2) The Access exe moved from
C:\Program Files\Microsoft Office\Office\MSACCESS.EXE to
C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE
I should have used
ProgName = SysCmd(acSysCmdAccessDir) & &quot;MsAccess.exe&quot;
in the first place.

That was it. However, I didn't do *exactly* what you are doing.

The best I can suggest is to try to print out some debug information at various steps of the way in the two programs.

Good luck!

 

[charcurley]

I have pinpointed the module. Thanks for answering. They are willing to use Access 97 on this machine. But the person that set up the machine said they had installed 97 into a separate directory and it did not work. So now I'm wondering if there is an issue using Access 97 on a Windows 2000 machine. I was using ME and installed both 97 and 2000 and this program worked with no problem.

 

[vbajock]

If you think you have some type of corruption problem, create an empty mdb and import all the objects from the suspect mdb and then use the new mdb. This works 99% of the time, and can also tell you what object is corrupt. I've seen form corruption on converted 97 mdbs, so tbis could be the cause and I would bet on that before any &quot;module&quot; corruption. If you want to go one step further, create a new form in design view, and copy and paste the controls and code from the suspect form into it until you have a clone of the suspect form. Rename the original and then rename the clone to the original.

Looking at your code, if it worked in 97 it should work in 2000.
The only thing I might play with in the code would be the ! operators on
Forms!FrmPhraseLookup.LstPhrasePicks.Column(1, varPhrase)
might try
forms!FrmPhraseLookup!LstPhrasePicks.Column(1, varPhrase)

 

[charcurley]

Did that first. Created a blank and inserted. Same thing. Just updated the code with your suggestion and no go. This has me stumped, appreciate the help!

 

[charcurley]

Going to try the recreation of the form suggestion now. I have done similar in the past with reports.

 

[vbajock]

it might help a lot if you add an error handler.

'right after Dims
On error goto Err_h


'At bottom of proc right before End Function

Err_h:

msgbox str$(err)+&quot; &quot;+error$
stop

End Function

You might trap something

 

[gavinjb]

not sure if this helps, but I have had simular problems before with access functions reporting errors in 2k and not in 97 and this was due to an invalid reference in access 2k, sometimes caused by access declaring a DAO recordset as:

Dim rsDAO as Recordset

instead of

Dim rsDAO as DAO.Recordset

in another function.