Problem with function.

[billa856]

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(PalletN o)
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;2 1404" in textbox (PalletNo).

so
AutoNo = 1
PalletNo = ;21400;21403;21 404

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

Like
AutoNo = 1
PalletNo = ;21400;21403;21 404
PalletNo1=21403


I am using code like this

Private Sub PalletNo1_After Update()

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.Ba ckColor = 255
Else
Me.PalletNo1.Ba ckColor = 65280
End If
Next i
End Sub

and two functions i created are like this

Public Function CountValues(Ent ryString 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$(EntryStrin g, 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(Ent ryString)) Then
ReturnValue = 0
Else
EntryLength = Len(EntryString )
Do While (i < EntryLength) And (EntryCounter <> EntryNumber)
i = i + 1
ch = Mid$(EntryStrin g, 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(ResultStrin g)
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.

[Stewart Ross]

Hi Billa. As the person who wrote the CountValues and ReturnPalletNo functions for you in response to a previous post I note that you have changed the ReturnPalletNo code significantly and included additional IFs etc which are specific to your forms. These functions were tested and did function correctly when supplied to you - see thread http://www.thescripts.com/forum/thread775535.html.

When I remove your additional code the ReturnPalletNo function works again as when I supplied it to you, and I would suggest that you use the original code and this alone in your application. Its sole purpose was to provide you with the ability to extract pallet numbers from your string lists, and it works in exactly the way it is supposed to do.

-Stewart

[billa856]

I made very silly mistake but now I got the problem.
i was using
Exit For
, in funtion(ReturnP alletNo) itself.But i have to use it after function return the value.So i replace the code of functions to origanl code given by u before ,and Now finaly its working in very properly manner I want.

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

Thanks a lot for ur help.

[billa856]

Now I am using this function again in another form.

In that I have one ListBox(PalletI DOrTrackingNo_L istBox) and one textbox(LONO_Te xt).
Now when I select an item from ListBox then it will check using these function that it is already in that TextBox or not?if not then it will enter the value in Textbox and if it is already in that then leave it as it is.

for that I am using this code

Private Sub PalletIDOrTrack ingNo_ListBox_A fterUpdate()
Dim var As Variant
Dim j As Integer
Dim y As Integer
Dim LongVariable As String

For Each var In Me.PalletIDOrTr ackingNo_ListBo x.ItemsSelected

If (IsNull(Len(Me. LONO_Text))) Then
'if Textbox is empty then it simply enter item in it.
Me.LONO_Text = Me.LONO_Text & ";" & Me.PalletIDOrTr ackingNo_ListBo x.ItemData(var)

Else

j = CountValues(Me. LONO_Text)
'PROBLEM IS IN THIS LOOP
For y = 1 To j
LongVariable = ReturnPalletNo( y, Me.LONO_Text)

If (Me.PalletIDOrT rackingNo_ListB ox.ItemData(var ) = LongVariable) Then

Exit For

Else

Me.LONO_Text = Me.LONO_Text & ";" & Me.PalletIDOrTr ackingNo_ListBo x.ItemData(var)

End If
Next y
End If

Next var
End Sub

In that Loop first time(y=1; j=1) when item is not in list,it will simply enter it in but next time(y=1;j=2),i t will check again from first item and it not match the criteria so again it will enter item and next time(y=2;j=2)it will check again and it will enter item.

I think below is the solution for this problem but i don't know how to use it.
In this loop first it has to compare the value of item I selected from listbox to all values in textbox.then if it is already in textbox then leave it as it is and not then it will enter item in textbox.

I try to explain by example.
For Example
List is like this in Listbox(Me.Pall etIDOrTrackingN o_ListBox)

LONO|PalletNo|D escription-----------|BatchNO|CRTN|P cs|Total|remain |Location
->1------|2---------|3---------------------------------|4---------------|5----|6----|7-----|8------|9--------


->7807 | 21102 | DKNY BE DELICIOUS | 79658BV1 | 20 | 10 | 200 | 200 | 256BA
->7807 | 21103 | DKNY BE DELICIOUS | 79658BV3 | 30 | 10 | 300 | 300 | 256BB
->7807 | 21104 | DKNY BE DELICIOUS | 79658BV1 | 20 | 10 | 200 | 200 | 256BA
->7808 | 21105 | DKNY BE DELICIOUS | 79658BV2 | 20 | 10 | 200 | 200 | 256BD
->7808 | 21106 | DKNY BE DELICIOUS | 79658BV2 | 20 | 10 | 200 | 200 | 256BD
->7807 | 21107 | DKNY BE DELICIOUS | 79658BV1 | 20 | 10 | 200 | 200 | 256BA
->7807 | 21108 | DKNY BE DELICIOUS | 79658BV1 | 20 | 10 | 200 | 200 | 256BA
->7808 | 21109 | DKNY BE DELICIOUS | 79658BV2 | 20 | 10 | 200 | 200 | 256BD

Now if I select first one 7807 then LONO_Text=;7807
After that if again I select 7807 then LONO_Text will remain as it is.
then if I select 7808 then LONO_Text=7807; 7808
then if i select 7807 then LONO_Text=7807; 7808

then if i select 7808 then LONO_Text=7807; 7808;7808;7808
but it should be LONO_Text=7807; 7808

I hope I explained properly what I want by example.
Help me plz.

[billa856]

I solve the previous problem.
But now there is another one coming

List is like this in Listbox(Me.Pall etIDOrTrackingN o_ListBox)

LONO----|PalletNo|Descr iption-----------|BatchNO--|CRTN|Pcs|Total |Remain|Locatio n
->1------|2---------|3----------------------------|4-------------|5----|6----|7-----|8-----|9--------

->7807 | 21102 | DKNY BE DELICIOUS | 79658BV1 | 20 | 10 | 200 | 200 | 256BA
->7807 | 21103 | DKNY BE DELICIOUS | 79658BV3 | 30 | 10 | 300 | 300 | 256BB
->7807 | 21104 | DKNY BE DELICIOUS | 79658BV1 | 20 | 10 | 200 | 200 | 256BA
->7808 | 21105 | DKNY BE DELICIOUS | 79658BV2 | 20 | 10 | 200 | 200 | 256BD
->7808 | 21106 | DKNY BE DELICIOUS | 79658BV2 | 20 | 10 | 200 | 200 | 256BD
->7807 | 21107 | DKNY BE DELICIOUS | 79658BV1 | 20 | 10 | 200 | 200 | 256BA
->7807 | 21108 | DKNY BE DELICIOUS | 79658BV1 | 20 | 10 | 200 | 200 | 256BA
->7808 | 21109 | DKNY BE DELICIOUS | 79658BV2 | 20 | 10 | 200 | 200 | 256BD

How can I add selected items from column 8(Remain)?
Like If I select first line then TotalRemain=200
then If I select 2nd line then TotalRemain=200 +300=500
then If I select 3rd line then TotalRemain=200 +300+200=700

then If I disselect 2nd line then TotalRemain=200 +300+200-300=400


I am using this code but its not working properly

For Each var In Me.PalletIDOrTr ackingNo_Combo. ItemsSelected
Me.TotalQuantit y1 = Me.TotalQuantit y1 + Me.PalletIDOrTr ackingNo_Combo. Column(8, var)
Next var

using this code things gone like this

Like If I selected first line then TotalRemain=200
then If I select 2nd line then TotalRemain=200 +200+300=700 instead of 200+300=500
then If I select 3rd line then TotalRemain=200 +200+300+300+20 0=1200 instead of 200+300+200=700

then If I disselect 2nd line then TotalRemain=200 +200+300+300+20 0+1200=2400 instead of 200+300+200-300=400


I don't get it why this is happening but i Think its bcz I am using FOR EACH.
can anyone help me?