help to fix error of this code

M

Mandy

Hi Guys,
I have problem with this code intending to check if there
is double entry of same ID.
Each time I run this code, syntax error arises, as shown
below. I am using Access 2000.
Please help solving this problem.
Many Thanks!!!!!!!!!!


Dim ID As String
ID = InputBox("Please enter the 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]",
***the above line is shaded and syntax error appears****
CurrentProject.Connection, adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name]![ID] = ID
Forms![<form name>].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
 
G

Guest

Hi Van T. Dinh,
Thankyou for the reply.
[data entry] refers to the table with the IDs that is
going to call to compare with the newly entered ID, to see
if it is already entered into the database [data entry].
looking forward for the solution.
Thanks again
Mandy
-----Original Message-----
.... and what is [data entry]?

--
HTH
Van T. Dinh
MVP (Access)



Mandy said:
Hi Guys,
I have problem with this code intending to check if there
is double entry of same ID.
Each time I run this code, syntax error arises, as shown
below. I am using Access 2000.
Please help solving this problem.
Many Thanks!!!!!!!!!!


Dim ID As String
ID = InputBox("Please enter the 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]",
***the above line is shaded and syntax error appears****
CurrentProject.Connection, adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name]![ID] = ID
Forms![<form name>].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


.
 
V

Van T. Dinh

Try without the square brackets ...

--
HTH
Van T. Dinh
MVP (Access)




Hi Van T. Dinh,
Thankyou for the reply.
[data entry] refers to the table with the IDs that is
going to call to compare with the newly entered ID, to see
if it is already entered into the database [data entry].
looking forward for the solution.
Thanks again
Mandy
-----Original Message-----
.... and what is [data entry]?

--
HTH
Van T. Dinh
MVP (Access)



Mandy said:
Hi Guys,
I have problem with this code intending to check if there
is double entry of same ID.
Each time I run this code, syntax error arises, as shown
below. I am using Access 2000.
Please help solving this problem.
Many Thanks!!!!!!!!!!


Dim ID As String
ID = InputBox("Please enter the 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]",
***the above line is shaded and syntax error appears****
CurrentProject.Connection, adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name]![ID] = ID
Forms![<form name>].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


.
 
G

Guest

Sorry Van T. Dinh, it still does not work without the
square brackets, still same error appears...
Any other solution?
Thanks
Mandy
-----Original Message-----
Try without the square brackets ...

--
HTH
Van T. Dinh
MVP (Access)




Hi Van T. Dinh,
Thankyou for the reply.
[data entry] refers to the table with the IDs that is
going to call to compare with the newly entered ID, to see
if it is already entered into the database [data entry].
looking forward for the solution.
Thanks again
Mandy
-----Original Message-----
.... and what is [data entry]?

--
HTH
Van T. Dinh
MVP (Access)



Hi Guys,
I have problem with this code intending to check if there
is double entry of same ID.
Each time I run this code, syntax error arises, as shown
below. I am using Access 2000.
Please help solving this problem.
Many Thanks!!!!!!!!!!


Dim ID As String
ID = InputBox("Please enter the 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]",
***the above line is shaded and syntax error appears****
CurrentProject.Connection, adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name]![ID] = ID
Forms![<form name>].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



.


.
 
V

Van T. Dinh

I am not sure whether it is because of the newsgroup word-wrap or not but:

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

should be all on one line, i.e. the whole thing is one VBA statement.
 
G

Guest

Yes, when it is in the same sentence, no more syntax
error, Thanks.

But then when I run the following code,
Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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 "[Parkinson data entry]",
CurrentProject.Connection, adOpenStatic
#####The above sentence which is on one line, is shaded
again with message:Run time error'2147217900(80040e14)'####
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name>]![ID] = ID
Forms![<form name>].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

End Sub

Please help, I hope this is the last error message!!
Thanks!!!!
Mandy


