Deadline Today! Help with Conversion!

[S Jackson]

I don't have any excuses other than sheer ignorance but when I began
designing my application I used Access 2000. Now, of course, I am having
problems with converting the db to Access 97. I have converted it and then
gone to another user's machine with Office 97 installed to test it out and
so far, here are the errors I have encountered when trying to Compile and
Save all Modules:

1. Missing DAO Library references - this was easy to fix: unchecked the
missing library and checked the DAO version available

2. The following code on this form gives me this error: Expected Function
or Variable. The code stops at & .MoveLast. Can anyone help?

'Set variables
Dim stDocName As String
Dim stLinkCriteria As String

'Set which form to open
stDocName = "frmSurveyors"

'Set search criteria, "Jane" opens up all Surveyors with the name "Jane"
stLinkCriteria = "[SvyName] Like " & "'" & Me![txtSearch] & "*" & "'"
'Open the form in hidden view until record count complete
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden

'Count the number of records found and return number to user (if none
found-msgbox displayed)
With Forms(stDocName).RecordsetClone
If .RecordCount = 0 Then
MsgBox "The database did not find any records, please try again", ,
"Surveyor Search Results"
DoCmd.Close acForm, stDocName
Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"
Forms(stDocName).Visible = True
End If
End With

3. Switchboard problem: Compile Error: Method or data member not found.
Error occurs at Set con=Application.CurrentProgect.Connection in Private Sub
FillOptions:

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

Any help is GREATLY appreciated. Thank you in advance.
S. Jackson

 

[Cheryl Fischer]

Hi Shelly,

In Item 2 in your code:

Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

you have embedded the .MoveLast method in a string and I don't think that's
going to work for you. Try changing it to:

Else
.MoveLast
MsgBox "The database found " _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

hth,

 

[S Jackson]

Thanks Cheryl. That seems to work. Any thoughts on that switchboard
problem? I hate to have to redo the entire thing - ugh!

(I have the Access 2000 VBA Handbook by Susan Novalis, so hopefully after I
have a chance to study, I'll have a better understanding of what I am doing
here)
Thank you again.

S. Jackson

Hi Shelly,

In Item 2 in your code:

Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

you have embedded the .MoveLast method in a string and I don't think that's
going to work for you. Try changing it to:

Else
.MoveLast
MsgBox "The database found " _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I don't have any excuses other than sheer ignorance but when I began
designing my application I used Access 2000. Now, of course, I am having
problems with converting the db to Access 97. I have converted it and then
gone to another user's machine with Office 97 installed to test it out and
so far, here are the errors I have encountered when trying to Compile and
Save all Modules:

1. Missing DAO Library references - this was easy to fix: unchecked the
missing library and checked the DAO version available

2. The following code on this form gives me this error: Expected Function
or Variable. The code stops at & .MoveLast. Can anyone help?

'Set variables
Dim stDocName As String
Dim stLinkCriteria As String

'Set which form to open
stDocName = "frmSurveyors"

'Set search criteria, "Jane" opens up all Surveyors with the name "Jane"
stLinkCriteria = "[SvyName] Like " & "'" & Me![txtSearch] & "*" & "'"
'Open the form in hidden view until record count complete
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden

'Count the number of records found and return number to user (if none
found-msgbox displayed)
With Forms(stDocName).RecordsetClone
If .RecordCount = 0 Then
MsgBox "The database did not find any records, please try

again",

,
"Surveyor Search Results"
DoCmd.Close acForm, stDocName
Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"
Forms(stDocName).Visible = True
End If
End With

3. Switchboard problem: Compile Error: Method or data member not found.
Error occurs at Set con=Application.CurrentProgect.Connection in Private Sub
FillOptions:

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

Any help is GREATLY appreciated. Thank you in advance.
S. Jackson

 

[Cheryl Fischer]

Hi Shelly,

I use ADO so seldom that I rarely respond to those questions (preferring
instead to lurk about reading the answers). However, I looked at a
database of mine which does use ADO and found that I use:

Dim con As ADODB.Connection
Set con = CurrentProject.Connection

You might also want to make sure that you have a reference set to the
appropriate ADO lib(s).

