VB6 and Access 2003

[keithsimpson3973]

OK. I will try to make this understandable so here goes:

I have a vb 6 form called Forecast
I have 4 seperate forms, LMA1, LMA2,LMA3, and Impact Areas
LMA1,LMA2,and LMA3 all append to the Forecast Form to a text box on it
So the textbox on the Forecast Form will have multiple entries put into it

I need to take the data inside that text box and look for duplicates and then display a warning if duplication is found.

For example

from the LMA1 form the user selected Area1, Area4, and Area 8
from the LMA2 form the user selected Area2, Area4, and Area7
from the LMA3 form a user selected Area1, Area3, and Area10

So my textbox on the Forecast form would have all 9 of those selections in it.
The format for them is a single space between each value so my textbox data would be: Area1 Area4 Area8 Area2 Area4 Area7 Area1 Area3 Area10

I need the program to look at this text and see the duplicates and then show a warning that includes the Area number for duplication

Thanks in advance

[willakawill]

OK. I will try to make this understandable so here goes:

I have a vb 6 form called Forecast
I have 4 seperate forms, LMA1, LMA2,LMA3, and Impact Areas
LMA1,LMA2,and LMA3 all append to the Forecast Form to a text box on it
So the textbox on the Forecast Form will have multiple entries put into it

I need to take the data inside that text box and look for duplicates and then display a warning if duplication is found.

For example

from the LMA1 form the user selected Area1, Area4, and Area 8
from the LMA2 form the user selected Area2, Area4, and Area7
from the LMA3 form a user selected Area1, Area3, and Area10

So my textbox on the Forecast form would have all 9 of those selections in it.
The format for them is a single space between each value so my textbox data would be: Area1 Area4 Area8 Area2 Area4 Area7 Area1 Area3 Area10

I need the program to look at this text and see the duplicates and then show a warning that includes the Area number for duplication

Thanks in advance

Hi. Nice problem :)

Store the entries into an array via the Split() function

Dim ar As Variant
 
ar = Split(Textbox1.Text, " ")
 

Run it through a double loop to check for duplicates

For OuterLoop = 0 To UBound(ar) - 1
   For InnerLoop = OuterLoop + 1 To UBound(ar)
      If StrComp(ar(OuterLoop), ar(InnerLoop) = 0 Then
         'we have a duplicate so name it
         'either
         MsgBox "We have a duplicate for " & ar(OuterLoop)
 
         'or
         MsgBox "We have a duplicate for #" & Right(ar(OuterLoop), 1)
      End If
   Next InnerLoop
Next OuterLoop
 

Hope this helps

[Killer42]

Hi. Nice problem :)

Ah nuts! You beat me to it again.

I went off and wrote some sample code in VB, came back, refreshed the thread just in case, and willakawill strikes again. :)

One thing I was going to suggest is that by using a richtextbox rather than a regular textbox, one can actually highlight the duplicated words in some way - change their colour, or use bold, underline or whatever.

[willakawill]

Ah nuts! You beat me to it again.

I went off and wrote some sample code in VB, came back, refreshed the thread just in case, and willakawill strikes again. :)

One thing I was going to suggest is that by using a richtextbox rather than a regular textbox, one can actually highlight the duplicated words in some way - change their colour, or use bold, underline or whatever.

I only go for the ones that I know you are working on. It's a matter of professional pride :)

The offending item can also be highlighted in a plain textbox with SetFocus, SelStart and SelLength

[Killer42]

I only go for the ones that I know you are working on. It's a matter of professional pride :)

The offending item can also be highlighted in a plain textbox with SetFocus, SelStart and SelLength

Ah, but don't forget...

• Only for a single item (what if two are duplicated), and
• Only (I think) if HideSelection property is False.

[willakawill]

Ah, but don't forget...

• Only for a single item (what if two are duplicated), and
• Only (I think) if HideSelection property is False.

Au contraire mon ami :)
The HideSelection property only has effect when the control looses focus which is why I included the SetFocus method above ;)

[Killer42]

Au contraire mon ami :)
The HideSelection property only has effect when the control looses focus which is why I included the SetFocus method above ;)

From the context, I'd say the chances are good that it's not meant to have the focus in the first place.

Plus, my first point still applies.

[keithsimpson3973]

You guys are awesome! This is what I had started with. Obviously, I am not where even close to the calibur you guys are!


'Begin code

Public Sub Compare_Strings()

Dim Output1 As String
Dim Output2 As String
Dim Output3 As String
Dim Output4 As String
Dim Output5 As String
Dim Output6 As String
Dim Output7 As String
Dim Output8 As String
Dim Output9 As String
Dim Output10 As String
Dim Output11 As String
Dim Output12 As String
Dim Output13 As String
Dim Output14 As String
Dim Output15 As String
Dim Output16 As String
Dim Output17 As String
Dim Output18 As String
Dim Output19 As String
Dim Output20 As String
Dim Output21 As String
Dim Output22 As String
Dim Output23 As String
Dim Output24 As String
Dim Output25 As String
Dim Output26 As String
Dim Output27 As String
Dim Output28 As String
Dim Output29 As String
Dim Output30 As String
Dim Output31 As String
Dim Output32 As String
Dim Output33 As String
Dim Output34 As String
Dim Output35 As String
Dim Output36 As String
Dim Output37 As String
Dim Output38 As String
Dim Output39 As String
Dim Output40 As String
Dim Output41 As String
Dim Output42 As String
Dim Output43 As String
Dim Output44 As String
Dim Output45 As String
Dim Output46 As String
Dim Output47 As String
Dim Output48 As String


inputstring = frmGlobalForecast.txtSelectImpactArea.Text & frmGlobalForecast.txtLandMgmtAreaForecast.Text


Output1 = Split(inputstring, " ")(0)
On Error Resume Next
Output2 = Split(inputstring, " ")(1)
On Error Resume Next
Output3 = Split(inputstring, " ")(2)
On Error Resume Next
Output4 = Split(inputstring, " ")(3)
On Error Resume Next
Output5 = Split(inputstring, " ")(4)
On Error Resume Next
Output6 = Split(inputstring, " ")(5)
On Error Resume Next
Output7 = Split(inputstring, " ")(6)
On Error Resume Next
Output8 = Split(inputstring, " ")(7)
On Error Resume Next
Output9 = Split(inputstring, " ")(8)
On Error Resume Next
Output10 = Split(inputstring, " ")(9)
On Error Resume Next
Output11 = Split(inputstring, " ")(10)
On Error Resume Next
Output12 = Split(inputstring, " ")(11)
On Error Resume Next
Output13 = Split(inputstring, " ")(12)
On Error Resume Next
Output14 = Split(inputstring, " ")(13)
On Error Resume Next
Output15 = Split(inputstring, " ")(14)
On Error Resume Next
Output16 = Split(inputstring, " ")(15)
On Error Resume Next
Output17 = Split(inputstring, " ")(16)
On Error Resume Next
Output18 = Split(inputstring, " ")(17)
On Error Resume Next
Output19 = Split(inputstring, " ")(18)
On Error Resume Next
Output20 = Split(inputstring, " ")(19)
On Error Resume Next
Output21 = Split(inputstring, " ")(20)
On Error Resume Next
Output22 = Split(inputstring, " ")(21)
On Error Resume Next
Output23 = Split(inputstring, " ")(22)
On Error Resume Next
Output24 = Split(inputstring, " ")(23)
On Error Resume Next
Output25 = Split(inputstring, " ")(24)
On Error Resume Next
Output26 = Split(inputstring, " ")(25)
On Error Resume Next
Output27 = Split(inputstring, " ")(26)
On Error Resume Next
Output28 = Split(inputstring, " ")(27)
On Error Resume Next
Output29 = Split(inputstring, " ")(28)
On Error Resume Next
Output30 = Split(inputstring, " ")(29)
On Error Resume Next
Output31 = Split(inputstring, " ")(30)
On Error Resume Next
Output32 = Split(inputstring, " ")(31)
On Error Resume Next
Output33 = Split(inputstring, " ")(32)
On Error Resume Next
Output34 = Split(inputstring, " ")(33)
On Error Resume Next
Output35 = Split(inputstring, " ")(34)
On Error Resume Next
Output36 = Split(inputstring, " ")(35)
On Error Resume Next
Output37 = Split(inputstring, " ")(36)
On Error Resume Next
Output38 = Split(inputstring, " ")(37)
On Error Resume Next
Output39 = Split(inputstring, " ")(38)
On Error Resume Next
Output40 = Split(inputstring, " ")(39)
On Error Resume Next
Output41 = Split(inputstring, " ")(40)
On Error Resume Next
Output42 = Split(inputstring, " ")(41)
On Error Resume Next
Output43 = Split(inputstring, " ")(42)
On Error Resume Next
Output44 = Split(inputstring, " ")(43)
On Error Resume Next
Output45 = Split(inputstring, " ")(44)
On Error Resume Next
Output46 = Split(inputstring, " ")(45)
On Error Resume Next
Output47 = Split(inputstring, " ")(46)
On Error Resume Next
Output48 = Split(inputstring, " ")(47)
On Error Resume Next


'Eliminate the potential for duplicate empty strings
If Output1 = "" Then
Output1 = "N/A 1"
End If

If Output2 = "" Then
Output2 = "N/A 2"
End If

If Output3 = "" Then
Output3 = "N/A3 "
End If

If Output4 = "" Then
Output4 = "N/A4 "
End If

If Output5 = "" Then
Output5 = "N/A5 "
End If

If Output6 = "" Then
Output6 = "N/A6 "
End If

If Output7 = "" Then
Output7 = "N/A7 "
End If

If Output8 = "" Then
Output8 = "N/A8 "
End If

If Output9 = "" Then
Output9 = "N/A9 "
End If

If Output10 = "" Then
Output10 = "N/A10 "
End If

If Output11 = "" Then
Output11 = "N/A11 "
End If

If Output12 = "" Then
Output12 = "N/A12 "
End If

If Output13 = "" Then
Output13 = "N/A13 "
End If

If Output14 = "" Then
Output14 = "N/A14 "
End If

If Output15 = "" Then
Output15 = "N/A15 "
End If

If Output16 = "" Then
Output16 = "N/A16 "
End If

If Output17 = "" Then
Output17 = "N/A17 "
End If

If Output18 = "" Then
Output18 = "N/A18 "
End If

If Output19 = "" Then
Output19 = "N/A19 "
End If

If Output20 = "" Then
Output20 = "N/A20 "
End If

If Output21 = "" Then
Output21 = "N/A21 "
End If

If Output22 = "" Then
Output22 = "N/A22 "
End If

If Output23 = "" Then
Output23 = "N/A23 "
End If

If Output24 = "" Then
Output24 = "N/A24 "
End If

If Output25 = "" Then
Output25 = "N/A25 "
End If

If Output26 = "" Then
Output26 = "N/A26 "
End If

If Output27 = "" Then
Output27 = "N/A27 "
End If

If Output28 = "" Then
Output28 = "N/A28 "
End If

If Output29 = "" Then
Output29 = "N/A29 "
End If

If Output30 = "" Then
Output30 = "N/A30 "
End If

If Output31 = "" Then
Output31 = "N/A31 "
End If

If Output32 = "" Then
Output32 = "N/A32 "
End If

If Output33 = "" Then
Output33 = "N/A33 "
End If

If Output34 = "" Then
Output34 = "N/A34 "
End If

If Output35 = "" Then
Output35 = "N/A35 "
End If

If Output36 = "" Then
Output36 = "N/A36 "
End If

If Output37 = "" Then
Output37 = "N/A37 "
End If

If Output38 = "" Then
Output38 = "N/A38 "
End If

If Output39 = "" Then
Output39 = "N/A39 "
End If

If Output40 = "" Then
Output40 = "N/A40 "
End If

If Output41 = "" Then
Output41 = "N/A41 "
End If

If Output42 = "" Then
Output42 = "N/A42 "
End If

If Output43 = "" Then
Output43 = "N/A43 "
End If

If Output44 = "" Then
Output44 = "N/A44 "
End If

If Output45 = "" Then
Output45 = "N/A45 "
End If

If Output46 = "" Then
Output46 = "N/A46 "
End If

If Output47 = "" Then
Output47 = "N/A47 "
End If

If Output48 = "" Then
Output48 = "N/A48 "
End If


