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
11 7437
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
will go in.
I mean: - !'your table field name'= Me("txtCh" & I)
Welcome to bytes ;
Jerry.
Also you don't seem to have declared I as a variable. You will need to declare it as an integer.
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
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 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 : - 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.
- 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.
- Line #7 is entirely redundant.
- .AddNew & .Update should be outside of the loop. Currently you are attempting to add a new record for each control.
- 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.
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
@NeoPa:
Didn't mean to step on your toes, we both must have been posting at or near the exact same time.
NeoPa 32,556
Expert Mod 16PB
No worries my friend. It happens to me (and by me as much) all the time :-)
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 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!
It seems that ADezii's code was similar to my suggestions in many respects anyway.
@NeoPa:
Great minds think alike, don't they?
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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()...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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)...
|
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...
|
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
|
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...
|
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...
| |