Results 1 to 13 of 13
  1. #1
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107

    looping code doesn't loop

    Can't figure out what is wrong with mycode. I have table "tbl_Tracking" with filed "EHT_Stage" and a form"frmTrackingProgress" with field name"txtEHTStage" (controlsource "EHT_Stage") and unbound column named "txtDescr"


    What I am trying to accomplish is based on value in "txtEHTStage"populate column "txtDescr"
    What my code does however, is that itevaluates only first record in txtEHTStage and fill out the column"txtDescr" with the same values ignoring the rest.


    For example

    txtEHTStage, txtDescr


    1 Designed
    0 Designed
    2 Designed

    What I am expecting to see is

    txtEHTStage txtDescr


    1 Designed
    0 Designnot Started
    2 Checked


    Thanks for your help.
    [CODE]

    Option Compare Database

    Option Explicit

    Dim db As DAO.Database

    Dim rs As DAO.Recordset



    Private Sub Form_Load()

    Dim txtDescr As String


    Set db = CurrentDb

    Set rs =db.OpenRecordSet("tbl_Tracking", dbOpenDynaset, dbSeeChanges)

    Do While Not rs.EOF

    If Nz(IsNull(Me.txtEhtStage)) Then

    Me.txtDescr = "N/A"

    ElseIf Me.txtEhtStage = "" Then

    Me.txtDescr = "Not Required"

    ElseIf Me.txtEhtStage = "0" Then

    Me.txtDescr = "Design not Started"

    ElseIf Me.txtEhtStage = "1" Then

    Me.txtDescr = "Designed"

    ElseIf Me.txtEhtStage = "2" Then

    Me.txtDescr = "Checked"

    Else

    Me.txtDescr = "Hold"

    End If

    rs.MoveNext

    Loop

    End Sub
    [/CODE]


  2. #2
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    You're looping a recordset, but not testing values from it. Your code all refers to the form, which stays on the first record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And by the way, an unbound textbox will display the same value for every record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  4. #4
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    pbaldy, this is my first attempt to do looping code, I have revised code following your suggestions bu but still it sits on first record
    Code:
    Option Compare Database
    
    
    Option Explicit
    
    
    
    
    
    Dim db As DAO.Database
    
    
    Dim rs As DAO.Recordset
    
    
    
    
    
    Private Sub Form_Load()
    
    
    Dim txtDescr As String
    
    
    
    
    
    Set db = CurrentDb
    
    
    Set rs = db.OpenRecordSet("tbl_Tracking",dbOpenDynaset, dbSeeChanges)
    
    
    If rs.RecordCount > 0 Then
    
    
    
    
    
    
    
    
    Do While Not rs.EOF
    
    
    
    
    
      IfNz(IsNull(Me.txtEhtStage)) Then
    
    
    
    
    
            Me.txtDescr ="N/A"
    
    
    
    
    
            ElseIfEHT_Stage = "" Then
    
    
            Me.txtDescr ="Not Required"
    
    
    
    
    
            ElseIfEHT_Stage = "0" Then
    
    
            Me.txtDescr ="Design not Started"
    
    
    
    
    
            ElseIfEHT_Stage = "1" Then
    
    
            Me.txtDescr ="Designed"
    
    
    
    
    
            ElseIfEHT_Stage = "2" Then
    
    
            Me.txtDescr ="Checked"
    
    
    
    
    
            Else
    
    
    
    
    
            Me.txtDescr ="Hold"
    
    
    
    
    
        End If
    
    
    
    
    
    rs.MoveNext
    
    
    Loop
    
    
    End If
    End Sub

  5. #5
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    What I mean is the code in red is referring to the form, not the recordset (and none of your code in the loop refers to the recordset):

    IfNz(IsNull(Me.txtEhtStage)) Then

    I'm not clear on what you're trying to achieve.
    If you're trying to affect the data in the table, you'd update the recordset rather than the form (or at least that's what I'd do). To refer to a field from the recordset:

    rs!FieldName

    to update data in the recordset:

    rs.Edit
    rs!FieldName = DesiredValue
    rs.Update



    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why are lines missing space after each If?

    The first If using Nz() makes no sense. Remove the Nz() function.

    As already pointed out, your code is not referencing recordset fields.

    If EHT_Stage is supposed to be recordset field then: rs!EHT_Stage. However, all the code will then do is repeatedly change value of textbox on form.

    What exactly are you trying to accomplish with recordset?
    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
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    And maybe I can clarify something. The recordset and form are two different things, even though they may refer to the same data. You're looping the recordset, and I think it's looping fine. The form never moves from the first record, so everything you do tests and updates that record.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    what I am trying to do is based on "eht_stage" value populate Descr. column, below is what I am gettig. I was thinking that if I loop trough recrdset I will get te values from my "If" statement for all the records.

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    Why are you not just using a bound form and bound textbox?
    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.

  10. #10
    pbaldy's Avatar
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,518
    I'll get out of the way.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  11. #11
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    Thanks pbaldy for pointing me in the right direction

  12. #12
    HS_1 is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Jan 2016
    Posts
    107
    June7, the form is bounded to table and txt box txtEhtDescr I also bounded to field in the same table as pbaldy recommended

  13. #13
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    There are better ways to accomplish this. There really is no need to save description as it can be calculated when needed.

    1. simple expression in textbox
    = Choose(Nz([EHT_Stage],3) + 1, "Design not Started", "Designed", "Checked", "Hold")

    2. multi-column combobox using a table/query or value list as RowSource

    3. lookup table for Stages and join tables in query

    4. if you must save description, an UPDATE action sql could do it for existing records, otherwise do it during data entry for a new record


    Last edited by June7; 08-08-2019 at 04:15 PM.
    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 some butchered code.
    By Homegrownandy in forum Programming
    Replies: 6
    Last Post: 05-18-2017, 07:19 AM
  2. Replies: 12
    Last Post: 06-05-2015, 04:27 PM
  3. looping code, a better way ?
    By trevor40 in forum Programming
    Replies: 8
    Last Post: 01-12-2015, 03:54 PM
  4. Replies: 13
    Last Post: 08-20-2014, 09:17 AM
  5. Looping code for printing reports
    By Lockrin in forum Access
    Replies: 2
    Last Post: 02-09-2010, 05:48 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