Automatically Add Certain Number of Records 2

[infinitx]

Hi,

I want a button to add 10 records with a value of "none" to a table called ICL 1.

Here's the table structure:

Item ID (Autonumber)
ICL 1 (Text)

I want the ICL 1 Field to be populated with 10 records that have a value of "none."

Is this possible?

Thanks!

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[gol4]

one way using SQL

dim strsql as string, x as integer
strsql = "insert into tablename ([icl 1]) values ('none')"
for x = 1 to 10
currentdb. execute strsql
next x

 

[scriverb]

First of all, don't name tables and fields the same name. ACCESS can get really confused by this.

Try something like this:

Dim db as DAO.Database
Dim rs as DAO.Recordset, i as Integer
Set db = CurrentDB
Set rs = db.OpenRecordset("tblICL 1", dbOpenDynaset)
For i = 1 to 10
   rs.AddNew
   rs("ICL 1") = "Done"
   rs.Update
Next i
rs.close
db.close

The autonumber will increment by itself without any action initiated. Good luck with your project.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[infinitx]

gol4,

I get the following error message:

"Run-time Error '3134':
Syntax error in INSERT INTO statement."

This is the code that I am using:

Dim strsql As String, x As Integer
strsql = "insert into ICL 1 ([icl 1]) values ('none')"
For x = 1 To 10
CurrentDb.Execute strsql
Next x

ICL 1 is the tablename as well as the field name.

What is wrong?

Thanks!

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[infinitx]

scriverb,

Thanks! I have one question. How can I rename tables and automatically change that name on all of my forms? Does this mean that I will have to change the table names in the SQL Statements manually?

Thanks again!



----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[infinitx]

scriverb,

Sorry, but I have one more question. Sometimes I will need to add 10 records but other times I will need to add less, say 5. How can I automate the code so when a value of a textbox on the form is "5", the code adds 5 records of "none" to the table?

Thanks!

----
Alex

Anytime things appear to be going better, you have overlooked something.

 

[scriverb]

This should do it:

Dim db as DAO.Database
Dim rs as DAO.Recordset, i as Integer, ii as Integer
Set db = CurrentDB
Set rs = db.OpenRecordset("tblICL 1", dbOpenDynaset)
ii = InputBox("Number of records to add: ")
For i = 1 to ii
   rs.AddNew
   rs("ICL 1") = "Done"
   rs.Update
Next i
rs.close
db.close

Good luck.

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[gol4]

Bob's solution is a good one using a recordset.
In answer to your question the reason for the error is since there is a space in the name of your table it needs to be enclosed in brackets. Just like I had to do with the field name

Dim strsql As String, x As Integer
strsql = "Insert into [ICL 1] ([icl 1]) values ('none')"
For x = 1 To 10
CurrentDb.Execute strsql
Next x

I should have picked up the table name with closer reading on my part of your original post.

 

[scriverb]

I hope you caught this but I put the wrong word in all your records. Change "Done" to "None".

[COLOR=006633]Bob Scriver[/color]

[COLOR=white 006633]MSU Spartan[/color]

 

[infinitx]

gol4,

That makes sense. Thanks!

scriverb,

Yes, I did catch that! Thanks! Everything works great!

----
Alex

Anytime things appear to be going better, you have overlooked something.