×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Insert Into Query filling table with all but one record

Insert Into Query filling table with all but one record

Insert Into Query filling table with all but one record

(OP)
Folks,

I have an Insert Into query that fills a table. When I run the query as a simple Select query I get one more record than when the Insert query runs.

I've checked the filtering criteria, and the missing record in the Insert query meets the criteria which is proved when running the query as the simple Select query.

Has anyone ever run into this before?

Thanks,

Vic

RE: Insert Into Query filling table with all but one record

Typically a table will have constraints like unique indexes and relationships. Is the failing record caused by one of these rules?

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

RE: Insert Into Query filling table with all but one record

(OP)
Duane,
Thanks for your suggestion.

I did some more investigating and tried to run the delete table query and insert into table query manually. In the context of my code, I set the Warnings off. So I never saw the error messages causing the problem.

When running the queries manually, the Insert Into query indicated I would be inserting 2513 records into the table. When I allowed it to do so, I received a msg saying that 1 record had a 'Validation Rule Violation'. I reviewed all the Validation Rules in the receiving table and no fields had any Validation Rules listed.

The website I found about Validation Rules also indicated that if there were no Validation Rules, maybe there was a problem with text fields' "Required" rule being set to No or the fields' "Allow Zero Length" rule being set to No that was causing the problem. It also indicated if I allowed the Insert Into to continue without the offending record, a Paste Errors or Insert Errors table would be created. That never happened during the manual events. So I was still confused as to what the cause was.

I looked at the data in the table where the original data resides, and from which, the query was extracting to put into the receiving table. I looked at every 'empty' text field and entered each to perform a delete. Still the Insert Into query failed at that same record.

I then copied that record into an Excel spreadsheet and went thru each 'empty' text field and deleted the contents. Then I pasted that data back into the original table and reran the queries. The Insert Into worked this time and all records were inserted into the receiving table.

But at this point, I still don't know why this issue appeared since both times I 'deleted' the contents of 'empty' text fields with only the second time (the Excel version of deleting) working.

I will need to understand this so that I can prevent this issue from occurring in the future.

Vic

RE: Insert Into Query filling table with all but one record

Do all of your fields allow zero length strings? Tables also have validation rules.

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close