stop loop

[icemonster]

so i got this code off a site, it's neat cause it loops through the form.

but how do i add in the else statement so that it won't loop? because basically what's happening is for each field in the form is the insert being run. any ideas?

       NotFilledIn = False
       For Each ctrl In Me
            If InStr(ctrl.Tag, "Required") Then
                 If IsNull(ctrl) Then 'first if
                     NotFilledIn = True
                     ctrl.BackColor = 10092543   'Yellow
                     ctrl.BorderColor = 13311    'red
                     
                 Else 'first if
                 
                        If IsNull(Me.txtemployerid) Then 'second if
    
                            If Not IsNull(Me.txtemployer) And Not IsNull(Me.txtjobtitle) And Not IsNull(Me.dtlastdayconv) And Not IsNull(Me.dtstartedconv) Then
                                  strSQL = "INSERT INTO tbl_employer " & _
                                           "(employer_name, employer_jobtitle, employer_startdate, employer_lastday, employer_createdon) VALUES ('" & Me.txtemployer & "', '" & Me.txtjobtitle & "', " _
                                         & "'" & Me.dtstartedconv & "', '" & Me.dtlastdayconv & "', Now());"
                                  .Execute strSQL, , adCmdText + adExecuteNoRecords
                                  strSQL = "SELECT Last_Insert_ID();"
                                    With .Execute(strSQL, , adCmdText)
                                        If Not (.BOF And .EOF) Then
                                            ' Found the new ID - build the second Insert SQL string
                                            lngLastEmployerID = .Fields(0)
                                        Else
                                            ' Abort
                                        End If
                                    .Close
                                    End With
                             End If
                        
                        Else 'second if
                        
                        End If 'second if


                  End If 'first if
                    
            End If
        Next ctrl

 

[VilaRestal]

I'm not quite sure what you mean but perhaps this will help:

You can exit a For loop with the line
Exit For
anywhere within it

 

[Severin]

your code loops through your form and when the tag is "required" it will run the if and if employee and titel are filled it will run the sql. So what you need is a way of telling the procedure that you have already run the sql. If it's 2007 or 2010 you could use tempvars otherwise you could have a hidden tickbox. The idea is to set a variable or tick the box when you run the sql and the add the criteria in the if clause!~)

 

[icemonster]

@VilaRestal exit for worked. thanks