Type mismatch when setting an object

[nvwildfire]

I think I am completely missing the problem here. I have 5 text boxes (txtregime1, txtregime2, txtregime3, etc...) that I want to loop through and put there text into a database, should be no problem? I keep getting a type mismatch error when I set my objtxtbox to a string that is the name of the txt box I want to reference. I am obviously missing something here, I could just write the code to do each text box but now that I have ventured down this trail I cannot admit defeat. The following is the procedure I am having problems with. Any help would be greatly appreciated.

thank you in andvance,

kgk

Public Sub AddFireRegime()
Dim RS As ADODB.Recordset
Dim strSQL As String
Dim strTxtBox As String
Dim objTxtBox As Object
Dim i As Integer
strSQL = "SELECT * From tblFireRegime "

Set RS = New ADODB.Recordset

With RS
.CursorType = adOpenStatic
.LockType = adLockOptimistic
.Open strSQL, conn

For i = 1 To 5
strTxtBox = "txtRegime" & i
Set objTxtBox = strTxtBox
.AddNew
!SID = pintSID
!FireRegime = i
!FRWeight = objTxtBox
.Update
Next

End With

RS.Close
Set RS = Nothing
End Sub

 

[TheVampire]

What you need is to use a control array. Have all the text boxes with the same name ( strTxtBox ), and each having a different index. ( 1 through 5 ).

Then your code would look like:

For i = 1 To 5
Set objTxtBox = strTxtBox(i)
.AddNew
!SID = pintSID
!FireRegime = i
!FRWeight = objTxtBox
.Update
Next

Hope this helps,

Robert

 

[nvwildfire]

Robert,

thanks a million that worked perfectly.

thank you,

kgk