I have code that is running a query from an access db & returning the output into excel based upon user form inputs (on the excel template). The application is working fine for myself & one other user, however when I tried to run it on a third machine, I am getting a fatal error (RT Error 91- Object Variable or With Block Variable Not Set). I suspect that perhaps the user is not getting any data returned, but I can't figure out where the problem is occuring. Could someone please give me an idea on where my problem resides....I would GREATLY appreciate it!! I've excluded the section of code where the query output is formatted in excel, as I'm fairly confident the error isn't in this section (it isn't returning data, so I'm not getting that far in the query). The first section of the code is just evaluating the user form to determine which query to run & what parameters to use.
Dim MyRst As DAO.Recordset
Dim MyDB As DAO.Database
Dim MyQdf As DAO.QueryDef
Dim QueryName As String
Dim ContractNumber As String
Dim StartDate As Date
Dim EndDate As Date
Dim CustName As String
Dim OutputType As String
Dim PartOutput As String
On Error GoTo ErrorHandler
'set error location
'define dbase
Set MyDB = DBEngine.Workspaces(0).OpenDatabase(THE_DB)
Application.Cursor = xlWait
QueryName = "q_Invoicing_1b_KDetail"
Set MyQdf = MyDB.QueryDefs(QueryName)
MyQdf.Parameters("[Enter Beg Date]") = cboBegDate
MyQdf.Parameters("[Enter End Date]") = cboEndDate
MyQdf.Parameters("[Enter Contract Number]") = CStr(cbo_Contract)
Set MyRst = MyQdf.OpenRecordset
With MyRst
If .RecordCount = 0 Then
MsgBox "No data esists for the date range &/or contract that you selected!!", vbOKOnly, "Xomox-Contract Pricing Utility"
GoTo EndIt
End If
End With
Dim xlsht As Excel.Worksheet
Set xlsht = Sheets("Sheet1")
Rows("1:10000").Select
Selection.Delete Shift:=xlUp
xlsht.Range("A5").CopyFromRecordset MyRst
also posted question on:
www.mrexcel.com
www.utteraccess.com
Bookmarks