Macro cannot run Code

[Phil Smith]

I have a piece of code that runs just fine, as long as I run it
manually. If I attempt to run it from a Macro, (which is of course what
I need to do,) it does not run. I get an error, "The object does not
contain the Automation object "Function", error 2950.

I posted something in the ModulesCoding forum, but did not get very far,
and it became apparent that is not a coding problem, but a Macro problem.

The only things I can find about this error is it is not in a trusted
location, (but it is,) and a single reference about having to rerun the
Microsoft Office configuration program, which I cannot seem to find.
Access 2007.

This project is just about complete, and I need to hand it off, but
handing it off with instructions on how to open VBA, choose your module,
and run the code from there is not going to cut it.

Any help is greatly appreciated.

Phil

 

[Steve Schapel]

Phil,

Please provide details of the VBA code, and details of the macro, and
when/how it is being run. Thanks.

 

[Phil Smith]

The macro is simple:
RunCode
OpenQuery
OpenQuery
RunCode

The first piece of code runs without a hitch.
The second works fine if I run it maually, fails to run from a macro.
The Macro runs from a button on a form.

The function is pretty simple as well:
-----------------------------------------------------------------
Public Function CycleBrand_Division()

Dim rs As ADODB.Recordset
Dim sSQL As String

Dim SelectedBrand As String
Dim SelectedDivision As String
Dim pathname As String
Dim filename As String

pathname = "s:\MasterList\"

DoCmd.SetWarnings False
sSQL = "SELECT DISTINCT [brand]![name] AS Brand, [division]![name]
as Division "
sSQL = sSQL & "FROM item_warehouse_link INNER JOIN (division INNER
JOIN (brand "
sSQL = sSQL & " INNER JOIN item ON brand.brand_id = item.brand_id)
ON division.division_id = item.division_id) "
sSQL = sSQL & "ON item_warehouse_link.item_id = item.item_id "
sSQL = sSQL & "WHERE (((item_warehouse_link.onhand_qty)>0))"
sSQL = sSQL & "ORDER BY [brand]![name], [division]![name];"

Debug.Print sSQL

Set rs = New ADODB.Recordset
rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Do While Not rs.EOF
SelectedBrand = rs.Fields(0)
SelectedDivision = rs.Fields(1)
filename = Replace(SelectedBrand, Chr$(32), Chr$(45)) & "-" &
Replace(SelectedDivision, Chr$(32), Chr$(45))
selectSQL = "SELECT " & Chr$(34) & SelectedBrand & Chr$(34) & "
AS Brand, " & Chr$(34) & SelectedDivision & Chr$(34) & " AS Division
INTO Brand_DivisionSelectortbl;"


Set DB = CurrentDb()
Set Q = DB.QueryDefs("Brand_DivisionSelector")
Q.SQL = selectSQL
Q.Close
'Update Branddivision Selector
DoCmd.OpenQuery "Brand_DivisionSelector", acViewNormal, acEdit
Debug.Print selectSQL & " " & DCount("Brand",
"MasterList_Step5Auto")
If DCount("Brand", "MasterList_Step5Auto") = 0 Then GoTo
Movealong


On Error GoTo TransferFailure
Debug.Print "Attempting to creat" & pathname & filename & ".xls"
Debug.Print ""
DoCmd.TransferSpreadsheet acExport, 8, "MasterList_Step5Auto",
pathname & "MLR-" & filename & ".xls", False, ""

GoTo skipit
Movealong:
Debug.Print "Zero Count, Skipped"
Debug.Print " "

skipit:
rs.MoveNext
Loop

rs.Close

Set rs = Nothing
DoCmd.SetWarnings True
Exit Function

TransferFailure:
MsgBox Error$
Debug.Print "Error Creating File"
Resume skipit

End Function