If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Duplicate Record Error on the AFterUpdate Event

Hello All,

I am trying to create a Duplicate Record using the wizard, but I am receiveing an error.

I am using two combo boxes to populate data into an input form (code below).

Both combo boxes populate the data well. Now I want to add a Duplicate Record button to duplicate a record. I thought I could just use the wizard to create a button, but it is not working. It is erroring out on the Combo Box 2 - AfterUpdate Event. :-(

I need to create a duplicated record with a new PK. I want to duplicate most of the data and remove 3 fields so I can update those fields.

It's a rather simple task and you don't need to actually create a query object in your database: you can have a SQL expression executed by the JET engine (= the database engine or the SQL interpretor) in a VBA module.

If we want to use the same technique in a bound Form where the row to be duplicated is the current row, with the additional constraint that the newly created row should become the current record for the form, we can use:

I did try to use your code below, but it is not working. I may not have updated it incorrectly. I am using a combo box that is attached to a query to populate the data.

So, when I click on the button it looks like it added a new record, but when I check the table the dup record was not created. I had to comment out the “CurrentDb.Execute strSQL, dbFailOnError” because it gave me a Run-time error: Syntax error in INSERT INTO statement.

Obviously, there is an issue with my Insert Into statement, but I can't seem to find it.

b) When the code stops executing, open the immediate window (Ctrl+G) and copy the text printed into it (it's the string contained in the variable strSQL).
c) Create a new query, switch to SQL view and paste the string copied in b).
d) Switch the query to DataSheet view. The error that will be reported by the query interpretor should be more explicit than the one reported by the VBA interpretor.

an even quicker (and dirtier ) approach woudl be to copy and paste the the row in a form
select the bar to the left of the row you want to copy
<ctrl + C> to copy the selected row
add a new record
<ctrl + P> to paste the selected row
then edit the data
..the big advantage form my perspective is no programming effort required
..the big disadvantage form my perspective is that its all to easy so users coudl well end up duplicating rows uneccesarily, compromising the data integrity. they 'forget' to change the 3 columns that are different

if you are adding data and want to carry forward the last entered rows then I guess you could always stuff the previous row's values into form global variables in the forms before or after update event and then have the button
1) add a new row
2) copy the values form your forms global variables into the relevant controls.

I wouldn't personally use a query for this, but then again I don't like the idea of letting users copy rows... it just doesn't 'feel' right, has the possible whiff of flaky normalised design. it may well be fine, but it doesn't 'sound' right

I agree that the big drawback would be forgetting to change those 3 columns; only if those Fields were set up to be unique, with validation, could you be sure that copied data was replaced in the New Record.

As for the copying of the last Record entered meaning that the database was non-normalized, that would depend on what the business needs required.

A database which had someone doing 'production' style data entry, documenting, for instance, the work done by each of three dozen telemarketers, on each of the previous 30 workdays, tracking for each day

How many cold calls were made

How many contacts were actually made

How many sales of ProductA were made

How many sales of Accessory Kits for ProductA were made

would be normalized, but very tiresome, because of all of the repetitive data entry required!

Linq ;0)>

Hope this helps!

The problem with making anything foolproof...is that fools are so darn ingenious!