Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Do While Loop (Win XP Access 2003)

    Can you find my error with this routine? I get a "Loop without Do" error, with the loop statement near the end of the routine marked.

    With rst1 'tblFormulas
    strSearch = "[IDProducto] = " & Me!cmbNombreProducto
    .FindFirst strSearch
    If .NoMatch Then
    MsgBox "ID not found"
    Exit Sub
    End If

    Do While rst1!IDProducto = Me!cmbNombreProducto

    'How do I make the Do While loop?

    With rst
    .AddNew
    !NumOT = Me!NumOT
    !IDMaterial = rst1!IDMaterial
    !Cantidad = rst1!Cantidad
    .Update
    Loop
    .Close
    End With

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

    Re: Do While Loop (Win XP Access 2003)

    The inner With ... End With block should be nested entirely within the Do While ... Loop block.
    You don't loop at all since you don't move to a next record. Will DProductNo remain the same for some records? If so, you could use rst1.MoveNext. Otherwise, it's not clear what you want to do. I would probably have opened a recordset that selects only records for which DProducto equals Me!cmbNombreProducto.

    Does this work?

    With rst1 'tblFormulas
    strSearch = "<!t>[IDProducto]<!/t> = " & Me!cmbNombreProducto
    .FindFirst strSearch
    If .NoMatch Then
    MsgBox "ID not found"
    Exit Sub
    End If
    Do While rst1!IDProducto = Me!cmbNombreProducto
    With rst
    .AddNew
    !NumOT = Me!NumOT
    !IDMaterial = rst1!IDMaterial
    !Cantidad = rst1!Cantidad
    .Update
    End With
    .MoveNext
    Loop
    rst.Close
    .Close
    End With

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do While Loop (Win XP Access 2003)

    I run to the "Do While" statement, then the program jumps to the rst.close statement without running any of the code in between.

    As you mentioned, It might be easier to open the Dynaset with a filter to return only the required IDProducto records. How is that done?

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

    Re: Do While Loop (Win XP Access 2003)

    If rst is a recordset on tblFormulas, you can use something like

    Dim strSQL As String
    strSQL = "SELECT * FROM tblFormulas WHERE [IDProducto] = " & Me!cmbNombreProducto
    Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    Do While Not rst.EOF
    ' code goes here
    ...
    rst.MoveNext
    Loop
    rst.Close
    Set rst = Nothing

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Do While Loop (Win XP Access 2003)

    Very elegant. Thank you.

Posting Permissions

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