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
.
.