its just strange, the system has been built over 10+ years. The specific component is just an inventory audit and its the only part not working via forcing the DSN via Linked Tables.
Will the debug throw out some data you want me to post?
its just strange, the system has been built over 10+ years. The specific component is just an inventory audit and its the only part not working via forcing the DSN via Linked Tables.
Will the debug throw out some data you want me to post?
set but im not seeing anything extra
Actually the query doesn't work, I thought it did but anytime I changed the linkedtable that's under the first code I pasted the query fails with unable to access sql, doesn't exist or access denied.
So I tested another app and it creates the connection no errors, THEN I run this one so connection should be live, but still get same error. Is there something about this query/macro that references or I can force reference the SQL Server
Basically, I would look at the output at each point, check the SQL, check all the characteristics and error messages at each stage, and see what I saw. It's hard to guess what the issue is, without seeing the details. This is a shoe leather kind of a job. Walk each sidewalk however far it goes.
Hate sounding like a tard but where does the Debug info go? Not seeing much on screen
Alt-PF11 to open the VBA editor.
If the "Immediate" window is not visible - generally at the bottom below the code window - then control G to open the immediate window.
You could also check the "view" menu to see what other windows are available. I don't use them all, but you can set up the "watch" window to watch the value of a particular variable, and other cool and useful things.
In the immediate window, type this and hit enter:
That's where any debug.print output should be showing up.Code:Debug.print "Hello World"
You can also type things like this:
or, if you had a sub called "DoThis" and a function called "DoThat"Code:label12.Caption = "Fred" combo13.Value = 12 words$ = " is now " Debug.print label12.Caption & words$ & combo13
Code:Call DoThis Text23 = DoThat()
Heres a question, I can run this MDB anywhere (any machine) with no DSN Set and it still knows how to connect to the SQL Server, I cant find a reference to Server name or Database name though nor the connect method. Anyway I can go about finding that?
Heres the output:
Code:SELECT Items.ItemNo, Items.Name, ISNULL(ItemInvQty.SumOfQtyToStock, 0) AS InvQty, ISNULL(ItemPOrdered_ByWeek.SumOfSumOfDblQty1, 0) AS POQty, ISNULL(ItemSOReq_ByWeek.SumOfItemTotalQty, 0) AS SOqty, ISNULL(ItemInvQty.SumOfQtyToStock, 0) + ISNULL(ItemPOrdered_ByWeek.SumOfSumOfDblQty1, 0) - ISNULL(ItemSOReq_ByWeek.SumOfItemTotalQty, 0) AS FCqty, Items.Stock, Categories.CategoryCode, ItemPOrdered_ByWeek.POQty1, ItemPOrdered_ByWeek.POQty2, ItemPOrdered_ByWeek.POQty3, ItemPOrdered_ByWeek.POQty4, ItemPOrdered_ByWeek.POQty5, ItemSOReq_ByWeek.SOQty1, ItemSOReq_ByWeek.SOQty2, ItemSOReq_ByWeek.SOQty3, ItemSOReq_ByWeek.SOQty4, ItemSOReq_ByWeek.SOQty5, Items.Rack, Items.Bin, Items.ReorderLevel, Items.ReorderQTY, Items.Userdefined1 FROM Categories INNER JOIN Items LEFT OUTER JOIN (SELECT ItemNo, SUM(SumOfDblQty1) AS SumOfSumOfDblQty1, SUM(CASE WHEN DateDiff(d, GetDate(), DueDate) <= 7 THEN SumOfDblQty1 ELSE 0 END) AS POQty1, SUM(CASE WHEN DateDiff(d, GetDate(), DueDate) > 7 AND DateDiff(d, GetDate(), DueDate) <= 14 THEN SumOfDblQty1 ELSE 0 END) AS POQty2, SUM(CASE WHEN DateDiff(d, GetDate(), DueDate) > 14 AND DateDiff(d, GetDate(), DueDate) <= 21 THEN SumOfDblQty1 ELSE 0 END) AS POQty3, SUM(CASE WHEN DateDiff(d, GetDate(), DueDate) > 21 AND DateDiff(d, GetDate(), DueDate) <= 28 THEN SumOfDblQty1 ELSE 0 END) AS POQty4, SUM(CASE WHEN DateDiff(d, GetDate(), DueDate) > 28 OR DueDate IS NULL THEN SumOfDblQty1 ELSE 0 END) AS POQty5 FROM (SELECT PO.PONo, PODetails.ItemNo, CASE WHEN SUM(QtyToBuy - ISNULL(SumofQtyReceived, 0)) > 0 THEN SUM(QtyToBuy - ISNULL(SumofQtyReceived, 0)) ELSE 0 END AS SumOfDblQty1, UOMs.UOMCode, PODetails.DueDate FROM PO INNER JOIN UOMs INNER JOIN PODetails LEFT OUTER JOIN (SELECT PODetailID, SUM(QtyReceived) AS SumOfQt yReceived, SUM(QtyOrdered) AS SumOfQtyOrdered FROM ReceivingDetails GROUP BY PODetailID) AS ItemReceiving ON PODetails.PODetailID = ItemReceiving.PODetailID ON UOMs.UOMID = PODetails.BuyUOMID ON PO.POID = PODetails.POID WHERE (PODetails.POStatuID IN (1, 4, 6)) GROUP BY PO.PONo, PODetails.ItemNo, UOMs.UOMCode, PODetails.DueDate) AS ItemPOrdered_Detail GROUP BY ItemNo) AS ItemPOrdered_ByWeek ON Items.ItemNo = ItemPOrdered_ByWeek.ItemNo LEFT OUTER JOIN (SELECT InventoryItems.ItemNo, SUM(InventoryItems.QtyToStock) AS SumOfQtyToStock, UOMs_1.UOMCode FROM Locations INNER JOIN InventoryItems INNER JOIN UOMs AS UOMs_1 ON InventoryItems.StockUOMID = UOMs_1.UOMID ON Locations.LocationID = InventoryItems.LocationID WHERE (Locations.LocationCode <> 'FINGDS') GROUP BY InventoryItems.ItemNo, UOMs_1.UOMCode) AS ItemInvQty ON Items.ItemNo = ItemInvQty.ItemNo LEFT OUTER JOIN (SELECT Items_1.ItemNo, SUM(SOStrucReq.ItemTotalQty) AS SumOfItemTotalQty, Categories_1.CategoryCode, Items_1.Name, SUM(CASE WHEN DateDiff(d, GetDate(), SOStrucReq.DueDate) <= 7 THEN ItemTotalQty ELSE 0 END) AS SOQty1, SUM(CASE WHEN DateDiff(d, GetDate(), SOStrucReq.DueDate) > 7 AND DateDiff(d, GetDate(), SOStrucReq.DueDate) <= 14 THEN ItemTotalQty ELSE 0 END) AS SOQty2, SUM(CASE WHEN DateDiff(d, GetDate(), SOStrucReq.DueDate) > 14 AND DateDiff(d, GetDate(), SOStrucReq.DueDate) <= 21 THEN ItemTotalQty ELSE 0 END) AS SOQty3, SUM(CASE WHEN DateDiff(d, GetDate(), SOStrucReq.DueDate) > 21 AND DateDiff(d, GetDate(), SOStrucReq.DueDate) <= 28 THEN ItemTotalQty ELSE 0 END) AS SOQty4, SUM(CASE WHEN DateDiff(d, GetDate(), SOStrucReq.DueDate) > 28 THEN ItemTotalQty ELSE 0 END) AS SOQty5 FROM Categories AS Categories_1 INNER JOIN Items AS Items_1 ON Categories_1.CategoryID = Items_1.CategoryID INNER JOIN WorkOrderDetails RIGHT OUTER JOIN (SELECT SalesOrder.SalesOrderNo, ItemSpecFullStruc.ChildItemSpecID, SalesOrderDetails.SalesOrderDetailID, SalesOrderDetails.[LineNo], SalesOrder.SOStatus, SalesOrder.EntryDate, CASE WHEN ToBeScheduled.ToBeScheduledID IS NULL THEN Sale sOrder.DueDate ELSE ToBeScheduled.DueDate END AS DueDate, CASE WHEN ToBeScheduled.ToBeScheduledID IS NULL THEN SalesOrderDetails.QtyOrdered ELSE ToBeScheduled.Qty END AS QtyOrdered, ItemSpecFullStruc.TotalQtyPerRoot * CASE WHEN ToBeScheduled.ToBeScheduledID IS NULL THEN SalesOrderDetails.QtyOrdered ELSE ToBeScheduled.Qty END AS ItemTotalQty, ItemSpecFullStruc.TotalQtyPerRoot, SalesOrder.SalesOrderID, ItemSpecs.ItemSpecID, ItemSpecs.ItemID, ItemSpecFullStruc.[Level], ItemSpecFullStruc.ItemSpecStrucID, ItemSpecFullStruc.ParentItemSpecID, ItemSpecFullStruc.RootItemSpecID FROM SalesOrder INNER JOIN ItemSpecFullStruc INNER JOIN ItemSpecs ON ItemSpecFullStruc.RootItemSpecID = ItemSpecs.ItemSpecID INNER JOIN SalesOrderDetails LEFT OUTER JOIN WorkOrderDetails AS WorkOrderDetails_1 ON SalesOrderDetails.SalesOrderDetailID = WorkOrderDetails_1.SalesOrderDetailID LEFT OUTER JOIN ToBeScheduled ON WorkOrderDetails_1.WorkOrderDetailID = ToBeScheduled.WorkOrderDetailID ON ItemSpecs.ItemSpecID = SalesOrderDetails.ItemSpecID ON SalesOrder.SalesOrderID = SalesOrderDetails.SalesOrderID WHERE (SalesOrder.SOStatus IS NULL) OR (SalesOrder.SOStatus <> 5)) AS SOStrucReq INNER JOIN ItemSpecs AS ItemSpecs_1 ON SOStrucReq.ChildItemSpecID = ItemSpecs_1.ItemSpecID ON WorkOrderDetails.SalesOrderDetailID = SOStrucReq.SalesOrderDetailID ON Items_1.ItemID = ItemSpecs_1.ItemID WHERE (WorkOrderDetails.Completed IS NULL) OR (WorkOrderDetails.Completed = 0) GROUP BY Items_1.ItemNo, Categories_1.CategoryCode, Items_1.Name) AS ItemSOReq_ByWeek ON Items.ItemNo = ItemSOReq_ByWeek.ItemNo ON Categories.CategoryID = Items.CategoryID WHERE (Items.Stock = 1) AND (NOT (Categories.CategoryCode IN ('wood', 'lam'))) AND (Items.Description LIKE '%' + 'A' + '%') AND (ISNULL(ItemInvQty.SumOfQtyToStock, 0) <> 0 OR ISNULL(ItemPOrdered_ByWeek.SumOfSumOfDblQty1, 0) <> 0 OR ISNULL(ItemSOReq_ByWeek.SumOfItemTotalQty, 0) <> 0) ORDER BY Items.Name
Here's the form that's being run which includes all the VB Code etc:
When I try to open the Query under Query Design I just get an error:Code:Option Compare Database Option Explicit Private Sub Form_Close() If Not Me.Recordset Is Nothing Then DestroyConnection Me.Recordset.ActiveConnection End If End Sub Private Sub Form_Load() Dim db As Object Dim qry As Object Dim rs As ADODB.Recordset Dim SQL As String Dim ProductGroup As String Set db = Application.CurrentDb Set qry = db.QueryDefs("ItemQtyStatusSQL") SQL = qry.SQL Set qry = Nothing Set db = Nothing ProductGroup = InputBox("Product Group", "Enter Parameter Value") If StrPtr(ProductGroup) = 0 Then DoCmd.Close acForm, Me.Name Else SQL = Replace(SQL, "[Product Group]", "'" & ProductGroup & "'", , , vbTextCompare) Debug.Print SQL Set rs = New ADODB.Recordset rs.Open SQL, gcn, adOpenStatic, adLockReadOnly, adCmdText Set Me.Recordset = rs Set rs = Nothing End If End Sub Private Sub Inventory_BeforeUpdate(Cancel As Integer) End Sub Private Sub ItemQtyInvFC_Click() On Error GoTo Err_ItemQtyInvFC_Click Dim stDocName As String stDocName = "ItemForecast_form" DoCmd.RunMacro stDocName Exit_ItemQtyInvFC_Click: Exit Sub Err_ItemQtyInvFC_Click: MsgBox Err.Description Resume Exit_ItemQtyInvFC_Click End Sub Private Sub Requirements_Click() On Error GoTo Err_Requirements_Click Dim stDocName As String Dim stLinkCriteria As String stDocName = "ItemRequirements" stLinkCriteria = "[ItemNo]=" & "'" & Me![ItemNo] & "'" DoCmd.OpenForm stDocName, , , stLinkCriteria Exit_Requirements_Click: Exit Sub Err_Requirements_Click: MsgBox Err.Description Resume Exit_Requirements_Click End Sub 'Private Sub Purchase_history_Click() 'On Error GoTo Err_Purchase_history_Click ' ' Dim stDocName As String ' Dim stLinkCriteria As String ' ' stDocName = "ItemPurchaseHistory" ' ' stLinkCriteria = "[ItemNo]=" & "'" & Me![ItemNo] & "'" ' DoCmd.OpenForm stDocName, , , stLinkCriteria ' 'Exit_Purchase_history_Click: ' Exit Sub ' 'Err_Purchase_history_Click: ' MsgBox Err.Description ' Resume Exit_Purchase_history_Click 'End Sub 'Private Sub Command13_Click() 'On Error GoTo Err_Command13_Click ' ' ' Screen.PreviousControl.SetFocus ' DoCmd.FindNext ' 'Exit_Command13_Click: ' Exit Sub ' 'Err_Command13_Click: ' MsgBox Err.Description ' Resume Exit_Command13_Click 'End Sub 'Private Sub SalesOrderReqDetail_Click() 'On Error GoTo Err_SalesOrderReqDetail_Click ' ' Dim stDocName As String ' Dim stLinkCriteria As String ' ' stDocName = "ItemSOReq_detail" ' ' stLinkCriteria = "[ItemNo]=" & "'" & Me![ItemNo] & "'" ' DoCmd.OpenForm stDocName, , , stLinkCriteria ' 'Exit_SalesOrderReqDetail_Click: ' Exit Sub ' 'Err_SalesOrderReqDetail_Click: ' MsgBox Err.Description ' Resume Exit_SalesOrderReqDetail_Click 'End Sub 'Private Sub POOrderedDetail_Click() 'On Error GoTo Err_POOrderedDetail_Click ' ' Dim stDocName As String ' Dim stLinkCriteria As String ' ' stDocName = "ItemPOrdered_Detail" ' ' stLinkCriteria = "[ItemNo]=" & "'" & Me![ItemNo] & "'" ' DoCmd.OpenForm stDocName, , , stLinkCriteria ' 'Exit_POOrderedDetail_Click: ' Exit Sub ' 'Err_POOrderedDetail_Click: ' MsgBox Err.Description ' Resume Exit_POOrderedDetail_Click 'End Sub 'Private Sub InvFCReport_Click() 'On Error GoTo Err_InvFCReport_Click ' ' Dim stDocName As String ' ' stDocName = "ItemForecast_Form" ' DoCmd.OpenReport stDocName, acPreview ' 'Exit_InvFCReport_Click: ' Exit Sub ' 'Err_InvFCReport_Click: ' MsgBox Err.Description ' Resume Exit_InvFCReport_Click 'End Sub 'Private Sub ItemQtyInvFC_Click() 'On Error GoTo Err_ItemQtyInvFC_Click ' ' Dim stDocName As String ' ' stDocName = "ItemForecast_form" ' DoCmd.RunMacro stDocName ' 'Exit_ItemQtyInvFC_Click: ' Exit Sub ' 'Err_ItemQtyInvFC_Click: ' MsgBox Err.Description ' Resume Exit_ItemQtyInvFC_Click 'End Sub
ODBC--call failed.
[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid Column name 'Product Group'/ (#207)
It doesn't even load the query
Any thoughts on that one?