Yeah, the SQL's fine. I stopped it in there just to make sure, but it's
also the same SQL for that preview pane (which is working now thank God).
It's erroring out on the:
.Cells(1, intCurrentColumn) = fld.name
line.
Thanx, Doug
-Jayyde
p.s. Leaving work for weekend now, so I'll keep checking back but won't
have the code to test in front of me =\
So when you write the content of strSQL to the immediate window, copy it
into a query and run it, it runs correctly?
Do you know exactly what line of the Excel code it's complaining about?
--
Doug Steele, Microsoft Access MVP
(no private e-mails, please)
lol. As far as the subform goes put the me. in there and still get the
error:
"The record source 'SELECT blah blah...' specified on this form or
report does not exist"
The "export stuff" was a pdf that you had on smart access or somesuch.
The code I'm trying is:
*******************CODE**
Dim oActiveWorkbook As Object
Dim oXL As Object
Dim oWorksheet As Object
Dim rs As Recordset
Dim fld As Field
Dim blnXLCreated As Boolean
Dim intCurrentColumn As Integer
Dim intCurrentSheet As Integer
Dim strMsg As String
Dim strSQL As String
Dim strWorkbookName As String
Dim strSelectSQL As String
Dim strQuery As String
strWorkbookName = CurrentDb().name
strWorkbookName = Left$(strWorkbookName, InStr(strWorkbookName,
".mdb") - 1) & " - " & Format(VBA.Date(), "dd-mmm-yyyy") & ".xls"
If Len(Dir(strWorkbookName)) > 0 Then
strMsg = strWorkbookName & " already exists." & vbCrLf & "Delete
it?"
Select Case MsgBox(strMsg, vbYesNoCancel + vbQuestion,
"Overwrite Verification")
Case vbYes
Kill strWorkbookName
Case vbCancel
Exit Sub
Case Else
End Select
End If
On Error Resume Next
Set oXL = GetObject(, "Excel.Application")
If Err.Number = 0 Then
blnXLCreated = False
Else
Set oXL = CreateObject("Excel.Application")
blnXLCreated = True
End If
'On Error GoTo Err_ExportToExcel
oXL.Application.Workbooks.Add
Set oActiveWorkbook = oXL.Application.ActiveWorkbook
strSelectSQL = GetSelectSQL
strQuery = GetQuery
strSQL = strSelectSQL & strQuerySQL
Set rs = CurrentDb.OpenRecordset(strSQL)
If (rs.BOF = False And rs.EOF = False) Then
intCurrentSheet = intCurrentSheet + 1
If (oActiveWorkbook.Worksheets.Count < intCurrentSheet) Then
oActiveWorkbook.Worksheets.Add
After:=oActiveWorkbook.Worksheets(intCurrentSheet - 1)
End If
With oActiveWorkbook.Worksheets(intCurrentSheet)
.name = strQuery
intCurrentColumn = 1
For Each fld In rs.Fields
.Cells(1, intCurrentColumn) = fld.name
intCurrentColumn = intCurrentColumn + 1
Next fld
.Cells(2, 1).CopyFromRecordset rs
.Rows("1:1").Font.Bold = True
.Columns.Autofit
End With
End If
rs.Close
oActiveWorkbook.Close SaveChanges:=True, FileName:=strWorkbookName
Set rs = Nothing
Set db = Nothing
Set oActiveWorkbook = Nothing
If blnXLCreated Then
oXL.Application.Quit
End If
Set oXL = Nothing
********************CODE**
And like I said, when it gets to the rs.fields it says something about
object or with block not set. Just figured I'd pick your brain while
you were here
.
-Jayyde
Try putting in the reference to the main form (Me.), like I had
Afraid I don't know to what "Excel export stuff" code you're
referring.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
That's basically what I'm doing. It's been renamed. The literal
code I
have is:
subExportPreview.Form.RecordSource = strSQL
I'll add the me. and see what happens. But while I have you here
Doug,
I'm
using your Excel export stuff as a base (trying anyway). What in
that
code
would make it create the recordset, let it pass through the .BOF &
.EOF =
false statement then throw errors on the fields of "Object variable
or
With
block variable not set"?
Thanx guys! =)
-Jayyde
message
First, be aware that there's a difference between a subform control
and
a
form being used as a subform. You've got a subform control on the
main
form.
One of the properties of that subform control is the Source Object,
which
will be the actual form being used as a subform.
If you create your subform by dragging one form onto the other, the
subform
control is (usually) named the same as the form being used as a
subform.
However, it may end up with a different name. If you create your
subform
by
adding a subform control from the toolbox onto the main form, the
name
of
the subform control will definitely not be the same as the form
being
used
as a subform.
What you need to use in your code is the name of the subform
control.
Try Me.NameOfSubformControl.Form.Recordsource = strSQL
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Need the help of all you gurus out there again. Alright, what I
have
going
this time is: 1 form that allows the user to select product type
and
which
fields they want included for an export to excel. Great, I
haven't
actually
tested the export yet, but I'm not there yet either. On this form
I
have
a
subform which is obviously not actually linked to anything and is
there
to
be a preview pane of sorts for the upcoming export. It's this
subform
that
is currently being a pain in my posterier. I've tried a few
different
ways
so far. I've tried creating a queryDef then setting that as
[subform].sourceobject -- no go. I've also tried
[subform].form.recordsource = [theSQLstatement] -- also no go.
I've
checked
the SQL statement itself so I know that's good, incredibly long
potentially
but syntatically Ok. No matter which way I've tried this I get
errors,
If
anyone knows an easier way to do this feel super-free to let me
know
what
it
is =).
Thanx ahead of time!
-Jayyde