'Trap value of output1
If Output1 = Output2 Or Output1 = Output3 Or Output1 = Output4 Or Output1 = Output5 Or _
Output1 = Output6 Or Output1 = Output7 Or Output1 = Output8 Or Output1 = Output9 Or Output1 = Output10 Or _
Output1 = Output11 Or Output1 = Output12 Or Output1 = Output13 Or Output1 = Output14 Or _
Output1 = Output15 Or Output1 = Output16 Or Output1 = Output17 Or Output1 = Output18 Or _
Output1 = Output19 Or Output1 = Output20 Or Output1 = Output21 Or Output1 = Output22 Or _
Output1 = Output23 Or Output1 = Output24 Or Output1 = Output25 Or Output1 = Output26 Or _
Output1 = Output27 Or Output1 = Output28 Or Output1 = Output29 Or Output1 = Output30 Or _
Output1 = Output31 Or Output1 = Output32 Or Output1 = Output33 Or Output1 = Output34 Or _
Output1 = Output35 Or Output1 = Output36 Or Output1 = Output37 Or Output1 = Output38 Or _
Output1 = Output39 Or Output1 = Output40 Or Output1 = Output41 Or Output1 = Output42 Or _
Output1 = Output43 Or Output1 = Output44 Or Output1 = Output45 Or Output1 = Output46 Or _
Output1 = Output47 Or Output1 = Output48 Then
frmGlobalForecast.CheckArea = 1
'MsgBox "The Land Management Area(s) and/or Impact Area(s) you have chosen have already " & _
'"been scheduled for training use!", vbOKOnly, "Conflicting Schedule"
End If

End Sub

[willakawill]

OK here is a shorter version for you.

Dim OuterLoop As Long
Dim InnerLoop As Long
Dim ar As Variant
 
ar = Split(inputstring, " ")
 
For OuterLoop = 0 To UBound(ar) - 1
   For InnerLoop = OuterLoop + 1 To UBound(ar)
      If StrComp(ar(OuterLoop), ar(InnerLoop) = 0 Then
         'we have a duplicate so name it
        frmGlobalForecast.CheckArea = 1
        MsgBox "The Land Management Area(s) and/or Impact Area(s) " _
               & ar(OuterLoop) _
               & " have already been scheduled for training use" _
               ,vbOKOnly, "Conflicting Schedule"
      End If
   Next InnerLoop
Next OuterLoop
 

[Killer42]

This provides an excellent illustration of exactly why arrays exist.

[Killer42]

You guys are awesome! This is what I had started with. Obviously, I am not where even close to the calibur you guys are!

Nonsense. It's just a matter of a bit of time and experience. You'll be doing stuff like this in your sleep, before you know it. :)

Just a small point - once you issue the On Error Resume Next, it stays in effect until you change it. In other words, where you have this

On Error Resume Next
Output2 = Split(inputstring, " ")(1)
 On Error Resume Next
Output3 = Split(inputstring, " ")(2)
 On Error Resume Next
Output4 = Split(inputstring, " ")(3)
 On Error Resume Next
Output5 = Split(inputstring, " ")(4)

you would achieve exactly the same effect with this

On Error Resume Next
Output2 = Split(inputstring, " ")(1)
Output3 = Split(inputstring, " ")(2)
Output4 = Split(inputstring, " ")(3)
Output5 = Split(inputstring, " ")(4)

[keithsimpson3973]

Killer42 and WillakaWill,
Thanks so much for the help! And I hope you are right Killer42, that maybe some day I might actually be able to post here to help someone out!
Thanks again so very much!
And thanks for the last tip about the error statement.

[Killer42]

Killer42 and WillakaWill,
Thanks so much for the help! And I hope you are right Killer42, that maybe some day I might actually be able to post here to help someone out!
Thanks again so very much!

No problem. If you just hang around, you'll be amazed at how soon you see a question and think "Hang on! I know that."

[willakawill]

Killer42 and WillakaWill,
Thanks so much for the help! And I hope you are right Killer42, that maybe some day I might actually be able to post here to help someone out!
Thanks again so very much!
And thanks for the last tip about the error statement.

That's very true. Killer started asking questions here only 2 weeks ago. Now look at him fly :)

[Killer42]

That's very true. Killer started asking questions here only 2 weeks ago. Now look at him fly :)

Actually, it was more like five or six weeks - guess I'm just a bit s..l..o..w... ;)