Help with a For Loop in VB

[JFo]

I want to do a 'for loop' in an access database so that it loops through a number of text boxes and combo boxes (for different records for the same fields on the same form) for each question and enters it into a different row in the table, this code will not work, but it must be fairly close

Private Sub cmdSaveClose_Click()
Dim x As Integer
Dim i As Integer


x = MsgBox("Are you sure you want to save changes?", 4, "Exit?")

'VbNo constant throws back #7
'VbYes constant throws back #6

If x = 7 Then
Exit Sub
Else

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb

For i = 1 To 45

db.Execute " INSERT INTO [tblChecklistAnswer]" & "([Answer], [Response]) VALUES " _


& "('" & Me.txtAnswer(i) & "'," & "'" & Me.cmbResponse(i) & "'" & ");"

Next i

DoCmd.Close

End If

End Sub

My problem being that for each input text box it is called txtAnswer1 , txtAnswer2, etc up to txtAnswer45 in this example

and cmbResponse1, cmbResponse2, etc up to cmbResponse45

This is obviously not a method and maybe needs to be converted to a string, help is appreciated I don't particularly want to type the same formula out 45 times so a for loop is desirable

I get compile error & "('" & Me.txtAnswer(i) & "'," & "'" & Me.cmbResponse(i) & "'" & ");"
method or data member not found
thanks

[stmoong]

Just a suggestion for better programming. We should use those vb constants instead of the actual value.

Eg.

Response = ("Are you sure you want to save changes?", vbYesNo, "Exit?") 
 
If Response = vbYes Then
    ' Perform some action.
Else
    ' Perform some action.
End If

About looping through those textboxes, will something like this work? (I haven't tested the code)

Use these to access the value:
Me.Controls("txtAnswer" & idx).Value
Me.Controls("cmbResponse" & idx).Value

[JFo]

Thanks for your quick response I have noted your suggestion and changed my code. This is actually my first real vb coding and I am not sure I understand what you mean I have done a bit of programming in other languages which is where I got my idea from as you may be able to tell

I am unfamiliar with controls however what is idx? I can't seem to find this

My code actually all works perfectly I just don't want to repeat the same piece of code 45 times

Thanks

[stmoong]

'idx' is just index. You can just use 'i' in your case.

Personally, I just prefer to use constants because it is more readable compare to using those magic numbers

The other point is this. This is unlikely, but if Microsoft decides to change those constant values, then it would be a nightmare to maintain different values for different versions. However, it would be more apparent, if you're using other third-parties or developers code, so, it is always more beneficial for you to use constants rather than the "naked" value.

My code actually all works perfectly I just don't want to repeat the same piece of code 45 times

And this is good practice, it's basically refactoring of code.

[JFo]

Awesome thanks very much this works

Cheers

[stmoong]

You're welcome.