Page 2 of 2 FirstFirst 12
Results 16 to 25 of 25
  1. #16
    techieanalyst is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    16

    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?

  2. #17
    techieanalyst is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    16
    set but im not seeing anything extra

  3. #18
    techieanalyst is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    16
    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

  4. #19
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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.

  5. #20
    techieanalyst is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    16
    Hate sounding like a tard but where does the Debug info go? Not seeing much on screen

  6. #21
    Dal Jeanis is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2013
    Location
    Dallas TX
    Posts
    1,742
    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:
    Code:
    Debug.print "Hello World"
    That's where any debug.print output should be showing up.

    You can also type things like this:
    Code:
    label12.Caption = "Fred"
    combo13.Value = 12
    words$ = " is now "
    Debug.print label12.Caption & words$ & combo13
    or, if you had a sub called "DoThis" and a function called "DoThat"
    Code:
    Call DoThis
    Text23 = DoThat()

  7. #22
    techieanalyst is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    16
    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?

  8. #23
    techieanalyst is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    16
    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

  9. #24
    techieanalyst is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    16
    Here's the form that's being run which includes all the VB Code etc:

    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
    When I try to open the Query under Query Design I just get an error:

    ODBC--call failed.
    [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid Column name 'Product Group'/ (#207)

    It doesn't even load the query

  10. #25
    techieanalyst is offline Novice
    Windows 8 Access 2010 32bit
    Join Date
    Jul 2013
    Posts
    16
    Any thoughts on that one?

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Code Issue
    By pbaker in forum Programming
    Replies: 9
    Last Post: 08-07-2012, 07:57 AM
  2. Intermittant VBA Code Issue
    By eking002 in forum Forms
    Replies: 9
    Last Post: 07-10-2012, 02:20 PM
  3. connecting SQL code for a Query
    By Huddle in forum Queries
    Replies: 1
    Last Post: 02-08-2012, 03:42 PM
  4. Code issue on Format of label
    By Gavroche in forum Reports
    Replies: 2
    Last Post: 09-14-2011, 06:19 AM
  5. Form Code issue
    By Gavroche in forum Forms
    Replies: 2
    Last Post: 09-10-2011, 09:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums