Looping Problem

[ddrew]

Im trying to loop through the records on a form but Im unsure how to do it! Below is the code I have written so far but it dosent work! Ive read through lots of articales but cant seem to understand how do it.

                If Me.txtsite = 0 Then
                    Do
                        While OpenRecordCheck > 0
                            If Me.Soloution = "Moved Crew" Then

                                Me.Total_Time_Down = ((Format(Me.Crew_Moved_Time, "hh") * 60) + Format(Me.Crew_Moved_Time, "nn")) - ((Format(Me.Time_Reported, "hh") * 60) + Format(Me.Time_Reported, "nn"))
                                Me.Time_Equipment_Back_Up = Now()
                                Me.Time_Equipment_Back_Up_TMIS = Now()
                                Me.Requery
                                TurnBlack
                            Else
                                Me.Time_Equipment_Back_Up = Now()
                                Me.Time_Equipment_Back_Up_TMIS = Now()
                                Me.Requery
                                Me.Total_Time_Down = ((Format(Me.Time_Equipment_Back_Up, "hh") * 60) + Format(Me.Time_Equipment_Back_Up, "nn")) - ((Format(Me.Time_Reported, "hh") * 60) + Format(Me.Time_Reported, "nn"))
                                TurnBlack

                            End If


                            If OpenRecordCheck = 0 Then
                        Exit Do
                    End If

 

[pr2-eugin]

Hello ddrew.. as we know absolutely nothing about your application, could you please give a bit more information on what is that you are trying to do? What is TurnBlack? What is OpenRecordCheck?

 

[ddrew]

Fair point! OpenRecordCheck is a count of all of the records on the form

OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", WhereClause)

TurnBlack is a procedure that turns a button black thats all

The important bit is:

                If Me.txtsite = 0 Then
                    Do
                        While OpenRecordCheck > 0
                            If Me.Soloution = "Moved Crew" Then

                                Me.Total_Time_Down = ((Format(Me.Crew_Moved_Time, "hh") * 60) + Format(Me.Crew_Moved_Time, "nn")) - ((Format(Me.Time_Reported, "hh") * 60) + Format(Me.Time_Reported, "nn"))
                                Me.Time_Equipment_Back_Up = Now()
                                Me.Requery
                                Else
                                Me.Time_Equipment_Back_Up = Now()
                                Me.Requery
                                Me.Total_Time_Down = ((Format(Me.Time_Equipment_Back_Up, "hh") * 60) + Format(Me.Time_Equipment_Back_Up, "nn")) - ((Format(Me.Time_Reported, "hh") * 60) + Format(Me.Time_Reported, "nn"))
                            End If


                            If OpenRecordCheck = 0 Then
                        Exit Do
                    End If

 

[pr2-eugin]

You still have not answered "What you actually want to do" with this piece of code..

Fair point! OpenRecordCheck is a count of all of the records on the form

DCount is a Domain function in the sense it looks into Tables/Queries not Forms.. If you wish to use the count of records on the Form you need to use

Me.Count

However this will not be entirely true if you have used some filters on your form..

The important bit is:..........

I am afraid this important bit does not make any sense to me unless you help us understand what you want to achieve..

 

[ddrew]

The form is made up of a bunch of filtered records, they are filtered by a checkbox. As soon as you click the checkbox to true it will dissapear from the form. There is a button on the form that allows the user to export all the records to a spredsheet. in order to do this all the records need to be closed. I need to loop through any open records (hence DCount OpenRecordCheck) and if there are open records to insert the time, work out the time lapse and close the record.

 

[pr2-eugin]

There is a button on the form that allows the user to export all the records to a spredsheet.

Are you using a function that performs the export or is it by using DoCmd.TransferSpreadSheet method??

in order to do this all the records need to be closed.

Not sure if this is 100% true.. it does not need to be closed.. It can be open..

I need to loop through any open records (hence DCount OpenRecordCheck) and if there are open records to insert the time, work out the time lapse and close the record.

There is no movement from one record to another.. so how are you performing the close operation? Is calculating a time lapse a part of the requirement?

 

[ddrew]

I already have the export working thats fine. The form shows a bunch of filered records. One of the ields is a start time the other is an end time there is also a calculated lapsed time field. The lapsed time is calculated when the user clicks the checkbox which also removes the record from the form (its a continuos form). The point is, the user wants all the records to be closed prior to export, so if the user hasnt closed them manually by clicking the checkbox then he wants the program to do it automatically as part of the export process.

Therefore prior to export the program has to look atthe form, count to see if there are any records and give them all a time (Now()) and calculate the lapsed then remove the recored from the form, then do the export (which is already working.

 

[pr2-eugin]

Okay now that is a good description of what you want to do and what you have already done. So that makes sense.. What you need is not DCount but Recordset's since it is from you can use

Me.RecordsetClone

and assign it to a recordset object, initially you check the record count of the object exit if it is 0 else manually update the recordset and call the export function at the end.. something along the lines of

Dim rstForm As Recordset

'see if form has any records displayed
Set rstForm = Me.RecordsetClone
If rstForm.EOF = True Then 
    MsgBox "Sorry, there are no records to export"
    Exit Sub
End If

Do While Not rstForm.EOF 
    'do your thing
    rstForm.MoveNext
Loop

rstForm.Close
Set rstForm = Nothing

There are several methods associated with RecordSet look in here for Edit, Update

 

[ddrew]

This is the complete code without your adjustments. Im unsure where I should put the modifications!

        'Checks for open records
        Dim WhereClause As String
        WhereClause = "isNull([CLS_Attended_At]) AND isNull ([AnalystAttended]) OR isNull ([Fault]) or isNull([Soloution]) AND Not([Fault_Rectified])"
        OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", WhereClause)
        If OpenRecordCheck > 0 Then
            MsgBox "There are " & OpenRecordCheck & " records that are missing required data.", vbOKOnly
        Else
            OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]")
            If OpenRecordCheck > 0 Then

                Me.Fault_Rectified = True

                If Me.txtsite = 0 Then
                    Do
                        While OpenRecordCheck > 0
                            If Me.Soloution = "Moved Crew" Then

                                Me.Total_Time_Down = ((Format(Me.Crew_Moved_Time, "hh") * 60) + Format(Me.Crew_Moved_Time, "nn")) - ((Format(Me.Time_Reported, "hh") * 60) + Format(Me.Time_Reported, "nn"))
                                Me.Time_Equipment_Back_Up = Now()
                                Me.Requery
                                TurnBlack
                            Else
                                Me.Time_Equipment_Back_Up = Now()
                                Me.Requery
                                Me.Total_Time_Down = ((Format(Me.Time_Equipment_Back_Up, "hh") * 60) + Format(Me.Time_Equipment_Back_Up, "nn")) - ((Format(Me.Time_Reported, "hh") * 60) + Format(Me.Time_Reported, "nn"))
                                TurnBlack

                            End If


                            If OpenRecordCheck = 0 Then
                                Exit Do
                            End If
                            XLExport 'This is a procedure that carries out the export

                            If MsgBox("Are you sure you want to delete all records?", vbQuestion + vbYesNo + vbDefaultButton2, "Delete Records?") = vbNo Then Exit Sub
                            With DoCmd
                                .SetWarnings False
                                .OpenQuery "qryDelete"
                                .OpenQuery "qrydeletetimings"
                                .SetWarnings True
                            End With
                            MsgBox "All records have been removed.", vbInformation, "Records Removed"
                            Me.Requery

                        End If

                    End If