Creating a Custom Incrementing Primary Key

[PraizesLiberato]

Hi all,

I had a problem with my ms access database table(main table) when it gave me a record called #deleted because this MS access DB was on the network i guess it caused this problem. But I managed to get around it without compacting and repairing the database (as it had given me a lot of trouble) with my forms.

what i had done is i had copied all the data from the main table into a new table with the same structure but now i cant use the same auto number field as its showing as 1,2,3,4.

Obviously that hadn't helped so Now what i have done is i have turned the primary key field in my table (main table) to a number because I have lots of related records in over 15 tables (Related Child Tables).

Now the solution I need is that I am looking for a code that I can run in VBA-SQL as well as in the ms access form so that a primary key will be created like an increment to the max value in the Main table. And I guess the child records would probable pick up the PK as their FK automatically.

I know how to write a code for this solution but I don’t know exactly where i should put it and also that the best way to do this.

Could anybody give me a link or an MS Access database that does this solution well?

Need help. Lots of work has stopped because of this and we are running to older processes at the moment.

Any help is very much appreciated.

 

[PraizesLiberato]

ok.. its been a while...is anyone out there.

 

[vbaInet]

Perform an Advanced Search on the forum for this. It's been covered several times. It's got something to do with the DMax() function.

 

[PraizesLiberato]

Perform an Advanced Search on the forum for this. It's been covered several times. It's got something to do with the DMax() function.

ok plse give me a direct answer..i sure you have this fugured out.

how about using dmax of the pk and adding one as a default value on the create record page.

 

[vbaInet]

There you go. You knew it all along

 

[PraizesLiberato]

ok plse give me a direct answer..i sure you have this fugured out.

how about using dmax of the pk and adding one as a default value on the create record page.

Anyways it works i tried using a query using max but this dmax may be better.

Still have to test this.

But let me know. I will lookup for the other answers.

 

[vbaInet]

As the Default Value property:

=DMax("[FieldName]", "[TableName]") + 1

 

[PraizesLiberato]

As the Default Value property:

=DMax("[FieldName]", "[TableName]") + 1

Kool!! thanks. This wasnt working a few moments ago.

Wasn't using the double quotes

anyhow the bigger problem is FIXED.

 

[vbaInet]

Glad we could help.