Solved

Access Code to Loop Through All Records on a Filtered Form

Posted on 2013-01-28
13
2,502 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 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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
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

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

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

776 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