Here's a good question I received from a student, asking about looping through fields on a form...

---

I have a form fixed with 9 rows(textbox) and 3 columns(textbox) . The users have a choice of entering in the text box from 1 to  9. Instead of hard code all of the 9 rows. Can I use the loop to insert all the rows into certain table??

test1 = Forms![New Contracts]![Bid Group F].Form![Contract1].Value
test2 = Forms![New Contracts]![Bid Group F].Form![IssueBy1].Value
test3 = Forms![New Contracts]![Bid Group F].Form![supplypartner1].Value   
 
  DoCmd.RunSQL "INSERT INTO [Contract All2] ( contract, [supplier partner], [Requested By] ) " & _
  "Values( '" & test1 & "' ,  '" & test2 & "' , '" & test3 & "' ); "

---

My Reply...

Well, my first thought is "why are you not using a SUBFORM?" This way you're not limited to just 9 items AND you could easily loop through all of the records. You could just run through a loop with either a Recordset or using GoToRecord commands.

However, YES, you could create a loop based on the text box names... I don't think I cover it in my classes, but you could say something like:

For X = 1 to 9
  TextBoxName = "Contract" & X
  TextBoxValue = Me(TextBoxName)
  Docmd.RunSQL ' put your INSERT command here
Next X

See what this does? Instead of having to refer to a form field as:

  Me!FirstName

You can refer to it as:

  Me("FirstName")

This is extremely handy if you want to dynamically change the NAME of the field you're looking for in your code.

Give it a try and let me know if this helps.