Capturing data during loop and performing "if" statement

Ernest Grogg
Ernest Grogg used Ask the Experts™
on
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

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2016

Commented:
try commenting these two lines

        'Cancel = True
        'Exit Sub
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 InfoSec

Author

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
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

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

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.
Ernest GroggSecurity Management InfoSec

Author

Commented:
Abhigyan Srivastava:

perfect, exactly what I was looking for.
Good. Feel free to let me know if you face any other difficulties.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial