Is there a way to specify only new records in an append query?

[spherey]

Hello everyone -

Yesterday we generated a long, long thread
- thread702-587534 - about the best way to enter data from one form into two tables simultaneously when a user elected to do so. After a lot of helpful suggestions which for the most part didn't work for some reason, one of the forum members suggested that I not bother with the visual basic coding and opt for a more straightforward append query. So, I set that up, and guess what? It actually works! But, it's pretty convoluted, and I get an error message every time I run it telling me that it isn't going to add any of the records which are duplicates. And that's great - I don't want duplicate records. So, I have two questions:
1.) Can I specify in the append query that I only want to add those records which are new? As in, those records which have not ALREADY been added to the table? That way, the query won't try to add them anyway and return a message saying that it can't duplicate the records and has only added the newer ones.
2.) This is how I have it set up right now - and like I said, it does work, although I get the error message which is sure to alarm the end users. I'll describe how it works, and if anyone can see an immediately obvious way to make this less circuitous and more elegant, by all means clue me in.
Big thanks to everyone who's taken time to help me work this stuff out. I, too, am finding out that the more versed I become in Access, the more I come across things that still seem new or puzzling.

So: The situation is, I have a form which of course sends data to its underlying table; but if a user chooses, I want some of the data to also be sent to a second table, whose relevant field names match the primary table. How I've set this up is with an append query - if a box is check in the primary table (and thus in the query), the fields are appended to the second table. In the form, I've included the box to check, and a command button underneath it to run the append query. So the user would check the box, signifying that this data is to be sent to the second table, and then click the button, signifying 'do it now,' essentially. And, because there are other records which have previously been added to the form, the query is at this point trying to add them again, realizing that it can't (because doing so would duplicate a value in a primary key) and returning an error message to the tune of "none of the duplicate records were added."
The biggest thing that would help me is learning a way to tell the query that it's only supposed to add fields which are NOT duplicates, i.e., that are new.
Any advice?

Thanks a lot,

Spherey

 

[ReluctantDataGuy]

Yes there is a way. First, there's an easy "cheat" here. In the code that runs the append, add (above the append):

DoCmd.SetWarnings False

And turn warnings on after the append:

DoCmd.SetWarnings True

Now for a solution I use. I'll step you through it to show how it works.

First, make a new query and put the table with the records you want to append FROM on the left hand side. Put the table which is going to recieve the new records on the right side.

Link the two tables on the key field. Double click the link line and make it one to many so that the arrow points towards the table on the right - the one that will get appended TO.

Now add the key field from the table on the left into the query grid in column 1. Add the key field from the table on the right - the table records will be appended to - into the query grid in column 2.

In column 2's criteria row, add"

Is Null

Run the query. You should have a result that shows only the records NOT already in the table you want to append to.

Now, uncheck column 2 and add the remaining fields you want to append. Make it an append query. Then specify that you want to append column 1's value into the key field of the table that you're appending records to.

You should be able to test it with this query, then copy the query into code using a string for the SQL.

Hope that helps...

 

[spherey]

ReluctantDataGuy -

Thanks for your help! I made the new query, and tested it, and got up to this step:
"Now, uncheck column 2 and add the remaining fields you want to append. Make it an append query. Then specify that you want to append column 1's value into the key field of the table that you're appending records to."

I made it an append query, and it looks like it's defaulting to appending column 1's value into the key field of the table that I'm appending records to; but testing the query from that point gives me this error message:

Duplicate Output Destination 'Company Name'

(Company Name is the name of the key field in the tables I'm appending from and to.)

- and that makes sense, because as it is it looks like I've specified in the grid that the query is supposed to append Company Name from both tables into the Company Name of one - I'd think that I'd just delete column 2, where it's now being told to append the data to itself from the same source, except that's where you advised me to put the "Is Null" criteria. What have I done wrong?
Next, after I get that right and can make it work, how can I copy the query into code using a string for the SQL? I don't know how to do that...
And then, I add

DoCmd.SetWarnings False

above the code for the query that I've just created? Am I replacing the old query with this new one, or do I need them both?

Thanks,

Spherey

 

[ReluctantDataGuy]

Oh, sorry I wasn't more clear on that.

If you use the SetWarnings method, you don't need to create the append query I was talking about. I just mentioned that because you said you had an append working, but it gave the warning that it couldn't add some records due to duplicate key values. You can prevent that error from popping up by turning off Warnings prior to the append, and then turning it on again after.

The query I described is a way to only append the new records, without attempting to append duplicate records and thus avoiding the error in that way.

Now the error message you're getting: "Duplicate output destination" is probably caused by having both column 1 and column 2 checked (ie: included in your query result). The solution is to uncheck column 2.

I know that sounds wierd - uncheck the key field from the table you're appending to. But if you remember, column 2 has no values in that table - that's why this query works as an append - its appending records that don't currently exist in your main table.

Instead, use column 1 to append to your main tables key field.

Now, copying the query into code can get a little tricky. The basic method is to open the query in SQL mode and copy the text - then go to the code window and paste the text there.

It should look something like:


Dim strSQL as string

strSQL = "Select ....... From ...... Where ...."
DoCmd.RunSQL strSQL

Another way to do it is save the query once you get it working properly. Then in code you would go:

DoCmd.RunQuery "MyQueryName"

Hope this is helping.

 

[spherey]

It's definitely helping - thanks for your advice, and thanks for sticking with me on this. I'd like to be able to use the second option - I think in the long run it would be easier to do it that way. I'm still getting hung up on something, though - I follow the steps you've listed (I tried it a second time, and got the same results), but when I make the query an append query, the check boxes to choose whether to show a column or not all disappear. So, I think you're right: the problem is most likely having both column one and column two selected - but without the check box, how can I deselect column two without deleting it altogether?

 

[ReluctantDataGuy]

I'll have to look into it some more when I get back to work Monday, but you might try it in two query steps.

Instead of changing the query to an append query, just pull in all the fields you need, uncheck the one we talked about before so you don't get the duplicate. Save that query.

Then create an append query using the saved query as the source table.

That should work, and its been so long, I may have done it that way but don't remember. I hate saved queries though.