Problem Opening Recordset 1

[bernie10]

Hello everyone,

I have some queries built which get some of their paramaters from a form. The queries work fine and pull the appropriate data. I am now trying to build a spreadsheet based on one of the queries.

I first open a recordset and then I use the CopyFromRecordset routine:

 rs.Open "select StepMember, TicketNo, PoleOwner, Priority, County, Place, Location, JobType " & _
    "from qryNextToGo_filtered", CurrentProject.Connection, adOpenKeyset, adLockOptimistic

objSht.Cells(1, 1).CopyFromRecordset rs

However I get an error on my the rs.Open line that reads "No value given for one or more required paramaters". I assume this is because the query, qryNextToGo_filtered, uses a value from a form. But my form is open when I run this code so should it not still work? If I open the query, qryNextToGo_filtered, manually it opens fine. Also if I use this code to open another similar query which does not require a value from a form it opens fine.

Any thoughts or suggestions?

Thanks,
Collen

 

[ideafixer]

Are all of the field names in the select clause present and named EXACTLY the same in the record source? Sometimes you get that error when you have a field in your selection list that does not appear on the record source.

 

[bernie10]

Ideafixer,
Thanks for the reply. Yes, the field names in the select cause are exactly the same as in the record source.

Any other thoughts?

Collen

 

[PHV]

Try to launch the query before opening the recordset:
DoCmd.OpenQuery "qryNextToGo_filtered", , acReadOnly
rs.Open ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ideafixer]

When you run in debug, you are getting a value forqryNextToGo right?

 

[bernie10]

PH,
The query does sucessfully open with no errors, but the rs.open line still gives the same error with the query open.

Ideafixer,
Not sure how to tell that? As soon as the debugger gets to the rs.open line it produces an error.

Thanks for the continued help,
Collen

 

[RoyVidar]

When there are parameters from forms, one needs to resolve them somehow. Here's two snippets...

The following method should work in 2002+ versions

[tt]dim cmd as adodb.command
dim prm as adodb.parameter
dim cn as adodb.connection

set cn=currentproject.connection
set cmd=new adodb.command
with cmd
.activeconnection=cn
.commandtext = <your sql>
.commandtype = adcmdtext
for each prm in .parameters
prm.value = eval(prm.name)
end if
end with
rs.open cmd,,adOpenKeyset, adLockOptimistic[/tt]

I don't know how to do this with a dynamic SQL string in 2000 (there's a problem with the command objects parameters collection, and needs to be populated through ADOX), so, perhaps, if 2000 is the case, use a stored query with the fields you need, and

[tt]dim cmd as ADODB.Command
dim prm as ADODB.Parameter
dim cn as ADODB.connection
dim cat as ADOX.Catalog
dim prc as ADOX.Procedure

set cn=currentproject.connection

set cat = new ADOX.Catalog
cat.ActiveConnection=cn
set prc = cat.Procedures("myquery")
set cmd = prc.Command
for each prm in cmd.Parameters
prm.Value = Eval(prm.Name)
next prm
rs.open cmd,,adOpenKeyset, adLockOptimistic[/tt]

Roy-Vidar

 

[ideafixer]

Just pop a message box up displaying that value before the open command kicks off. If the table name is not right or the box is empty, there is nothing in that variable. Do you typically set exclusive on. Sometimes tyypos can get ya when assigning variable names.

Anyway I think this is the solution:

rs.Open "select StepMember, TicketNo, PoleOwner, Priority, County, Place, Location, JobType from " & qryNextToGo_filtered , CurrentProject.Connection, adOpenKeyset, adLockOptimistic

objSht.Cells(1, 1).CopyFromRecordset rs

*** Your code has the variable concatinated INSIDE your literal text. It is taking qryNextToGo_filtered as the table name. Not the variable contents. Look at how you are concatinating. I tries to rectify with my snippit but I am sure you get the idea.

 

[ideafixer]

Sorry read something wrong. You are right, when opening a query this way from a form you get that problem. Try the guys solutions above. I was answering as if your query name was on the form.

 

[bernie10]

