Invalid SQL statement on rst.Open to same table in 'Current' event

  • Thread starter Thread starter WDSnews
  • Start date Start date
W

WDSnews

I have a 12 record table called Color Values and a form called Color Values.
The form has twelve boxes which I wish to color according to the R, G, and B
values in the table. Each box is to display a different color based on the
record it represents. I'm using the 'Current' event to color the twelve
boxes so the twelve colors can be seen side-by-side. My strategy is to open
a recordset to the same table the form is bound to. Although I've borrowed
this code from one of my other forms, it gives me an error on this form.

Here's the error....

Run-time error '-2147217900 (80040e14)':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.

Here's my code which causes the error....

Private Sub Form_Current()
Dim cnnColor As ADODB.Connection
Dim rstColor As ADODB.Recordset

Set cnnColor = Application.CurrentProject.Connection
Set rstColor = New ADODB.Recordset

' get Colors
rstColor.Open Source:="Color Values", ActiveConnection:=cnnColor, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic

Why is the code giving me an error on this form? I welcome your suggestions
and thank you very much.
 
WDSnews -

I suspect your table name needs square brackets since there is a space in
it. "[Color Values]"
 
thank you. You're probably correct. I implemented a recordset clone as a
work around. However, your solution would be better since I'd prefer ADO and
SQL type solutions. Thanks for your help.



Daryl S said:
WDSnews -

I suspect your table name needs square brackets since there is a space in
it. "[Color Values]"

--
Daryl S


WDSnews said:
I have a 12 record table called Color Values and a form called Color
Values.
The form has twelve boxes which I wish to color according to the R, G,
and B
values in the table. Each box is to display a different color based on
the
record it represents. I'm using the 'Current' event to color the twelve
boxes so the twelve colors can be seen side-by-side. My strategy is to
open
a recordset to the same table the form is bound to. Although I've
borrowed
this code from one of my other forms, it gives me an error on this form.

Here's the error....

Run-time error '-2147217900 (80040e14)':
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE',
'SELECT', or 'UPDATE'.

Here's my code which causes the error....

Private Sub Form_Current()
Dim cnnColor As ADODB.Connection
Dim rstColor As ADODB.Recordset

Set cnnColor = Application.CurrentProject.Connection
Set rstColor = New ADODB.Recordset

' get Colors
rstColor.Open Source:="Color Values", ActiveConnection:=cnnColor, _
CursorType:=adOpenKeyset, LockType:=adLockPessimistic

Why is the code giving me an error on this form? I welcome your
suggestions
and thank you very much.


.
Click to expand...
Click to expand...
 
Back
Top