Problem with function.

[billypit786]

Hi, my project is in MS Access 2002.
In that I have one form.In that thare are couple of textboxes and a
combobox.
now at first i select perticular number from combobox(AutoNo)
then it will fetch the value of PalletNo from table and set it as the
value of textbox(PalletNo)
In my table there entries like
AutoNo PalletNo
1 | 21400
2 | 21401
3 | 21402
1 | 21403
1 | 21404
2 | 21405
etc


Now if i select "1" from AutoNo then it will return value ";
21400;21403;21404" in textbox (PalletNo).

so
AutoNo = 1
PalletNo = ;21400;21403;21404

Now I have one more TextBox(PalletNo1)
now i want to check that value i enter in textbox(PalletNo1) is in
textbox(PalletNo) or not.And if its there then change the background
color of textbox(PalletNo1) to GREEN.and if not then RED.

Like
AutoNo = 1
PalletNo = ;21400;21403;21404
PalletNo1=21403


I am using code like this


Private Sub PalletNo1_AfterUpdate()

Dim i As Integer
Dim j As Integer
Dim LongVariable As String
Dim ResultString As String
j = CountValues(PalletNo)
For i = 1 To j
LongVariable = ReturnPalletNo(i, PalletNo)
If (Me.PalletNo1 = LongVariable) Then
Me.PalletNo1.BackColor = 255
Else
Me.PalletNo1.BackColor = 65280
End If
Next i
End Sub


and two functions i created are like this


Public Function CountValues(EntryString As String) As Integer
Dim EntryCounter As Integer, EntryLength As Integer
Dim i As Integer, CharCount As Integer, ch As String
Const Separator = ";"
EntryLength = Len(EntryString)
For i = 1 To EntryLength
ch = Mid$(EntryString, i, 1)
If ch = Separator Then
If CharCount > 0 Then
EntryCounter = EntryCounter + 1
CharCount = 0
End If
Else
CharCount = CharCount + 1
End If
Next i
If CharCount > 0 Then
EntryCounter = EntryCounter + 1
End If
CountValues = EntryCounter
End Function


and the other one is

Public Function ReturnPalletNo(EntryNumber As Integer, EntryString As
String) As Long
'Returns the specified pallet number from the entry string, or
'0 if the EntryNumber is invalid
Dim EntryCounter As Integer, EntryLength As Integer
Dim t As Integer, i As Integer, w As Integer, m As Integer, o As
Integer, CharCount As Integer, ch As String
Dim ResultString As String, ReturnValue As Long
Const Separator = ";"
If (EntryNumber <= 0) Or (EntryNumber > CountValues(EntryString)) Then
ReturnValue = 0
Else
EntryLength = Len(EntryString)
Do While (i < EntryLength) And (EntryCounter <> EntryNumber)
i = i + 1
ch = Mid$(EntryString, i, 1)
If ch = Separator Then
If CharCount > 0 Then
EntryCounter = EntryCounter + 1
CharCount = 0
End If
Else
CharCount = CharCount + 1
If CharCount = 1 Then
ResultString = ch
If (Me.PalletNo1 = ResultString) Then
Exit Do
End If
Else
ResultString = ResultString & ch
If (Me.PalletNo1 = ResultString) Then
Exit Do
End If

End If
End If
Loop
End If
If ResultString <> "" Then
ReturnValue = Val(ResultString)
End If
ReturnPalletNo = ReturnValue
End Function


when i run the form it will simply execute the code but it won't match
the values
ReturnPalletNo = ReturnValue
it always return the last PalletNo in my Entrystring.
I can't get it what's problem with code.
Can anyone help me?
Thank You.