Copying a record in VBA

Question

I work with a product that comes in a 2000lb sack and placed on a pallet. When this product is made it has many different elements that are tested and each test has a field that the numerical data is placed in. Each of these records of tests are then assign a number, for example, L20444.

Now we have the ability to take that 2000lb sack and convert it into 80 20lb bags. Only 40 20lb bags can fit on one pallet, taking now the one pallet L20444 and making two pallets that have the number L20444. This causes a problem with inventory because the number L20444 can only be assign one warehouse location, not two.

So my bosses what to create a number that is almost the same, but different enough to place the second pallet in the warehouse. The second pallet will now be L20444B. It will still have all the same tested numbers and is a "copy" of the original L20444.

My question is can I take the record L20444 and copy all the data for that record and then save it as L20444B so that it can be placed in the warehouse.

So is it possible for VBA to copy a record, rename it, and then save it in the same database as a new record?

Any help would be appreciated, Thanks.

Answer 1

Why don't you create a new table, which tracks the location of the two pallets (and the new number(s)), which links back (with a foreign key) to the single record for the stock in the original table?

That should work, and will avoid what will otherwise become a nightmare of redundant data.

Answer 2

If I'm reading you right it sounds like you want a SQL statement to create a new record.

You're using Microsoft Access? I would recommend first creating a query that does this in the query editor. It will be an Append query, something along the lines of:

INSERT INTO TableA ( ID,col1, col2 )
SELECT [ID] & "B" AS NewName,col1, col2
FROM TableA
WHERE (([ID]="L20444"));

Test this first to make sure it's doing what you want, and make "L20444" into a parameter ([OldID], or something). Then add some code in your VBA script that executes this query. It should pop up asking you for OldID when you run it.

Then you'll need to learn how to execute parameterized queries from VBA. Something like this:

Set qdf1 = CurrentDb.QueryDefs("myQuery")
qdf1.Parameters("OldID") = theOldID
qdf1.Execute

Not tested, search VBA help for QueryDefs if my syntax isn't quite right.