Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Looping through recordset (2003)

    I have code that loops through a record set to find specific occurences of work orders. The code works only partially. My first three records contain PG, PM and UF in that order. The code finds UF but does not count it - see attached code.
    I have been pulling my hair out to find the logic error here. Can anybody help?

    Thanks so much....

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Schenectady, New York, USA
    Posts
    273
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Looping through recordset (2003)

    Should
    If work2 = "UG" Or work2 = "UG" Then

    be
    If work2 = "UG" Or work2 = "UF" Then
    Don
    <img src=/S/flags/USA.gif border=0 alt=USA width=30 height=18> <img src=/S/flags/NewYork.gif border=0 alt=NewYork width=30 height=18> "Life on Earth is expensive, but at least it includes a free trip around the Sun."

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through recordset (2003)

    In addition to Don Lansing's reply: unless the primary key of the table is the combination of strEquipNo and strWorkType, you cannot count on the records being traversed in the order you apparently expect. It'd be better to sort the recordset or to open a recordset on an SQL statement that sorts the records the way you want.

  4. #4
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Looping through recordset (2003)

    Thanks for the UF UG problem - I obviously cannot see the forest for the trees. However, that did not take care of the issue.
    Hans, I prepare the data by selecting all the PM, PG, UG, UF records from the raw data, then use a query to look for duplicates and make a new table. There is no good primary key available. Even Equip, Work type and date are not unique. The code seems to go through the data in the right order - notice the msgboxes. I use those to check the equipment number and Work type. What I am looking for is an occurrence of PM or PG followed by UG or UF for each equipment no. If it is found then go back to the PM or PG and mark it with the number of duplicate UG/UF.

    Thanks,

    itconc

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through recordset (2003)

    Could you post a stripped down copy of your database? See <post#=401925>post 401925</post#> for instructions.

  6. #6
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Looping through recordset (2003)

    Thank you - I am attaching a zip file.

    itconc

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through recordset (2003)

    After checking the next record, you should move back to the one where you were, otherwise the next record will be skipped. Try the attached code. I made the indenting more consistent and cleaned up the code a bit.

  8. #8
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Looping through recordset (2003)

    Thank you so much for your help. The table that I sent you run great. However, when I run the code with the previous and the next (1 and 3) it would not count multiples. When a pm/pg is followed by multiple uf/ug they each need to be counted. It does in table 2. I will look at the code tonight at home and see what I can find out.
    Thank you again!!

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through recordset (2003)

    What is the number of records involved? If it's less than 65,536 it might be a lot easier to do this in Excel instead of in Access.

  10. #10
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Looping through recordset (2003)

    The number of records is below 200 by the time I split out each month. We used to do it in Excel - my job is to automate the process in Access. In Excel it was a lot of visual inspection of the data. I can write code in Excel but would prefer to stay in Access. Some of my users are out in the field were it is difficult to help them if something goes wrong.
    I am wondering if I can use a dcount in the loop to count the ug/uf after a pm/pg.

    Thanks,

    itconc

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through recordset (2003)

    Does the query qryMultiples in the attached version do what you want? I added an AutoNumber field to the table to have a unique ID.

    Test thoroughly, for I made some implicit assumptions about your data that may not be correct.

  12. #12
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Looping through recordset (2003)

    The query is very interesting and it counts the various work types. However, it counts the pm/pg and the uf/ug. I am attaching a spreadsheet that is color coded with regards to the correct calculations (pink) and the missing ones (green).
    What I am looking for is:
    equip number 17251 had a pm done on 1/3/06. On 1/7/06, 1/10/06, and 1/28/06 3 ufs were done. I want the count for the pm on 1/3/06 to show a 3 not just a 1.
    Does this make sense?
    Thanks,

    itconc

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through recordset (2003)

    Is the attached better? I imported your Excel table into it.

  14. #14
    3 Star Lounger
    Join Date
    Oct 2004
    Location
    Houston, Texas, USA
    Posts
    242
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Looping through recordset (2003)

    I think so. I am not sure I understand the logic behind the queries - it is very clever. After looking at it a little longer, I will have to figure out how to combine that with the code in access to get a crosstab of the final result for 2006 data that shows the pm/pg with the numbers by month.
    Will let you know how it all comes out!

    Thanks

    itconc

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Looping through recordset (2003)

    I apologize, the queries aren't correct under all circumstances. I'll have to rethink this.

Page 1 of 2 12 LastLast

Posting Permissions

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