469,323 Members | 1,567 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,323 developers. It's quick & easy.

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

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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command19_Click()
  2. Dim DBSS As Database, RSTT As Recordset, STRSQLL As String, Table1 As TableDef
  3. Set DBSS = CurrentDb
  4. STRSQLL = "SELECT * FROM Table1;"
  5. Set RSTT = DBSS.OpenRecordset(STRSQLL)
  6. With RSTT
  7.     I = 1
  8.     For I = 1 To 4
  9.         .AddNew
  10.         !'XXXXXXXXX'= Me("txtCh" & I)
  11.         .Update
  12.     Next I
  13. End With
  14. End Sub
Thanks in advance for the kind helpers.

Oct 14 '10 #1
11 7106
Jerry Maiapu
259 100+
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
Expand|Select|Wrap|Line Numbers
  1. Me("txtCh" & I) 
will go in.

I mean:

Expand|Select|Wrap|Line Numbers
  1. !'your table field name'= Me("txtCh" & I)
Welcome to bytes ;

Oct 15 '10 #2
14,534 Expert Mod 8TB
Also you don't seem to have declared I as a variable. You will need to declare it as an integer.

Expand|Select|Wrap|Line Numbers
  1. Dim I As Integer
Oct 15 '10 #3
Thanks Jerry for your kind reply.

First of all, there is one error in my posting. That is instead of "!'XXXXXXXXX'= 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',....'txtF80'. 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,
Oct 15 '10 #4
332 100+

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.
Oct 15 '10 #5
32,173 Expert Mod 16PB
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 :
    Expand|Select|Wrap|Line Numbers
    1. .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.
Oct 15 '10 #6
8,800 Expert 8TB
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:
Expand|Select|Wrap|Line Numbers
  1. Dim RSTT As DAO.Recordset
  2. Dim STRSQLL As String
  3. Dim I As Integer
  5. STRSQLL = "SELECT * FROM Table1;"
  7. Set DBSS = CurrentDb
  8. Set RSTT = DBSS.OpenRecordset(STRSQLL, dbOpenDynaset)
  10. With RSTT
  11.   .AddNew
  12.      For I = 1 To 4
  13.        .Fields("F" & CStr(I)) = Me.Controls("txtCh" & CStr(I))
  14.      Next I
  15.   .Update
  16. End With
  18. RSTT.Close
  19. Set RSTT = Nothing
Oct 15 '10 #7
8,800 Expert 8TB
Didn't mean to step on your toes, we both must have been posting at or near the exact same time.
Oct 15 '10 #8
32,173 Expert Mod 16PB
No worries my friend. It happens to me (and by me as much) all the time :-)
Oct 15 '10 #9
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,

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command19_Click()
  2. Dim DBSS As Database
  3. Dim RSTT As Recordset
  4. Dim STRSQLL As String
  5. Dim I As Integer
  7. Set DBSS = CurrentDb
  8. STRSQLL = "SELECT * FROM Table1;"
  9. Set RSTT = DBSS.OpenRecordset(STRSQLL)
  10. With RSTT
  11.     .AddNew
  12.     For I = 1 To 4
  13.         .Fields("F" & CStr(I)) = Me("txtCh" & CStr(I))
  14.     Next I
  15.     .Update
  16. End With
  17. RSTT.Close
  18. Set RSTT = Nothing
  19. End Sub
  21. Private Sub Command20_Click()
  22. Dim DBSS As Database
  23. Dim RSTT As Recordset
  24. Dim STRSQLL As String
  25. Dim I As Integer
  27. Set DBSS = CurrentDb
  28. STRSQLL = "SELECT * FROM Table2;"
  29. Set RSTT = DBSS.OpenRecordset(STRSQLL)
  30. With RSTT
  31.     .AddNew
  32.     For I = 1 To 4
  33.         .Fields("xyz" & CStr(I)) = Me("txtCh" & CStr(I))
  34.     Next I
  35.     .Update
  36. End With
  37. RSTT.Close
  38. Set RSTT = Nothing
  39. End Sub
Oct 15 '10 #10
32,173 Expert Mod 16PB
No worries. It seems that ADezii's code was similar to my suggestions in many respects anyway.

Welcome to Bytes!
Oct 15 '10 #11
8,800 Expert 8TB
It seems that ADezii's code was similar to my suggestions in many respects anyway.
Great minds think alike, don't they?
Oct 16 '10 #12

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by Shannan Casteel via AccessMonster.com | last post: by
reply views Thread by ReidarT | last post: by
1 post views Thread by Nkagi | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by Gurmeet2796 | last post: by
reply views Thread by mdpf | last post: by
reply views Thread by harlem98 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.