Roy,
Thanks for the reply. The code runs without error using your solution, but now I am getting an empty recordset. I know this would seem to imply a prolblem with my SQL, but I have tested the SQL in an access query and it does return records. I've posted my code below. Any thoughts to why I could be getting an empty recordset???

    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim cn As ADODB.Connection

    Set cn = CurrentProject.Connection
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandText = "SELECT StepMember, TicketNo, PoleOwner, Priority, " & _
                        "County, Place, Location, JobType from qryNextToGo_filtered"
        .CommandType = adCmdText
        For Each prm In .Parameters
            prm.Value = Eval(prm.Name)
        Next prm
    End With
    rs.Open cmd, , adOpenKeyset, adLockOptimistic
    MsgBox (rs.RecordCount) 'Displays a zero since its an empty recordset

Ideafixer,
qryNextToGo_filtered is the query name.

Collen

 

[bernie10]

Oh and we are using Access 2002+.

Collen

 

[RoyVidar]

No, I haven't, but I've never done such with dynamic SQL either.

I'd suggest either creating a dynamic sql statemet without usage of stored queries, and concatenate the criterion into the string or create a new stored query with the fields you need for this (and not do select fieldlist from storedquery).

The replacement for the latter, could look something like this:

[tt] .CommandText = "mynewstoredquery"
.properties("Jet OLEDB:Stored Query")=true[/tt]

I've seen some challenges with ADO when using nested stored queries before - would DAO be an option?

Roy-Vidar

 

[bernie10]

Roy-Vidar,
I'd be okay with using a stored query with the fields I need, however I am still getting an empty recordset using the code with a stored query (qrySSNextToGo_Filtered is my new query):

    Dim cmd As ADODB.Command
    Dim prm As ADODB.Parameter
    Dim cn As ADODB.Connection

    Set cn = CurrentProject.Connection
    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = cn
        .CommandType = adCmdText
        .CommandText = "qrySSNextToGo_Filtered"
        .Properties("Jet OLEDB:Stored Query") = True
        For Each prm In .Parameters
            prm.Value = Eval(prm.Name)
        Next prm
    End With
    rs.Open cmd, , adOpenKeyset, adLockOptimistic
    MsgBox (rs.RecordCount)

Any other thoughts?

I am not too familar with DAO but I suppose it could be an option as well.

Thanks for all your help,
Collen

 

[RoyVidar]

I can't say I understant this, but try DAO, it could probably look like this:

[tt]dim qd as dao.querydef
dim prm as dao.parameter
dim db as dao.database
dim rs as dao recordset

set db=currentdb
set qd=db.querydefs("qrySSNextToGo_Filtered")
for each prm in qd.parameters
prm.value=eval(prm.name)
next prm
set rs=qd.openrecordset()[/tt]

On my setup, this gives the exact same result as the ADO samples above

- remember to set a reference to Microsoft DAO 3.6 Object library (in VBE - Tools | References)

Roy-Vidar

 

[bernie10]

That worked!

I wish I understood what was going on but I'm glad we got it working. Thanks for all the help.

Collen

 

[RoyVidar]

Should you find out, then please post it;-)

Did you try the version with ADOX? You'll need a reference to Microsoft ADO Ext. 2.# for DDL and security.

I'm using that for a couple of applications that works without any problems in 2000 through 2003. The other version, which needs 2002 version+, I've only tested, but it did work on the samples I've tested.

The disadvantage of using ADOX vs the DAO, is that it is significantly slower. The advantage, that it's probably easier portable to other database platforms, but even though I'm an ADO fan (probably converted for all the wrong reasons...), I'll have to admit DAO is most often faster on native tables, probably also linked Access tables.

Here's another go on the code you used (remove the line with adcmdtext), trying to specify/resolve the parameter explicitly:

[tt] .ActiveConnection = cn
' remove .CommandType = adCmdText
.CommandText = "qrySSNextToGo_Filtered"
.Properties("Jet OLEDB:Stored Query") = True
.parameters("[forms]![formname]![controlname]").value = _
forms("formname").controls("controlname").value
' or - replacing the reference in the query with for instance
' [myparameter], then try
'.parameters("[myparameter]").value = _
forms("formname").controls("controlname").value[/tt]

In both of the latter samples, I think you'd need to declare the parameter within the query - in design view - Query | Parameter, specify the parameters (full form reference, or the [myparameter]) and datatype.

Roy-Vidar