This is continued from yesterday. I am still at the point of getting the
3709 error. Thought it would be easier to just copy and paste what had been
done so far. Still needing help with this one.

History is below:


Still not working, but different symptoms. If I use the three quotes, I get
a syntax error, but two quotes seems to be ok with syntax. Now when the line
executes I get the following error:

3709 - The connection cannot be used to perform this operation. It is
either closed or invalid in this context.

Again, the code in sample 1 works, but brings back everything from the DB.
I never
imagined it would be so hard to get something out of Access through Excel!

"Jim Thomlinson" wrote:

rs.Open "Select *FROM tblDoctorBladesQuery WHERE
tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value"""" ,
cn,
adOpenDynamic, adLockOptimistic, adCmdText

I just can't get the quotes correct today... The value returnved by
cboMachineLocation.Value must be in Quotes in the select statement... I think
the quotes line up now...

"Jim Thomlinson" wrote:

Sorry Try this...

rs.Open "Select *FROM tblDoctorBladesQuery WHERE
tblDoctorBladesQuery.MachineLocation = """ & cboMachineLocation.Value""" ,
cn,
adOpenDynamic, adLockOptimistic, adCmdText.

Needs the Quotes...

"Vince" wrote:

Jim,
I made the change below, I still get the error when the rs.open is executed.
The error I get is -2147217900. I don't know if that error code helps or
not. My code reads as follows:
rs.Open "Select *FROM tblDoctorBladesQuery WHERE
tblDoctorBladesQuery.MachineLocation = " & cboMachineLocation.Value , cn,
adOpenDynamic, adLockOptimistic, adCmdText.

Your help with this is greatly appreciated.

"Jim Thomlinson" wrote:

tblDoctorBladesQuery.MachineLocation = " &
cboMachineLocation.Value ,cn,adOpenDynamic,adLockOptimistic,adCmdText

Try this. Your cbo value was just a string ("cboMachineLocation.Value"), not
the value form the cbo...

HTH

"Vince" wrote:


The code in example #1 works fine, but I am trying to figure out what is
wrong with the code in example #2. I get an error when I try to open the
recordset. I am trying to pass a value from a combo box to the query in
example #2 so the user can select different values. I can't get it to work,
I am looking for help.

example #1

Dim sSql as String
Dim i as Integer
Dim cn as ADODB.Connection, rs as ADODB.Recordset, r AS Long, c ASLong

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.Oledb.4.0; " & _
"Data Source=C:\DoctorBlades.mdb;"

Set rs = New ADODB.Recordset
rs.Open "tblDoctorBladesQuery",cn,adOpenDynamic,adLockOpti mistic


example #2

Dim sSql as String
Dim i as Integer
Dim cn as ADODB.Connection, rs as ADODB.Recordset, r AS Long, c AS Long

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.Oledb.4.0; " & _
"Data Source=C:\DoctorBlades.mdb;"

Set rs = New ADODB.Recordset
rs.Open "Select * FROM tblDoctorBladesQuery WHERE
tblDoctorBladesQuery.MachineLocation =
cboMachineLocation.Value",cn,adOpenDynamic,adLockO ptimistic,adCmdText