Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access Code to Loop Through All Records on a Filtered Form

Posted on 2013-01-28
13
Medium Priority
?
2,772 Views
Last Modified: 2013-01-28
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!
0
Comment
Question by:Senniger1
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38826893
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38826895
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

0
 
LVL 49

Expert Comment

by:Dale Fye
ID: 38826917
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).
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38826931
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
0
 
LVL 61

Expert Comment

by:mbizup
ID: 38826955
cap,

It would be nice to give your fellow Experts (fyed in this case) a word of acknowledgement when you build on their ideas.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38826967
my post at http:#a38826931 was derived from fyed comment at http:#a38826917
0
 

Author Comment

by:Senniger1
ID: 38827657
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!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38827742
<capricorn1's code below resulted in a RunTime error 3021 - No current record.>

which line is raising the error?







.
0
 

Author Comment

by:Senniger1
ID: 38827775
If !Approved = True Then
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 38827815
what is the name of the field bound to control Approved?
0
 

Author Comment

by:Senniger1
ID: 38827862
The field is named Approved.
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 total points
ID: 38827896
try this

    With Me.RecordsetClone
         .movefirst
        Do until .eof
            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

            .Movenext

        Loop
   End With
0
 

Author Closing Comment

by:Senniger1
ID: 38827916
Exactly what I wanted.  Thanks!
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

782 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question