recordset.Open will not see my Public varible

[Brian]

Hello. My public variable is acting wierd.
Here is a copy of my code:


Sub Form_Load()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

Debug.Print "LOAD " & strsearch
rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenKeyset
rst.LockType = adLockOptimistic
--> rst.Open strsearch, Options:=adCmdText
Set Me.Recordset = rst

End Sub

The line with the arrow is where is i get an error:

"No value given for one or more required parameters."

The variable strsearch is declared in Module1 as:
Public strsearch As String

strsearch just contains an SQL statement stored as a
string.

At the debug line in the code above, it prints out the
value in strsearch just fine. But when the code reaches
the rst.Open line, it errors out with the above error.
Why will debug.print see the variable and rst.open not
see it?

Thanks for any help on this.

Brian

 

[Brian]

I am running Access 2000, by the way....

 

[Gerald Stanley]

The error message you are seeing is refering to a problem
with the actual SQL (usually a mistyped column name) and is
nothing to do with strsearch.

Hope This Helps
Gerald Stanley MCSD

 

[Brian]

Hello. Thanks for replying. I looked at my column names
and they match with the names in my tables. But here is
my SQL statement.

"SELECT ACCOUNT_LIST.COMPLEMENTOR, ACCOUNT_LIST.ID,
ACCOUNT_LIST.ACCOUNT_NAME, REP_LIST.REP_ID,
CS_LIST.CS_ID, ACCOUNT_LIST.CAT,
ACCOUNT_LIST.MARKET_AREA, ACCOUNT_LIST.MAKER_LOC,
ACCOUNT_LIST.STATE, ACCOUNT_LIST.AGENCY_NAME,
ACCOUNT_LIST.PENDING, ACCOUNT_LIST.ADDED,
ACCOUNT_LIST.IHD, ACCOUNT_LIST.CODE, ACCOUNT_LIST.OUT,
ACCOUNT_LIST.REMARKS
FROM REP_LIST INNER JOIN (CS_LIST INNER JOIN ACCOUNT_LIST
ON CS_LIST.id = ACCOUNT_LIST.CS) ON REP_LIST.id =
ACCOUNT_LIST.REP
WHERE (((ACCOUNT_LIST.COMPLEMENTOR)='Wal-Mart'))"

Maybe I have bad Syntax. I added the quotes around the
statement and got a different error:

Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'

I don't understand that because it says Select at the
front of the statement.
I also tried it with and without the semicolon at the end
of the statement and received the same error.

Have any idea of what's going on?

Thanks,

Brian

 

[Dirk Goldgar]

Hello. Thanks for replying. I looked at my column names
and they match with the names in my tables. But here is
my SQL statement.

"SELECT ACCOUNT_LIST.COMPLEMENTOR, ACCOUNT_LIST.ID,
ACCOUNT_LIST.ACCOUNT_NAME, REP_LIST.REP_ID,
CS_LIST.CS_ID, ACCOUNT_LIST.CAT,
ACCOUNT_LIST.MARKET_AREA, ACCOUNT_LIST.MAKER_LOC,
ACCOUNT_LIST.STATE, ACCOUNT_LIST.AGENCY_NAME,
ACCOUNT_LIST.PENDING, ACCOUNT_LIST.ADDED,
ACCOUNT_LIST.IHD, ACCOUNT_LIST.CODE, ACCOUNT_LIST.OUT,
ACCOUNT_LIST.REMARKS
FROM REP_LIST INNER JOIN (CS_LIST INNER JOIN ACCOUNT_LIST
ON CS_LIST.id = ACCOUNT_LIST.CS) ON REP_LIST.id =
ACCOUNT_LIST.REP
WHERE (((ACCOUNT_LIST.COMPLEMENTOR)='Wal-Mart'))"

Maybe I have bad Syntax. I added the quotes around the
statement and got a different error:

Invalid SQL statement;
expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT',
or 'UPDATE'

I don't understand that because it says Select at the
front of the statement.
I also tried it with and without the semicolon at the end
of the statement and received the same error.

Have any idea of what's going on?

Thanks,

Brian

If you put quotes around the statement, it will no longer be recognized
as anything but a literal value, so naturally the SQL parser can't find
any valid SQL keyword.

It may help to copy the value of strsearch into the SQL view of a new
query, then switch to datasheet view and see what, if anything, Access
prompts you for.

 

[Brian]

Thanks for the help. I took the qoutes out and there are
no errors.

Thanks!!!!

 

[Dirk Goldgar]

Thanks for the help. I took the qoutes out and there are
no errors.

Thanks!!!!

I'm not sure if you mean your recordset is now being opened properly, so
your problem is solved, or if you mean that the query runs fine in the
query designer but still doesn't work when you try to open your
recordset. Which is it?