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.Valu e = Me.DateOff.Value
Forms!frmPTOCalendar.End_T ime.Value = Me.DateOff.Value
Forms!frmPTOCalendar.Descr iption.Val ue = 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.Valu e = Me.DateOff.Value
Forms!frmPTOCalendar.End_T ime.Value = Me.DateOff.Value
Forms!frmPTOCalendar.Descr iption.Val ue = Me.Comments.Value
DoCmd.GoToRecord , "frmPTOCalendar"
Loop Until .EOF
End With
End If
Thanks in advance for your assistance!
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
Forms!frmPTOCalendar.Start
Forms!frmPTOCalendar.End_T
Forms!frmPTOCalendar.Descr
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
Forms!frmPTOCalendar.Start
Forms!frmPTOCalendar.End_T
Forms!frmPTOCalendar.Descr
DoCmd.GoToRecord , "frmPTOCalendar"
Loop Until .EOF
End With
End If
Thanks in advance for your assistance!
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
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).
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
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.
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
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.Valu e = Me.DateOff.Value
Forms!frmPTOCalendar.End_T ime.Value = Me.DateOff.Value
Forms!frmPTOCalendar.Descr iption.Val ue = 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.Recor dsetClone
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.Reque ry
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.Valu e = Me.DateOff.Value
Forms!frmPTOCalendar.End_T ime.Value = Me.DateOff.Value
Forms!frmPTOCalendar.Descr iption.Val ue = Me.Comments.Value
DoCmd.GoToRecord , "frmPTOCalendar"
Thanks!
capricorn1's code below resulted in a RunTime error 3021 - No current record.
With Me.RecordsetClone
Do
If !Approved = True Then
Forms!frmPTOCalendar.Title
Forms!frmPTOCalendar.Start
Forms!frmPTOCalendar.End_T
Forms!frmPTOCalendar.Descr
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.Recor
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.Reque
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
Forms!frmPTOCalendar.Start
Forms!frmPTOCalendar.End_T
Forms!frmPTOCalendar.Descr
DoCmd.GoToRecord , "frmPTOCalendar"
Thanks!
<capricorn1's code below resulted in a RunTime error 3021 - No current record.>
which line is raising the error?
.
which line is raising the error?
.
ASKER
If !Approved = True Then
what is the name of the field bound to control Approved?
ASKER
The field is named Approved.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Exactly what I wanted. Thanks!
With Me.RecordsetClone
Do
If !Approved = True Then
Forms!frmPTOCalendar.Title
Forms!frmPTOCalendar.Start
Forms!frmPTOCalendar.End_T
Forms!frmPTOCalendar.Descr
DoCmd.GoToRecord , "frmPTOCalendar"
end if
Loop Until .EOF
End With