Type conversion error in append query that worked minutes ago

[Robert2019]

I have a query that appends data to an existing table. This query is run from a form. It sets query criteria from one field in the form. It's been working great for weeks. It worked yesterday.

I created another append query to append data that's calculated differently and to be appended to the same table as the first. Again it uses some criteria submitted from the form and subform. After much searching of the web I finally figured out how to properly call for a criteria value from my subform. The append query worked.

Then I thought I would get smart and after running the query, I added anew instruction to refresh the form. (I have a refresh button, but I thought if refresh could just happen after running the append query, it would be much nicer.)

Now the append query gets this error: ms access set 0 fields to null due to a type conversion failure

I removed the refresh action. But it's broken. Both append queries are broken. The whole point of the database is to run these queries which split costs for resources across multiple campuses at varying cost shares.

It makes no sense that the queries appended fine one moment and get conversion type errors now. Nothing in the original table was changed, (that I'm aware I changed). Even running the queries outside of the form and without the calls to form data, the conversion error comes up.

 

[Deleted member 147267]

ms access set 0 fields to null due to a type conversion failure

That's only part of a much longer message, yes? It covers about 4 different reasons with a number for each. You have posted the part that doesn't apply (0 records). Can you post the rest?

If you run it anyway, you may or may not be able to see what records were not affected. I'm going to go out on a limb and say those that don't work have null values in one or more fields, thus your query fails, but the rest of the message would help.

 

[Robert2019]

Sorry about truncating the error message. I thought it was standard from there out:

"Microsoft Office Access set 0 fields to null due to a type conversion failure, and it didn't add 8 records to the table due to key violations, 0 records due to lock violations, and 0 records due to validation rule violations."

The query with or without the append setting finds 8 records, which is the correct number to find. If I turn off the append I get eight records, and all fields look correct. They are (appear to be) number fields as they should.

Edit: The table I'm appending to has an autonumber field and I am not trying to put any of my queried fields into that field nor do they have a label of ID.

 

[Deleted member 147267]

it didn't add 8 records to the table due to key violations

This is telling you that one or more fields of the query contain values that cannot be appended because these values already exist in a field(s) where those values are only allowed to be there 1x These fields could be Primary Key fields, or indexed with unique values set to Yes (meaning each value has to be unique in the indexed field). You need to do something like

a) determine which records contain the same values in the indexed field(s) and don't try to write those (if using a select query first, tweak it to not include them) or
b) turn warnings on and off and run the append anyway (not what I would do)
c) use Execute method of CurrentDb object without dbFailOnError parameter (what I would do).

b and c assume that you might be routinely appending to this table from another source, for example, and just need to add what can be added and forget the rest. It's a common approach IMHO, and one that I would just let Access do the work for.

 

[Robert2019]

Okay, thanks Micron. There are three indexed fields. The primary (no duplicates0, the invID, and the resourceID. The last two permit duplicates as there can be ten campuses paying a share of each resource per invoice.

Fortunately, I sent a copy of this database to the potential client yesterday and that version still works. I will rebuild this morning's "improvements" and see if I can identify a breaking point. The original copy has other odd behaviors going on where subforms are completely locked I can't find the reason they became locked. Instead I'll just revert and start saving a backup daily.

 

[Deleted member 147267]

One approach that might work for you is to convert a copy of the action query/queries to saved Select query, then use find duplicates wizard where you link target table to new query and look for dupes on indexed (no dupes) and PK fields. You would at least see what Access is attempting to append that it cannot.
Good luck

 

[Robert2019]

The database is still small enough, testing size, that I ran as a select query and filtered the table and can't find the duplicates. But, as I'm rebuilding it in yesterday's copy, I'm thinking that the problem may be multiple tables have a primary key labeled--ID. Even though when I query them they've got some altered label, Access might be picking up that they are PK "ID" and those numbers probably duplicate the destination tables PK "ID." So I've renamed all my primary keys to be TableNameID.

Append Query one, is working fine in the restored version. Append Query two, the new one, also seems to be working fine. Plus I added the Refresh function after the query completes and it looks better with one fewer click.

 

[Deleted member 147267]

they've got some altered label,

Your fields might have captions, which could confuse you. In table design, see what the field name is and look at the property sheet for the caption property (or review them and switch back to datasheet view if you can remember what is what). I agree, ID makes a poor name for a field and can cause design mistakes when so many have the same name.
Good luck!