-----Original Message-----
I am not sure whether it is because of the newsgroup word- wrap or not but:

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

should be all on one line, i.e. the whole thing is one VBA statement.

--
HTH
Van T. Dinh
MVP (Access)




Sorry Van T. Dinh, it still does not work without the
square brackets, still same error appears...
Any other solution?
Thanks
Mandy


.
 
V

Van T. Dinh

The syntax looks correct but you need to check "Parkinson Data Entry".

I NEVER used spaces or special characters in Access Object names so I never
had to use the square brackets. Spaces and special characters only make
things more complex so I avoid them.

If "Parkinson Data Entry" is a proper data source name (Table / Query), try
without the square brackets.
 
T

Tom Wickerath

Hi Mandy,

I just created a test database that mimics your design. It includes a table named "Data Entry"
with a text field named "ID". I am able to run your code without generating Run time error
'2147217900 (80040e14) on the line that you indicate below.

You may have some problem with your MDAC (Microsoft Data Access Components) installation. I
recommend downloading the MDAC Component Checker tool and running it on your PC. If it
identifies any problems, correct those first. See the following KB article:

http://support.microsoft.com/?id=307255


Tom
___________________________________


Yes, when it is in the same sentence, no more syntax
error, Thanks.

But then when I run the following code,
Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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 "[Parkinson data entry]",
CurrentProject.Connection, adOpenStatic
#####The above sentence which is on one line, is shaded
again with message:Run time error'2147217900(80040e14)'####
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name>]![ID] = ID
Forms![<form name>].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

End Sub

Please help, I hope this is the last error message!!
Thanks!!!!
Mandy


-----Original Message-----
I am not sure whether it is because of the newsgroup word- wrap or not but:

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

should be all on one line, i.e. the whole thing is one VBA statement.

--
HTH
Van T. Dinh
MVP (Access)




Sorry Van T. Dinh, it still does not work without the
square brackets, still same error appears...
Any other solution?
Thanks
Mandy


.
 
G

Guest

Hi Tom,
I tried the following in another computer with some
changes of the bracket etc with trial and error. It works
in the sense that no more error message. But a new form
pop up whether the ID is a new one or is already existed
in the data base. What happen?

Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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", CurrentProject.Connection,
adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![Parkinson data entry]![ID] = ID
Forms![Parkinson 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

End Sub

Thankyou again for the effort, I will find out if
something is wrong in my lap top.
Thanks!!!
-----Original Message-----
Hi Mandy,

I just created a test database that mimics your design.
It includes a table named "Data Entry"
with a text field named "ID". I am able to run your code
without generating Run time error
'2147217900 (80040e14) on the line that you indicate below.

You may have some problem with your MDAC (Microsoft Data
Access Components) installation. I
recommend downloading the MDAC Component Checker tool and running it on your PC. If it
identifies any problems, correct those first. See the following KB article:

http://support.microsoft.com/?id=307255


Tom
___________________________________


Yes, when it is in the same sentence, no more syntax
error, Thanks.

But then when I run the following code,
Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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 "[Parkinson data entry]",
CurrentProject.Connection, adOpenStatic
#####The above sentence which is on one line, is shaded
again with message:Run time error'2147217900 (80040e14)'####
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name>]![ID] = ID
Forms![<form name>].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

End Sub

Please help, I hope this is the last error message!!
Thanks!!!!
Mandy


-----Original Message-----
I am not sure whether it is because of the newsgroup
word-
wrap or not but:
pat_data.Open "[data entry]", CurrentProject.Connection, adOpenStatic

should be all on one line, i.e. the whole thing is one VBA statement.

--
HTH
Van T. Dinh
MVP (Access)




Sorry Van T. Dinh, it still does not work without the
square brackets, still same error appears...
Any other solution?
Thanks
Mandy


.


.
 
G

Guest

Hi,
I tried the following in another computer with some
changes of the bracket etc with trial and error. It works
in the sense that no more error message. But a new form
pop up whether the ID is a new one or is already existed
in the data base. What happen?

Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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", CurrentProject.Connection,
adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![Parkinson data entry]![ID] = ID
Forms![Parkinson 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

End Sub

Thankyou again for the effort, I will find out if
something is wrong in my lap top.
Thanks!!!
-----Original Message-----
The syntax looks correct but you need to check "Parkinson Data Entry".

I NEVER used spaces or special characters in Access Object names so I never
had to use the square brackets. Spaces and special characters only make
things more complex so I avoid them.

If "Parkinson Data Entry" is a proper data source name (Table / Query), try
without the square brackets.

--
HTH
Van T. Dinh
MVP (Access)


Yes, when it is in the same sentence, no more syntax
error, Thanks.

But then when I run the following code,
Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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 "[Parkinson data entry]",
CurrentProject.Connection, adOpenStatic
#####The above sentence which is on one line, is shaded
again with message:Run time error'2147217900 (80040e14)'####
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name>]![ID] = ID
Forms![<form name>].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

End Sub

Please help, I hope this is the last error message!!
Thanks!!!!
Mandy


.
 
V

Van T. Dinh

* Post details of "data entry" (Table / Query?).
* Post details of the RecordSource of the Form "Parkinson data entry".
* Is [ID] a Numeric Field or a Text Field?
 
G

Guest

data entry is a table containing the information entered
from the form"parkinson data entry"
ID is a Text field
Thankyou
-----Original Message-----
* Post details of "data entry" (Table / Query?).
* Post details of the RecordSource of the Form "Parkinson data entry".
* Is [ID] a Numeric Field or a Text Field?

--
HTH
Van T. Dinh
MVP (Access)


Hi,
I tried the following in another computer with some
changes of the bracket etc with trial and error. It works
in the sense that no more error message. But a new form
pop up whether the ID is a new one or is already existed
in the data base. What happen?

Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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", CurrentProject.Connection,
adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![Parkinson data entry]![ID] = ID
Forms![Parkinson 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

End Sub

Thankyou again for the effort, I will find out if
something is wrong in my lap top.
Thanks!!!


.
 
V

Van T. Dinh

Does [ID] is included in the RecordSource of the Form [Parkinson data
entry]?

--
HTH
Van T. Dinh
MVP (Access)




data entry is a table containing the information entered
from the form"parkinson data entry"
ID is a Text field
Thankyou
-----Original Message-----
* Post details of "data entry" (Table / Query?).
* Post details of the RecordSource of the Form "Parkinson data entry".
* Is [ID] a Numeric Field or a Text Field?

--
HTH
Van T. Dinh
MVP (Access)


Hi,
I tried the following in another computer with some
changes of the bracket etc with trial and error. It works
in the sense that no more error message. But a new form
pop up whether the ID is a new one or is already existed
in the data base. What happen?

Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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", CurrentProject.Connection,
adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![Parkinson data entry]![ID] = ID
Forms![Parkinson 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

End Sub

Thankyou again for the effort, I will find out if
something is wrong in my lap top.
Thanks!!!


.
 
T

Tom Wickerath

Hi Mandy,

I copied the revised code you show below, and pasted it into the frmDataEntry form on the sample
database that I had previously created. I found that I needed to enclose "data entry" in square
brackets, as follows:

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

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


Without the square brackets, I received an error message with the same numbers that you reported
earlier:

Run-Time Error '-2147217900 (80040e14)':

Invalid SQL Statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.

I find that your code is working correctly on my PC. When I enter an existing ID, I receive the
expected message box, from which I can choose Yes or No. If I choose Yes, I see:

Record 1 of 1 (Filtered)

in the navigation buttons. Note: You will want the Navigation Buttons property set to Yes in
Parkinson data entry form. If I enter a brand new text ID, I see the following in the navigation
buttons display:

Record 8 of 8

The above, with reference to 8 records, is just an example. The form's recordset includes all
records from the data entry table. As I add more records, the number increments as expected.
Have you tried setting a break point, perhaps on the line of code that reads:

If pat_data.EOF Then

and then step through the code one-line-at-a-time, using the F8 key. You can hover your mouse
over the variables to see their values in break mode.

If you are still stuck, feel free to send me a compacted & zipped copy of the applicable parts of
your database. My reply e-mail address is pretty easy to figure out--some spammers have not had
any trouble--you need to remove four joined words from the username.

Tom
_________________________________________


Hi Tom,
I tried the following in another computer with some
changes of the bracket etc with trial and error. It works
in the sense that no more error message. But a new form
pop up whether the ID is a new one or is already existed
in the data base. What happen?

Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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", CurrentProject.Connection, adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![Parkinson data entry]![ID] = ID
Forms![Parkinson 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

End Sub

Thankyou again for the effort, I will find out if
something is wrong in my lap top.
Thanks!!!
-----Original Message-----
Hi Mandy,

I just created a test database that mimics your design.
It includes a table named "Data Entry"
with a text field named "ID". I am able to run your code
without generating Run time error
'2147217900 (80040e14) on the line that you indicate below.

You may have some problem with your MDAC (Microsoft Data
Access Components) installation. I
recommend downloading the MDAC Component Checker tool and running it on your PC. If it
identifies any problems, correct those first. See the following KB article:

http://support.microsoft.com/?id=307255


Tom
___________________________________


Yes, when it is in the same sentence, no more syntax
error, Thanks.

But then when I run the following code,
Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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 "[Parkinson data entry]",
CurrentProject.Connection, adOpenStatic
#####The above sentence which is on one line, is shaded
again with message:Run time error'2147217900 (80040e14)'####
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name>]![ID] = ID
Forms![<form name>].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

End Sub

Please help, I hope this is the last error message!!
Thanks!!!!
Mandy


-----Original Message-----
I am not sure whether it is because of the newsgroup
word-
wrap or not but:
pat_data.Open "[data entry]", CurrentProject.Connection, adOpenStatic

should be all on one line, i.e. the whole thing is one VBA statement.

--
HTH
Van T. Dinh
MVP (Access)




Sorry Van T. Dinh, it still does not work without the
square brackets, still same error appears...
Any other solution?
Thanks
Mandy


.


.
 
G

Guest

Yes, it is one of the item that is to be filled in the
form [Parkinson data entry].
Mandy, Many thanks!!!!!!!!!
-----Original Message-----
Does [ID] is included in the RecordSource of the Form [Parkinson data
entry]?

--
HTH
Van T. Dinh
MVP (Access)




data entry is a table containing the information entered
from the form"parkinson data entry"
ID is a Text field
Thankyou
-----Original Message-----
* Post details of "data entry" (Table / Query?).
* Post details of the RecordSource of the
Form "Parkinson
data entry".
* Is [ID] a Numeric Field or a Text Field?

--
HTH
Van T. Dinh
MVP (Access)


Hi,
I tried the following in another computer with some
changes of the bracket etc with trial and error. It works
in the sense that no more error message. But a new form
pop up whether the ID is a new one or is already existed
in the data base. What happen?

Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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", CurrentProject.Connection,
adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![Parkinson data entry]![ID] = ID
Forms![Parkinson 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

End Sub

Thankyou again for the effort, I will find out if
something is wrong in my lap top.
Thanks!!!



.


.
 
V

Van T. Dinh

OK. There is something missing but I can pick it up through the posts.

See Tom's posts see if you can get it to work correctly.

If the database is small, compact and zip and post it to a download site and
I grab a copy and have a look.
 
G

Guest

Hi Tom,
I am so grad that finally it works!!!!!!!!!!!!!!!!!!!!!!
Many thanks to the kind efforts and patience!!!!!!!!!!!!!
Mandy
-----Original Message-----
Hi Mandy,

I copied the revised code you show below, and pasted it
into the frmDataEntry form on the sample
database that I had previously created. I found that I
needed to enclose "data entry" in square
brackets, as follows:

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

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


Without the square brackets, I received an error message
with the same numbers that you reported
earlier:

Run-Time Error '-2147217900 (80040e14)':

Invalid SQL Statement;
expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.

I find that your code is working correctly on my PC.
When I enter an existing ID, I receive the
expected message box, from which I can choose Yes or No. If I choose Yes, I see:

Record 1 of 1 (Filtered)

in the navigation buttons. Note: You will want the
Navigation Buttons property set to Yes in
Parkinson data entry form. If I enter a brand new text
ID, I see the following in the navigation
buttons display:

Record 8 of 8

The above, with reference to 8 records, is just an
example. The form's recordset includes all
records from the data entry table. As I add more records,
the number increments as expected.
Have you tried setting a break point, perhaps on the line of code that reads:

If pat_data.EOF Then

and then step through the code one-line-at-a-time, using
the F8 key. You can hover your mouse
over the variables to see their values in break mode.

If you are still stuck, feel free to send me a compacted
& zipped copy of the applicable parts of
your database. My reply e-mail address is pretty easy to
figure out--some spammers have not had
any trouble--you need to remove four joined words from the username.

Tom
_________________________________________


Hi Tom,
I tried the following in another computer with some
changes of the bracket etc with trial and error. It works
in the sense that no more error message. But a new form
pop up whether the ID is a new one or is already existed
in the data base. What happen?

Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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", CurrentProject.Connection, adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![Parkinson data entry]![ID] = ID
Forms![Parkinson 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

End Sub

Thankyou again for the effort, I will find out if
something is wrong in my lap top.
Thanks!!!
-----Original Message-----
Hi Mandy,

I just created a test database that mimics your design.
It includes a table named "Data Entry"
with a text field named "ID". I am able to run your code
without generating Run time error
'2147217900 (80040e14) on the line that you indicate below.

You may have some problem with your MDAC (Microsoft Data
Access Components) installation. I
recommend downloading the MDAC Component Checker tool and running it on your PC. If it
identifies any problems, correct those first. See the following KB article:

http://support.microsoft.com/?id=307255


Tom
___________________________________


Yes, when it is in the same sentence, no more syntax
error, Thanks.

But then when I run the following code,
Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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 "[Parkinson data entry]",
CurrentProject.Connection, adOpenStatic
#####The above sentence which is on one line, is shaded
again with message:Run time error'2147217900 (80040e14)'####
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name>]![ID] = ID
Forms![<form name>].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

End Sub

Please help, I hope this is the last error message!!
Thanks!!!!
Mandy


-----Original Message-----
I am not sure whether it is because of the newsgroup
word-
wrap or not but:
pat_data.Open "[data entry]", CurrentProject.Connection, adOpenStatic

should be all on one line, i.e. the whole thing is one VBA statement.

--
HTH
Van T. Dinh
MVP (Access)




Sorry Van T. Dinh, it still does not work without the
square brackets, still same error appears...
Any other solution?
Thanks
Mandy


.


.


.
 
G

Guest

Hi Van T Dinh,

I am so grad that finally it works!!!!!!!!!!!!!!!!!!!!!!
Many thanks to the kind efforts and patience!!!!!!!!!!!!!
Mandy
-----Original Message-----
OK. There is something missing but I can pick it up through the posts.

See Tom's posts see if you can get it to work correctly.

If the database is small, compact and zip and post it to a download site and
I grab a copy and have a look.

--
HTH
Van T. Dinh
MVP (Access)




Yes, it is one of the item that is to be filled in the
form [Parkinson data entry].
Mandy, Many thanks!!!!!!!!!


.
 
T

Tom Wickerath

Hi Mandy,

You're welcome. I'm still curious to know if my original suggestion, to use the MDAC Component
Checker, helps to solve the problem on your laptop PC.

Tom
____________________________________


Hi Tom,
I am so grad that finally it works!!!!!!!!!!!!!!!!!!!!!!
Many thanks to the kind efforts and patience!!!!!!!!!!!!!
Mandy
-----Original Message-----
Hi Mandy,

I copied the revised code you show below, and pasted it
into the frmDataEntry form on the sample
database that I had previously created. I found that I
needed to enclose "data entry" in square
brackets, as follows:

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

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


Without the square brackets, I received an error message
with the same numbers that you reported
earlier:

Run-Time Error '-2147217900 (80040e14)':

Invalid SQL Statement;
expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.

I find that your code is working correctly on my PC.
When I enter an existing ID, I receive the
expected message box, from which I can choose Yes or No. If I choose Yes, I see:

Record 1 of 1 (Filtered)

in the navigation buttons. Note: You will want the
Navigation Buttons property set to Yes in
Parkinson data entry form. If I enter a brand new text
ID, I see the following in the navigation
buttons display:

Record 8 of 8

The above, with reference to 8 records, is just an
example. The form's recordset includes all
records from the data entry table. As I add more records,
the number increments as expected.
Have you tried setting a break point, perhaps on the line of code that reads:

If pat_data.EOF Then

and then step through the code one-line-at-a-time, using
the F8 key. You can hover your mouse
over the variables to see their values in break mode.

If you are still stuck, feel free to send me a compacted
& zipped copy of the applicable parts of
your database. My reply e-mail address is pretty easy to
figure out--some spammers have not had
any trouble--you need to remove four joined words from the username.

Tom
_________________________________________


Hi Tom,
I tried the following in another computer with some
changes of the bracket etc with trial and error. It works
in the sense that no more error message. But a new form
pop up whether the ID is a new one or is already existed
in the data base. What happen?

Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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", CurrentProject.Connection, adOpenStatic
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![Parkinson data entry]![ID] = ID
Forms![Parkinson 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

End Sub

Thankyou again for the effort, I will find out if
something is wrong in my lap top.
Thanks!!!
-----Original Message-----
Hi Mandy,

I just created a test database that mimics your design.
It includes a table named "Data Entry"
with a text field named "ID". I am able to run your code
without generating Run time error
'2147217900 (80040e14) on the line that you indicate below.

You may have some problem with your MDAC (Microsoft Data
Access Components) installation. I
recommend downloading the MDAC Component Checker tool and running it on your PC. If it
identifies any problems, correct those first. See the following KB article:

http://support.microsoft.com/?id=307255


Tom
___________________________________


Yes, when it is in the same sentence, no more syntax
error, Thanks.

But then when I run the following code,
Option Compare Database

Private Sub Command2_Click()
Dim ID As String
ID = InputBox("Please enter the 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 "[Parkinson data entry]",
CurrentProject.Connection, adOpenStatic
#####The above sentence which is on one line, is shaded
again with message:Run time error'2147217900 (80040e14)'####
pat_data.Find "[ID]='" & ID & "'"

If pat_data.EOF Then
DoCmd.OpenForm stDocName
DoCmd.GoToRecord , , acNewRec
Forms![<form name>]![ID] = ID
Forms![<form name>].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

End Sub

Please help, I hope this is the last error message!!
Thanks!!!!
Mandy


-----Original Message-----
I am not sure whether it is because of the newsgroup
word-
wrap or not but:
pat_data.Open "[data entry]", CurrentProject.Connection, adOpenStatic

should be all on one line, i.e. the whole thing is one VBA statement.

--
HTH
Van T. Dinh
MVP (Access)




Sorry Van T. Dinh, it still does not work without the
square brackets, still same error appears...
Any other solution?
Thanks
Mandy


.


.


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top