Copying Records With Autonumber Fields 2

[Culby]

:^) THE SITUATION: I have 2 database files. One is open to multiple people for data entry. In the other only I have access. I review the entries and if acceptable I append or copy them to the secure database.

:-( THE PROBLEM: I am copying from one table to another table and each has an autonumber field. Unfortunately I have used this autonumber to identify images in another folder that are displayed in forms in the database. When I copy from one autonumber field to another autonumber field, access changes the number to the next available number on the table I am copying to. Since this changes the autonumber value it no longer matches my the file names in my image folder.

: HOW DO I FIX THIS? Or, what is a better way of accomplishing the same thing?
Thanks, Culby

 

[Quehay]

If the target table has valid, distinct IDs that are used for images (although this sounds like a modeling problem) why not create another column for IMPORT_ID (Long) Indexed No Duplicates .

Then in your review form just have an "Import" button that executes the SQL to INSERT the record.

Turn your headache into my project!
Jeffrey R. Roberts
Insight Data Consulting
Access and SQL Server Development

http://www.InsightDataConsulting.com

 

[JeremyNYC]

Culby,

I actually question your setup. Why not just secure the database and grant users minimal permissions. Then all you'll have to do is flip a yes/no field in the records that you want to approve.

If you're not going to do that, just change the autonumber field in the table in the "secure" database to a numeric field. If you're just going to be getting the records from this other table, there's no need to make that field an autonumber. But do make sure it's your primary key.

Jeremy

==
Jeremy Wallace
AlphaBet City Dataworks

http://www.ABCDataworks.com

Access Databases for Non-Profit Organizations

Please post in the appropriate forum with a descriptive subject; code and SQL, if referenced; and expected results. See thread181-473997 for more pointers.