473,320 Members | 1,910 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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.

Regards,
M
Oct 14 '10 #1
11 7437
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 ;

Jerry.
Oct 15 '10 #2
MMcCarthy
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,
M
Oct 15 '10 #4
Mariostg
332 100+
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.
Oct 15 '10 #5
NeoPa
32,556 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
ADezii
8,834 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
  4.  
  5. STRSQLL = "SELECT * FROM Table1;"
  6.  
  7. Set DBSS = CurrentDb
  8. Set RSTT = DBSS.OpenRecordset(STRSQLL, dbOpenDynaset)
  9.  
  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
  17.  
  18. RSTT.Close
  19. Set RSTT = Nothing
Oct 15 '10 #7
ADezii
8,834 Expert 8TB
@NeoPa:
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
NeoPa
32,556 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,
Manthiralaya.

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
  6.  
  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
  20.  
  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
  26.  
  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
  40.  
Oct 15 '10 #10
NeoPa
32,556 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
ADezii
8,834 Expert 8TB
It seems that ADezii's code was similar to my suggestions in many respects anyway.
@NeoPa:
Great minds think alike, don't they?
Oct 16 '10 #12

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

Similar topics

2
by: Colm O'Hagan | last post by:
Hi there, I having a problem with a database I'm setting up, I would be delighted if someone out there could help. The database I'm setting up is a task register datebase, it will be used to...
2
by: andyw | last post by:
Hi, I have a form which I have put some check boxes on. Say each of these checkboxes has a value, option1, option2, option3, option4. How can i set up something that when a user ticks the...
0
by: brijeshmathew | last post by:
Hi I use Visual Basic 6, Service Pack 6, Microsoft ActiveX Data Objects 2.8 Library(msado15.dll) and access 2000 database using JET 4 OLE. I have an application that adds records simultaneously...
13
by: Jan | last post by:
Hi I have a database that I use to keep track of the sales promotions that we send to companies. I normally send a mailing based on a subset of the companies in the database (found using the...
13
by: Shannan Casteel via AccessMonster.com | last post by:
I set up two tables (one with the regular claim info and another with ClaimNumber, PartNumber, and QuantityReplaced). The ClaimNumber is an autonumber and the primary key in both tables. I made a...
0
by: ReidarT | last post by:
I use ADODB to add some records from one table to another. It seems that the last record is missing in the updated table. call LeggTilFirma() .... Public Function LeggTilFirma() Dim...
1
by: Nkagi | last post by:
Hi All, I have a problem of adding records into table, if the cursor is on the first record the record that I add replaces the first record and then I moved it to the last record and the new...
3
by: dbanalyst | last post by:
I have a VB6 app that executes an SQL stored procedure that returns a recordset that I loop through 1 record at a time. During the looping process, I am attempting to insert contents from the...
9
ADezii
by: ADezii | last post by:
One question which pops up frequently here at TheScripts is: 'How do I retrieve data from a Recordset once I've created it?' One very efficient, and not that often used approach, is the GetRows()...
20
by: jmar93 | last post by:
I am using Access 2007 and am trying to add records from a table to a recordset. I understand how to add all of the records from the table, but need to figure out how to only add records until a...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.