Trap Append Query Information

[Quintios]

During an Append Query, Access gives you a dialog box informing you of how many records will be appended, and then another informing how many were not appended due to key violations and such.

I was wondering if there was a way to trap this information into a variable that I could use in a MsgBox?

Thanks in advance,
Onwards,

Q-

 

[andrep]

First thing you can go to the menu click tools, options, select the edit/find tab , and uncheck the confirm action queries check box. This will remove the first pop up you
receive.

Now if your append query is correctly built you should not receive the second part telling you about the violations !

Hth
André

 

[thevillageinn]

Andre, and Q correct me if I'm wrong, but I think the question is whether the "warnings" that come up during an append query contain system variables that can be used to supply a message box with a count of the records to be changed.

I believe Q is familiar with 'docmd.setwarnings false' but would like to get the info that is being suppressed by that command in the form of a variable.

Q - am I on track here with what you were asking? I'm curious too...short of looping through the recordset and counting each one, I didn't think those numbers were available.

-Dan

 

[Quintios]

thevillageinn you are right on track.

This query will take place multiple times, the first time to populate the table, and then after that it depends on what the enduser does with the database.

Not a lot of people understand databases very well and cryptic warning messages tend to make people nervious, so what I wanted to do was to trap the information and put it into a much more user-friendly message box - something they'll understand.

I expect to get the key violations when a second append is performed.

For example, lets say the first append puts 1100 records into the table. The user adds a certain type of record and then is instructed to "update" the database by clicking a button that will perform the append. Now the append will put in 1256 records, but 1100 records are not appended because of the key violations. (This is a good thing!) I want to subtract the difference and pop up a box that says "156 Records Were Added to blah blah blah...", or something like that.

Any way to do this?

If you care to read on, I'm sure someone will ask why I'm doing this. It's because *all* the records in the main table are related to each other in a certain way, and when a certain record is added I need to have a table that has information that compares the records together. So, let's say the special records are call 'X' records. Every other record in the main table is related to each 'X' record, and information regarding the relationship needs to be stored. Kinda like this:

------------
Result Table
------------
Tag1               Tag2                Action
-----             ------              --------
RegularRecord1    X_Record1             info
RegularRecord1    X_Record2             info
RegularRecord1    X_Record3             info
RegularRecord2    X_Record1             info
RegularRecord2    X_Record2             info
etc...

So if someone deletes an "X" type record that fine because cascade delete will take care of it, same with the RegularRecords. However, if someone adds another "X" type record the table needs to be updated. I don't know anything about update queries; besides, I don't want to overwrite the "info" in the last column.

So there it is.

Back to the original subject, any way to get some numbers out of this?

Onwards,

Q-