Populate a form field from a non related query

[gdomino]

I am currently working on a project that requires a primary key to be composed of 3 letters starting at AAA, the next AAB when the last letter hits Z then the next set would be ABA and so on. The way I am currently trying to make this work is to store each letter in a separate column in a table as a number i.e.
NPID1 = 1
NPID2 = 1
NPID3 = 1

Then in a query add 64 to each value, convert to a character, and concatenate them. I then need to pull them into the form for the user to view and combine with other data to create records. My plan after the combination has been used is through a series of if statements to increment the appropriate column so that it is ready for the next set.

Can someone help me figure out how to get the set of characters from the query that is not linked to the form?

Or suggest a better way to obtain this set of letters?

Thank you.
Gregg

 

[FoFa]

OK, why not take the max of the column, take the last character, and add 1 to it, if it exceeds "Z" then set it to A and add 1 to the second character, check it for exceeding "Z" etc. Use the asc and chr functions.

ChkStr = MAX(MyKey) ' Lets say it is AAZ
MyStr=right(ChkStr,1)
MyStr = chr(asc(MyStr)+1)
If MyStr > "Z" than
.
.
.

Basically like that
You could even create a function to return the new value to you that can use. If taking the MAX doesn't work, store the last used key value in a table and pull it from there.
Just my 2 cents

 

[gdomino]

Thanks it helped me sort things out.