To Kevin: How To Automate Record Duplication

  • Thread starter Thread starter Kevin
  • Start date Start date
K

Kevin

Jody,

OK, I forgot about something. The append query is trying
to append your Activity _ID field into the table and can't
because that is the primary key for the table. Do this:

1. Change the current Append Query to a select query, then
from that select query make a new query with this one
being an append query into your table. In this query, do
not include the Activity_ID. Save the query.

2. In the copy function code you will have to change the
name of the query your calling to this new query.

Now test what you've done.

If this still does not work, comment out the
docmd.setwarnings False and docmd.setwarnings True lines
by placing a single quote (') to the left of each line.
Also, before any code except the function header put the a
line that reads "On Error Goto 0" (that is zero).

If you have a code error, you will get a message that will
allow you to Debug or to End the subroutine. Choose debug.
Post back at that point and tyell me what happens.

If the problem is due to a problem in the query you will
get a message that indicates that access could not append
the records and will give some other information. Read
that message carefully and post back with what it tells
you.

When you post back, post back to this thread so I can look
back at what I have told you so far and so other people
can follow what is happening and maybe chime in with a
solution.

With these two actions, you should get an error message of
some sort if something is still wrong.

Hope that helps and sorry for the misguided information!

Kevin
 
1. I changed my append query to a select query and called
it Q:Select_Activity. And made a new append query called
Q:Duplicate_Activity using Select_Activity without the
Activity_ID field.

I had to comment out the docmd.setwarnings because I
wasn't getting any other messages. Here's the messages I
do receive:

"You are about to run an append qery that will modify data
in your table." When I click Yes, I get the following
message: "You are about to append 0 row(s)."

The append query is not picking up on the selected
Activity_ID from the select query -- Q:Select_Activity.

Jody
 
Jody,

The first message is expected. The second message should
say something like Microsoft Access can't append all the
records in the append query. The it should have a
paragraph where the problems are detailed. Example it may
say Microsoft Access set 0 records to Null due to a Type
Conversion Error, and it didn't add 1 record to table due
to Key violations, ... If it runs normally (with warnings
turned off it will usually have just one message that says
something like "Your about to append 1 row(s)".

Can you tell me from that second message is it giving you
any reason for 0 rows? Also, your select query, does it
return rows?

Kevin
 
Here's the message:
"Travel can't append all the records in the append query.
Travel set 0 (zero) field(s) to Null due to a type
conversion failure and it didn't add 1 record due to key
violations, 0 (zero) records due to lock violations and 0
(zero) records due to validation rule violations."

When I run my select query manually, I have to plug in an
Activity_ID and then it returns the select row. When I
run the append query manually, I have to plug in the
Activity_ID and it will return a record. And that's when
the above append message comes into play.

Jody
 
In the Append Query, are you including the Activity_ID? If
so, delete that field. If I remember right, this field was
your primary key and it is an autonumber field, thus you
don't need to append it because the application is
creating it for you. The Activity_ID needs to be present
in the Select Query because you are using it for a
selection criteria, but it does not need to be there when
you append because it is being generated by the
application when the new record is added to the table.

Hope that helps! Repost if it doesn't and we will look at
having you send me the file.

Kevin
 
Back
Top