Looping

[ddrew]

How do I loop through all the records in a query to check if conditions are met


please. This is the code that I have right now, it checks to see if there are
any records in the query and then I need it to check to see that
Me.AnalystAttended is not an empty field and I need it to do that for each of
the records.

I would really appreciate some
help on this, I know I need to do a loop, I've read some posts on looping but I
cant get my head around it. Thanks

Private Sub Command203_Click()

Dim OpenRecordCheck As Integer

OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", "[Fault_Rectified] = False")
        If OpenRecordCheck > 0 Then
            If IsNull(Me.AnalystAttended) Then
             MsgBox "You must first select an Analyst", vbOKOnly
            Next
        Else
         MsgBox (OpenRecordCheck)
         End If
End Sub

[John_G]

Hi -

You need to use VBA to put the query results into a recordset, then loop through the records.

Your code would look something like this:

Dim db as Database, rst as RecordSet, SQL as string
set db = currentdb
SQL = "Select * from [Technical_Incident _Report Query] Where [Fault_Rectified] = False"
set rst = db.openrecordset(SQL)
while not rst.EOF
if isnull(rst!AnalystAttended) then
' Do something
endif
rst.movenext
wend

rst.close
set db=nothing

You could also just use DCount to see if there are any offending records, and give a message:

OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", "[Fault_Rectified] = False AND isnull(AnalystAttended)")
If OpenRecordCheck > 0 Then
MsgBox "There are " & OpenRecordCheck & " records without an analyst.", vbOKOnly
End If


Your current code above is in the On Click event of a form button; what is the code behind the button supposed to do?

John

[ddrew]

Hi -

You need to use VBA to put the query results into a recordset, then loop through the records.

Your code would look something like this:

Dim db as Database, rst as RecordSet, SQL as string
set db = currentdb
SQL = "Select * from [Technical_Incident _Report Query] Where [Fault_Rectified] = False"
set rst = db.openrecordset(SQL)
while not rst.EOF
if isnull(rst!AnalystAttended) then
' Do something
endif
rst.movenext
wend

rst.close
set db=nothing

You could also just use DCount to see if there are any offending records, and give a message:

OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", "[Fault_Rectified] = False AND isnull(AnalystAttended)")
If OpenRecordCheck > 0 Then
MsgBox "There are " & OpenRecordCheck & " records without an analyst.", vbOKOnly
End If


Your current code above is in the On Click event of a form button; what is the code behind the button supposed to do?

John

Hi John,
thanks for your reply, the button that its currently on is there just to allow me to make this small part of the process work. The button that it will finally be attached to is an 'Export' button where all the records get exported into a spreadsheet (this already works) but sometimes the guys at work leave some of the records unfinished so this will check the records prior to that. The form that the records are on is fed by a query 'echnical_Incident _Report Query'. I think your idea with the message box will fulfill this so I will give it a go tomorrow at work. I will let you know.

Many thanks

[ddrew]

Hi -

You need to use VBA to put the query results into a recordset, then loop through the records.

Your code would look something like this:

Dim db as Database, rst as RecordSet, SQL as string
set db = currentdb
SQL = "Select * from [Technical_Incident _Report Query] Where [Fault_Rectified] = False"
set rst = db.openrecordset(SQL)
while not rst.EOF
if isnull(rst!AnalystAttended) then
' Do something
endif
rst.movenext
wend

rst.close
set db=nothing

You could also just use DCount to see if there are any offending records, and give a message:

OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", "[Fault_Rectified] = False AND isnull(AnalystAttended)")
If OpenRecordCheck > 0 Then
MsgBox "There are " & OpenRecordCheck & " records without an analyst.", vbOKOnly
End If


Your current code above is in the On Click event of a form button; what is the code behind the button supposed to do?

John

Hi John, I used the code this morning

    OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", "[Fault_Rectified] = False AND isnull(AnalystAttended)")
    If OpenRecordCheck > 0 Then
        MsgBox "There are " & OpenRecordCheck & " records without an analyst.", vbOKOnly
    End If

And it works great but it needs more development as I have other criteria that needs to be met (three other fields).

What it needs to say is:
If the fields 'AnalystAttended' and 'CLS_Attended_At' or if 'Fault' or 'Soloution' are Null then (Message)

