Looping through a subform recordset

[GLT]

Hi,

I am trying to loop through an unbound subform, test it's three records with
the main forms three records, and if there is a match, then NOT execute the
SQL.

Is there a way to write this loop more effeciently? I am old school with
loops (for-next etc) - what would be the best loops to acheive this?

'Search in the clone set of the subform
Set rs = Me.subfrmPermSrvcsIgnore.Form.RecordsetClone

skip = 0

Do While Not rs.EOF

test1 = rs![Type]
test2 = rs![Server]
test3 = rs![Service Name]

If test1 = Me![fldSelShutType] Then

If test2 = Me![fldSelServer] Then

If test3 = Me![fldSelService] Then

MsgBox "This service is alreay in the ignore table under
the 'ALL' catagory - no update made"
skip = 1

End If

End If

End If

rs.MoveNext

Loop

'Set rs = Nothing

If skip = 0 Then

CurrentDb.Execute strSQL, dbFailOnError

Else

skip = 0

End If

 

[Stefan Hoffmann]

hi,

I am trying to loop through an unbound subform, test it's three records with
the main forms three records, and if there is a match, then NOT execute the
SQL.

Is there a way to write this loop more effeciently? I am old school with
loops (for-next etc) - what would be the best loops to acheive this?

I wouldn't use a loop at all. I would simply use one singel UPDATE SQL
statement using your IF condition's in the WHERE clause, e.g. like this

UPDATE yourTable
SET field = whatEver
WHERE NOT EXISTS (
SELECT *
FROM yourTable
WHERE [Type] = Me![fldSelShutType]
AND [Server] = Me![fldSelServer]
AND [Service Name] = Me![fldSelService]
)

With

CurrentDb.Execute strSQL, dbFailOnError
If CurrentDb.RecordsAffected = 0 Then
MsgBox "No update."
End If


mfG
--> stefan <--

 

[GLT]

Hi Stefan,

Thanks for your reply, I have looked at this SQL for a while and also copied
it to the Query Grid to figure out how it works and am a bit lost here...

In a nutshell what i was hoping to do was to loop through all the records in
the subform, test three feilds in both sub and main form, and if there is a
match not run the following SQL:

strSQL = "INSERT INTO tblPermSrvcsIgnore " & _
"( Type, Server, [Service Name] )" & _
" SELECT [FORMS]![frmAddPermNoMon]![fldSelShutType] AS Type" & _
", IIf([FORMS]![frmAddPermNoMon]![Frame7]=2, 'ALL', " & _
"[FORMS]![frmAddPermNoMon]![fldSelServer]) AS Server" & _
", [FORMS]![frmAddPermNoMon]![fldSelService] AS [Service Name];"

I dont want to run the above SQL, because that record is already exists in
the subforms table...

In your SQL, where does the 'field = whatever' fit into this?

Cheers,
GLT.

hi,

I am trying to loop through an unbound subform, test it's three records with
the main forms three records, and if there is a match, then NOT execute the
SQL.

Is there a way to write this loop more effeciently? I am old school with
loops (for-next etc) - what would be the best loops to acheive this?

I wouldn't use a loop at all. I would simply use one singel UPDATE SQL
statement using your IF condition's in the WHERE clause, e.g. like this

UPDATE yourTable
SET field = whatEver
WHERE NOT EXISTS (
SELECT *
FROM yourTable
WHERE [Type] = Me![fldSelShutType]
AND [Server] = Me![fldSelServer]
AND [Service Name] = Me![fldSelService]
)

With

CurrentDb.Execute strSQL, dbFailOnError
If CurrentDb.RecordsAffected = 0 Then
MsgBox "No update."
End If


mfG
--> stefan <--
.

 

[Lynn Trapp]

Is the procedure working as you expect it to? If so, then the only thing I
would suggest is not using a set of nested IF statements. As your code is,
the msgbox only fires if all three conditions are True. So why not just do it
all in one statement.

If (test1 = Me![fldSelShutType]) and (test2 = Me![fldSelServer]) And
(test3 = Me![fldSelService]) Then

MsgBox "This service is alreay in the ignore table under
the 'ALL' catagory - no update made"
skip = 1

End If