Results 1 to 2 of 2

Thread: For Each...Next

  1. #1
    Join Date
    Mar 2004
    Posts
    139

    Unanswered: For Each...Next

    Hi All,

    I have a form with a subform. The subform is a continuous form and has a check box named Auto_Restock. I want to click on a button on the form that will set each check box (Auto_Restock) in every record on the subform to -1. Here's what I'm using-

    Dim ctl As CheckBox

    Forms!TT2!OrderDetailSubform.SetFocus

    Set ctl = Forms!TT2!OrderDetailSubform!Auto_Restock

    For Each ctl In Screen.ActiveForm
    ctl = -1
    Next ctl

    This is giving me a 'type mismatch' error. If I switch the variable to Variant for fun I don't get an error but nothing happens. I'm using Access 2002.

    Any ideas?
    Thanks,
    Bill

  2. #2
    Join Date
    Feb 2004
    Posts
    533
    Quote Originally Posted by beeyule
    The subform is a continuous form and has a check box named Auto_Restock. I want to click on a button on the form that will set each check box (Auto_Restock) in every record on the subform to -1.
    Any ideas?
    I think a better approach to this is to Update the underlying recordset to set the value for the "AutoRestock" field to -1, then refresh the form to show the results.

    Do not have time to hack out an example but you can use DAO, it will go something like this. (Just to show Flow not exact code)

    set rst = NameOfSubform.RecordsetClone
    If rst.RecordCount > 0 Then
    rst.MoveLast
    rst.MoveFirst

    With rst
    Do While Not .EOF
    .Edit
    .[AutoRestock] = -1
    .MoveNext
    Loop
    Set rst = Nothing
    .Close
    End With
    End If

    NameOfSubform.Refresh
    ~

    Bill

Posting Permissions

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