Method Not Found, Access 97/2000 Conversion

Hello Fellow New Group Folks,

Here's today's problem. I was called in to help convert an Access 97
database to Access 2000. 99% of all my Access Dev. work has occurred
in 2000, so I know very little about 97, however, from everything I
read, it sounded like a conversion from 97 to 2000 should go smoothly.
Boy was I wrong.

Here's what we got. There is a form, that lists a group of employees.
There is a command button, that when pressed, is supposed to go out and
see if there are any "results" for this particular employee. If there
isn't any results, it is supposed to prompt the user to see if they'd
like to add a result at that time. Works great in 97. Gives a method
not found. I looked at the code, with no avail. Here is the code.

************************************************** ************************************************** *******
Private Sub Results_Click()
On Error GoTo results_err
Dim a, mers As Recordset, f As Form
Dim mydb As Database, resrs As Recordset
If Me.Dirty Then DoCmd.DoMenuItem 0, 0, 4
Select Case Me!stype
Case 2:
Set mers = Me.RecordsetClone
mers.Bookmark = Me.Bookmark
If IsNull(mers.[res_no]) Or mers.[res_no] = 0 Then
a = MsgBox("There are no results yet for this appointment. Do
you wish to add some?", 36, "No results yet")
If a = 6 Then
Set mydb = CurrentDb
Set resrs = mydb.OpenRecordset("result-alcohol")
resrs.AddNew
resrs.date_reptrcv = date
mers.Edit
mers.[res_no] = resrs.result_no
mers.Update
resrs.Update
DoCmd.OpenForm "results-alcohol", , , "[result_no] = " &
CStr(mers.[res_no]), , acDialog
Me!Positive = alcres("results-alcohol")
DoCmd.Close acForm, "results-alcohol"
End If
Else
DoCmd.OpenForm "results-alcohol", , , "[result_no] = " &
CStr(mers.[res_no]), , acDialog
If Forms.[results-alcohol].cpos Then Me!Positive = 1 Else
Me!Positive = 2
DoCmd.Close acForm, "results-alcohol"
End If
Case 3:
Set mers = Me.RecordsetClone
mers.Bookmark = Me.Bookmark
If IsNull(mers.[res_no]) Or mers.[res_no] = 0 Then
a = MsgBox("There are no results yet for this appointment. Do
you wish to add some?", 36, "No results yet")
If a = 6 Then
Set mydb = CurrentDb
Set resrs = mydb.OpenRecordset("result-drug")
resrs.AddNew
resrs.date_reptrcv = date
mers.Edit
mers.[res_no] = resrs.result_no
mers.Update
resrs.Update
DoCmd.OpenForm "drug result", , , "[result_no] = " &
CStr(mers.[res_no]), , acDialog
mers.Edit
If Pos("drug result") Then mers.Positive = 1 Else mers.Positive
= 2
If susp("drug result") Or (mers.Positive = 1) Then mers.mro =
False Else mers.mro = True
mers.Update
DoCmd.Close acForm, "drug result"
End If
Else
DoCmd.OpenForm "drug result", , , "[result_no] = " &
CStr(mers.[res_no]), , acDialog
mers.Edit
If mers.Positive < 3 And Pos("drug result") Then mers.Positive =
1 Else If mers.Positive < 3 Then mers.Positive = 2
If susp("drug result") Or (mers.Positive = 1) Then mers.mro =
False Else mers.mro = True
mers.Update
DoCmd.Close acForm, "drug result"
End If

End Select

Me.Refresh
results_cont:
DoCmd.SetWarnings True: DoCmd.Hourglass False
Exit Sub
results_err:
If Err = 3260 Then
MsgBox "This data is currently locked out. Hit Enter to try again"
DoEvents
Resume
End If

msgdisp
GoTo results_cont
End Sub
************************************************** ************************************************** *****

It fails on this line, so far.
If IsNull(mers.[res_no]) Or mers.[res_no] = 0 Then
What I don't understand is what "method" in 97 is res_no? I was
thinking that the brackets would indicate that it is a field, but there
is no object in the database with that name.

I also know that it fails on this lines as well:
resrs.date_reptrcv = date
and what is date_reptrcv

Any help would be appreciated. If anyone has a utility or something
that helps sift thru this code and made recommendations for changes,
that is helpful also.

No matter what, this group has always been good to help out. Any help
is appreciated.

Have you checked the code references?

Cy wrote:[color=blue]
> Hello Fellow New Group Folks,
>
> Here's today's problem. I was called in to help convert an Access 97
> database to Access 2000. 99% of all my Access Dev. work has occurred
> in 2000, so I know very little about 97, however, from everything I
> read, it sounded like a conversion from 97 to 2000 should go smoothly.
> Boy was I wrong.
>
> Here's what we got. There is a form, that lists a group of employees.
> There is a command button, that when pressed, is supposed to go out
> and see if there are any "results" for this particular employee. If
> there isn't any results, it is supposed to prompt the user to see if
> they'd like to add a result at that time. Works great in 97. Gives
> a method not found. I looked at the code, with no avail. Here is
> the code.
>
> ************************************************** ************************************************** *******
> Private Sub Results_Click()
> On Error GoTo results_err
> Dim a, mers As Recordset, f As Form
> Dim mydb As Database, resrs As Recordset
> If Me.Dirty Then DoCmd.DoMenuItem 0, 0, 4
> Select Case Me!stype
> Case 2:
> Set mers = Me.RecordsetClone
> mers.Bookmark = Me.Bookmark
> If IsNull(mers.[res_no]) Or mers.[res_no] = 0 Then
> a = MsgBox("There are no results yet for this appointment. Do
> you wish to add some?", 36, "No results yet")
> If a = 6 Then
> Set mydb = CurrentDb
> Set resrs = mydb.OpenRecordset("result-alcohol")
> resrs.AddNew
> resrs.date_reptrcv = date
> mers.Edit
> mers.[res_no] = resrs.result_no
> mers.Update
> resrs.Update
> DoCmd.OpenForm "results-alcohol", , , "[result_no] = " &
> CStr(mers.[res_no]), , acDialog
> Me!Positive = alcres("results-alcohol")
> DoCmd.Close acForm, "results-alcohol"
> End If
> Else
> DoCmd.OpenForm "results-alcohol", , , "[result_no] = " &
> CStr(mers.[res_no]), , acDialog
> If Forms.[results-alcohol].cpos Then Me!Positive = 1 Else
> Me!Positive = 2
> DoCmd.Close acForm, "results-alcohol"
> End If
> Case 3:
> Set mers = Me.RecordsetClone
> mers.Bookmark = Me.Bookmark
> If IsNull(mers.[res_no]) Or mers.[res_no] = 0 Then
> a = MsgBox("There are no results yet for this appointment. Do
> you wish to add some?", 36, "No results yet")
> If a = 6 Then
> Set mydb = CurrentDb
> Set resrs = mydb.OpenRecordset("result-drug")
> resrs.AddNew
> resrs.date_reptrcv = date
> mers.Edit
> mers.[res_no] = resrs.result_no
> mers.Update
> resrs.Update
> DoCmd.OpenForm "drug result", , , "[result_no] = " &
> CStr(mers.[res_no]), , acDialog
> mers.Edit
> If Pos("drug result") Then mers.Positive = 1 Else mers.Positive
> = 2
> If susp("drug result") Or (mers.Positive = 1) Then mers.mro =
> False Else mers.mro = True
> mers.Update
> DoCmd.Close acForm, "drug result"
> End If
> Else
> DoCmd.OpenForm "drug result", , , "[result_no] = " &
> CStr(mers.[res_no]), , acDialog
> mers.Edit
> If mers.Positive < 3 And Pos("drug result") Then mers.Positive =
> 1 Else If mers.Positive < 3 Then mers.Positive = 2
> If susp("drug result") Or (mers.Positive = 1) Then mers.mro =
> False Else mers.mro = True
> mers.Update
> DoCmd.Close acForm, "drug result"
> End If
>
>
> End Select
>
> Me.Refresh
> results_cont:
> DoCmd.SetWarnings True: DoCmd.Hourglass False
> Exit Sub
> results_err:
> If Err = 3260 Then
> MsgBox "This data is currently locked out. Hit Enter to try again"
> DoEvents
> Resume
> End If
>
> msgdisp
> GoTo results_cont
> End Sub
> ************************************************** ************************************************** *****
>
> It fails on this line, so far.
> If IsNull(mers.[res_no]) Or mers.[res_no] = 0 Then
> What I don't understand is what "method" in 97 is res_no? I was
> thinking that the brackets would indicate that it is a field, but
> there is no object in the database with that name.
>
> I also know that it fails on this lines as well:
> resrs.date_reptrcv = date
> and what is date_reptrcv
>
> Any help would be appreciated. If anyone has a utility or something
> that helps sift thru this code and made recommendations for changes,
> that is helpful also.
>
> No matter what, this group has always been good to help out. Any help
> is appreciated.[/color]

--
regards,

Br@dley

If you are referring to Tools, Reference? Yes. It has DAO 3.6
checked. I did a comparison from the 97 to 2000 and they look the same.

On 26 Jan 2006 16:18:49 -0800, "Cy" <googlejunk@computunity.com> wrote:
[color=blue]
>Hello Fellow New Group Folks,
>
>Here's today's problem. I was called in to help convert an Access 97
>database to Access 2000. 99% of all my Access Dev. work has occurred
>in 2000, so I know very little about 97, however, from everything I
>read, it sounded like a conversion from 97 to 2000 should go smoothly.
>Boy was I wrong.
>
>Here's what we got. There is a form, that lists a group of employees.
>There is a command button, that when pressed, is supposed to go out and
>see if there are any "results" for this particular employee. If there
>isn't any results, it is supposed to prompt the user to see if they'd
>like to add a result at that time. Works great in 97. Gives a method
>not found. I looked at the code, with no avail. Here is the code.
>
>************************************************* ************************************************** ********
>Private Sub Results_Click()
>On Error GoTo results_err
> Dim a, mers As Recordset, f As Form
> Dim mydb As Database, resrs As Recordset
> If Me.Dirty Then DoCmd.DoMenuItem 0, 0, 4
> Select Case Me!stype
> Case 2:
> Set mers = Me.RecordsetClone
> mers.Bookmark = Me.Bookmark
> If IsNull(mers.[res_no]) Or mers.[res_no] = 0 Then
> a = MsgBox("There are no results yet for this appointment. Do
>you wish to add some?", 36, "No results yet")
> If a = 6 Then
> Set mydb = CurrentDb
> Set resrs = mydb.OpenRecordset("result-alcohol")
> resrs.AddNew
> resrs.date_reptrcv = date
> mers.Edit
> mers.[res_no] = resrs.result_no
> mers.Update
> resrs.Update
> DoCmd.OpenForm "results-alcohol", , , "[result_no] = " &
>CStr(mers.[res_no]), , acDialog
> Me!Positive = alcres("results-alcohol")
> DoCmd.Close acForm, "results-alcohol"
> End If
> Else
> DoCmd.OpenForm "results-alcohol", , , "[result_no] = " &
>CStr(mers.[res_no]), , acDialog
> If Forms.[results-alcohol].cpos Then Me!Positive = 1 Else
>Me!Positive = 2
> DoCmd.Close acForm, "results-alcohol"
> End If
> Case 3:
> Set mers = Me.RecordsetClone
> mers.Bookmark = Me.Bookmark
> If IsNull(mers.[res_no]) Or mers.[res_no] = 0 Then
> a = MsgBox("There are no results yet for this appointment. Do
>you wish to add some?", 36, "No results yet")
> If a = 6 Then
> Set mydb = CurrentDb
> Set resrs = mydb.OpenRecordset("result-drug")
> resrs.AddNew
> resrs.date_reptrcv = date
> mers.Edit
> mers.[res_no] = resrs.result_no
> mers.Update
> resrs.Update
> DoCmd.OpenForm "drug result", , , "[result_no] = " &
>CStr(mers.[res_no]), , acDialog
> mers.Edit
> If Pos("drug result") Then mers.Positive = 1 Else mers.Positive
>= 2
> If susp("drug result") Or (mers.Positive = 1) Then mers.mro =
>False Else mers.mro = True
> mers.Update
> DoCmd.Close acForm, "drug result"
> End If
> Else
> DoCmd.OpenForm "drug result", , , "[result_no] = " &
>CStr(mers.[res_no]), , acDialog
> mers.Edit
> If mers.Positive < 3 And Pos("drug result") Then mers.Positive =
>1 Else If mers.Positive < 3 Then mers.Positive = 2
> If susp("drug result") Or (mers.Positive = 1) Then mers.mro =
>False Else mers.mro = True
> mers.Update
> DoCmd.Close acForm, "drug result"
> End If
>
>
> End Select
>
>Me.Refresh
>results_cont:
> DoCmd.SetWarnings True: DoCmd.Hourglass False
> Exit Sub
>results_err:
> If Err = 3260 Then
> MsgBox "This data is currently locked out. Hit Enter to try again"
> DoEvents
> Resume
> End If
>
> msgdisp
> GoTo results_cont
>End Sub
>************************************************* ************************************************** ******
>
>It fails on this line, so far.
> If IsNull(mers.[res_no]) Or mers.[res_no] = 0 Then
>What I don't understand is what "method" in 97 is res_no? I was
>thinking that the brackets would indicate that it is a field, but there
>is no object in the database with that name.
>
>I also know that it fails on this lines as well:
> resrs.date_reptrcv = date
>and what is date_reptrcv
>
>Any help would be appreciated. If anyone has a utility or something
>that helps sift thru this code and made recommendations for changes,
>that is helpful also.
>
>No matter what, this group has always been good to help out. Any help
>is appreciated.[/color]

Things to check -

Any missing references?

Make sure a reference is set to the DAO 3.6 Object Library.

If this is a completed database with no future expansions expected, remove the reference to Active x Data Objects (ADO).

If it possible that there will be further work done on the database, leave the reference to ADO but put the DAO 3.6
reference above it in the reference list. (higher priority).
You should also then specifically qualify any recordsets dimmed in the database as DAO.Recordset. This is because
recordsets are a member of both the DAO and ADO object libraries. If the variables aren't qualified the library with the
highest priority will be used. This is the likely cause of yoour problems.

You can do a Find/Replace on the current project to replace all instances of "As Recordset" with "As DAO.Recordet".

Wayne Gillespie
Gosford NSW Australia

Wayne,

Thanks. I have already moved the DAO up in the list. I did also, just
minutes ago, added the dao.recordset, but no luck.

Any other ideas?

Cy wrote:[color=blue]
> If you are referring to Tools, Reference? Yes. It has DAO 3.6
> checked. I did a comparison from the 97 to 2000 and they look the
> same.[/color]

I should have read it more closely....
[color=blue]
> If IsNull(mers.[res_no]) Or mers.[res_no] = 0[/color]

Should be...

If IsNull(mers![res_no]) Or mers![res_no] = 0

Newer versions of Access are stricter on the use of the operators. The Dot
operator is for methods, not fields. (Hence, the "method not found" error)
--
regards,

Br@dley

Make sure you change all of your DAO declarations to include the DAO part of
the statement, this would go for ALL recordsets, database, querydef, and
field definitions, plus some others. The Recordset one is the most critical
and common.

Next, if you've done that and you've check the reference for the DAO 3.6
Object Library, there may be another reference that is broken. A broken
reference can cause problems with items that have nothing to do with the
broken reference. For more information of check for this and fixing it, see
this page:

http://www.allenbrowne.com/ser-38.html

--
Wayne Morgan
MS Access MVP

"Cy" <googlejunk@computunity.com> wrote in message
news:1138322971.992523.63100@g49g2000cwa.googlegro ups.com...[color=blue]
> Wayne,
>
> Thanks. I have already moved the DAO up in the list. I did also, just
> minutes ago, added the dao.recordset, but no luck.
>
> Any other ideas?
>[/color]

Thanks for you help...
I was able to correct all the problems, but one now...

If mers!Positive < 3 And Pos("drug result") Then mers!Positive =
1 Else If mers!Positive < 3 Then mers.Positive = 2
If susp("drug result") Or (mers.Positive = 1) Then mers.mro =
False Else mers.mro = True

else if mers!positive < 3 gives me a method or data member not
found...thoughts?

Never mind, figured it out.

Thank you very much for your help

Wayne, much appreciated.

"Cy" <googlejunk@computunity.com> wrote in
news:1138321129.907868.276660@g49g2000cwa.googlegr oups.com:
[color=blue]
> I also know that it fails on this lines as well:
> resrs.date_reptrcv = date
> and what is date_reptrcv[/color]

Since resrs is a DAO recordset, I don't have a clue as to why the
code works in A97, either, as you can't use dot notation for fields
in a recordset. You have to either use resrs("fieldname") or
resrs!fieldname.

Try changing all your resrs.fieldname references to resrs!fieldname.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Thanks David,

The suggestions from Wayne and Br@dley worked great. Yours is the
same, as what I did to "resolve" my issue.

Appreciate your help.

Ron

Cy wrote:[color=blue]
> Thanks for you help...
> I was able to correct all the problems, but one now...
>
> If mers!Positive < 3 And Pos("drug result") Then mers!Positive =
> 1 Else If mers!Positive < 3 Then mers.Positive = 2
> If susp("drug result") Or (mers.Positive = 1) Then mers.mro =
> False Else mers.mro = True
>
> else if mers!positive < 3 gives me a method or data member not
> found...thoughts?[/color]

Isn't it "ElseIf", not "Else If" ie. no space?
--
regards,

Br@dley