xls export question

[GaryPanic]

OK guys I have a qry that works - (between 2 dates and 1 filter from a drop down)
I need it to pick up some information from the form I am running it from (see Month bound) will this work?
any pointers

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 = "Oxygen Bdx"
Set rstGetRecordSet = dbs.OpenRecordset("Oxygenqrymaster")
objActiveWkb.Worksheets("Oxygenqrymaster").Cells(1, 1) = "Cover Holder"
'objActiveWkb.Worksheets("Oxygenqrymaster").Cells(1, 2) = "Month Bound" ***** I want this to bea field on my form =me.Form!zzzfrm!month

 

[pbaldy]

Sure, should work fine with a proper form reference:

http://www.mvps.org/access/forms/frm0031.htm

 

[GaryPanic]

ok - now I just ran it and
Set rstGetRecordSet = dbs.OpenRecordset("Oxygenqrymaster")
fell over here - now the query runs fine - but I know I missed something

the qry asks for dates( from and two) from a form and aalso a contract drop down

someone told me I was missing something - but I cannot for the life of me remember what ?

 

[pbaldy]

I assume you get this error:

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

Aside from the solution provided there, you can generally wrap the form references in your query in the Eval() function and get around the error.

 

[GaryPanic]

Huh Eval () -
Not quite up on this can you expand ...

 

[pbaldy]

Did you look in help?

Eval('Forms!FormName.ControlName')

 

[GaryPanic]

today my help is not helping (Not playing at all -)

 

[GaryPanic]

I've read this and re-read it and it seems to be pants ..
Query works outside of a form yet when I want to mess around with it it has a hissy fit

there must be an easier way of doing this - a simplier solution would be to make a temp table and extract then there is no date problem - but if I can make a table - why does it not allow a straight export in the manner I really Ineed - this is quite a nightmare for those of a limited intelleigence (mainly me)
I have read the eval () and not quite got my head round it ...

 

[GaryPanic]

Dim dbSample As Database
Dim qdfSample As QueryDef
Set dbSample = CurrentDB()
Set qdfSample = dbSample .QueryDefs("Oxygenqrymaster")
qdfSample![Form!zzzfrm!month ] = _
Forms![zzzfrm]![month]

and it still went wrong (Which I expected - but I had 3 problems and I expected it to go down to 2)

 

[pbaldy]

Did you try simply wrapping the form references in the query in the Eval() function, as I showed in post 6? Can you post the db?

 

[GaryPanic]

not sure give me 10 mins while I strip it down

 

[GaryPanic]

its having a little hissy fit ..
and I lost all of the exporting I had put in - I will come back to this tomorrow

 

[GaryPanic]

here is a very stripped down version

the qry works fine (I have put a button on the form)

now to get it into xls is weird

any help would be great (I know I can run the qry and export but that isn't really on as the xls it has to go in has some formatting to be done - so I need it to go in at line 8

 

[pbaldy]

Are we looking at the code behind Command50? That refers to a query named "Cancellationboundxlsqry" which doesn't exist in this database.

 

[GaryPanic]

scrap that - I am putting a revised demo up

My whole system wnet into over drive whan I tried to strip this down

 

[GaryPanic]

ok - just uploaded whizzbang

this has the same problem (excalty)

sorry I got it wrong yesterday it was pass 11 at night and the grey cells weren't firing on all 2 and a half cylinder (never mind 4)

which might explain my small rant-

I have read other sites on Var - and I cannot see it (I kinda understand- but not wholely)

 

[GaryPanic]

brain fried - will come back to this tomorrow....

 

[GaryPanic]

I have an example of what I want to do and I see that the person who did this made a temp table then it works exporting this out .. (all of the variables have then been done in the temp table ..(I think this is a get round not a solution and its one I had in mind -as I ahve 3 varants(?) so far but I think its more likely to be 5

as I ahve to filter out currecy and tax as well as what I have already uploaded
seems weird
-

 

[GaryPanic]

Bump..

I don't think this solution will work - part of my qry asks for a date range between x and y...(Eval)