check content in textbox.

**Stewart Ross** · Feb 26 '08, 09:33 PM

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:
[code=vb]For I = 1 to CountValues(You rControlName)[/code]
The ReturnPalletNo function returns a Long value. It is called as follows:
[CODE=vb]LongVariable = ReturnPalletNo (WhichOne, ctlName)[/CODE]
where WhichOne is an integer value, 1 for the first value, 2 for the second and so on.

[code=vb]
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

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(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
Else
ResultString = ResultString & ch
End If
End If
Loop
End If
If ResultString <> "" Then
ReturnValue = Val(ResultStrin g)
End If
ReturnPalletNo = ReturnValue
End Function
[/code]

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:

Code:

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

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** · Feb 27 '08, 05:14 AM

Would this maybe work as a shortcut to CountValues?

Code:

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** · Feb 27 '08, 12:50 PM

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

**billa856** · Feb 27 '08, 07:46 PM

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<1 0,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** · Feb 28 '08, 01:27 AM

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** · Feb 28 '08, 01:34 AM

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** · Feb 28 '08, 01:48 AM

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

check content in textbox.

check content in textbox.

Comment

Comment

Comment

Comment

Comment

Comment

Comment