Use of loop for adding more records to the table - Reg.

[manthiralaya]

Hi,
This query is related to MSAccess-Forms-VBA coding to adding new records in to a table using recordset.
Say for example, I have a table named as 'Table1' with fields F1, F2, F3, F4. In the Form, I have four TextBoxs named as txtF1, txtF2, txtF3, txtF4. I have a Command button named as 'Command19'.
I would like to insert a record into the 'Table1'. I want to use a loop to add the values from TextBoxes to the Fields in the 'Table1'. I want to loop so that I can insert more data.
Here is the code I had written. It gives an error of "Item not found in this collection". What am I doing wrong? Can anyone help?
Private Sub Command19_Click()
Dim DBSS As Database, RSTT As Recordset, STRSQLL As String, Table1 As TableDef, fldVar As Field
Set DBSS = CurrentDb
STRSQLL = "SELECT * FROM Table1;"
Set RSTT = DBSS.OpenRecordset(STRSQLL)
With RSTT
I = 1
For I = 1 To 4
.AddNew
!fldVar("F" & I) = Me("txtF" & I)
.Update
Next I
End With
End Sub

Thanks in advance for your kind help.
Regards,
Manthiralaya

 

[boblarson]

Fields in a recordset are ZERO-BASED so you are trying to get the wrong number for the field. You should have:

!fldVar("F" & I-1) = Me("txtF" & I)

 

[manthiralaya]

Thanks Boblarson for your kind reply.
1. In case my field name is abcd1, abcd2 etc......, how should it be handled?
2. I tried your way too but ends in vain and the same error encountered.
Any other suggestion?
Kind regards
manthiralaya

 

[jamesmor]

I don't know if it matters, but I always did my For...Next loop starting at 0 and going up, and just added any numbers to the end of my field, so it'd be


I = 0
For I = 0 To 3
.AddNew
!fldVar("F" & I+1) = Me("txtF" & I+1)
.Update
Next I

it'd be the same for whatever your field name is. again, don't know if it matters, but it was always easiest to keep my recordset straight that way for me.

 

[manthiralaya]

Thanks Jamesmor......

Still the same error James........

And also if I add by the way of Fieldo, Field1 etc...., some times the fields are shuffled themselves when open the table. I dont know why it is happening.

In order to more control over there, I wanted to add the data to the specific field name. For easiness, I gave textbox names as txtF1, txtF2 etc... and the table name as F1, F2 etc.... so that I thought I can use counter and loop as I said in my earlier post. As I have hundred textbox (named as I said) and hundred field in a table.

Any suggestion to modify the code?

Thanks.

 

[boblarson]

I must not have posted what I typed and then lost it somehow. This is my other change I was going to suggest:

![B][COLOR=red]Fields[/COLOR][/B]("F" & I) = Me("txtF" & I)

what is fldVar by the way?

 

[manthiralaya]

Hi Boblarson,

I tried that too.... as you said....but ends in vain........

I just want to assign a field variable. If I am using !Fields(), then there is no need of that thing........

*******************
Private Sub Command19_Click()
Dim DBSS As Database, RSTT As Recordset, STRSQLL As String, Table1 As TableDef, fldVar As Field
Set DBSS = CurrentDb
STRSQLL = "SELECT * FROM Table1;"
Set RSTT = DBSS.OpenRecordset(STRSQLL)
With RSTT
I = 0
For I = 0 To 3
.AddNew
!Fields("F" & I) = Me("txtF" & I + 1)
.Update
Next I
End With
End Sub
**********************

 

[boblarson]

Here's what I would have (sometimes concatenating in the item doesn't work):

Private Sub Command19_Click()
Dim DBSS As Database
Dim RSTT As Recordset
Dim STRSQLL As String
[B][COLOR=red]Dim strRef As String[/COLOR][/B]
 
Set DBSS = CurrentDb
 
STRSQLL = "SELECT * FROM Table1;"
 
Set RSTT = DBSS.OpenRecordset(STRSQLL)
 
With RSTT
 
[B][COLOR=seagreen]' Add new should be outside of the loop as you are wanting to add one record for all 4 fields[/COLOR][/B]
.AddNew
 
