Need help with VBA, after converting from 2003 to 2010 problem with finding data

[hawaiianwarrior]

I posted this thread before, I really hope someone can help me with this one. Yesterday I opened the 2003 version again on an old computer and the original VBA code worked fine, it adds the right results in the record. Why it goes wrong in this VBA code (in new 2010 database) I have no idea.

I am working on a database which is converted from 2003 to 2010. After conversion there are some problems with VBA code. One of the problems is that it add the wrong data from a linked table. In this table it should look at the order and ship dates and then add the right data in the (sub)form. What it does now it looks for the item# and add the first entry found.
The table are related to each other. The database is build by several persons, I am just new to the company and trying to find out what their thought process was and how things are working in this database.

Please can anyone help me with a solution for this problem. Thanks in advance.

Sub fill_in_deals_data()
  Dim db As DAO.Database, CustomersRs As DAO.Recordset
  Dim Deals As DAO.Recordset
  Set db = DBEngine.Workspaces(0).Databases(0)
  Set CustomersRs = db.OpenRecordset("Customers", DB_OPEN_DYNASET)   'change_TABLE
  'CustomersRs.Index = "PrimaryKey"
  Set Deals = db.OpenRecordset("Deal Details", DB_OPEN_DYNASET)     'change_TABLE
  'Deals.Index = "Index 1"
   
  Dim Valid_Deal As Integer
  Dim Continue As Integer
  Dim A_Deal_Was_Found As Integer
  Dim Str1 As String
  Dim Str2 As String
  Dim Str3 As String
  Dim Item As String
  Dim Princ_ID As String
  Dim Cust_ID As String
  Dim Cust_Code As String
  Dim m_Order_Date As Date
  Dim m_Ship_Date As Date
  Dim m_Order_Type As String
   
  Item = Forms![Orders]![Orders Subform].Form![F-Item Num]
  Princ_ID = Forms!Orders![F-Princ ID]
  Cust_ID = Forms!Orders![F-Cust ID]
  m_Order_Date = Forms!Orders![F-Order Date]
  m_Ship_Date = Forms!Orders![F-Ship Date]
  m_Order_Type = Forms!Orders![F-Order Type]
  Valid_Deal = 1
  A_Deal_Was_Found = 0
  Str1 = "LESS $"
  Str2 = "LESS "
  Str3 = "/Case Off Invoice "
   
  Cust_Code = CustomersRs![Class of Trade]
  CustomersRs.Close
  Deals.FindFirst "[Princ ID] = '" & Princ_ID & "' and [Item Num] = '" & Item & "'"
  If Deals.NoMatch Then
      'MsgBox "No Deals"
      Continue = 0
  Else
      'MsgBox "Good Deal"
      Continue = 1
  End If
  Do While Continue = 1
      '*** Check for valid customer.
      If InStr(Deals![Cust Codes], Cust_Code) Or Deals![Cust Codes] = "A" Then
        'The deal is good
        Valid_Deal = 1
      Else 'The deal was no good
        Valid_Deal = 0
      End If
      '**** Check for valid dates.
    'The Order date is always checked to see if it is within the deal's
    'order date and ship date ranges.
      If Valid_Deal = 1 Then
        'I wish I could create a case statement for this.
        'I will simulate one.
        If IsNull(Deals![Order Start Date]) And Not IsNull(Deals![Ship Start Date]) Then
          'Only Check out ship dates.
           If m_Ship_Date < Deals![Ship Start Date] Or m_Ship_Date > Deals![Ship End Date] Then
             Valid_Deal = 0
           End If
           GoTo end_of_simulated_case
        End If
        If Not IsNull(Deals![Order Start Date]) And IsNull(Deals![Ship Start Date]) Then
          'Only Check out Order dates.
           If m_Order_Date < Deals![Order Start Date] Or m_Order_Date > Deals![Order End Date] Then
             Valid_Deal = 0
           End If
           GoTo end_of_simulated_case
        End If
        If Not IsNull(Deals![Order Start Date]) And Not IsNull(Deals![Ship Start Date]) Then
          'Check out both order dates ship dates.
           If m_Ship_Date > Deals![Ship Start Date] Or m_Ship_Date  < Deals![Ship End Date] Or m_Order_Date > Deals![Order Start Date]  Or m_Order_Date < Deals![Order End Date] Then
            Valid_Deal = 1
         Else
            Valid_Deal = 0
         End If
         GoTo end_of_simulated_case
      End If
      End If
  end_of_simulated_case:
        '****** Warn user if just missed deal
        If Valid_Deal = 0 Then
          If Abs(DateDiff("d", m_Ship_Date, NullToDate(Deals![Ship Start  Date]))) < 8 Or Abs(DateDiff("d", m_Ship_Date,  NullToDate(Deals![Ship End Date]))) < 8 Or Abs(DateDiff("d",  m_Order_Date, NullToDate(Deals![Order Start Date]))) < 8 Or  Abs(DateDiff("d", m_Order_Date, NullToDate(Deals![Order End Date])))  < 8 Then
            MsgBox "A deal was missed by 7 days or less"
          End If
        End If
      '** End of checking for valid dates
      If Valid_Deal = 1 Then
        'The deal has passed all tests so fill in the data into the order.
        A_Deal_Was_Found = 1


