How to build a User Friendly Field

[nushi]

I know this thread is pretty old, but I am still a bit unclear as to the solution proposed by Marc2012. I am creating a "user friendly" ID for assignments, involving a calculated field (concatenated Client initials, actual serial, and then added the assignment code and serial). I need for this ID to be saved, and never recalculated again, since this will also be assigned to a hard copy file for filing. And yes, in future this particular number might be recalculated if the person editing changed the client's name, which cannot be permitted since the label will be added to a physical file.

So my issue, I can do all the calculations, but, when the record is being created, I would like to be able to get the field for the ID to be filled automatically. Any suggestions? Additional note, I am also trying to restrict the whole database to Macro's only, since I am not versed in VBA yet.

Thanks in advance!

[zmbd]

[[{split from old thread http://bytes.com/topic/access/answer...ld#post3789696 ]]

Nushi:
As Neopa Mentioned, we do ask that new questions be posted in new threads; however, as I've done above, you can link to the old thread for any context.

Macros... well... you will not get a lot of immediate support here using macros as most of us (including myself) moved away from them in favor of VBA a decade ago :-) due to various limitations of the macro script. But I'll give it a shot if you will be patient with me as I have to really dig back deep into the ole long-term memory to do this. ;-)

However, if you are building a sharepoint database then macros are really the only way to go - which is monkychunks - anyway, if not SP then I would advise learning VBA instead (not that hard really I promise) but I digress.

Normally what you are asking is not done as a stored field; however, within the workflow you are describing this sounds like one of the exceptions.

I need to know two things:
Version of Office/Access?
Are you working within a form or directly within the tables?

[nushi]

zmbd,

Hi, thanks for starting this new thread and taking the time to look into my issue! I am sorry I did not realise my question was significantly different to warrant a new thread, as I thought the problem was pretty much the same (although granted for a different reason) and did not want to duplicate.

With regards to your questions:
1/ MS Access 2010
2/ I am working directly in a form. A slight background, the database is a customer / assignment management system for a very small law office. The particular form I am working in is a tabbed form to collect information with regards to an assignment and create a new entry for tblAssignments, and two junction tables for assignment-clients and assignment-opponents (if any). As you understood from my previous post, for filing purposes they do need some way to identify the assignment both in the system and the hard copy file. As a further explanation, in order to make it more user friendly and filing friendly, I have decided to use Alpha Numeric IDs, based on client names and the normal serial number in the table. I am currently dealing with people who are using an excel spreadsheet and still missing files, so I need some more intuitive than just numbers.

Just as a note, I did create a followup to my post (not sure where it went...). I was in fact able to figure out a way to make it work as follows:

At the end of the form, there is a "save" button, which saves the record to the tblAssignment, and opens and saves the relevant junction tables, through the use of some temporary variables and the "SetPropert y" macro. All I did was add an extra line, prior to saving the tblAssignment record, I call the SetProperty macro:

Control Name: AssignID (a field within the Assignment table, this is just a text field)
Property: Value
Value: my calculation for the AssigID (based on client ID, assignment type and serial).

This is then followed by the save record macro commend, allowing the record to be written to the assignment table (with the newly populated AssignID field), and then the junction table entries are created. A little long winded I guess, but so far my testing is showing that it is working perfectly. I am concerned that this particular form is a bit bulky and has too many controls, but so far I see no other way around it and in general that is a topic for another thread I guess.

Thanks again for taking the time to look at this, if you have any comments you think may be helpful, please do let me know.