'AnalystAttended' and 'CLS_Attended_At' can either both be filled in or either one of them, whereas 'Fault' and 'Soloution' must both be filled in.

I'm not sure how to work this into the code that you wrote. Thanks

[rpeare]

Hi John, I used the code this morning
Code:

OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", "[Fault_Rectified] = False AND isnull(AnalystAttended)") If OpenRecordCheck > 0 Then MsgBox "There are " & OpenRecordCheck & " records without an analyst.", vbOKOnly End If
And it works great but it needs more development as I have other criteria that needs to be met (three other fields).

What it needs to say is:
If the fields 'AnalystAttended' and 'CLS_Attended_At' or if 'Fault' or 'Soloution' are Null then (Message)

'AnalystAttended' and 'CLS_Attended_At' can either both be filled in or either one of them, whereas 'Fault' and 'Soloution' must both be filled in.

I'm not sure how to work this into the code that you wrote. Thanks



Assuming you want to cycle through [Technical_Incident_report_Query] you would have this:

dim db as database
dim rst as recordset
dim sAnalyst
dim sCLS
dim sFault
dim sSolution

set db = currentdb

OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", "[Fault_Rectified] = False AND isnull(AnalystAttended)")     If OpenRecordCheck > 0 Then         
     MsgBox "There are " & OpenRecordCheck & " records without an analyst.", vbOKOnly     
     set rst = db.openrecordset ("Technical_Incident_Report_Query")
     rst.movefirst
     do while rst.eof <> true
         sAnalyst = rst.fields("AnalystAttended")
         sCLS = rst.fields("CLS_Attended_At")
         sFault = rst.fields("Fault")
         sSolution = rst.fields("solution")

         'perform whatever checks you want with the variables you mentioned    
        if not isnull(sanalyst) or not isnull(scls) then
            'is this where you find a 'positive' result?
        elseif not isnull(sfault) and not isnull(ssolution) then
           'is this where you find a 'failing' result?
        else
           'you have to provide for the possibility of fault or solution being null while the other is filled in
           'you have to provide for the possibility that neither analyst or cls is filled in AND neither fault or solution is filled in either and so on
        endif
        rst.movenext
    loop
End If

set db = nothing

[ddrew]

Hi thanks for the reply, think I may have confuse you! Of the fours boxes, Fault and Soloution must be filled in, AnalystAttended OR CLS_Attended_At (or both of them) to be filled in. Hope that makes sense.

Its basically checking to make sure these criteria have been met and if they havent then a message is displayed.

[rpeare]

Right, I gave you the method of extracting the variables, how you test them is up to you

to test for null it's

isnull(variablename)

to test for non null it's

not isnull(variablename)

Just nest your IF statements any way you want to select the data

[John_G]

Hi -

Assuming all you want to do at first is to see if there are any records that fail the crtiteria, then you can modify the DCount to include the other conditions:


dim WhereClause as String
WhereClause = "not([Fault_Rectified] AND [Soloution]) OR not (AnalystAttended'or 'LS_Attended_At)"
OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", WhereClause)
If OpenRecordCheck > 0 Then
MsgBox "There are " & OpenRecordCheck & " records that are missing required data.", vbOKOnly
End If

(I'm not sure I have the field names right.)

I'm also assuming that all your criteria fields are Boolean (Yes/No or T/F); if this is not the case, then you'll need to use isNull(...) for those fields, but the basic structure of the Criteria string will be the same.

Note that this will tell you only that there are records that fail the criteria; it doesn't tell you which ones they are. If you want to see and edit those records, then you can requery your form using the same where clause as a filter.

John

[ddrew]

dim WhereClause as String
WhereClause = "not([Fault_Rectified] AND [Soloution]) OR not (AnalystAttended'or 'LS_Attended_At)"
OpenRecordCheck = DCount("[ExID]", "[Technical_Incident _Report Query]", WhereClause)
If OpenRecordCheck > 0 Then
MsgBox "There are " & OpenRecordCheck & " records that are missing required data.", vbOKOnly
End

Hi John, thanks for your reply, this is helpful. The fields are in fact text and combo boxes. I had to modify the code a little but it certainley put me in the right direction, so many thanks for that. I have posted the modified code below.

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