Solved

Access Code to Loop Through All Records on a Filtered Form

Posted on 2013-01-28
13
2,630 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 48

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 500 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

630 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