Today's Access 97 To 2000 Problem

[Rebecca Smith]

I’ve been using the code below in Access 97 for years now without any
problems at all. Unfortunately, I now have to convert this db to Access
2000. I tried using the conversion feature and that was a bust. All my
reports, queries and other forms work OK except for this routine. The first
hang-up begins with the “dbOpenSanpshot” – does this exist in 2000?
Also when I type “rstPatient” the “FindFirst” isn’t a method available to me
nor is "NoMatch. What’s the alternative?
Thanks tons,
Becky

Private Sub txtMRN_BeforeUpdate(Cancel As Integer)
'**Looks for duplicate MRN's

Dim rstPatient As Recordset
Dim strMessage As String

strMessage = "THIS MRN IS ALREADY IN THE DATABASE."
strMessage = strMessage & vbCrLf & vbCrLf & "Click OK then click ..."
strMessage = strMessage & vbCrLf & vbCrLf & "Accept to accept the MRN or
Cancel to enter a new MRN."

Set rstPatient = CurrentDb.OpenRecordset("tblShared_Patients",
dbOpenSnapshot)
rstPatient.FindFirst "MRN = '" & txtMRN & "'"

If Not rstPatient.NoMatch Then
MsgBox strMessage, vbInformation
strTargetMRN = txtMRN
With cmdAccept '**allow user to accept MRN
.Enabled = True
.Default = True
End With

cmdCancel.Enabled = True

Cancel = True
SendKeys "{Esc 3}", False
End If
rstPatient.Close
Set rstPatient = Nothing

End Sub

[Larry Linson]

In a module window, open the Tools | References. Check the DAO library. Then
qualify all the declarations of objects that are common to both, like
Recordset:

Dim rstPatient As DAO.Recordset

Just as a safety factor, you can move the DAO Reference up above the ADO
reference in the list -- in case you miss qualifying some vital object when
you declare it. Or, if you aren't going to use ADO at all, you can uncheck
the ADO library reference.

What you experienced is because, in Access 2000, only the ADO reference is
checked by default. That is true also in Access 2002, but they "smartened
up" to the fact that most people still use DAO in preference to ADO, for Jet
databases, and now, once again, check DAO by default. But, ADO is checked,
too, so you still need to qualify the references.

One question, though: why are you converting to a version of Access that is,
itself, already out of support? Why not convert to either Access 2002 or,
better, 2003 for which Microsoft support is still available should you get
"backed into a corner" and have to call them?

Larry Linson
Microsoft Access MVP


"Rebecca Smith" <rp*****@pcez.com> wrote in message
news:10*************@corp.supernews.com...

I've been using the code below in Access 97 for years now without any
problems at all. Unfortunately, I now have to convert this db to Access
2000. I tried using the conversion feature and that was a bust. All my
reports, queries and other forms work OK except for this routine. The first hang-up begins with the "dbOpenSanpshot" - does this exist in 2000?
Also when I type "rstPatient" the "FindFirst" isn't a method available to me nor is "NoMatch. What's the alternative?
Thanks tons,
Becky

Private Sub txtMRN_BeforeUpdate(Cancel As Integer)
'**Looks for duplicate MRN's

Dim rstPatient As Recordset
Dim strMessage As String

strMessage = "THIS MRN IS ALREADY IN THE DATABASE."
strMessage = strMessage & vbCrLf & vbCrLf & "Click OK then click ..."
strMessage = strMessage & vbCrLf & vbCrLf & "Accept to accept the MRN or Cancel to enter a new MRN."

Set rstPatient = CurrentDb.OpenRecordset("tblShared_Patients",
dbOpenSnapshot)
rstPatient.FindFirst "MRN = '" & txtMRN & "'"

If Not rstPatient.NoMatch Then
MsgBox strMessage, vbInformation
strTargetMRN = txtMRN
With cmdAccept '**allow user to accept MRN
.Enabled = True
.Default = True
End With

cmdCancel.Enabled = True

Cancel = True
SendKeys "{Esc 3}", False
End If
rstPatient.Close
Set rstPatient = Nothing

End Sub

[Rebecca Smith]

Larry,
IT WORKS! Thanks a bunch.

The reason for using 2000 is because that's the last update to Access I've
done at home and it's the last update the company I work for has done. Very
few people use Access (where I work) so even to have it on a machine takes
tons of request and pleading.
Becky


"Larry Linson" <bo*****@localhost.not> wrote in message
news:1oo1d.4783$MS1.3399@trnddc02...

In a module window, open the Tools | References. Check the DAO library. Then qualify all the declarations of objects that are common to both, like
Recordset:

Dim rstPatient As DAO.Recordset

Just as a safety factor, you can move the DAO Reference up above the ADO
reference in the list -- in case you miss qualifying some vital object when you declare it. Or, if you aren't going to use ADO at all, you can uncheck
the ADO library reference.

What you experienced is because, in Access 2000, only the ADO reference is
checked by default. That is true also in Access 2002, but they "smartened
up" to the fact that most people still use DAO in preference to ADO, for Jet databases, and now, once again, check DAO by default. But, ADO is checked,
too, so you still need to qualify the references.

One question, though: why are you converting to a version of Access that is, itself, already out of support? Why not convert to either Access 2002 or,
better, 2003 for which Microsoft support is still available should you get
"backed into a corner" and have to call them?

Larry Linson
Microsoft Access MVP

[Larry Linson]

There have been three Service Packs for Access 2000. There have been eight
Service Packs for the Jet 4.0 database engine. I strongly recommend you
obtain and apply all of them. Without the SPs, you may encounter some nasty
bugs that have been fixed.

Larry Linson
Microsoft Access MVP
"Rebecca Smith" <rp*****@pcez.com> wrote in message
news:10*************@corp.supernews.com...

Larry,
IT WORKS! Thanks a bunch.

The reason for using 2000 is because that's the last update to Access I've
done at home and it's the last update the company I work for has done. Very few people use Access (where I work) so even to have it on a machine takes
tons of request and pleading.
Becky


"Larry Linson" <bo*****@localhost.not> wrote in message
news:1oo1d.4783$MS1.3399@trnddc02...

In a module window, open the Tools | References. Check the DAO library.

Then

qualify all the declarations of objects that are common to both, like
Recordset:

Dim rstPatient As DAO.Recordset

Just as a safety factor, you can move the DAO Reference up above the ADO
reference in the list -- in case you miss qualifying some vital object

when

you declare it. Or, if you aren't going to use ADO at all, you can uncheck the ADO library reference.

What you experienced is because, in Access 2000, only the ADO reference is checked by default. That is true also in Access 2002, but they "smartened up" to the fact that most people still use DAO in preference to ADO, for

Jet

databases, and now, once again, check DAO by default. But, ADO is checked, too, so you still need to qualify the references.

One question, though: why are you converting to a version of Access that

is,

itself, already out of support? Why not convert to either Access 2002 or, better, 2003 for which Microsoft support is still available should you get "backed into a corner" and have to call them?

Larry Linson
Microsoft Access MVP