Capturing data during loop and performing "if" statement

I got some help to get this code below to help loop through a "from" and "to" to enter all into the table... so now I want to capture the data as it loops through to check to see if this is already entered into the table

Dim strSQL As String
Dim Result As String


For iI = 0 To iTo - iFrom
         strSQL = "Insert into Table(Field1,Field2,Field3, Field4, Field5) values(""" & Me.iFrom + iI & """,""" & _
            Me.Field2 & """,""" & Me.Field3 & """,""" & Me.Field4 & """,""" & Me.Field 5 & """ )"
            CurrentDb.Execute strSQL, dbFailOnError

Next

Open in new window

I entered this below but if I put it in between, I can capture the first loop only...how can I capture and loop through all and show the MSGBox showing each one that is already in use? Using the 1 msgbox?

Dim strSQL As String
Dim Result As String

For iI = 0 To iTo - iFrom
Result = Me.iFrom + iI
    If Result = DLookup("Field1", "Table", "Field1=" & Result) Then
        'Debug.Print Result
        MsgBox "this is already in use" & " " & Result
        Cancel = True
        Exit Sub
    Else

         strSQL = "Insert into Table(Field1,Field2,Field3, Field4, Field5) values(""" & Me.iFrom + iI & """,""" & _
            Me.Field2 & """,""" & Me.Field3 & """,""" & Me.Field4 & """,""" & Me.Field5 & """ )"
            CurrentDb.Execute strSQL, dbFailOnError
       
    End If
Next

Open in new window

Dim strSQL As String Dim Result As String Dim sNotification as String sNotification = "" For iI = 0 To iTo - iFrom Result = Me.iFrom + iI If Result = DLookup("Field1", "Table", "Field1=" & Result) Then 'Debug.Print Result sNotification = sNotification & Result & ", " Else strSQL = "Insert into Table(Field1,Field2,Field3, Field4, Field5) values(""" & Me.iFrom + iI & """,""" & _ Me.Field2 & """,""" & Me.Field3 & """,""" & Me.Field4 & """,""" & Me.Field5 & """ )" CurrentDb.Execute strSQL, dbFailOnError End If Next MsgBox "These ids are already in use: " & sNotification

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LVL 120

Rey Obrero (Capricorn1)Commented: 2016-01-02

try commenting these two lines

'Cancel = True
'Exit Sub

LVL 8

Abhigyan SrivastavaCommented: 2016-01-02

You don't Exit Sub. If you call that, you will come out of the loop and the function which means further records will not be entered.

What you can do is remove the two lines:

Cancel = True
Exit Sub

Now this is how it should run:
For every record found using DLookup you would get the messagebox.

For records that are not found, the program will insert the record.

Ernest GroggSecurity Management InfoSecAuthor Commented: 2016-01-02

That does work but brings up separate msgbox's? is that a way to get it under one to show the list of all for example

this is already in use 11,12,13,14,15

Oh. Yes you can do that too. There are two ways to do it.

Either you can store the results in a string and display the string in the messagebox after you exit the loop or you can put a textbox on the form with multiline property set to true and keep on adding the text to that.

Here is how you can do the first one.

You would have a extra "," in the messagebox. You can remove it from the string variable sNotification by using the below code. I did not do the modification so that it remains clear.

sNotification  = Left(sNotification, len(sNotification)-2)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial

If you wish to show the message in a text box, you can do that by simply replacing MsgBox with textbox1.Text =

But you should consider writing this program in VB.NET. It will be much faster than VBA and has got several built in functions to help you.

Abhigyan Srivastava:

perfect, exactly what I was looking for.

Good. Feel free to let me know if you face any other difficulties.

Capturing data during loop and performing "if" statement

Who is Participating?