Debugs

[Mandy]

Hi Guys,
Please I need help to debug the following code, intending
to check in the main form whether there is double entry of
data, but when I try to run it, it says Syntax error...How
to fix it. I am using the MicrosoftR Access 2000(9.0 3B21
SR-1). Thanks a lot.

Dim ID As String
ID = InputBox("Please enter patient's ID number")

If ID = "" Then
Exit Sub
End If


Dim stDocName As String
stDocName = "<Parkinson data entry>"

Dim pat_data As New ADODB.Recordset
pat_data.Open "[data entry]",
(###THIS IS SHADED, and Syntax error appeared here###)
CurrentProject.Connection, adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<data entry>]![ID] = ID
Forms![<data entry>].Dirty = False
Else
Dim create_new As Integer
create_new = MsgBox("A person with the ID you entered
already
exists
in the database. Would you like to edit this entry?",
vbYesNo +
vbQuestion, "Patient record already exists")
If (create_new = 6) Then
Dim stLinkCriteria As String
stLinkCriteria = "[ID]='" & ID &
"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If

HELP!!!! PLEASE!!!!! MANY THANKS!!!!!!!!! Mandy

 

[Fred D]

Hi Mandy,
My first guess would be that you haven't declared a
connection object. It's been a long time since I worked
with .adp Anyway, a couple of things you can try:

Dim cnn as ADODB.Connection
cnn.connection = CurrentProject.Connection

Dim stDocName As String
stDocName = "<Parkinson data entry>"

Dim pat_data As New ADODB.Recordset
pat_data.Open "[data entry]", cnn, adOpenStatic

pat_data.Find "[ID]='" & ID & "'"

......

If that doesn't quite work, try

Dim cnn as NEW Adodb.connection

I can't remember if you need to declare a new instance of
the connection object when you're using
CurrentProject.connection

Good luck,

Fred D

 

[Fred D]

Looking at this some more and wishing I still had a
computer here with Access 2k on it.

BUT, if the connection thing doesn't work. There are two
other things that might be causing the problem in the
recordset.open line:

Try using adOpenDynamic instead of adOpenStatic.

It's been a while, but I think I recall ADO being unhappy
about trying to open a table directly with a static
cursor. So, your line

pat_data.Open "[data entry]", CurrentProject.Connection,
adOpenStatic

would look like this instead:

pat_data.open "[data entry]", CurrentProject.Connection,
adOpenDynamic

I'm not fond of opening a recordset using adOpenDynamic,
if I don't plan on changing the data, but you can set the
locks to readonly to keep it from being modified. To do
so, you'd take it one step further, you'd write the code
as:

pat_data.open "[data entry]", CurrentProject.Connection,
adOpenDynamic, adLockReadOnly

If you are planning on modifying the data, you can omit
this argument or choose one of the other locking styles.
adLockOptimistic, for instance.


Hope that helps,

Fred D.

-----Original Message-----
Hi Mandy,
My first guess would be that you haven't declared a
connection object. It's been a long time since I worked
with .adp Anyway, a couple of things you can try:

Dim cnn as ADODB.Connection
cnn.connection = CurrentProject.Connection

Dim stDocName As String
stDocName = "<Parkinson data entry>"

Dim pat_data As New ADODB.Recordset
pat_data.Open "[data entry]", cnn, adOpenStatic

pat_data.Find "[ID]='" & ID & "'"

......

If that doesn't quite work, try

Dim cnn as NEW Adodb.connection

I can't remember if you need to declare a new instance of
the connection object when you're using
CurrentProject.connection

Good luck,

Fred D

-----Original Message-----
Hi Guys,
Please I need help to debug the following code, intending
to check in the main form whether there is double entry of
data, but when I try to run it, it says Syntax error...How
to fix it. I am using the MicrosoftR Access 2000(9.0 3B21
SR-1). Thanks a lot.

Dim ID As String
ID = InputBox("Please enter patient's ID number")

If ID = "" Then
Exit Sub
End If


Dim stDocName As String
stDocName = "<Parkinson data entry>"

Dim pat_data As New ADODB.Recordset
pat_data.Open "[data entry]",
(###THIS IS SHADED, and Syntax error appeared here###)
CurrentProject.Connection, adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<data entry>]![ID] = ID
Forms![<data entry>].Dirty = False
Else
Dim create_new As Integer
create_new = MsgBox("A person with the ID you entered
already
exists
in the database. Would you like to edit this entry?",
vbYesNo +
vbQuestion, "Patient record already exists")
If (create_new = 6) Then
Dim stLinkCriteria As String
stLinkCriteria = "[ID]='" & ID &
"'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If
End If

HELP!!!! PLEASE!!!!! MANY THANKS!!!!!!!!! Mandy
.

.