469,159 Members | 1,511 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,159 developers. It's quick & easy.

Loop to get the value of the text box

115 100+
I wonder
If i have 12 unbound text box
and i want to take the value of those text box.
those text box are named (article1, article2, article3,...,article12)
So i write this:
Expand|Select|Wrap|Line Numbers
  1.   Article1=me.article1.value
  2.   Article2=me.article2.value 
  3.   ..........
  4.   Aticle12=me.article12.value
  5.  
Is it possible do this in a for loop or something like that?
Expand|Select|Wrap|Line Numbers
  1. for i=1 to i=12 
  2. Article1 = article& i &.value
  3. next i 
  4.  
I tried this one but it wont work !
Thanks for advance
Aug 17 '10 #1

✓ answered by Stewart Ross

Use the Controls collection to refer to controls on a form by a computed name. Assuming your textboxes contain whole numbers:

Expand|Select|Wrap|Line Numbers
  1. Dim lngTotal as Long
  2. Dim intCtrlNo as Integer
  3. Const cCtrlName = "Article"
  4. for intCtrlNo = 1 to 12
  5.   lngTotal = lngTotal + Clng(Nz(Me.Controls(cCtrlName & intCtrlNo), 0)) 
  6. Next intCtrlNo
The CLng(Nz()) part is to prevent errors arising from null values in your textboxes, which would otherwise prevent the addition from returning the correct result.

-Stewart

7 4625
Stewart Ross
2,545 Expert Mod 2GB
Use the Controls collection to refer to controls on a form by a computed name. Assuming your textboxes contain whole numbers:

Expand|Select|Wrap|Line Numbers
  1. Dim lngTotal as Long
  2. Dim intCtrlNo as Integer
  3. Const cCtrlName = "Article"
  4. for intCtrlNo = 1 to 12
  5.   lngTotal = lngTotal + Clng(Nz(Me.Controls(cCtrlName & intCtrlNo), 0)) 
  6. Next intCtrlNo
The CLng(Nz()) part is to prevent errors arising from null values in your textboxes, which would otherwise prevent the addition from returning the correct result.

-Stewart
Aug 17 '10 #2
hannoudw
115 100+
And what if we have a text box that contain a String value??
could we use this ?
Expand|Select|Wrap|Line Numbers
  1. Dim TypeVal As String
  2. Dim intCtrlNo As Integer
  3. Const cCtrlType = "Type"
  4. For intCtrlNo = 1 To 12
  5. TypeVal = CStr(Nz(Me.Controls(cCtrlType & intCtrlNo, 0)))
  6. Next intCtrlNo
  7.  
Aug 17 '10 #3
Stewart Ross
2,545 Expert Mod 2GB
Sorry, but what you've suggested makes no sense to me. If your textboxes contain strings then all that your loop will actually do is set Typeval to be the value of the last control, or "0" if it is null. I'm not sure at all what you'd be trying to achieve here, and it is not what you asked in post #1.

The reason for typecasting to Long in my response was to ensure that Nz returns a value of 0, not the string value "0" which it may return depending on the context of how it is used. There is no need to use CStr if the textbox contains a string. Change Nz(..., 0)to Nz(...) as Nz(..., 0) is intended to return a number.

It is certainly possible to loop through a series of textboxes to access each value in turn, but what you've posted gives me no idea of why you would do so in your case.

-Stewart
Aug 17 '10 #4
Jerry Maiapu
259 100+
The question is: Why do you want to loop through every control on the form?

I suggest you tell the forum what you're trying to achieve rather than what you are trying to do.
There maybe a better way of handling your problem rather than looping through every record.

That way I think those who wish to help can have a clear background idea of what you're trying to achieve. Otherwise, any suggestions posted would contradict to what you were initially trying to bring across.
This would result in possible confusion that will delay what you're actually asking for.

Just guidelines there;
Aug 18 '10 #5
hannoudw
115 100+
I have a form that contain a lots of unbound text box. they are on a shape of a matrix (5*12)
and they are From, article ,size, quantity, price
from1,from2,.....,from12
size1,size2,.....size12
quantity1,quantity2,...quantity12
price1,price2,.....,price12
article1,article2,....article12

I want to be able to do this sql:
Expand|Select|Wrap|Line Numbers
  1. insert into InvoiceDetails value(1,123,17,1,1,15000,"R")
  2. insert into InvoiceDetails value(2,555,41,1,2,15000,"S")
  3. insert into InvoiceDetails value(3,456,35,1,1,15000,"R")
  4.  
InvoiceDetails (InvoiceDetailsNum as number
Aticle as number
size as number
InvoiceNum as number
Quantity as number
Price as number
From as String)

Here's the code that i wrote that Stewart Ross Inverness
helped me with, but it's not working it's giving me an error:
Can't find the field Article1 reffered to in your expression
Expand|Select|Wrap|Line Numbers
  1. Dim ArticleValue, SizeValue, QtyValue, PriceValue, DiffValue, AchatNumValue As Long
  2.     Dim FromValue As String
  3.  
  4.    Dim intCtrlNo As Integer
  5.    Const cCtrlArticle = "Article"
  6.    Const cCtrlSize = "size"
  7.    Const cCtrqty = "quantity"
  8.    Const cCtrlPrice = "price"
  9.    Const cCtrlDiff = "Difference"
  10.    Const cCtrlType = "Type"
  11.    Const cCtrlachnum = "achat_num"
  12.  
  13.    For intCtrlNo = 1 To 12
  14.    ArticleValue = CLng(Nz(Me.Controls(cCtrlArticle & intCtrlNo), 0)) 'this line is highlighted with yellow
  15.    SizeValue = CLng(Nz(Me.Controls(cCtrlSize & intCtrlNo), 0))
  16.    QtyValue = CLng(Nz(Me.Controls(cCtrqty & intCtrlNo), 0))
  17.    PriceValue = CLng(Nz(Me.Controls(cCtrlPrice & intCtrlNo), 0))
  18.    DiffValue = CLng(Nz(Me.Controls(cCtrlDiff & intCtrlNo), 0))
  19.    FromValue = CStr(Nz(Me.Controls(cCtrlType & intCtrlNo), "R"))
  20.    AchatNumValue = CLng(Nz(Me.Controls(cCtrlachnum & intCtrlNo), 0))
  21.  
  22.     If (Not IsNull(AchatNumValue)) Then
  23.     sql2 = "insert into achat values(" _
  24.             & CStr(newAchatNum) & "," _
  25.             & CStr(ArticleValue) & "," _
  26.             & CStr(SizeValue) & "," _
  27.             & CStr(newFactNum) & "," _
  28.             & CStr(QtyValue) & "," _
  29.             & CStr(PriceValue) & "," _
  30.             & CStr(DiffValue) & ",'" _
  31.             & CStr(FromValue) & "'); "
  32.     db.Execute (sql2)
  33.  
  34.      End Select
  35.     End If
  36.     Next intCtrlNo
  37.  
Aug 18 '10 #6
Stewart Ross
2,545 Expert Mod 2GB
I echo Jerry's comment - why are you doing it this way? There is no substitute for proper database design, and the un-normalised approach you are taking is bound to lead to complications which are avoidable if your tables are designed following relational principles from the start.

The method for looping through controls I provided is simple and works as I expected when I have tested it on on an unbound form.

As long as you are not testing your loop with the form concerned in design view - which will fail because you cannot access the value of a control when it is open in design view - I can only assume that you do not have a control of the name 'article1' on the form at the time you are running the routine.

I strongly advise that you rethink your approach, however.

-Stewart
Aug 18 '10 #7
hannoudw
115 100+
Ok Thanks Stewart
actually the code worked !
many thanks to you and to all the team :)
Aug 18 '10 #8

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by mikea_59 | last post: by
2 posts views Thread by Tom Fitzgibbon | last post: by
5 posts views Thread by Josť Carlos | last post: by
13 posts views Thread by kronecker | last post: by
2 posts views Thread by Geethu03 | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.