hth and be understanding if this doesn't work!
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Thanks Cheryl. That seems to work. Any thoughts on that switchboard
problem? I hate to have to redo the entire thing - ugh!

(I have the Access 2000 VBA Handbook by Susan Novalis, so hopefully after I
have a chance to study, I'll have a better understanding of what I am doing
here)
Thank you again.

S. Jackson

Hi Shelly,

In Item 2 in your code:

Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

you have embedded the .MoveLast method in a string and I don't think that's
going to work for you. Try changing it to:

Else
.MoveLast
MsgBox "The database found " _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I don't have any excuses other than sheer ignorance but when I began
designing my application I used Access 2000. Now, of course, I am having
problems with converting the db to Access 97. I have converted it and then
gone to another user's machine with Office 97 installed to test it out and
so far, here are the errors I have encountered when trying to Compile and
Save all Modules:

1. Missing DAO Library references - this was easy to fix: unchecked the
missing library and checked the DAO version available

2. The following code on this form gives me this error: Expected Function
or Variable. The code stops at & .MoveLast. Can anyone help?

'Set variables
Dim stDocName As String
Dim stLinkCriteria As String

'Set which form to open
stDocName = "frmSurveyors"

'Set search criteria, "Jane" opens up all Surveyors with the name "Jane"
stLinkCriteria = "[SvyName] Like " & "'" & Me![txtSearch] & "*"

&
"'"

'Open the form in hidden view until record count complete
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden

'Count the number of records found and return number to user (if none
found-msgbox displayed)
With Forms(stDocName).RecordsetClone
If .RecordCount = 0 Then
MsgBox "The database did not find any records, please try

again",

,
"Surveyor Search Results"
DoCmd.Close acForm, stDocName
Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"
Forms(stDocName).Visible = True
End If
End With

3. Switchboard problem: Compile Error: Method or data member not found.
Error occurs at Set con=Application.CurrentProgect.Connection in

Private
Sub

FillOptions:

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

Any help is GREATLY appreciated. Thank you in advance.
S. Jackson

 

[S Jackson]

Hey Cheryl:

I might have taken the long way around, but here is what I did instead:

