check content in textbox.

[billa856]

Hi,
My project is in MS Access 2002.
In that I have two forms which I am using for Shipping Entry.
Now First Form(ShippingAlerts) Is fillup by persons working in customer services.and the Second Form(ShippingEntry) is fillup by workers operating Hi-Low machines to put Boxes in Trucks.

Now In First Form(ShippingAlerts)I have some fields like
AutoNo,BillToAddress,ShipToAddress. and some other like
CustomerCode,ItemNo,PONo,LONo,PalletNo etc.all fields in this line are dependent fields.
AutoNo is automatically generated.and allocated to one ShippingAlert Form at a time.
Now in that Form when person select Customer from CustomerCode(ComboBox) then it will generate list for another ComboBox(ItemNo).Now when person select Item form ItemNo(ComboBox) then it will generate list for PONo(multiple select ListBox).same thing happen when select PONo(multiple select ListBox) then generate list for LONo(multiple select ListBox).and when select LONo(multiple select LIstBox) then generate list for PalletNo(multiple select ListBox).Now person have to choose which PalletNo he has to ship from that list.when he select mutiple PalletNo from list then it will automatically enter in new TextBox(FinalPalletNo).in that value is like this
24914;24913;24912;24905;23105;;23205.
Here I used ";" to seperate PalletNos.and sometimes there is no PalletNo then it will be like ";;" as I shown in upper line.

Now In Second Form(ShippingEntry) person working on machine has to select AutoNo of First Form(ShippingAlerts).So it will automatically fill some fields from that form.like
BillToAddress,ShipToAddress And value TextBox(FianlPalletNo) allocated to Another TextBox(PalletNo) on this form.
Now person working on machine scan the barcode of PalletNo and automatically it will enter data regarding that PalletNo in form.At the same time I want to higlight the same PalletNo in that TextBox(PalletNo).So person will know which PalletNo are remainng and which already done.I can do this thing if single value in TextBox(PalletNo).But there is value like this
24914;24913;24912;24905;23105;;23205
So I don't Know hos to seperate it

Anyone have solution to this problem then plz give me.
Thanks.

[Stewart Ross]

Hi Billa856. I have provided two functions for you which can help with your multiple pallet number problem. The first, CountValues, returns the number of pallet numbers contained in the entry string (the value from your text field). The second, ReturnPalletNo, returns a specified value from the list. You will need to devise a loop yourself in which to process the pallet numbers, but at least CountValues gives you the end value of your loop. It can be used to set the end value for a for-next or other kind of loop, as follows:

For I = 1 to CountValues(YourControlName)

The ReturnPalletNo function returns a Long value. It is called as follows:

LongVariable = ReturnPalletNo (WhichOne, ctlName)

where WhichOne is an integer value, 1 for the first value, 2 for the second and so on.

 
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
 
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 I 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
                Else
                    ResultString = ResultString & ch
                End If
            End If
        Loop
    End If
    If ResultString <> "" Then
        ReturnValue = Val(ResultString)
    End If
    ReturnPalletNo = ReturnValue
End Function
 

In normal circumstances the use of Instr would have cut down the loop processing, but as you indicated in your post there are occasions when you have multiple separators ";;" within the text values, and as I could not guarantee that there would be a particular number of these I simply generalised the routines to function regardless of how many consecutive separators are present.

Sample output from immediate window of VBE:

? ReturnPalletno(1, "20315;;20316")
 20315
? ReturnPalletno(2, "20315;;20316")
 20316 
? ReturnPalletno(3, "20315;;20316")
 

Note that an invalid pallet number request will return a 0 result.

Hope this helps you complete a solution to extract the values from your text string.

-Stewart

[missinglinq]

Would this maybe work as a shortcut to CountValues?

PN = Me.PalletNo
 If Not IsNull(PN) Then Pallets = Len(Replace(PN, ";;", ";")) - Len(Replace(Replace(PN, ";;", ";"), ";", "")) + 1
 

What needs to be said here, of course, is that one of the Cardinal Rules of Relational Databases has been broken! That is, of course, the one-control/one-value rule. Multiple values should never be stored in a single control/field!

Linq ;0)>

[billa856]

Thanks a lot buddy.I gurantee that it will help me.

[billa856]

I modify ur code as per my requirement.its working but I only want to know in Acces is there any function like FLAG or Label.I don't know its name properly.
But in C or C++ we use this function to jump at perticular line in code.

this is just example code is not working.this is just sample to show what i want?.

1 x=some string
2 for(i=1,j=1;i<10,j<strlen(X);i++,j++)
3 {
4 if(i=j)
5 go to LabelA
6 else
7 some code
8 }
9 some code
10
.
.
.
20 LabelA:
21 for(k=0;k<10;k++)
22 {
23 print("string match");
24 }

How can I jump to line 5 to line 20?

[NeoPa]

What you have there is the correct syntax for branching in VBA.
However, it's use is highly non-recommended. Procedural coding was supposed to do away with that construct generally. Procedural coding was the forerunner to OO coding. That's how long ago branching was considered (all but) redundant.

PS. I'd maybe consider using the Split() function to get the individual elements out of your [PalletNo] TextBox.

[Scott Price]

Open your VBA code editor window and type in GoTo. Position the cursor within the word and press F1. This brings up the help window associated with this command.

As NeoPa says, this is not a recommended programming procedure any more. It's only common usage in modern day VBA programming is in error handling code. However, it is provided, mostly for backward compatibility with existing code.

Regards,
Scott

[NeoPa]

That's two errors in my post :(
The command is GoTo rather than Go To as Scott indicated.
Branching bacame old news when Procedural Programming was introduced. Procedural Programming was replaced by Structured Programming and THAT was superseeded by Object Oriented Programming.
Not highly recommended then :D