Autonumber Problem?

[gmatriix]

Hello All,

Here is my issue. I have 3 Access dbs that all use a field called "product id" that uses autonumber. The person who set this up is using the auto number like a part number.

I need to combine all three to together but when I do it will not let continuing using autonumber because their is data in the field now.

Is their anyway to get around this. I still need to use autonumber after combining.

Any Ideas?

 

[jzwp22]

Just to clarify, you said that you have 3 Access databases. I assume that means that each database contains a table that, in turn, has an autonumber productID field? Is your intent to combine other fields from the 3 tables in the 3 databases as well? Are you planning on moving forward with just 1 database with 1 table?

 

[gmatriix]

Hello,

Yes, each database contain a table with product id. Im just taking the data from all 3 tables from the 3 separate dbs and combining them into one database. I will be then uploading that table with all the data to SQL for backend once I get this to work.

After combining all 3 in to one I wanted to set an autonumber on Product ID. I have tried this below "on dirty" but I keep get error

Nz(DMax("Product ID", "Master Product List"), 10,000) + 1

Thanks for your help!

 

[jzwp22]

Nz(DMax("Product ID", "Master Product List"), 10,000) + 1

The above would work when entering a new product via a form and if the product ID was not an autonumber field, but it will not work when trying to combine records into a single table.

I would also recommend getting rid of your spaces in the table and field names: ProductID and MasterProductList

Are there any overlapping Product ID numbers in the 3 tables you have now? If there are you will have to determine whether they represent the same product or different products.

What I propose is as follows:

Create a new table where ProductID is not an autonumber field. You can still have an autonumber field but it will be unrelated to your productID. I do not think that there is any way around this given that you have 3 separate sources for your records.

I have read several discussions saying that that the primary key of a table should not have significance to the user. Since the ProductID has significance to the user in your case, it would follow that it should not be used as the primary key anyway.


Run three append queries (one on each database/table), to append the records from each into the new table. You will have to address any overlaps at the time you do the append. You may have to link two of the tables into your current database in order to do the append queries.

Migrate the table to SQL Server. Use a form for data entry that uses your expression to increment the productID for each new product:

DMax("ProductID", "MasterProductList") + 1

 

[gmatriix]

Hello

Thanks for the help! I still getting an error when I put DMax("Product ID", "Master Product List") + 1. Maybe im putting it in the wrong spot. Just to test. I created a text box and put the dmax in and it came back "#error"

So I dont really know what im doing wrong????

 

[jzwp22]

I would usually put the DMAX() function in the after insert event of the form. See the form frmProduct in the attached database.

 

[gmatriix]

Thank you very much....this was very helpful.

Thanks again

Take Care!

 

[jzwp22]

You're welcome.

 

[pensize599]

Are you planning on moving forward with just 1 database with 1 table?

 

[gmatriix]

Hello,

Yes I am