Created a new db in Access 97
Imported all tables and queries
Imported all frms (except Switchboard) one-at-a-time, doing a Compile and
Save after each (don't know if this was necessary, but wanted to be safe)
Imported all rpts one-at-a-time, """"
Imported all macros
Imported all Modules doing a Compile and Save after each import
Imported the Switchboard form
Renamed it Switchboard-org
Renamed the Switchboard Items Table to Switchboard Items-org
Used the wizard to create a new Switchboard, made my design changes
Deleted the Switchboard-org form
Deleted the newly created Switchboard Items table
REnamed the Switchboard Items-org to Switchboard Items
Did a Compile and Save

Viola! It worked.

Only one more problem and I am going to put it in a separate post.

Thanks for your quick reponses!

S. Jackson

Hi Shelly,

I use ADO so seldom that I rarely respond to those questions (preferring
instead to lurk about reading the answers). However, I looked at a
database of mine which does use ADO and found that I use:

Dim con As ADODB.Connection
Set con = CurrentProject.Connection

You might also want to make sure that you have a reference set to the
appropriate ADO lib(s).

hth and be understanding if this doesn't work!
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Thanks Cheryl. That seems to work. Any thoughts on that switchboard
problem? I hate to have to redo the entire thing - ugh!

(I have the Access 2000 VBA Handbook by Susan Novalis, so hopefully

after

I
have a chance to study, I'll have a better understanding of what I am doing
here)
Thank you again.

S. Jackson

Hi Shelly,

In Item 2 in your code:

Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

you have embedded the .MoveLast method in a string and I don't think that's
going to work for you. Try changing it to:

Else
.MoveLast
MsgBox "The database found " _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX


I don't have any excuses other than sheer ignorance but when I began
designing my application I used Access 2000. Now, of course, I am having
problems with converting the db to Access 97. I have converted it and
then
gone to another user's machine with Office 97 installed to test it

out
and

so far, here are the errors I have encountered when trying to

Compile
and

Save all Modules:

1. Missing DAO Library references - this was easy to fix:

unchecked
the

missing library and checked the DAO version available

2. The following code on this form gives me this error: Expected
Function
or Variable. The code stops at & .MoveLast. Can anyone help?

'Set variables
Dim stDocName As String
Dim stLinkCriteria As String

'Set which form to open
stDocName = "frmSurveyors"

'Set search criteria, "Jane" opens up all Surveyors with the name "Jane"
stLinkCriteria = "[SvyName] Like " & "'" & Me![txtSearch] &

"*"

&

"'"
'Open the form in hidden view until record count complete
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden

'Count the number of records found and return number to user (if none
found-msgbox displayed)
With Forms(stDocName).RecordsetClone
If .RecordCount = 0 Then
MsgBox "The database did not find any records, please try again",
,
"Surveyor Search Results"
DoCmd.Close acForm, stDocName
Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"
Forms(stDocName).Visible = True
End If
End With

3. Switchboard problem: Compile Error: Method or data member not
found.
Error occurs at Set con=Application.CurrentProgect.Connection in Private
Sub
FillOptions:

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

Any help is GREATLY appreciated. Thank you in advance.
S. Jackson

 

[Dan Artuso]

Hi,
Access 97 does not have:
Application.CurrentProject.Connection

That is only available in 2000 or later.

HTH
Dan Artuso., MVP

Thanks Cheryl. That seems to work. Any thoughts on that switchboard
problem? I hate to have to redo the entire thing - ugh!

(I have the Access 2000 VBA Handbook by Susan Novalis, so hopefully after I
have a chance to study, I'll have a better understanding of what I am doing
here)
Thank you again.

S. Jackson

Hi Shelly,

In Item 2 in your code:

Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

you have embedded the .MoveLast method in a string and I don't think that's
going to work for you. Try changing it to:

Else
.MoveLast
MsgBox "The database found " _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

I don't have any excuses other than sheer ignorance but when I began
designing my application I used Access 2000. Now, of course, I am having
problems with converting the db to Access 97. I have converted it and then
gone to another user's machine with Office 97 installed to test it out and
so far, here are the errors I have encountered when trying to Compile and
Save all Modules:

1. Missing DAO Library references - this was easy to fix: unchecked the
missing library and checked the DAO version available

2. The following code on this form gives me this error: Expected Function
or Variable. The code stops at & .MoveLast. Can anyone help?

'Set variables
Dim stDocName As String
Dim stLinkCriteria As String

'Set which form to open
stDocName = "frmSurveyors"

'Set search criteria, "Jane" opens up all Surveyors with the name "Jane"
stLinkCriteria = "[SvyName] Like " & "'" & Me![txtSearch] & "*"

&
"'"

'Open the form in hidden view until record count complete
DoCmd.OpenForm stDocName, , , stLinkCriteria, , acHidden

'Count the number of records found and return number to user (if none
found-msgbox displayed)
With Forms(stDocName).RecordsetClone
If .RecordCount = 0 Then
MsgBox "The database did not find any records, please try

again",

,
"Surveyor Search Results"
DoCmd.Close acForm, stDocName
Else
MsgBox "The database found " _
& .MoveLast _
& .RecordCount _
& " case(s).", vbOKOnly + vbInformation, "Surveyor Search Results"
Forms(stDocName).Visible = True
End If
End With

3. Switchboard problem: Compile Error: Method or data member not found.
Error occurs at Set con=Application.CurrentProgect.Connection in

Private
Sub

FillOptions:

' Open the table of Switchboard Items, and find
' the first item for this Switchboard Page.
Set con = Application.CurrentProject.Connection
stSql = "SELECT * FROM [Switchboard Items]"
stSql = stSql & " WHERE [ItemNumber] > 0 AND [SwitchboardID]=" &
Me![SwitchboardID]
stSql = stSql & " ORDER BY [ItemNumber];"
Set rs = CreateObject("ADODB.Recordset")
rs.Open stSql, con, 1 ' 1 = adOpenKeyset

Any help is GREATLY appreciated. Thank you in advance.
S. Jackson