Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2008
    Posts
    78

    Unanswered: Creating an exception/problem table from a main table interatively

    I have an access table with several thousand orders. Each order is assigned to a particular engineer and there can be up to 20 things wrong with an order. It could be missing a Circuit ID, a LAN Block, one of the due dates could be missing, one of the many due dates could have gone late, one of the IP addresses could be malformed, etc.

    I'm new in access and my first solution was to produce a series of reports that point out some of the problems the engineers can miss on their orders. My problem is that some of the orders, of course, appeared on multiple reports. This caused problems with the engineers going in and touching the order multiple times in order to fix it. Some of the problems are not obvious and require calculations/processing to uncover.

    What I'd like to do is create a temp table (orderProblems) that has a structure simililar to the following:


    OrderNum
    Engineer
    Status
    MissingLANBLock
    MissingCircuitID
    DesignOverdue
    InstallOverdue
    etc.

    The first three columns will identify the order, and the rest of the colums will be checked off if that problem exists. When done, the engineers will get a spreadsheet they can filter on, etc.

    The difficulty I'm having is that I'm unfamiliar with Access and don't know how I should iterate through the main ordertable. I don't think this is a query I can write. I'd rather start up a code module that starts at the top of the orders table, runs through some conditionals, if it finds problem(s), populates the "orderProblems" table with the info, then iterates to the next record until all the orders have been examined.

    I'll be left with a roll-up table that I can report on, do cross-tab queries for counts, etc.

    What mechanism should I use?

    For example, assume I want to skip through the order table and populate the ordersProblems table with any order belonging to "Nelson". Now I know that could be done in one easy query but I will eventually be testing for up to 20 or more conditions. How would I achieve this? Is there some simple code someone could post to get me going. I'd surely be able to modify it to include the 20+ tests I'll be using.


    So far I've only used the query,table, macro, modules and reports. I have not used forms. I'm thinking a form where I press one button that fires off some code in a module, but not sure of the approach.

    Thanks in advance!

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I'll pretend to forget about the fact you haven't used forms.

    Code to loop over records would be something like this:

    Code:
    Dim RSO as RecordSet
    Dim RST as RecordSet
    Set RSO = CurrentDB.OpenRecordset("SELECT * FROM tblOrders")
    Set RST = CurrentDB.OpenRecordset("SELECT * FROM OrderProblems")
    RSO.MoveFirst
    Do
       If IsNull(RSO!CircuitID) Then
          RST.AddNew
          RST!MissingCircuitID = True
          RST.Update
       End If
       RSO.MoveNext
    Loop Until RSO.EOF
    
    Set RSO = Nothing
    Set RST = Nothing
    That should get you started.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Aug 2008
    Posts
    78
    That's a great start. I can see the structure of the code and how I would modify it. Thanks much.

    Very newbie question though, where would I run this? How do I trigger it?

    I've coded a simple function in the module section that a query uses, but I'm not sure where I would be putting this code... A button on a form? Is there an interactive way I can trigger it w/out a form? At least for testing then I'll figure out how to put a form in front of it and let one of the users run this in the future ...

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome

    Normally you run all code from forms, but there are alternatives.

    You can run it from:

    * A macro.
    * Any event procedure on a form.
    * A custom menu item.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    Aug 2008
    Posts
    78
    I'm building a simple form and have the click event run the code. Thanks!

  6. #6
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by StarTrekker


    Code:
    Dim RSO as RecordSet
    Dim RST as RecordSet
    Set RSO = CurrentDB.OpenRecordset("SELECT * FROM tblOrders")
    Set RST = CurrentDB.OpenRecordset("SELECT * FROM OrderProblems")
    RSO.MoveFirst
    Do
       If IsNull(RSO!CircuitID) Then
          RST.AddNew
          RST!MissingCircuitID = True
          RST.Update
       End If
       RSO.MoveNext
    Loop Until RSO.EOF
    
    Set RSO = Nothing
    Set RST = Nothing
    That should get you started.
    Hi StarTrekker,

    I'm still not a genius at coding just yet, but could you explain in your code why you dimRSO and the dimRST? Do you have to DIM each Table you are creating the recordset from? I'm just curious because they both say dim AS Recordset.

    Just trying to learn something is all.

    have a nice one,
    BUD

  7. #7
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Sure.

    One recordset is used to loop through the orders (RSO). The other recordset is used to write order problems encountered while looping through the orders (RST).

    You need a separate recordset for each table you access simultaneously. If I were to loop through one recordset and then after that is complete, write to another, the same recordset object could be used for both because they are sequential. However, in this example, one recordset is partly looped through when it writes to another one... making it simultaneous access.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  8. #8
    Join Date
    Aug 2008
    Posts
    78
    Quote Originally Posted by StarTrekker
    Sure.

    One recordset is used to loop through the orders (RSO). The other recordset is used to write order problems encountered while looping through the orders (RST).

    You need a separate recordset for each table you access simultaneously. If I were to loop through one recordset and then after that is complete, write to another, the same recordset object could be used for both because they are sequential. However, in this example, one recordset is partly looped through when it writes to another one... making it simultaneous access.

    I must add that being a total newbie, this section of code worked out so well, I can't thank you enough. I was able to finish exactly what I had set out to do. In fact you can pass "RSO" as a parameter to a subroutine/function and have access to the entire record that RSO is pointing to. Very powerful and an excellent example piece of code that put me exactly on track. Thanks again!

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's not perfect though.... the whole process would die if there were no orders
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •