Method Not Found, Access 97/2000 Conversion

**Br@dley** · Jan 27 '06, 12:55 AM

Re: Method Not Found, Access 97/2000 Conversion

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.DoMenuIte m 0, 0, 4
> Select Case Me!stype
> Case 2:
> Set mers = Me.RecordsetClo ne
> 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.OpenRecord set("result-alcohol")
> resrs.AddNew
> resrs.date_rept rcv = 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.RecordsetClo ne
> 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.OpenRecord set("result-drug")
> resrs.AddNew
> resrs.date_rept rcv = 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.SetWarnin gs 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_rept rcv = 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

Cy · Jan 27 '06, 12:55 AM

Re: Method Not Found, Access 97/2000 Conversion

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.

**Wayne Gillespie** · Jan 27 '06, 12:55 AM

Re: Method Not Found, Access 97/2000 Conversion

On 26 Jan 2006 16:18:49 -0800, "Cy" <googlejunk@com putunity.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.DoMenuIte m 0, 0, 4
> Select Case Me!stype
> Case 2:
> Set mers = Me.RecordsetClo ne
> 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.OpenRecord set("result-alcohol")
> resrs.AddNew
> resrs.date_rept rcv = 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.RecordsetClo ne
> 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.OpenRecord set("result-drug")
> resrs.AddNew
> resrs.date_rept rcv = 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.SetWarnin gs 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_rept rcv = 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

Cy · Jan 27 '06, 01:05 AM

Re: Method Not Found, Access 97/2000 Conversion

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?

**Br@dley** · Jan 27 '06, 01:35 AM

Re: Method Not Found, Access 97/2000 Conversion

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

**Wayne Morgan** · Jan 27 '06, 03:35 AM

Re: Method Not Found, Access 97/2000 Conversion

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:

Microsoft Access tips: Solving Problems with Library References

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

How to identify and solve problems with VBA references in a Microsoft Access database. Includes a listing of the libraries needed for each version of Access.

--
Wayne Morgan
MS Access MVP

"Cy" <googlejunk@com putunity.com> wrote in message
news:1138322971 .992523.63100@g 49g2000cwa.goog legroups.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]

Cy · Jan 27 '06, 04:35 AM

Re: Method Not Found, Access 97/2000 Conversion

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...thought s?

Cy · Jan 27 '06, 04:35 AM

Re: Method Not Found, Access 97/2000 Conversion

Never mind, figured it out.

Thank you very much for your help

Cy · Jan 27 '06, 04:35 AM

Re: Method Not Found, Access 97/2000 Conversion

Wayne, much appreciated.

**David W. Fenton** · Jan 27 '06, 02:15 PM

Re: Method Not Found, Access 97/2000 Conversion

"Cy" <googlejunk@com putunity.com> wrote in
news:1138321129 .907868.276660@ g49g2000cwa.goo glegroups.com:
[color=blue]
> I also know that it fails on this lines as well:
> resrs.date_rept rcv = 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("fieldnam e") 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/

Cy · Jan 27 '06, 10:15 PM

Re: Method Not Found, Access 97/2000 Conversion

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

**Br@dley** · Jan 28 '06, 10:15 PM

Re: Method Not Found, Access 97/2000 Conversion

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...thought s?[/color]

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

Br@dley

Method Not Found, Access 97/2000 Conversion

Method Not Found, Access 97/2000 Conversion

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment