Another Golf Handicap Question

[Barry]

I'm trying to loop through a golf "scorecard" to find if a duplicate entry
was made for an entry in the handicap field. Handicap is a measure of the
difficulty of a hole, 1 being the hardest, 18 the easiest. Anyway, I can
load all the values into an array. I want to find if the value being checked
is located anywhere else in the array and then use a message to that effect.
My problem is that sometimes I locate the field being checked instead and I'm
not sure how to skip it.

Any help is greatly appreciated.

Barry

 

[Alex Dybenko]

Hi,
not sure I quite understand the problem, but you can either create a unique
index in order to prevent duplicate entries, or run find duplicated values
query wizard and create a query which will check duplicates

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Barry]

Alex:
Each hole on the golf course has a corresponding handicap index, a par, and
a yardage.

Hole 1 2 3... 18
Par 4 3 5
Yard 200 150 560
Handicap 3 17 1

As the entries are put in the handicap textbox ( all are undbound textboxes)
Iwant to assure that none of the entries are repeated. Each hole must have a
unique handicap index of 1 through 18.

I have started by creating an array of the hole numbers and handicaps.

Dim i As Integer
Dim HandicapControl As String

'Load the array
For i = 1 To 18
HandicapControl = "Handicap" & i
If IsNull(Me(HandicapControl).Value) Or Me(HandicapControl).Value = 0
Then Exit For
HoleHandicap(i) = Me(HandicapControl).Value
Next i

Now I need to go back and make sure that there are no duplicates. I hope
this helps to explain things a little better.

Barry

 

[Alex Dybenko]

Hi,
ok, thanks, now I understand. I don't have algorithm to check for duplicates
in array, but sure you can google for it like: array find duplicates
BTW, you can try to use collection instead of array, and put handicap value
into its key value, then it will check for duplicates for you.

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Barry]

Alex:
Thanks. I did look around a bit and Googled to find an answer. Can you
explain more about the collection method? How will a collection check for
duplicates?
Thanks,
Barry

 

[Alex Dybenko]

Hi Barry,
something like this:

Dim i As Integer
Dim HandicapControl As String
dim varItem as Variant
dim col as new collection

'Load the array
For i = 1 To 18
HandicapControl = "Handicap" & i
If IsNull(Me(HandicapControl).Value) Or Me(HandicapControl).Value = 0
Then Exit For

varItem=Me(HandicapControl).Value
on error resume next
col.Add varItem, "a" & varItem
if err.number then _
msgbox "Duplicate found!"

'HoleHandicap(i) = Me(HandicapControl).Value
Next

Second argument for Add method is a key, which should be unique, so once you
add duplicated value - error will raise

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Barry]

Thanks again for the explanation and insight into the collection method. The
only thing is that the handicap is NOT and indexed field or at least it's not
at the moment. I just wanted to share with you the other method I have come
up with, although I have a problem with it as well. It does not always
return the first instance of the duplicate. Anyway, here's that code.
Starts the same as before with loading up the array.

Dim i As Integer, j As Integer
Dim HandicapControl As String

'Load the array
For i = 1 To 18
HandicapControl = "Handicap" & i
If IsNull(Me(HandicapControl).Value) Or Me(HandicapControl).Value = 0
then Exit For
HoleHandicap(i) = Me(HandicapControl).Value
Next i

'Check for duplicates
For i = 18 To 1 Step -1
For j = i - 1 To 1 Step -1
If HoleHandicap(i) = HoleHandicap(j) Then
If HoleHandicap(i) = 0 Then Exit For
MsgBox "A handicap of " & HoleHandicap(i) & " has
already" & vbCr & _
"been assigned to hole number " & j
LoadHCapArray = True
Exit Function
End If
Next j
Next i

What do you think?

Thanks again,

Barry

 

[Alex Dybenko]

Hi Barry,
Your code looks ok, but I would really try to use table's unique index for
this

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Barry]

Alex:
Thanks for all of your help. I will see if making the handicap field makes
sense. That would make a 4 field key in my table. Currently the
tblCourseDetails table looks like this.

*CourseID
*HoleNumber (1 - 18)
*TeeID (Multiple sets of tees for any one hole on a course)
Par (3, 4, 5)
Yardage
Handicap (1 - 18 no duplicates allowed)

Thanks again,

Barry

 

[Alex Dybenko]

Hi Barry,
as I understand you - unique index should be for fields CourseID and
Handicap

--
Best regards,
___________
Alex Dybenko (MVP)
http://accessblog.net
http://www.PointLtd.com

 

[Barry]

Alex:
Thanks again for all of your help. I will try to put your suggestions to
good use.

Barry