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. 12 2226
Have you checked the code references?
Cy wrote: 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.
--
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" <go********@computunity.com> wrote: 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.
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: 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.
I should have read it more closely....
If IsNull(mers.[res_no]) Or mers.[res_no] = 0
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" <go********@computunity.com> wrote in message
news:11*********************@g49g2000cwa.googlegro ups.com... 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?
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
"Cy" <go********@computunity.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com: I also know that it fails on this lines as well: resrs.date_reptrcv = date and what is date_reptrcv
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: 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?
Isn't it "ElseIf", not "Else If" ie. no space?
--
regards,
Br@dley This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: chicha |
last post by:
Hey people,
I have to convert MS Access 2000 database into mysql database, the whole
thing being part of this project I'm doing for one of my faculty
classes. My professor somehow presumed I...
|
by: Chuck Grimsby |
last post by:
(Repost, due to lack of submissions...)
The Microsoft Access Product Group (the people who build Microsoft
Access) want your help!
One of the main things we're working on for the near future...
|
by: bala |
last post by:
hi access gurus
would appreciate if u can give me pointers regarding conversion of ms
access 97 application to ms access 2000, like what are the problems to
be expected and how to handle it.
...
|
by: Derek Riley |
last post by:
I have been using Access97 for some time now and decided to upgrade to 2000,
the problem is when I convert it to 2000 I get the following message "There
were compilation errors during the...
|
by: Jim M |
last post by:
For about a year and a half now I have been working in Access 2002 at
home and converting to Access 2000 for work (I need both versions). I
made a few changes to forms and queries then converted to...
|
by: deko |
last post by:
Do I get more scalability if I split my database? The way I calculate
things now, I'll be lucky to get 100,000 records in my Access 2003 mdb.
Here some math:
Max mdb/mde size = 2000 x 1024 =...
|
by: Greg Strong |
last post by:
Hello All,
Hello All,
What are the ToDo's / Gotchas to convert an MDB Access 2K2 database to
an Access Project (i.e. ADP) file for the front end using Microsoft
SQL Server 2000 Desktop Engine...
|
by: JohnR |
last post by:
From reading the documentation, this should be a relatively easy thing. I
have an arraylist of custom class instances which I want to search with
an"indexof" where I'm passing an instance if the...
|
by: ncsthbell |
last post by:
I have an access app that is in version 2000. We are upgrading to 2007 and I have made all the changes for the 2000 app to run under 2007 in Runtime Access. This app is basically a 'skeleton' in...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: jfyes |
last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| | |