Parameters in QueryDef

[sooty101]

Hello

I'm new to this so any help will be greatly appreciated.

I'm trying to open a recordset on a query with a parameter. I understand the way to open a recordset on a query is with Querydef. So i did some searching and thought i had found the solution.....however
The below does not work:
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(sOutput)
Set wks = appExcel.Worksheets(cTabOne)
Set dbs = CurrentDb
Set QD1 = dbs.QueryDefs("qry_DeveloperandSites")
QD1.Parameters![SiteID] = "763322"
Set rst2 = QD1.OpenRecordset


when i try to run this the error message " Item not found in this collection"..

Now SiteID does exist as i field in the "qry_DeveloperandSites" Query...Because i couldnt get to work with a parameter txtSiteID on a form i tried a literal value 763322 thinking if that works then just need to find out the syntax for referencing the field on an open form. Site Id is a text field so i have placed the correct delimiters round it as i understand it
Anyone understand what i have done wrong here?
Ultimately im creating two recordsets to transfer to excel the second recordset worked fine on a table but the first recordset on the query has caused me lots of problems
Please help

 

[MarkK]

Hello and welcome to the forum.

You have not specified which line causes the error and your code references three different collections on three different lines. You have also not posted the query text, so we don't know what parameters your query contains, if any. If you can clarify those points it will help someone provide you with a definitive solution.

Cheers,

 

[Galaxiom]

My guess:
Set wks = appExcel.Worksheets(cTabOne)
should be
Set wks = wbk.Worksheets(cTabOne)

I assume you want to open the tab in the workbook you have set as an object.

Otherwise:
qry_DeveloperandSites is not in the querydefs collection or does not have a parameter called SiteID.

 

[sooty101]

Thanks for your replies guys
I think my pasting in the excel data was a mistake. I am trying to export the recordset over to excel but i have done that before and that is not the problem
The problem is creating a filtered recordset on a query.
I have a query called "qry_Developerandsites" within this query there is a field called SiteID. I want to open a recordset on this query and filter for the moment by the value 763322 which is a value in the SiteID field in the qry_Developerandsites query
This is for testing because ultimately i want to filter not against a literal value but against the value in a parameter field on a form. Because i couldn't do this i thought lets just try to filter on the literal value. If that works move on and try doing against the form parameter value
The error message is on QD1.Parameters![SiteID] = "763322"
This is where the code breaks down with the error message " Item not found in this collection"....I can assure eveyone the value 763322 does appear a a value in the SiteID field within this query. I've not worked with opening a recordset on a query before but after doing many searches i thought i had the syntax right. Hope someone can help me

 

[Galaxiom]

The item not found in collection error is nothing to do with the value. The parameter does not exist.

There is a field called SiteID. Therefore there is no parameter called SiteID. This is what it is telling you.

You need to use a named parameter in the query as the criteria for the SiteID field then load the value to the parameter as you have been doing.

BTW. The line setting wks the worksheet property of the workbook is what you should be doing too even though it is not the cause of the error.

 

[MarkK]

Maybe what your looking for: You can write the query name into an SQL statement as if it were a table, filter the records returned with a WHERE clause, and open the recordset directly. Consider...

  dim rst as dao.recordset
  set rst = currentdb.openrecordset( _
    "SELECT * " & _
    "FROM qry_DeveloperandSites " & _
    "WHERE SiteID = " & SiteID)

See how that only selects certain records from the query?
hth

 

[sooty101]

Will check that out tonight when i get home from work. I did try opening by putting the sql in a variable just like a table but came unstuck which is why i started looking online . I think i read if the recordset was a query you needed to use querydef...but if this works then ideal. Thanks

 

[sooty101]

Hello
I think that must be working because there are no breaks in the code and when i step into the code and hover the cursor over the txt_siteID data item it shows a value and not an error message - However the data is still not exporting over to excel in Row 1 as intended
There are two datasets in the procedure. The first recordset is taken from a temp table used to hold data chosen from a list box. These are the detail rows and that seems to work fine and exports to row 17 as per the code
The second dataset is this one and i have used exactly the same code but choosing row 1 as the starting row but when the excel sheet opens row 1 is blank
Any further suggestions would be greatly appreciated but really appreciate the help you have given me already

 

[MarkK]

How do you 'choose row one?'
Many collections and arrays and things start at row zero. Maybe you need to 'choose row zero' instead?
Cheers,

 

[Galaxiom]

The easiest way to put recordset data into Excel is the CopyFromRecordset Method.

http://msdn.microsoft.com/en-us/library/office/aa165427(v=office.10).aspx

 

[Galaxiom]

How do you 'choose row one?'
Many collections and arrays and things start at row zero. Maybe you need to 'choose row zero' instead?
Cheers,

Excel and Word both start at 1 with their object indexes (documents, sheets, cells, tables etc) while Acess and VBA default to 0 (tables, controls, arrays, recordsets etc).