[COLOR=Red]'****** Till here it is going great, doing exactly what it suposed to be doing, but then it add the wrong data in the form.
'****** It is searching for the right Item # in the table, this Item# is  NOT unique, because the Item will be sold the whole year, but with  every quater a different promotion.
'****** With this said, every quater will give a different Deal Amt 1, F-Deal 1 Promo#, Bill Back Amt and F-Bill Back  Promo#.[/COLOR]


          If Deals![OI Type] = "$ Off Invoice" Then
              Forms![Orders]![Orders Subform].Form![Deal Amt 1] = Deals![OI Amount]
              Forms![Orders]![Orders Subform].Form![Deal Line 1] = Str1 & Format$(Deals![OI Amount], "#0.00") & Str3
              Forms![Orders]![Orders Subform].Form![F-Deal 1 Promo#] = Deals![OI Promo #]
          End If
          If Deals![OI Type] = "% Off Invoice" Then
              Forms![Orders]![Orders Subform].Form![Deal Amt 1] =  Deals![OI Amount] * 0.01 * Forms![Orders]![Orders Subform].Form![F-Case  Cost]
              Forms![Orders]![Orders Subform].Form![Deal Line 1] = Str2  & Format$(Deals![OI Amount], "#0") & "%" & Str3
              Forms![Orders]![Orders Subform].Form![F-Deal 1 Promo#] = Deals![OI Promo #]
          End If
          If Deals![BB Type] = "$ Bill Back" Then
              Forms![Orders]![Orders Subform].Form![Bill Back Line] =  "$" & Format$(Deals![BB Amount], "#0.00") & "/Case Bill Back "
              Forms![Orders]![Orders Subform].Form![Bill Back Amt] = Deals![BB Amount]
              Forms![Orders]![Orders Subform].Form![F-Bill Back  Promo#] = Deals![BB Promo #]
          End If
      End If
      Deals.MoveNext
      If Deals.EOF Then
          Continue = 0
      Else
          If Deals![Princ ID] <> Princ_ID Or Deals![Item Num] <> Item Then
             Continue = 0
          End If
      End If
  Loop
  If (A_Deal_Was_Found = 0 And Princ_ID = "MAN1") Or (A_Deal_Was_Found =  0 And Princ_ID = "MAN2") Or (A_Deal_Was_Found = 0 And Princ_ID =  "MAN3") Then
      'Fill in the special # for itmes with no deals. This number is
      'the last digit of the year in the ship date + "-998-99".
      ' ie 1995:5-998-99   1996:6-998-99
      Dim X As String
      X = Right$(DatePart("yyyy", m_Ship_Date), 2)
      'Forms![Orders]![Orders Subform].Form![F-Description] =  Forms![Orders]![Orders Subform].Form![F-Description] & "  " & x  & "-998-99"
      Forms![Orders]![Orders Subform].Form![F-Deal 1 Promo#] = X & "-998,99"
      If (b_Deal_Check = True) Then
          If Princ_ID = "MAN1" Then
              Forms![Orders]![Orders Subform].Form![F-Deal 1 Promo#] = X & "-999,99"
          End If
          If Princ_ID = "MAN2" Then
              Forms![Orders]![Orders Subform].Form![F-Deal 1 Promo#] = X & "-999,99"
          End If
          If Princ_ID = "MAN3" Then
              Forms![Orders]![Orders Subform].Form![F-Deal 1 Promo#] = X & "-999,99"
           End If
      End If
      If (b_Deal_Check1 = True) Then
          If Princ_ID = "MAN1" Then
              Forms![Orders]![Orders Subform].Form![F-Deal 1 Promo#] = X & "-998,99"
          End If
          If Princ_ID = "MAN2" Then
              Forms![Orders]![Orders Subform].Form![F-Deal 1 Promo#] = X & "-998,99"
          End If
          If Princ_ID = "MAN3" Then
              Forms![Orders]![Orders Subform].Form![F-Deal 1 Promo#] = X & "-998,99"
          End If
      End If
  End If
  Deals.Close
  End Sub

 

[boblarson]

If you are opening a table directly as the recordset then you can have a problem (it isn't just 2003 to 2010 related). Data in Access tables is not stored in any particular order. So, depending on the circumstances, you might get one set of data in one order this time and another the next UNLESS you use a QUERY instead of the table and you apply an ORDER to an appropriate field.

So, You need to do that if you expect it to work consistently.

 

[hawaiianwarrior]

Thanks for your quick reply, so in order to relate to the table deal details I have to do that with a query. Do I have to sort the query in a particular way? Because if I order for example on ship date (important field) then how does access know when the shipdate filled in on the mainform Orders is in a date range in the query?

Thanks again for your help.

 

[boblarson]

If I understand your code and what you are wanting, then I believe you want to ensure that your form is sorted by Ship Date DESC so that the latest date comes first. The same goes for the tables you are opening as dynaset in the recordset objects. That way the find first will find the first item # for the latest date that it was shipped.

 

[hawaiianwarrior]

That is partly correct, but it promotion are depending of quaters and timeframes during the year, like Halloween and Christmas. So it is not always the last ship date what gives the right answer. If the ship date is for example 25 May 2011, it falls in the 2nd quarter. but the last ship date time range for a promotion in the deal details table is the 4th quarter this year, so VBA will give that as answer with the wrong promotion code ([BB Promo #] and [BB Amount]). It gives or the first entry or the last entry from deal details, but not what is for the 2nd quarter. In 2003 it worked perfectly. It has to do something that in 2003 everything was in one database, now they split it up in a database with all the tables and the working database. Also they changed the code to DAO. Maybe this will help you to an answer.

Thanks so much for your help. I can't send the database as an attachment, because it is way to big to send. If you need more information, I am glad to copy and paste more code to the forum.