Loop to get the value of the text box

[hannoudw]

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:

  Article1=me.article1.value
  Article2=me.article2.value 
  ..........
  Aticle12=me.article12.value
 

Is it possible do this in a for loop or something like that?

for i=1 to i=12 
Article1 = article& i &.value
next i 
 

I tried this one but it wont work !
Thanks for advance

[Stewart Ross]

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

Dim lngTotal as Long
Dim intCtrlNo as Integer
Const cCtrlName = "Article"
for intCtrlNo = 1 to 12
  lngTotal = lngTotal + Clng(Nz(Me.Controls(cCtrlName & intCtrlNo), 0)) 
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

[hannoudw]

And what if we have a text box that contain a String value??
could we use this ?

Dim TypeVal As String
Dim intCtrlNo As Integer
Const cCtrlType = "Type"
For intCtrlNo = 1 To 12
TypeVal = CStr(Nz(Me.Controls(cCtrlType & intCtrlNo, 0)))
Next intCtrlNo
 

[Stewart Ross]

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

[Jerry Maiapu]

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;

[hannoudw]

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:

insert into InvoiceDetails value(1,123,17,1,1,15000,"R")
insert into InvoiceDetails value(2,555,41,1,2,15000,"S")
insert into InvoiceDetails value(3,456,35,1,1,15000,"R")
 

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

Dim ArticleValue, SizeValue, QtyValue, PriceValue, DiffValue, AchatNumValue As Long
    Dim FromValue As String
 
   Dim intCtrlNo As Integer
   Const cCtrlArticle = "Article"
   Const cCtrlSize = "size"
   Const cCtrqty = "quantity"
   Const cCtrlPrice = "price"
   Const cCtrlDiff = "Difference"
   Const cCtrlType = "Type"
   Const cCtrlachnum = "achat_num"
 
   For intCtrlNo = 1 To 12
   ArticleValue = CLng(Nz(Me.Controls(cCtrlArticle & intCtrlNo), 0)) 'this line is highlighted with yellow
   SizeValue = CLng(Nz(Me.Controls(cCtrlSize & intCtrlNo), 0))
   QtyValue = CLng(Nz(Me.Controls(cCtrqty & intCtrlNo), 0))
   PriceValue = CLng(Nz(Me.Controls(cCtrlPrice & intCtrlNo), 0))
   DiffValue = CLng(Nz(Me.Controls(cCtrlDiff & intCtrlNo), 0))
   FromValue = CStr(Nz(Me.Controls(cCtrlType & intCtrlNo), "R"))
   AchatNumValue = CLng(Nz(Me.Controls(cCtrlachnum & intCtrlNo), 0))
 
    If (Not IsNull(AchatNumValue)) Then
    sql2 = "insert into achat values(" _
            & CStr(newAchatNum) & "," _
            & CStr(ArticleValue) & "," _
            & CStr(SizeValue) & "," _
            & CStr(newFactNum) & "," _
            & CStr(QtyValue) & "," _
            & CStr(PriceValue) & "," _
            & CStr(DiffValue) & ",'" _
            & CStr(FromValue) & "'); "
    db.Execute (sql2)
 
     End Select
    End If
    Next intCtrlNo
 

[Stewart Ross]

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

[hannoudw]

Ok Thanks Stewart
actually the code worked !
many thanks to you and to all the team :)