help to fix error of this code

[Guest]

Hi Tom,
You are so nice!
I have not checked my access programme, cos i am not sure
how to download the software from,
http://support.microsoft.com/?id=307255
what is ?id=307255, i have no experience in doing this,
sounds silly but it is true...
I am not sure if my access has any problom, cos it is
running with the code well now...
The only minor but annoying thing is that my computer has
two default language setting (both English and Chinese-
with software for in put of Chinese when I type the
control space bar), yet some of the brackets to be filled
(not all) in the form automatically defaulted to input of
Chinese character, so each time when I come to them, I
have to press control space bar to go back to the english
language. So I go to delete the Chinese language as
default in Window Program file-language setting, but it
did not help!!!God!!!...
Also, do you mind telling me how to return to bar to the
first box of each new patient input form?
Sorry too many questions? hope not scare you away.
Cheers
Mandy

-----Original Message-----
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


.



.

.

.

 

[Tom Wickerath]

Hi Mandy,

what is ?id=307255

This refers to Microsoft Knowledge Base article # 307255. The Knowledge Base (or "KB") has lots
of good information available. The link that I provided,
http://support.microsoft.com/?id=307255, includes a section that reads:

MORE INFORMATION
To download Component Checker, visit the following Microsoft Web site:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess.asp

I am not sure if my access has any problom, cos it is
running with the code well now...

Is it running fine on your laptop PC? I believe you reported earlier that you were receiving
errors on your laptop PC, which you did not receive when you moved your database to another PC.

So I go to delete the Chinese language as
default in Window Program file-language setting, but it
did not help!!!

I dunno on this problem....

Also, do you mind telling me how to return to bar to the
first box of each new patient input form?

I assume you mean the cursor? Are you comfortable writing VBA code at all? Open the form in
design view. Click on View > Properties to display the Properties dialog box. Select the first
textbox. The name of this control should show up in the blue title bar of the properties dialog.
Write the name down or memorize it. Then click on the small black box in the upper left corner
to display Form Properties. Select the Event tab in the Form Properties dialog. Click on the
build button (the button with the three dots) on the first event procedure, which reads "On
Current". If you receive a "Choose Builder" dialog, then choose "Code Builder" and click on OK.
This should open up the code behind your form to the following event procedure:

Private Sub Form_Current()
<---Your mouse cursor blinking here
End Sub

Note: If you already have a Form_Current event procedure, then you will see existing code.

Enter the name of the textbox control that you recorded earlier, then press the period key and
enter SetFocus. For example, if the name of your first textbox is "txtFirstName", then your new
Form_Current event procedure should end up looking like this:

Private Sub Form_Current()
txtFirstName.SetFocus
End Sub

Click on Debug > Compile. Then click on Save to save your changes and close the VBA code window.
Open the form in normal preview mode. As you switch to new records, you should find that your
mouse cursor always moves to the textbox that you named in your Form_Current event procedure.


Tom

PS. Sorry, I was out of town for the weekend, so I didn't see your reply from Friday evening
until just now.


________________________________________


Hi Tom,
You are so nice!
I have not checked my access programme, cos i am not sure
how to download the software from,
http://support.microsoft.com/?id=307255
what is ?id=307255, i have no experience in doing this,
sounds silly but it is true...
I am not sure if my access has any problom, cos it is
running with the code well now...
The only minor but annoying thing is that my computer has
two default language setting (both English and Chinese-
with software for in put of Chinese when I type the
control space bar), yet some of the brackets to be filled
(not all) in the form automatically defaulted to input of
Chinese character, so each time when I come to them, I
have to press control space bar to go back to the english
language. So I go to delete the Chinese language as
default in Window Program file-language setting, but it
did not help!!!God!!!...
Also, do you mind telling me how to return to bar to the
first box of each new patient input form?
Sorry too many questions? hope not scare you away.
Cheers
Mandy

-----Original Message-----
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


.



.

.

.

 

[ÍÉÛÁ]

Hi Mandy,

what is ?id=307255

This refers to Microsoft Knowledge Base article # 307255. The Knowledge Base (or "KB") has lots
of good information available. The link that I provided,
http://support.microsoft.com/?id=307255, includes a section that reads:

MORE INFORMATION
To download Component Checker, visit the following Microsoft Web site:
http://msdn.microsoft.com/library/default.asp?url=/downloads/list/dataaccess
..asp

I am not sure if my access has any problom, cos it is
running with the code well now...

Is it running fine on your laptop PC? I believe you reported earlier that you were receiving
errors on your laptop PC, which you did not receive when you moved your database to another PC.

So I go to delete the Chinese language as
default in Window Program file-language setting, but it
did not help!!!

I dunno on this problem....

Also, do you mind telling me how to return to bar to the
first box of each new patient input form?

I assume you mean the cursor? Are you comfortable writing VBA code at all? Open the form in
design view. Click on View > Properties to display the Properties dialog box. Select the first
textbox. The name of this control should show up in the blue title bar of the properties dialog.
Write the name down or memorize it. Then click on the small black box in the upper left corner
to display Form Properties. Select the Event tab in the Form Properties dialog. Click on the
build button (the button with the three dots) on the first event procedure, which reads "On
Current". If you receive a "Choose Builder" dialog, then choose "Code Builder" and click on OK.
This should open up the code behind your form to the following event procedure:

Private Sub Form_Current()
<---Your mouse cursor blinking here
End Sub

Note: If you already have a Form_Current event procedure, then you will see existing code.

Enter the name of the textbox control that you recorded earlier, then press the period key and
enter SetFocus. For example, if the name of your first textbox is "txtFirstName", then your new
Form_Current event procedure should end up looking like this:

Private Sub Form_Current()
txtFirstName.SetFocus
End Sub

Click on Debug > Compile. Then click on Save to save your changes and close the VBA code window.
Open the form in normal preview mode. As you switch to new records, you should find that your
mouse cursor always moves to the textbox that you named in your Form_Current event procedure.


Tom

PS. Sorry, I was out of town for the weekend, so I didn't see your reply from Friday evening
until just now.


________________________________________


Hi Tom,
You are so nice!
I have not checked my access programme, cos i am not sure
how to download the software from,
http://support.microsoft.com/?id=307255
what is ?id=307255, i have no experience in doing this,
sounds silly but it is true...
I am not sure if my access has any problom, cos it is
running with the code well now...
The only minor but annoying thing is that my computer has
two default language setting (both English and Chinese-
with software for in put of Chinese when I type the
control space bar), yet some of the brackets to be filled
(not all) in the form automatically defaulted to input of
Chinese character, so each time when I come to them, I
have to press control space bar to go back to the english
language. So I go to delete the Chinese language as
default in Window Program file-language setting, but it
did not help!!!God!!!...
Also, do you mind telling me how to return to bar to the
first box of each new patient input form?
Sorry too many questions? hope not scare you away.
Cheers
Mandy

-----Original Message-----
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


.



.



.

.