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!
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
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
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
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
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
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:
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.
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.
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
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.
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
sNotification = Left(sNotification, len(sNotification)-2)
If you wish to show the message in a text box, you can do that by simply replacing MsgBox with textbox1.Text =
Premium Content
You need an Expert Office subscription to comment.Start Free Trial