How to Auto Increment A Text Field

My company has a database on SQL server. I am tyring to make an access interface that will make a lot of things easier. I cannot change the database structure or the field types.

I am trying to make a form where users can modify infromation in a 'notes' table. The notes table has three keys, PersonID, ClientID, and NoteID, the data Type for each of these is "text". I have everything working fine if the users modify an existing note, however I cannot figure out how to let users enter in a new record.

I crete a button and make some VBA code that uses the DoCmd.GoToRecord, "FormName", acNewRec command. I can then auto fill in the PersonId, and ClientID because that information is on the original form. However, NoteID seems to be a number that the SQL server auto generates.

What VBA code can I use to find what the next auto number should be?

I can find what I think is the highest current key by using:
AutoNum = DMax("NoteID", "dbo_PersonNotes")
But I do not know how to increment that number by one.

From memory, it seems like access will automatically insert the correct ID if the field type is set as "automumber" after the user enters in a new record. When I try it now I get an error "ODBC--call failed" then "You can't save this record at this time"

I have searched around a bit online but have not been able to find an anser. Any help is appreciated. Thanks

re: How to Auto Increment A Text Field

Hi. If the NoteID field is a text field it is highly unlikely that it can be set automatically by the SQLServer back-end. As you are not able to modify the field type I think you are going to have to live with some compromises over the use of this field at present - it is not ideal to be storing a number in a text field, but if there is nothing you can do about it so be it.

If the DMax is working for you at present you can simply add 1 to it to generate the next number in the sequence:

NextNum = DMax("NoteID", "dbo_PersonNotes") + 1

You would set this value only when you add a new record in your form.

You have not said so, but it is possible that the NoteID field uses a specific number format (such as the use of leading zeroes in a specified field width - 000123, 001234 and so on). If you need to use such a format with the NoteID field use the format function to do so. the example below takes as input a number (in this case, the number representing the next in sequence for your NoteId field) and returns a six-character string formatted with leading zeroes as necessary.

NextNumString = Format(DMax("NoteID", "dbo_PersonNotes") + 1, "000000")

-Stewart

re: How to Auto Increment A Text Field

The SQL back end creates a long complex ID with numbers and letters. I have not been able to dicern a pattern out of it.

I was able to find a compromise solution by just having access generate a unique key. I used the date, time, and user which should ensure that the same key is never generated twice. The keys generated from access and from SQL look very different, but so far it seems to be working.

Thanks for the help.