help updating concatenated "ID" field

[lscheer]

All,

I have borrowed this code from other messages on this forum, and it works, sort of.

Me.TrgCode = DMax("val(TrgCode)", "qryTrainingsForm", [ProgramCode] = Forms!frmTrainings!ProgramCode) + 1

What I need to do is tack a number onto a text value into a field that is later used to uniquely identify (to the average user--yes, there is actually a different PK field) a training course. The field consists of a Program Code and the number (chronological) of the course (e.g. AQ125 is the 125th course the air quality program has offered). I'd like this value to automatically increment by one for each individual program when users are entering data.

When I run the code, it just puts a number in the field and ignores the program code (in my example, "AQ")

Part of the problem might be that different users enter data for different programs, and I had the form's query set to a parameter (enter your program code), but when I had it set up that way, I get an automation error.

I need the code to somehow be able to recognize (or at least ask for) the program code (that is already entered into another field on the form) and then add the new number to it.

Any help would be greatly appreciated.

 

[KenHigg]

Me.TrgCode = Forms!frmTrainings!ProgramCode & DMax("val(TrgCode)", "qryTrainingsForm", [ProgramCode] = Forms!frmTrainings!ProgramCode) + 1

???

However, If you do not have this in production yet I may suggest you split the two elements out and leave just the numeric pc in the trgCode fld.

Also, be aware that likening the trgCode fld as an actual number ('the 125th class) may be erroneous for use in calculations as records/classes may be deleted, canceled, etc. If you need this type granularity maybe it would be best to use another methodology. Just a thought...

kh

 

[lscheer]

This syntax gives me a Run-time error (2001) that says "you canceled the previous operation"

There really is no better way (for my purposes) that I have figured out to achieve what I need, so I'd really like to get the syntax for this down.