Array question

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a group of unbound fields (Named Field1, Field2, Field3 etc.) that
must have a unique number (integer) place in them. Is there a way to check
if a number has been duplicated in the fields and in what field it was found.
I am loading the array as follows.

Option Base 1

Dim i As Integer
Dim MyArray(18)
Dim TheField As String

For i = 1 To 18
TheField = "Field" & i
MyArray(i) = Me(TheField).Value
Next i

Your help is greatly appreciated.

Thanks,

Barry
 
Option Base 1

Dim i As Integer, j As Integer
Dim MyArray(18)
Dim TheField As String

For i = 1 To 18
TheField = "Field" & i
For j = (i-1) to 1
If Me(TheField).Value = MyArray(j) Then
MsgBox "Duplicate value on Field" & i & " and Field" & j
End If
Next j
MyArray(i) = Me(TheField).Value
Next i

Is that what you were looking for?
 
Thanks for getting back so quickly. I'm not sure that I understand the j
loop, specifically the (i-1) to 1 statement. Because I don't understand i=18
so (18-1) to 1. I'm confused! However, the msgbox statement appears to be
what I'm looking for. If you can site additional reference to this type of
loop statement that I can study it further and gain better understanding I
would certainly appreciate it.

Barry
 
I'm sorry, that should have been:
For j = (i-1) to 1 Step -1

Basically, if i=18, then you want to compare the current value against array
values 17 (18-1) to 1 in decrementing steps of 1.
Thanks for getting back so quickly. I'm not sure that I understand the j
loop, specifically the (i-1) to 1 statement. Because I don't understand i=18
so (18-1) to 1. I'm confused! However, the msgbox statement appears to be
what I'm looking for. If you can site additional reference to this type of
loop statement that I can study it further and gain better understanding I
would certainly appreciate it.

Barry
Option Base 1
Click to expand...
[quoted text clipped - 35 lines]
Click to expand...
 
First, there are no fields on a form. Forms have controls. Tables and
queries have fields.

what the loops are doing is comparing an element of the array to all the
other elements in the array looking for duplicates.
The
i = 1 to 18
loop (outer loop) starts with the first element and loops through all the
elements in the array.

The
j = (i-1 To 1)
loop is incorrect. It will throw a Subscript Out Of Range error on the
first iteration.
Since i = 1, then it will set j to 0. The Option Base 1 means all array
references start at 1, so 0 is out of range.

Getting the array loaded is not as simple as stated. You will have to
either assign each element a value specifically:

ar(1) = Me.txtFirstOne
ar(2) = Me.txtSecondOne
etc.

Or, for less coding, you can use the Tag property of your controls and enter
a value in those that should be included in comparison. then loop through
the form's controls collection and populate the array. So, in design view of
the form, put a value that will be meaningful to you in the Tag property.
For the example, I will use "Bozo"

The correct control logic should be:

Private Sub FindDups()
Dim ctl As Control
Dim i As Long
Dim j As Long
Dim ar(18) as Variant

For each ctl In Me.Controls
i = i + 1
If ctl.Tag = "Bozo" Then
ar(i) = ctl.Value
End If
Next ctl

For i = 18 To 1 Step -1
For j = 1 To i - 1
If ar(i) = ar(j) Then
MsgBox i & " And " & j & " Both = " & ar(i)
End If
Next j
Next i

End Sub
 
Thanks for clearing that up for me. If I have it correctly then, we should
be comparing some value (i) against all other values already in the array
starting with the value (i-1). Sounds reasonable to me. Would the same
thing be accomplished by j=1 to i -1 or (i-1)?

Barry

kingston via AccessMonster.com said:
I'm sorry, that should have been:
For j = (i-1) to 1 Step -1

Basically, if i=18, then you want to compare the current value against array
values 17 (18-1) to 1 in decrementing steps of 1.
Thanks for getting back so quickly. I'm not sure that I understand the j
loop, specifically the (i-1) to 1 statement. Because I don't understand i=18
so (18-1) to 1. I'm confused! However, the msgbox statement appears to be
what I'm looking for. If you can site additional reference to this type of
loop statement that I can study it further and gain better understanding I
would certainly appreciate it.

Barry
Option Base 1
Click to expand...
[quoted text clipped - 35 lines]
Barry
Click to expand...
Click to expand...
Click to expand...
 
Yes, you've got the right idea.
Thanks for clearing that up for me. If I have it correctly then, we should
be comparing some value (i) against all other values already in the array
starting with the value (i-1). Sounds reasonable to me. Would the same
thing be accomplished by j=1 to i -1 or (i-1)?

Barry
I'm sorry, that should have been:
For j = (i-1) to 1 Step -1
Click to expand...
[quoted text clipped - 16 lines]
Click to expand...
 
Thanks for catching my syntax error (fields and controls). I was originally
using the tag property to loop through all the fields but thought that the
idea of a "simulated control array" which I found in a Microsoft KB seemed
cleaner and would skip lots of other controls on the form. Silly me! I did
receive the subscript out of range error and was curious about that as well.
All the suggestions I recieved right or wrong add to my knowledge and I am
greatly appreciative.

Barry
 
Back
Top