Adding records to the MSAccess table with recordset (using For...Next loop)

[manthiralaya]

Dear All,

This query is related to MSAccess-Forms-VBA coding to adding new records in to a table using recordset.

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. Can anyone help what code can be used in the place of 'XXXXXXXXX' in the following command button.

Private Sub Command19_Click()
Dim DBSS As Database, RSTT As Recordset, STRSQLL As String, Table1 As TableDef
Set DBSS = CurrentDb
STRSQLL = "SELECT * FROM Table1;"
Set RSTT = DBSS.OpenRecordset(STRSQLL)
With RSTT
    I = 1
    For I = 1 To 4
        .AddNew
        !'XXXXXXXXX'= Me("txtCh" & I)
        .Update
    Next I
End With
End Sub

Thanks in advance for the kind helpers.

Regards,
M

[Jerry Maiapu]

I guess you copied this from somewhere. Anway, 'xxxxxx' represents the field name in the table: IE the name of the field in which the value of

Me("txtCh" & I)

will go in.

I mean:

![B]'your table field name'[/B]= Me("txtCh" & I)

Welcome to bytes ;

Jerry.

[MMcCarthy]

Also you don't seem to have declared I as a variable. You will need to declare it as an integer.

Dim I As Integer

[manthiralaya]

Re: Adding records to the MSAccess table with recordset (using For...Next loop)

Thanks Jerry for your kind reply.

First of all, there is one error in my posting. That is instead of "!'XXXXXXXX X'= Me("txtF" & I)", I wrote !'XXXXXXXXX'= Me("txtCh" & I).

Sorry, your guess is wrong. It is not copied, probably I have asked this question in some other forum which you could have seen over there.:-).

Actually I am working one project which contains more fields. For convienence, I gave the field name as a running number, example 'F1', 'F2', ....'F80' and the textboxes names are as 'txtF1', 'txtF2',....'tx tF80'. So, if I use a loop I thought I can able to easily add as I mentioned by reducing numerous coding.

*****!'your table field name'= Me("txtCh" & I)******
Yes, I am aware of that. But the problem is if I use straight away F1, F2, F3....F80, then I have to write 80codes like this. I am trying to find out the solution to get rid of this.

To McCarthy:
Thanks for your reply too. I too tried as you said but ends in vain.

Any suggestion from bothplease?

Thanks & Regards,
M

[Mariostg]

manthiralaya,

Does the value of your loop counter "I" matches the column number scheme? I.e. when I=20, you write in F20?
If so then, that is doable.

[NeoPa]

There appear to be a number of issues with your code. I will deal with them one-by-one, but I may have to leave you with a few to fix before proceeding as the problems may leave your actual intention ambiguous at this time. Anyway, let's see what we can find :

1. As Mary said, you should Dim your I variable before using it. It's possible to do without this but it's very bad practice, for reasons explained in Require Variable Declaration.
2. Your OpenRecordset() call needn't be of SQL at all, as it really just needs the table opened as a table for this. Also, when opening a recordset of any kind in code, it is advisable at least to check the parameters. It may be you actually need all the defaults, but this should certainly be checked first. Better practice would be to pass the default parameters anyway. That way your intentions are clear.
3. Line #7 is entirely redundant.
4. .AddNew & .Update should be outside of the loop. Currently you are attempting to add a new record for each control.
5. Line #10 should be :
   

   .Fields("F" & I) = Me.Controls("txtF" & I)

Actually, I think all is covered here and if you resolve all these issues the code should work fine for you. No need to iterate the process as feared earlier.

Let us know how you get on.

[ADezii]

Your Syntax was off in a couple of areas, and your For...Next Loop was not positioned correctly, but the following should do the trick:

Dim RSTT As DAO.Recordset
Dim STRSQLL As String
Dim I As Integer

STRSQLL = "SELECT * FROM Table1;"

Set DBSS = CurrentDb
Set RSTT = DBSS.OpenRecordset(STRSQLL, dbOpenDynaset)

With RSTT
  .AddNew
     For I = 1 To 4
       .Fields("F" & CStr(I)) = Me.Controls("txtCh" & CStr(I))
     Next I
  .Update
End With

RSTT.Close
Set RSTT = Nothing

[ADezii]

@NeoPa:
Didn't mean to step on your toes, we both must have been posting at or near the exact same time.

[NeoPa]

No worries my friend. It happens to me (and by me as much) all the time :-)

[manthiralaya]

Re: Adding records to the MSAccess table with recordset (using For...Next loop)

Thanks NeoPa for your kind reply. I have amended accordingly.

Thanks ADezii for your kind amended code too. Brilliant!. The code is working fine.

Thanks to all too those who replied.

I wanted to attach the file for reference. But I dont know how to attach here. So I inserted the code here.... In the following code I just checked with replacing "F" with other variable say "xyz". I too created a Table2 contains xyz1, xyz2.... It worked fine.

See you guys in future......may be with a new queries..:-)))

Best regards,
Manthiralaya.

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

Private Sub Command20_Click()
Dim DBSS As Database
Dim RSTT As Recordset
Dim STRSQLL As String
Dim I As Integer

Set DBSS = CurrentDb
STRSQLL = "SELECT * FROM Table2;"
Set RSTT = DBSS.OpenRecordset(STRSQLL)
With RSTT
    .AddNew
    For I = 1 To 4
        .Fields("xyz" & CStr(I)) = Me("txtCh" & CStr(I))
    Next I
    .Update
End With
RSTT.Close
Set RSTT = Nothing
End Sub

[NeoPa]

No worries. It seems that ADezii's code was similar to my suggestions in many respects anyway.

Welcome to Bytes!

[ADezii]

It seems that ADezii's code was similar to my suggestions in many respects anyway.

@NeoPa:
Great minds think alike, don't they?