How do I create multiple records in Access?

[Astron2012]

Hi i'm relatively new to Access and have a simple database which I use to generate Publication numbers for our new publications. I would like to have a text box and button on a form that will generate duplicate records of the current record the same number of times as specified in the text box.



eg. If I need 6 copies of a record I would like to enter 6 in a text box, click a command button that will generate 6 duplicates of that record.

I would be grateful if someone could help me with the code in order to do this 'on click'.

Table = Publications
Form = Publicationsfrm
Text Box = Lines
Command Button = Submit
Primary Key = PublicationNo

I have tried the following code but it is not working.....

Dim i as Long, myRS as Recordset
Set myRS = CurrentDB.OpenRecordset("Publications")
For i = 1 to Me![Lines]
With myRS
.AddNew
![PublicationNo] = Me![PublicationNo]
.Update
End With
Next

Alternatively if I have a duplicate record button that uses a macro is there a way to run that macro the specified number of times in the 'Lines' text box?

Any help on this would be much appreciated

[Robeen]

Are you trying to add duplicate 'records' to a Table?

[Astron2012]

Hi,

Yes I wish to create multiple duplicates of the same record at once. The amount of duplicates can vary in each instance and can be as much as 80 duplications, which is why if I had the facility to enter the amount into a text box first on my form it would make life so much easier. I don't wish to give my users access to the main table to just copy and paste there.

I currently have a duplicate record buttone on the form but it only does one duplicate at a time, so not ideal if there are as much as 80.

Any help on this would be great!

Many Thanks

[Robeen]

I worked up this little routine and it is doing what I think you need it to do.

