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

Ernest GroggSecurity Management InfoSecAsked:
Who is Participating?
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.

Rey Obrero (Capricorn1)Commented:
try commenting these two lines

        'Cancel = True
        'Exit Sub
Abhigyan SrivastavaCommented:
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

Open in new window


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:
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
Your Guide to Achieving IT Business Success

The IT Service Excellence Tool Kit has best practices to keep your clients happy and business booming. Inside, you’ll find everything you need to increase client satisfaction and retention, become more competitive, and increase your overall success.

Abhigyan SrivastavaCommented:
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.

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

Open in new window

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)

Open in new window

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
Abhigyan SrivastavaCommented:
If you wish to show the message in a text box, you can do that by simply replacing MsgBox with textbox1.Text =
Abhigyan SrivastavaCommented:
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.
Ernest GroggSecurity Management InfoSecAuthor Commented:
Abhigyan Srivastava:

perfect, exactly what I was looking for.
Abhigyan SrivastavaCommented:
Good. Feel free to let me know if you face any other difficulties.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.