For I = 1 To 4
[B][COLOR=red]strRef = "F" & I[/COLOR][/B]
     !Fields([COLOR=red][B]strRef[/B][/COLOR]) = Me.[B][COLOR=red]Controls[/COLOR][/B]([B][COLOR=red]strRef[/COLOR][/B])
Next I
 
[B][COLOR=seagreen].Update ' Update should be after all fields are set.[/COLOR][/B]
 
End With
 
[B][COLOR=red]RSTT.Close[/COLOR][/B]
[B][COLOR=red]Set RSTT = Nothing[/COLOR][/B]
 
 
End Sub

 

[manthiralaya]

Sorry again.....boblarson. Nothing working out.

For your reference, I have inserted the image of what you have said.

If possible have a look.

Still breaking my head.....

Regards,
M

 

[boblarson]

oops, just realized something. Forgot you had txtF for the controls. Try this now:

Private Sub Command19_Click()
Dim DBSS As Database
Dim RSTT As Recordset
Dim STRSQLL As String
[COLOR=black]Dim strRef As String
[/COLOR][B][COLOR=red] Dim strCtl As String[/COLOR][/B]

Set DBSS = CurrentDb
 
STRSQLL = "SELECT * FROM Table1;"
 
Set RSTT = DBSS.OpenRecordset(STRSQLL)
 
With RSTT
 
[B][COLOR=seagreen]' Add new should be outside of the loop as you are wanting to add one record for all 4 fields[/COLOR][/B]
.AddNew
 
For I = 1 To 4
[COLOR=black]strRef = "F" & I[/COLOR]
[B][COLOR=red]strCtl = "txtF" & I[/COLOR][/B]
[COLOR=black]
[/COLOR]     !Fields([COLOR=black]strRef[/COLOR]) = Me.[COLOR=black]Controls[/COLOR]([B][COLOR=red]strCtl [/COLOR][/B])
Next I
 
[B][COLOR=seagreen].Update ' Update should be after all fields are set.[/COLOR][/B]
 
End With
 
[COLOR=black]RSTT.Close
Set RSTT = Nothing
[/COLOR] 
 
End Sub

 

[manthiralaya]

Hi Boblarson,

Thanks for the info....

Instead of !Fields(strRef), if I use .Fields(strRef) its working......

As like you, one of other forum friends helped me.....

I have attached the code for reference herewith.....

Thanks for all who helped....

See you in future....

Best regards,
manthiralaya

[LIST=1]<LI class=codeLI>Private Sub Command19_Click() <LI class=codeLI>Dim DBSS As Database <LI class=codeLI>Dim RSTT As Recordset <LI class=codeLI>Dim STRSQLL As String <LI class=codeLI>Dim I As Integer <LI class=codeLI>  <LI class=codeLI>Set DBSS = CurrentDb <LI class=codeLI>STRSQLL = "SELECT * FROM Table1;" <LI class=codeLI>Set RSTT = DBSS.OpenRecordset(STRSQLL) <LI class=codeLI>With RSTT <LI class=codeLI>    .AddNew <LI class=codeLI>    For I = 1 To 4 <LI class=codeLI>        .Fields("F" & CStr(I)) = Me("txtCh" & CStr(I)) <LI class=codeLI>    Next I <LI class=codeLI>    .Update <LI class=codeLI>End With <LI class=codeLI>RSTT.Close <LI class=codeLI>Set RSTT = Nothing 
[*]End Sub
[/LIST]

 

[manthiralaya]

Sorry..... here is the code.......

Private Sub Command19_Click()
Dim DBSS As Database
Dim RSTT As Recordset
Dim STRSQLL As String
Dim I As Integer
Set DBSS = CurrentDb
STRSQLL = "SELECT * FROM Table1;"
Set RSTT = DBSS.OpenRecordset(STRSQLL)
With RSTT
    .AddNew
    For I = 1 To 4
        .Fields("F" & CStr(I)) = Me("txtCh" & CStr(I))
    Next I
    .Update
End With
RSTT.Close
Set RSTT = Nothing
End Sub