Results 1 to 8 of 8
  1. #1
    silverspr is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2011
    Posts
    19

    Double loop recordset

    Hello everyone
    I'm looking for assistance with looping through a recordset. I'm doing this a bit backwards, I have 30 performance reviews to complete and am using a word form to collect the data. I had no input with the design of the form so I'm stuck with what I have. I've imported each of the form fields 1-65 containing the responses. Starting with field 10 I have a 5 fields with a pre-entered value of either 1 or 0 (true or false). Dependent on this value (1 or 0) I need to assign a value to field(1) as "strongly agree",field(2) as "agree"......field(5) as "disagree" where the field value is "1". This pattern needs to repeat every 7 fields from field(1) until the end of the recordset (field 65).

    I hope I was able to explain this well enough if someone could get me started, I envision an outer and inner loop? The inner loop advances through each of the 5 fields and edit/updates depending on the value 1 or 0, the outer loop increments 7 to start the process over for the next set of 5 fields?



    thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    What purpose does recordset serve? Are you trying to update table with new values? I think you are taking the hard way with VBA and recordset approach. Use an UPDATE query to replace the data or just calculate the alias values whenever needed in query or textbox on form. These fields are number (not yes/no) data type? Use IIf, Switch, or Choose function. Like for field(1):

    Choose(field1=0,"strongly agree", field1=1,"disagree")
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    silverspr is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2011
    Posts
    19
    Thank you for reading my post. Yes I am trying to update an existing table, the table holds the imported word form data (150 feedback forms, 65 rows each). I'm open to any way that helps me achieve my purpose. Each group of 5 rows, are the checkbox responses from a questionaire, the responses are "strongly agree", "agree", "somewhat agree", "somewhat disagree", "disagree", "strongly disagree". The evaluator would check of the value they agreed with. I'm only interested in the row containing a value of "1" which I want to edit/update with one of the responses from the survey. The survey starts at row 10. If row 10 (F1) has a value of "1", translate the "1" to "strongly agree", if there is a "1" in row 11 (F2), translate to "agree" and so forth until the 6th row is evaluated (row 15), then repeat the process for the next group of 6 rows, starting at row 17. There is only one "1" in the group of rows, all other values are "0". I'm not sure Choose will work as I need to evaluate both the field value of the row and the row position. Again thanks for all your help.

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Okay, that gives me a different picture. Data from 150 forms with 65 rows each = 9,750 records? I presume the dataset has another field that identifies the source field (1 to 65), otherwise, how would you know which value to use? I still think a query could accomplish this. Just means a different conditional expression for the alias value. Again, this expression could be basis for an UPDATE action or to simply calculate the alias whenever needed.

    Could you provde a sample of the data? Attach Access file or spreadsheet.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    silverspr is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2011
    Posts
    19
    Thanks again, I hope I'm attaching a pdf of an xls file...(not done this before).




    showdata.pdf

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The spreadsheet would have been better but I got the gist of the problem.

    The conditional statement was too long for query so wrote a function:
    Code:
    Function ResponseAlias(Question As Integer) As String
    Select Case Question
        Case 10, 17, 24, 31, 38, 45, 52, 59
            ResponseAlias = "strongly agree"
        Case 11, 18, 25, 32, 39, 46, 53, 60
            ResponseAlias = "agree"
        Case 12, 19, 26, 33, 40, 47, 54, 61
            ResponseAlias = "somewhat agree"
        Case 13, 20, 37, 34, 41, 48, 55, 62
            ResponseAlias = "somewhat disagree"
        Case 14, 21, 38, 35, 42, 49, 56, 63
            ResponseAlias = "disagree"
        Case 15, 22, 39, 36, 43, 50, 57, 64
            ResponseAlias = "strongly disagree"
    End Select
    End Function
    That function can be called in a query or even in a textbox on form or report to display the alias, like:
    SELECT QID, IIf([Response]="1",ResponseAlias([QID]),[Response]) AS EvalResponse FROM Table1;

    Or can be called in the UPDATE TO row in an update query under the Response field, like:
    UPDATE Table1 SET Response = IIf([Response]="1",ResponseAlias([QID]),[Response]);
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    silverspr is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Mar 2011
    Posts
    19

    Thumbs up

    Sorry I wasn't able to upload the xls worksheet...website kept throwing an error
    This is awesome, I've tested the function and it works as required. I would never have thought of tackling the problem this way. I'm not sure I even knew you could have a select statement in a function....(self learned novice). Thanks again and just in time, tomorrow is my first sit down with the employee and performance review data.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    A UDF is just a procedure. Any code structure can be used. Good Luck!
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Loop through recordset
    By eliedebs in forum Programming
    Replies: 1
    Last Post: 01-11-2012, 12:26 AM
  2. Double Relational
    By jammmie999 in forum Access
    Replies: 1
    Last Post: 05-19-2011, 01:37 PM
  3. Bulk Email / Loop through recordset
    By smikkelsen in forum Forms
    Replies: 4
    Last Post: 07-12-2010, 06:59 PM
  4. switching int/double
    By giladweil in forum Access
    Replies: 2
    Last Post: 07-05-2010, 01:13 AM
  5. Replies: 1
    Last Post: 11-13-2009, 03:03 AM

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