access modules in access 2000 from access 97

[Ken Snell [MVP]]

For ACCESS 97 and up, you can change the code to this:

Forms(FormName)(SubFormName).Form![KM Count] = kmcount
If (kmcount <> 0) Then
Forms(FormName)(SubFormName).Form![Date Completed] = Format(Now,
"dd-mmm-yy")
End If

If your compiler is saying it cannot find a Format function, then you likely
have a references problem in the database.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Thanks for the upate. The new layout of the command appears to be the fix
I
needed. I now have a new problem for you. I am so close to finishing
this
update. Anyway, here is some code I came across this afternoon:

Forms(FormName)(SubFormName).Form![KM Count] = kmcount
If (kmcount <> 0) Then
Forms(FormName)(SubFormName).Form![Date Completed] = Format$(Now,
"dd-mmm-yy")
End If

The problem appears with the Format$ command. I am assuming that the
Format$ command came from Access 2.0 or there abouts. What I need to know
is
what is the format of this command for Access 2000, as the Format$ command
is
used alot in a few areas of this program. From what I can tell, this
command
changes the format of data. For example, the date format is changed from
the
default to the format that would be used in most databases (DD-MMM-YY).
So
what can I use in place of format$ for access 2000.

Edward Letendre.

I think I have erred.

This code line that I'd posted:
db.querydefs("Data Output Query").Delete

should be changed to this:
db.querydefs.Delete "Data Output Query"

 

[Edward Letendre]

Thanks for the reply Ken. I was able to correct my my problem by re
installing office 2000 as the utility.mda reference was missing (I saw this
when I checked the references area as shown below). The utility.mda
reference was shown as missing. I unchecked this reference and added it
again. When I added the reference, and tried to compile my application, a
function (querydefs.delete) was not found and I could not resolve this
problem, until I reinstalled office 2000 and did a compile of the full
program again after the re-install.

Now that I have the code compiled and coverted to access 2000 from access
97, I need to know how to take the complete database in access 2000 and
convert it to an access 2000 project, with the tables loaded into SQL server
7 and the forms and functions and such still in access 2000 as a project? Do
you know where I can ask about access 2000 projects and the conversion
process from access 2000 to access 2000 project?

Edward Letendre.

For ACCESS 97 and up, you can change the code to this:

Forms(FormName)(SubFormName).Form![KM Count] = kmcount
If (kmcount <> 0) Then
Forms(FormName)(SubFormName).Form![Date Completed] = Format(Now,
"dd-mmm-yy")
End If

If your compiler is saying it cannot find a Format function, then you likely
have a references problem in the database.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file version of
certain files between the machine where the application was developed, and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar. Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them, unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back out
of the dialog, then go back in and unselect the reference you just added. If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html

--

Ken Snell
<MS ACCESS MVP>

Thanks for the upate. The new layout of the command appears to be the fix
I
needed. I now have a new problem for you. I am so close to finishing
this
update. Anyway, here is some code I came across this afternoon:

Forms(FormName)(SubFormName).Form![KM Count] = kmcount
If (kmcount <> 0) Then
Forms(FormName)(SubFormName).Form![Date Completed] = Format$(Now,
"dd-mmm-yy")
End If

The problem appears with the Format$ command. I am assuming that the
Format$ command came from Access 2.0 or there abouts. What I need to know
is
what is the format of this command for Access 2000, as the Format$ command
is
used alot in a few areas of this program. From what I can tell, this
command
changes the format of data. For example, the date format is changed from
the
default to the format that would be used in most databases (DD-MMM-YY).
So
what can I use in place of format$ for access 2000.

Edward Letendre.

I think I have erred.

This code line that I'd posted:
db.querydefs("Data Output Query").Delete

should be changed to this:
db.querydefs.Delete "Data Output Query"

--

Ken Snell
<MS ACCESS MVP>

message Again, I want to thank you for your help up to this point. But I have
another problem. When I add the following code my access application
(to
be
percise to the code behind a form), as show below:

db.querydefs("Data Output Query").Delete

as shown in the code you saw before in this post, I get the following
error
message about the .Delete part of the command above:

compile error: method or data member (Delete or .Delete) not found.

The weird thing is that this error does not occur in the other 20 to 30
or
so places that I have used this code to make the changes in the past to
the
new delete method for the querydefs. Why would this delete method
cause
an
error at this point and not in other parts of the application with the
same
code? This is very weird in deed.

Edward Letendre.


:

The following code block is what I would change:

If db.QueryDefs.Count > 0 Then
For I = 0 To db.QueryDefs.Count - 1
' MsgBox db.QueryDefs(I).Name, 48, ""
If (db.QueryDefs(I).Name = "Data Output Query") Then
delquery = 1
End If
Next I
End If

If (delquery = 1) Then
DeleteQueryDef("Data Output Query")
End If


I would change it to this:

If db.QueryDefs.Count > 0 Then
For I = db.QueryDefs.Count - 1 To 0 Step -1
If (db.QueryDefs(I).Name = "Data Output Query") Then
db.QueryDefs(I).Delete
Exit For
End If
Next I
End If

 

[Ken Snell [MVP]]

There are a number of articles at MSDN site and elsewhere on "upsizing" to
SQL Server.

There also is a newsgroup for adp's:
microsoft.public.access.adp.sqlserver

--

Ken Snell
<MS ACCESS MVP>

Thanks for the reply Ken. I was able to correct my my problem by re
installing office 2000 as the utility.mda reference was missing (I saw
this
when I checked the references area as shown below). The utility.mda
reference was shown as missing. I unchecked this reference and added it
again. When I added the reference, and tried to compile my application, a
function (querydefs.delete) was not found and I could not resolve this
problem, until I reinstalled office 2000 and did a compile of the full
program again after the re-install.

Now that I have the code compiled and coverted to access 2000 from access
97, I need to know how to take the complete database in access 2000 and
convert it to an access 2000 project, with the tables loaded into SQL
server
7 and the forms and functions and such still in access 2000 as a project?
Do
you know where I can ask about access 2000 projects and the conversion
process from access 2000 to access 2000 project?

Edward Letendre.

For ACCESS 97 and up, you can change the code to this:

Forms(FormName)(SubFormName).Form![KM Count] = kmcount
If (kmcount <> 0) Then
Forms(FormName)(SubFormName).Form![Date Completed] = Format(Now,
"dd-mmm-yy")
End If

If your compiler is saying it cannot find a Format function, then you
likely
have a references problem in the database.

Try following these steps (from a post by Douglas Steele, MVP):

This can be caused by differences in either the location or file version
of
certain files between the machine where the application was developed,
and
where it's being run (or the file missing completely from the target
machine). Such differences are common when new software is installed.

On the machine(s) where it's not working, open any code module (or open
the
Debug Window, using Ctrl-G, provided you haven't selected the "keep debug
window on top" option). Select Tools | References from the menu bar.
Examine
all of the selected references.

If any of the selected references have "MISSING:" in front of them,
unselect
them, and back out of the dialog. If you really need the reference(s) you
just unselected (you can tell by doing a Compile All Modules), go back in
and reselect them.

If none have "MISSING:", select an additional reference at random, back
out
of the dialog, then go back in and unselect the reference you just added.
If
that doesn't solve the problem, try to unselect as many of the selected
references as you can (Access may not let you unselect them all), back
out
of the dialog, then go back in and reselect the references you just
unselected. (NOTE: write down what the references are before you delete
them, because they'll be in a different order when you go back in)

For far more than you could ever want to know about this problem, check
out
http://www.accessmvp.com/djsteele/AccessReferenceErrors.html