Null Value in an Autonumber field... but im not INSERTing into the AutoNumber field.

[trevt]

Good day wonderful people!



I have made great strides in building an Access to Excel Export / Import set up. As long as the primary keys aren't Autonumbers. Basically, I Export all the fields that can be nicely imported (eg. no calculated fields) after making some changes in Excel. I need the PK field for the UPDATE query that i run after I run an APPEND (INSERT INTO) query. After much Googling, i arranged it so the blank PK field isn't in the INSERT statement, but I am still getting the Import Error tables about the Null Value in a Autonumber field.

The basics:

1. I Export :

 SELECT FT_ID, FIWP_No, Tag_No FROM FIWP_Tag_List

2. I Import to a Temp Table: tmp_FIWP_Tag_List (Same Fields)

3. I Append:

 INSERT INTO FIWP_Tag_List ( FIWP_No, Tag_No ) SELECT tmp_FIWP_Tag_List.FIWP_No, tmp_FIWP_Tag_List.Tag_NoFROM tmp_FIWP_Tag_List LEFT JOIN FIWP_Tag_List ON tmp_FIWP_Tag_List.[FT_ID] = FIWP_Tag_List.[FT_ID]
WHERE (((FIWP_Tag_List.FT_ID) Is Null))

4. I Update:

 UPDATE FIWP_Tag_List INNER JOIN tmp_FIWP_Tag_List ON FIWP_Tag_List.FT_ID = tmp_FIWP_Tag_List.FT_ID SET FIWP_Tag_List.FIWP_No = [tmp_FIWP_Tag_List].[FIWP_No], FIWP_Tag_List.Tag_No = [tmp_FIWP_Tag_List].[Tag_No]
WHERE (((FIWP_Tag_List.FT_ID)=[tmp_FIWP_Tag_List].[FT_ID]))

5. I get the Error Table...

Any ideas?

Thanks in Advance!

Trev

[John_G]

Which field is the Autonumber field in your table, and when does the error occur (if you get an runtime error).

Can you show us what the error table looks like?

Are you sure you want to do an append? You are exporting records from FIWP_Tag_List to Excel, making changes, and re-importing to the Temp table tmp_FIWP_Tag_List. That I can see. But why do you want to append more records into FIWP_Tag_List - wouldn't you want to just update the ones you exported in the first place?

[trevt]

Hi John_G,

The Autonumber field is FT_ID. Some of the changes could be the addition of new records, that's why i need to add any new records and update the rest. That's why FT_ID is in the Update query but not the Append query.



Thanks!

Trev

[trevt]

Le sigh... Then I figure it out on my own...

When I built the temp tables I copied the original table structure exactly... Including Autonumber fields. They were the ones bringing up the error tables.

Thanks again!

[John_G]

Glad you got it sorted out. Just out of curiosity, when you imported back to the temp table (with the autonumber still in it), what did it actually do with the autonumber fields? Did it put the old values back in, or did it create new numbers? It Access was working properly, it should have created new ones for all of the records, including the Nulls.

[trevt]

I can't seem to duplicate what was going on now that I've changed everything. It seems to be using the old values because the old records in the temp table were being deleted out before the new import, but the autonumber always started at 1. I am starting to think that the autonumber isn't the way to go as the PK now in some tables. It would allow duplicates in the main table if the same file was imported twice (the field would still be blank in the excel). Most tables have one unique field that I use for the PK, these are good. The others that I only use as an intermediate table for many to many relationship type stuff had the autonumber PK, which I actually need more of a no-duplicates in these two fields type set up. The current table above is that kind of table. There's a table "Tags" and another "FIWPs". There can be more than one tag per FIWP as well as more than one FIWP per Tag, but the Tag/FIWP combination should only be in there once.

More fun to be had!

Thanks!

[John_G]

but the Tag/FIWP combination should only be in there once.

You can enforce that by putting a unique two-field index on tag+FIWP.

[trevt]

Which I tried to play with quickly, bu ran into trouble with my Update query. (Which probably isn't even necessary in these type of tables now that I think about it.) Will play more later.

Thanks again!