Missing something sim ple

[GaryPanic]

Guys I have put th ewhole code i have - but its falling over at the query element - what have I missed ?

Private Sub Command50_Click()

Dim dbs As DAO.Database

Dim rstGetRecordSet As Recordset

Dim objXL As Object
Dim objCreateWkb As Object
Dim objActiveWkb As Object

Set dbs = CurrentDb
Set objXL = CreateObject("Excel.Application")
Set objCreateWkb = objXL.Workbooks.Add
Set objActiveWkb = objXL.Application.ActiveWorkbook

objXL.Visible = True
objActiveWkb.Sheets.Add
objActiveWkb.Worksheets(1).Name = "Premium Written Bdx "

Set rstGetRecordSet = dbs.OpenRecordset("Cancellationboundxlsqry")(This is where it is sticking)
objActiveWkb.Worksheets("Premium Written Bdx").Cells(9, 1) = "Status (Premium(PRM)/Additional Premium (AP)/Return Premium (RP))"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 2) = "Month Bound"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 3) = "Certificate/Policy No"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 4) = "Insured "
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 5) = "Inception"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 6) = "Expiry"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 7) = "Country Of Origin"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 8) = "Currency"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 9) = "Limit"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 10) = "Excess"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 11) = "Gross Premium excl. Premium Tax/IPT "
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 12) = "Tax/IPT %"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 13) = "Premium Tax/IPT "
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 14) = "Commission"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 15) = "Net Premium Due"
objActiveWkb.Worksheets("Premium Written Bdx").Cells(1, 16) = "Date Certificate issued"

'objActiveWkb.Worksheets("Aggregation").Cells(1, 1).Font.Name = "Aharroni"
'objActiveWkb.Worksheets("Aggregation").Cells(1, 1).Font.Size = 12
'objActiveWkb.Worksheets("Aggregation").columns("B").entirecolumn.autofit
'objActiveWkb.Worksheets("Aggregation").columns("H:J").entirecolumn.autofit
'objActiveWkb.Worksheets("Aggregation").columns("O:S").entirecolumn.autofit

'objActiveWkb.Worksheets("Settled Premium Bdx").Cells(2, 1).CopyFromRecordset rstGetRecordSet

' note replace names with correct qry
'objActiveWkb.Sheets.Add
'objActiveWkb.Worksheets(2).Name = "Settled Premium Bdx"







objActiveWkb.Worksheets(1).SaveAs FileName:="c:\Project X\Aggregations\ monthly Cancellation.xls"



objActiveWkb.Close

Set objActiveWkb = Nothing
Set objCreateWkb = Nothing
Set objXL = Nothing
rstGetRecordSet.Close
dbs.Close
Set rstGetRecordSet = Nothing
Set dbs = Nothing

 

[GaryPanic]

Back ground info

Qry one has to go into 1 spreadsheet
Qry 2 will have to go into the second page(tab)
They may or may not have any relationship to each other

THe informaiton headings will be almost the same but the data that goes in will (might be different)

 

[pbaldy]

Can you define "falling over"? What's the error? At a guess:

http://support.microsoft.com/default.aspx?scid=kb;en-us;209203

 

[GaryPanic]

nearly - it says expected ,2

the naming of the tab is ok - its just the qry
I have cut and pasted the code and renamed the bits I need from another export which works - ??

 

[pbaldy]

I assume your query has form references in the criteria. That number will match however many there are. As the link notes, you have to resolve the form references to use the query for a recordset. The link demonstrates one method, another is to wrap the form references in the Eval() function:

Eval('Forms!FormName.ControlName')

 

[gemma-the-husky]

its not DAO is it - you have DAO on the DIM db line, but not on the DIM recordset line.

is it DAO, or ADO maybe

 

[GaryPanic]

I assume your query has form references in the criteria. That number will match however many there are. As the link notes, you have to resolve the form references to use the query for a recordset. The link demonstrates one method, another is to wrap the form references in the Eval() function:

Eval('Forms!FormName.ControlName')

I think you are right as the orginal I copied it from - has no date requiements (I had to filter various tables into a temp table and I have exported this table straight out -Its late here in the UK 10.30 so I will come back to this tomorrow ater I have had a little rest and done a bit more research -

then ~I will try again

 

[pbaldy]

Understood; just said goodnight to my daughter, who lives in England.

 

[GaryPanic]

coming back to this
would it be better to use a temp table(2 of them)

The xls i have to dump this into ..
THe data that goes in to the first tab of each excel book changes in about 3 areas and the same for the second tab

more background
I have a product that is made up of parts
the parts have to be seperated out (for ease I will call them Red, Blue and Green )
Red has to go into one tab in one xls as being an order
THen in the same xls in another tab - Red has to go in when paid
the same entry may be in both or not so Tab one is order date and tab two is paid date (tab tow could have paid Reds that are 6 months old - whereas Tab one will only have this months orders in it taking April as an example

Tab one all order for April
Tab tow all payments made in april (there could be reds in there that are orders from Jan-Feb etc)
same for Blue

I had in mind a button that ran both qry's onme into each tab
does this sound right to everyone ?