Second SQL statement

Hi everyone ...
I posted on this last week, and have spent time finding/studying/doing tutorials on Databse access and ADO. I am still stuck and this seems it should be rather simple.
I am trying to do this only in code, not with any controls directly connected to the database(for this portion of the project), so I have most of the database connection stuff in a seperate .BAS module.
here is that code
EDIT: Using VB6 and Access2000

Option Explicit

Public cnMyCon As ADODB.Connection 'Connection to ADODB
Public MyRS As ADODB.Recordset 'Holds records

Public Sub ConnectToDBase(strSQL As String)
Dim lngRecordCount As Long

Set cnMyCon = New ADODB.Connection
Set MyRS = New ADODB.Recordset

MyRS.CursorLocation = adUseClient

cnMyCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Documents and Settings\Administrator\Desktop\test.mdb;Persist Security Info=False"
cnMyCon.CursorLocation = adUseClient
MyRS.Open strSQL, cnMyCon, adOpenStatic, adLockOptimistic
' MyRS.Open strSQL, cnMyCon, adOpenDynamic, adLockOptimistic

If Not MyRS.EOF Or Not MyRS.BOF Then
MyRS.MoveLast 'without these the recordcount
MyRS.MoveFirst 'ends up -1
End If

lngRecordCount = MyRS.RecordCount
Debug.Print "From Mod " & lngRecordCount 'visual proof that it has something
End Sub

That works great ... returns 14 records that I use to set the caption of dynamically created Option buttons. All that works perfect.
Now the problem .. and scenerio ..
the purpose of this particular routine is 2 fold ...
First display all the info.(in one table)on a particualr company based on the user selection.
Second, Update one column of that companies record(check the dropped checkbox in the Database table).

This is what I need to do ...
User selects an option button ..
I need to do a select statement that will return all fields from the database table where the CompanyName = the option button caption.
Display that info.
Do an update statement.
Every time I try, I either get no records returned or an error in the SQLstatement. :confused:
Here is that code

Private Sub Option1_Click(Index As Integer)

Dim strCoName As String
Dim strSQL As String

MyRS.Close ' Close the original RS returned.
strCoName = Option1(Index).Caption
strSQL = "Select * From Suppliers where CompanyName = '" & strCoName & "'"
'strSQL = "select * from Suppliers where Companyname like '" & strCoName & "'"
'strSQL = "Select * From Suppliers where CompanyName Like '" & strCoName & "'" & ";"

modTestConnect.ConnectToDBase (strSQL)
MyRS.Fields.Item("Dropped").Value = True
MyRS.Update

End Sub

When that routine is run, I get either no recordset or a syntax error.
Any ideas?
Thanks
Michael

may i ask first is there any apostrophe or single quote on your caption?

duane ...
A few of the captions do have an apostrophe in them and spaces...
Like Grandma Kelly's cookies
but no spaces in the table names or field names

that's why you get an error sometimes you should replace the apostrophe.

strSQL = "Select * From Suppliers where CompanyName = '" & replace(strCoName,"'","''") & "'"

you'll need to replace the single quote with double single quote as i've posted above.

and try to use the trim function.

Duane ..
First, THANK-You for taking the time with me on this.. I really do apprecitate it.

I will replace all those .. no problem.
I just added a test record and that seemed to work .. let me add a routine that will replace all those.
May I PM you the results? or would you prefer to continue this in this thread?
Either way I will post the fixed code here for others to see.
Michael

either way is fine with me

actually you don't need to add a routine you can directly replace the single quote in your select statement, read my post a few post up

Duane ...
Run Time error 3021 ... BOF or EOf = true

what part of the code?
can you post you new code?

The code that errored was the code you posted above. The Replace did not seem to work.
I did it this way, and have no more errors but it has now shown me where I NEED to do several different types of replace statements, before I build the strSQL.
If there are any types of punctuations it seems to not work. But that should be fairly simple.
I will try that now and let you know soon


strCoName = Option1(Index).Caption
strCoName = Replace(strCoName, "'", "", 1, , vbTextCompare)
strSQL = "Select * From Suppliers where CompanyName = '" & strCoName & "'" & ";"

Michael

now what happen to your code?

Duane ...
Problem is if there is a space in the variable.
If No spaces ... works great ...
If a space ... No records returned.
I know I have to use [] somehow in the strSQL, but cannot figure it out.
This is making me feel rather stupid ... :o
Michael

Duane ...
Problem is if there is a space in the variable.
If No spaces ... works great ...
If a space ... No records returned.
I know I have to use [] somehow in the strSQL, but cannot figure it out.
This is making me feel rather stupid ... :o
Michael

I told you to use the trim function

where trim(field) ='" & trim(strSQL) & "'"

Duane
Trim will only remove the leading and trailing spaces.
In the record, there are entries like
Ed's Kitchen Supplies
Bobbys
Blah
Blah Blah

The single word names work perfect with the code we discussed yesterday, but the double word entries return no records. That is what caused me to think that I needed to deal with the spaces with [], but being wrong in that assumption would not surprise me.