Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2007
    Posts
    49

    Unanswered: Problem with function.

    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.
    and when i enter last PalletNo 21404 in textbox(PalletNo1) then it will return true value.
    I can't get it what's problem with code.
    Can anyone help me.
    Thank You.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Have you considered the InStr function?
    Code:
    If Instr(1, "abc;", "123;abc;456;def;") Then
        MsgBox("True")
    Else
        MsgBox("False")
    End If
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2007
    Posts
    49
    I solve the problem.
    I was using ExitDo in Function instead of using ExitFor after function call.
    By the way i never used Instr function.But i think it will help me in future.
    Thanks for ur help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •