Duplicating a Record Multiple Times

[roxannep]

I'm wondering what is the best way to approach the following scenario:

Our company has 500 identical product items. We need them each individually entered into our inventory table with each one having a unique InventoryID (autonumber, primary key).

The challenge comes in when a data entry operator must enter the item description details, title, etc., then copy that record 500 times, then move to an entirely new item and repeat this process, but for maybe only 330 pieces.

We are trying to determine the fastest, most user-friendly (read: these folks don't know a table or paste append command from a hole in the wall) way to automate the creation of these multiple records.

Thanks for the guidance!

 

[kathryn]

Is there any rhyme or reason to the records with the identical products? Are the primary keys sequential? Do they have a ProductID that is identical?

Kathryn

 

[roxannep]

The items are identical because they may have been purchased as a box lot, but are being sold individually as the owner deems he wants to put them up for sale one at a time. There is no automatic date to put each one up, and no set price (these are going up on auction).

The only thing that would distinguish each item before going up for auction would be the InventoryID assigned by Access as the primary key (sequential, autonumber). So, if we have three items, they would all be identical in description except that IventoryID as they were entered into the Inventory database. Once they are assigned to an auction, they are assigned an end date and an auction code which begins to further identify each item uniquely.

The owner wants to know without going to the warehouse, how many are left, how many are out on auction, and how many have been purchased. He wants it to tie in and be accessible from the same database I have developed for his other auction items that are truly unique in nature (one kind only of a certain coin). So, I thought the easiest way was to determine the number of pieces and then simply make that many records with the IventoryID being each one's unique identifier until they are put into the auction system.

Does this answer what you asked? Notice I'm not asking if it makes sense, because to me, the request to begin with doesn't, but he's the boss and asked if it could be done, not whether I thought it was the best way to do it.

Thanks for your help and interest!

 

[kathryn]

So you want to enter an item X number of times. Each item would have a number of fields.

I would handle this by giving your users a form. The form would have unbound textboxes, call them txtItemDesc, txtItemCost, etc. for all the fields you need entered for an item and an unbound textbox, call it txtItemCount, into which they would enter the number of that item that need to be entered.

Give the users a button on a form to click to add the records. Call it cmdAdd.

In the click event of cmdAdd, enter this code.

dim rst as recordset
set rst = currentdb.openrecordset("YourInventoryTableName&quot

for 1 to me!txtItemCount
rst.addnew
rst![desc] = me!txtItemDesc
rst![cost] = me!txtItemCost
etc....
rst.update
next

This should get you started, I hope. Let me know how it goes.

Kathryn

 

[roxannep]

Thank you .... I will try it now ... I wondered if that was the easiest way and had started down that track, but am not that good with the coding side, so I appreciate the assist!

I'll let you know how it goes!

 

[roxannep]

It worked great! But I did have to add one bit to the code:

dim rst as recordset
dim intCounter as Integer
set rst = currentdb.openrecordset("YourInventoryTableName&quot

For intCounter = 1 to me!txtItemCount
rst.addnew
rst![desc] = me!txtItemDesc
rst![cost] = me!txtItemCost
etc....
rst.update
next