Solved

Access Code to Loop Through All Records on a Filtered Form

Posted on 2013-01-28
13
2,478 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 119

Expert Comment

by:Rey Obrero
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 47

Expert Comment

by:Dale Fye (Access MVP)
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
 
LVL 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
ID: 38826967
my post at http:#a38826931 was derived from fyed comment at http:#a38826917
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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 119

Expert Comment

by:Rey Obrero
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 119

Expert Comment

by:Rey Obrero
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 119

Accepted Solution

by:
Rey Obrero 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

863 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now