Link to home
Start Free TrialLog in
Avatar of Senniger1
Senniger1

asked on

Access Code to Loop Through All Records on a Filtered Form

In Access 2010 I have a Continuous Form that filters some records.

I have a Command Button named cmdSelectAll which puts a check in all the the checkboxes on this form.  Here's the code...

Private Sub cmdSelectAll_Click()
On Error GoTo Err_cmdSelectAll_Click
    With Me.RecordsetClone
       Do
           .Edit
           !Approved = True
            .Update
           .MoveNext
       Loop Until .EOF
    End With
Exit_cmdSelectAll_Click:
    Exit Sub
Err_cmdSelectAll_Click:
    MsgBox Err.Description
    Resume Exit_cmdSelectAll_Click
End Sub

I also have another Command Button on this form named cmdAddCalendar which when clicked will add the selected record to another form.  Here's this code...

Private Sub cmdAddCalendar_Click()
    Forms!frmPTOCalendar.Title.Value = Me.EmpInit.Value & " - PTO"
        Forms!frmPTOCalendar.Start_Time.Value = Me.DateOff.Value
            Forms!frmPTOCalendar.End_Time.Value = Me.DateOff.Value
                Forms!frmPTOCalendar.Description.Value = Me.Comments.Value
    DoCmd.GoToRecord , "frmPTOCalendar"
On Error Resume Next
    DoCmd.GoToRecord , , acNext
End Sub

What I need to be able to do now is when you click the cmdAddCalendar button, if there is a check in the checkbox (e.g. Approved = True) then loop through all the filtered records and add each one to the frmPTOCalendar form using the code.  Below is my attempt at this, but it doesn't work.

If Me.Approved = True Then
    With Me.RecordsetClone
       Do
        Forms!frmPTOCalendar.Title.Value = Me.EmpInit.Value & " - PTO"
            Forms!frmPTOCalendar.Start_Time.Value = Me.DateOff.Value
                Forms!frmPTOCalendar.End_Time.Value = Me.DateOff.Value
                    Forms!frmPTOCalendar.Description.Value = Me.Comments.Value
        DoCmd.GoToRecord , "frmPTOCalendar"
       Loop Until .EOF
    End With
End If

Thanks in advance for your assistance!
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

try this


    With Me.RecordsetClone
   
       Do

            If  !Approved = True Then

        Forms!frmPTOCalendar.Title.Value = Me.EmpInit.Value & " - PTO"
            Forms!frmPTOCalendar.Start_Time.Value = Me.DateOff.Value
                Forms!frmPTOCalendar.End_Time.Value = Me.DateOff.Value
                    Forms!frmPTOCalendar.Description.Value = Me.Comments.Value
        DoCmd.GoToRecord , "frmPTOCalendar"


       end if

       Loop Until .EOF

    End With
Try making use of the recordsets for both the source and target forms. Something like this:

dim rsOut as DAO.recordset
set rsOut = Forms!frmPTOCalendar.RecordsetClone
If Me.Approved = True Then
    With Me.RecordsetClone
       Do until .EOF
        rsOut.AddNew
        rsOut!Title.Value = !EmpInit.Value & " - PTO"
            rsOut!Start_Time.Value = !DateOff.Value
                rsOut!End_Time.Value = !DateOff.Value
                    rsOut!Description.Value = !Comments.Value
           rsOut.Update
        'DoCmd.GoToRecord , "frmPTOCalendar"
            .MoveNext
       Loop 
    End With
Forms!frmPTOCalendar.Requery
End If

Open in new window

Why are you looping, instead of using a SQL statement.

UPDATE yourQuery SET [YesNoField] = -1

You could then create an INSERT query to insert all of those dates into your calendar table (not the form).
using an  update query, you need to get the forms filter and use it as the where clause of the query..

dim sql
sql="update tableName" & _
 " set [Approved]=-1" & _
 " where  " & me.filter

currentdb.execute sql, dbfailonerror
cap,

It would be nice to give your fellow Experts (fyed in this case) a word of acknowledgement when you build on their ideas.
my post at http:#a38826931 was derived from fyed comment at http:#a38826917
Avatar of Senniger1
Senniger1

ASKER

Thanks for all the input so far...

capricorn1's code below resulted in a RunTime error 3021 - No current record.
    With Me.RecordsetClone
        Do
            If !Approved = True Then
                Forms!frmPTOCalendar.Title.Value = Me.EmpInit.Value & " - PTO"
                    Forms!frmPTOCalendar.Start_Time.Value = Me.DateOff.Value
                        Forms!frmPTOCalendar.End_Time.Value = Me.DateOff.Value
                            Forms!frmPTOCalendar.Description.Value = Me.Comments.Value
                DoCmd.GoToRecord , "frmPTOCalendar"
        End If
            Loop Until .EOF
                End With


mbizup's code below didn't error out, but didn't work.
dim rsOut as DAO.recordset
set rsOut = Forms!frmPTOCalendar.RecordsetClone
If Me.Approved = True Then
    With Me.RecordsetClone
       Do until .EOF
        rsOut.AddNew
        rsOut!Title.Value = !EmpInit.Value & " - PTO"
            rsOut!Start_Time.Value = !DateOff.Value
                rsOut!End_Time.Value = !DateOff.Value
                    rsOut!Description.Value = !Comments.Value
           rsOut.Update
        'DoCmd.GoToRecord , "frmPTOCalendar"
            .MoveNext
       Loop
    End With
Forms!frmPTOCalendar.Requery
End If

I'll look at the SQL statement and update query option, but I'm not sure I can figure it out.  I'll let you know.

I'm really fine with my code (below), but I just need it to do this for all the filtered records.
    Forms!frmPTOCalendar.Title.Value = Me.EmpInit.Value & " - PTO"
        Forms!frmPTOCalendar.Start_Time.Value = Me.DateOff.Value
            Forms!frmPTOCalendar.End_Time.Value = Me.DateOff.Value
                Forms!frmPTOCalendar.Description.Value = Me.Comments.Value
    DoCmd.GoToRecord , "frmPTOCalendar"

Thanks!
<capricorn1's code below resulted in a RunTime error 3021 - No current record.>

which line is raising the error?







.
If !Approved = True Then
what is the name of the field bound to control Approved?
The field is named Approved.
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Exactly what I wanted.  Thanks!