I created a Table called tblCourses with 4 fields:
1. ID [Primary Key, [Text - (but contains numbers)]
2. Description [Text]
3. StartDate [Date]
4. StartTime [Date].

I had to increment the ID because it is the Primary Key field in my table.

My Form displays one record at a time and in addition to the 4 fields from the Table, has a textbox named txtDuplications & a button named cmdUpdate.

Here is the code for the button:

 
Private Sub cmdUpdate_Click()
 
Dim ID, CourseDesc, StrSQL As String
Dim i, j, intID, intDuplications As Integer
Dim StartDate, StartTime As Date
Dim rs As Recordset
 
Set rs = CurrentDb.OpenRecordset("tblCourses")
 
DoCmd.SetWarnings False
 
intID = DMax("ID", "tblCourses")
 
Me.Description.SetFocus
CourseDesc = Me.Description.Text
 
Me.StartDate.SetFocus
StartDate = Me.StartDate.Value
 
Me.StartTime.SetFocus
StartTime = Me.StartTime.Value
 
Me.txtDuplications.SetFocus
intDuplications = Me.txtDuplications.Value
 
For i = 1 To intDuplications
    intID = intID + 1
 
    StrSQL = "INSERT INTO tblCourses (ID, Description, StartDate, StartTime) "
    StrSQL = StrSQL & "VALUES (" & intID & ", " & "'" & CourseDesc & "'" & ", #" & StartDate & "#, #" & StartTime & "#); "
 
    'I used this message box to make sure my sql was correct.
    'MsgBox "SQL before RunSQL command: " & StrSQL
 
    DoCmd.RunSQL StrSQL
Next
 
DoCmd.SetWarnings True
 
End Sub

Let me know if this works for you!

[Astron2012]

Hi thanks so much for that, I tried it using the same number of fields and field types as you did and it worked a treat. The only trouble is when I try to add other fields to it I get a runtime error which relates to "DoCmd.RunSQL StrSQL"
I've probably gone wrong with the code.....

Private Sub cmdUpdate_Click()
Dim ID, frmPublications, StrSQL As String
Dim i, j, intID, intDuplications As Integer
Dim StartDate, StartTime As Date
Dim rs As Recordset

Set rs = CurrentDb.OpenRecordset("tblPbns")

DoCmd.SetWarnings False

intID = DMax("ID", "tblPbns")

Me.Description.SetFocus
frmPublications = Me.Description.Text
Me.PublicationName.SetFocus
frmPublications = Me.PublicationName.Text

Me.StartDate.SetFocus
StartDate = Me.StartDate.Value
Me.StartTime.SetFocus
StartTime = Me.StartTime.Value

Me.txtduplications.SetFocus
intDuplications = Me.txtduplications.Value

For i = 1 To intDuplications
intID = intID + 1

StrSQL = "INSERT INTO tblPbns (ID, Description, PublicationName, StartDate, StartTime) "
StrSQL = StrSQL & "VALUES (" & intID & ", " & "'" & frmPublications & "'" & ", #" & StartDate & "#, #" & StartTime & "#); "

'I used this message box to make sure my sql was correct.
'MsgBox "SQL before RunSQL command: " & StrSQL

DoCmd.RunSQL StrSQL
Next

DoCmd.SetWarnings True

End Sub

If you are able to let me know what I've done wrong i'd be really grateful.

Another small problem I have is that when I tried it without the additional field it worked but I got an error message saying "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key or relationship. Change the data in the field, remove the index or redefine the index to permit duplicate entires and try again."

It then says "You cannot save this record at this time, close anyway?" I click yes but it still works and saves the new duplicates which is strange.

So I just need to figure out how to get rid of those messages as it is actually duplicating OK.

I don't want to remove the primary key or index as they are necessary.

Any help would be much appreciated

[Robeen]

The 'duplicate values' message means that you are attempting to put a value into a table field that is:
1. Already in the table - in that field.
2. Set up to NOT allow duplicate values.

You can test this behavior by opening the table itself and trying to put duplicate values into your table. You will get the same message there.
This is just the way databases work.
Your table is just doing what you asked it to do [prevent duplicates].

It would be difficult for me to troubleshoot this without having a copy of the database to work with.

Can you post a copy of your database here?

[Astron2012]

Hi Robeen, Thank you for your quick reply. The only field that does not allow duplicates is the Primary Key (ID) field.

I have attached the database, its really basic at the moment till I get this sorted.

I have hidden the start date and start time but kept them because I couldnt get the duplication operation to work without them, which is fine as I will actually need the date and time anyway. Although the duplicated dates are in American format for some reason but when I enter a publication in manually it shows in the correct UK format. Strange???

Thanks again for your help

[Astron2012]

Sorry the file didn't attach before, here it is.

[Robeen]

I just opened your database and ran the Form.
I created three new rows of data using the last record in the table.

I didn't get any errors. It ran without problems.

[Astron2012]

Hi Yes, sorry I should have mentioned that when you are copying an existing record it is fine, but when you do a new record the error messages appear. If you try doing a new test record you should see what I mean.

[Robeen]

I just created three new records - no problem.

BUT . . .

I WAS able to generate an error when I:
1. Tried to create a new record,
2. Left the 'Duplications' box blank, and then
3. Clicked on the 'Submit' button.
. . . then I got an 'Invalid use of Null'.

If that's not the error you are getting, tell me step-by-step what you do to get your error.

Remember - that 'Submit' button is not designed to add new records to the table.
It has only been coded to add duplicates of existing records to the table.

[Astron2012]

I go to a new record which the form does automatically anyway. Enter data in the description field. Then enter a number in the duplications txt box, click submit, then when I click exit to close the form the error messages appear.

[Robeen]

I followed your instructoins - no error.

I entered the text "Create Error", entered the number 2 in the Duplications box and clicked Submit twice.
Then I clicked the Exit button -> the Form closed without an error.
The Table then had 5 records with "Create Error" in them.

[Astron2012]

Thats really strange, the error always appears after I've created a new record, then selected to duplicate it. OK well nevermind at least its working. My main problem is trying to add fields to the form to duplicate as I can only seem to use the ones you created. When I try to substitute or add my own it doesn't work.

[Astron2012]

maybe the errors appear due to the different Access versions