ID Code

[MrM121]

Hi everyone,

I was wondering if someone can offer advice on creating an ID code. The idea I want is to have a code along the lines of (for example) THCS1234. Basically, this can be broken down into 3 parts - the first 2 letters are in relation to the results of one combobox, and the second 2 letters are the result of another combobox. Ok, so this part is easy enough to do.

The next thing I want to do is to have a number generated at the end of the combination of the letters. I would like to be able to search through all records which begin their ID with THCS (still using this example), and then to parse the data so that I can then find the max number, and then simply add 1 for the new code. I hope I have explained that well. Does anyone have any suggestions?

Thank you

 

[Crowley16]

I have done something very similar...

however I've used some code so if you're happy with doing that then here goes:

'sql to find the relevant ids and the number parts of those ids
dim sql as string
sql = "SELECT id, right(id, len(id) - 4) AS idNum FROM tbl WHERE left(id, 4) = 'THCS' ORDER BY right(id, len(id) - 4);"

'find the largest number
dim rs as dao.recordset
set rs = currentdb.openrecordset(sqlstr)
rs.movelast
nextid = blah & blah & (rs.fields("idNum") + 1)

...

you'll probably have to rewrite some bits cos this is just off the top of my head...

 

[MrM121]

Cheers for that. I will give it a crack now and see how I get on with it.

 

[MrM121]

Ok, just tried it - thanks so much for that.