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

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


"GLT" wrote:

> 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
> 
> 
> 
>