calculating incremental value contingent on other field

[JCA]

I'm using =DMax("PartcipantID","Participant")+1 in the default value of
ParticipantID so that new records are numbered sequentially based on the
previous highest value. How can I make this contingent on the contents of a
different field?

My 'Cohort' field contains either HG or CG. I'd like to generate records so
that e.g ParticipantID where Cohort = HG starts at 001 and increments using
DMax as above, but ParticipantID where Cohort = CG starts at 201.

Also - how do I concatenate field contents (e.g to create a single field
with CG201 based on ParticipantID and Cohort).

Thanks for any help.

 

[Dale Fye]

What happens when cohort "HG" gets more than 200 users?

Best way would be to determin the ParticipantID after the cohort is
determined. You could do this in the AfterUpdate event of your Cohort
control, using code similar to:

ParticipantID = NZ(DMAX("ParticipantID", "Participant", "Cohort = '" &
me.txt_Cohort & "'"), 0) + 1

The problem with doing it during the AfterUpdate event of the control is
that if another user, on another computer creates a new record, before yours
gets written to the table, you will end up with duplicate ParticipantID
values. A better way to do this, if feasible, is in the Forms BeforeUpdate
event. That way, it will get generated and written to the table almost
immediately.

A third way is to actually have a table containing the NextID value for each
category you might have. So the table might include fields of Category,
CatValue, and NextID. Where Category is "Cohort" (for this example),
CatValue would then be either HG or CG, and NextID would be the next ID value
for each Category/CatValue combination. If the only table you are generating
numbers for in this manner is this one, then you probably won't need the
Category field. Then you could write a function which would get that value
and increment it. Something like:

Public Function fnNextID(Category as String, CatValue as String) as Long

Dim strSQL as String
Dim rs as DAO.Recordset

strSQL = "SELECT NextID FROM yourTable " _
& "Where Category = '" & Category & "' " _
& "AND CatValue = '" & CatValue & "'"
setRS = currentdb.OpenRecordset strsql

fnNextID = rs.NextID
rs.Edit
rs("NextID") = rs("NextID") + 1
rs.Update
rs.close
set rs = nothing

End Function