Drowning in Loops

[GLT]

Hi,

Im at my wits end with this - I am trying to test a frame on my form and if
the value is (1), I test three fields on my main form against three feilds on
my (unlinked sub form). If these three fields test true, then an error
message is displayed advising the user that the the same record already
exists, and exits. If anyone of them are false, then SQL is executed to add
the new record.

If the frame = 2, then the SQL is executed.

I have tried several different ways of doing this and each way (including
case statements) fail with various errors, would someone kindly advise how to
fix this?

All help is greatly appreciated...

Private Sub Command21_Click()

On Error GoTo Command21_Click_Err

Dim strSQL As String
Dim skip As Integer
Dim rs As DAO.Recordset

'DoCmd.SetWarnings False

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];"

If Me![Frame7] = 2 Then

DoCmd.OpenQuery ("Query5")
CurrentDb.Execute strSQL, dbFailOnError


ElseIf Me![Frame7] = 1 Then


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

If Not rs.EOF Then

CurrentDb.Execute strSQL, dbFailOnError

Else

Do While Not rs.EOF

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


If test1 = Me![fldSelShutType] Then

skip = 1

End If

If test2 = Me![fldSelServer] Then

skip = 2

End If

If test3 = Me![fldSelService] Then

skip = 3

End If

If skip = 3 Then

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

Exit Do

Else

CurrentDb.Execute strSQL, dbFailOnError
skip = 0

End If

rs.MoveNext

Loop

Set rs = Nothing

End If

End If

Me![subfrmPermSrvcsIgnore].Requery

'DoCmd.SetWarnings True

Command21_Click_Exit:
Exit Sub
Command21_Click_Err:
'DoCmd.SetWarnings True
'MsgBox "This record already exists..."
MsgBox Error
Resume Command21_Click_Exit

End Sub

 

[Daryl S]

GLT -

I have updated the logic portion based on what I understand from your
posting. See if this helps.

Private Sub Command21_Click()

On Error GoTo Command21_Click_Err

Dim strSQL As String
Dim skip As Integer
Dim rs As DAO.Recordset
Dim ThreeFieldMatch as Boolean

'DoCmd.SetWarnings False

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];"

If Me![Frame7] = 2 Then

DoCmd.OpenQuery ("Query5")
CurrentDb.Execute strSQL, dbFailOnError

ElseIf Me![Frame7] = 1 Then

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

If rs.EOF Then 'Changed this from If NOT rs.EOF Then (run SQL to add
records if none exist)

CurrentDb.Execute strSQL, dbFailOnError

Else
ThreeFieldMatch = FALSE

Do While Not rs.EOF

If (rs![Type] = Me![fldSelShutType] AND rs![Server] =
Me![fldSelServer] AND rs![Service Name] = Me![fldSelService]) Then
ThreeFieldMatch = TRUE
End If

rs.MoveNext

Loop

'Now we have tested all records in subform. If any one record had all
three fields match, then show error msg.
If ThreeFieldMatch = TRUE Then
MsgBox "This service is alreay in the ignore table under the
'ALL' catagory - no update made"
Else 'otherwise, no match, execute SQL
CurrentDb.Execute strSQL, dbFailOnError
End If

Set rs = Nothing

End If

End If

Me![subfrmPermSrvcsIgnore].Requery

'DoCmd.SetWarnings True

Command21_Click_Exit:
Exit Sub
Command21_Click_Err:
'DoCmd.SetWarnings True
'MsgBox "This record already exists..."
MsgBox Error
Resume Command21_Click_Exit

End Sub

--
Daryl S

Hi,

Im at my wits end with this - I am trying to test a frame on my form and if
the value is (1), I test three fields on my main form against three feilds on
my (unlinked sub form). If these three fields test true, then an error
message is displayed advising the user that the the same record already
exists, and exits. If anyone of them are false, then SQL is executed to add
the new record.

If the frame = 2, then the SQL is executed.

I have tried several different ways of doing this and each way (including
case statements) fail with various errors, would someone kindly advise how to
fix this?

All help is greatly appreciated...

Private Sub Command21_Click()

On Error GoTo Command21_Click_Err

Dim strSQL As String
Dim skip As Integer
Dim rs As DAO.Recordset

'DoCmd.SetWarnings False

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];"

If Me![Frame7] = 2 Then

DoCmd.OpenQuery ("Query5")
CurrentDb.Execute strSQL, dbFailOnError


ElseIf Me![Frame7] = 1 Then


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

If Not rs.EOF Then

CurrentDb.Execute strSQL, dbFailOnError

Else

Do While Not rs.EOF

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


If test1 = Me![fldSelShutType] Then

skip = 1

End If

If test2 = Me![fldSelServer] Then

skip = 2

End If

If test3 = Me![fldSelService] Then

skip = 3

End If

If skip = 3 Then

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

Exit Do

Else

CurrentDb.Execute strSQL, dbFailOnError
skip = 0

End If

rs.MoveNext

Loop

Set rs = Nothing

End If

End If

Me![subfrmPermSrvcsIgnore].Requery

'DoCmd.SetWarnings True

Command21_Click_Exit:
Exit Sub
Command21_Click_Err:
'DoCmd.SetWarnings True
'MsgBox "This record already exists..."
MsgBox Error
Resume Command21_Click_Exit

End Sub