AutoPopulate a field & formatting a field question pls!

[tempestes]

Hello there!

I'm updating our db here for the next FY and we've incorporated a new naming system into our contracts.

For example:

Alysheba Incorporated submits a contract for a project called Kentucky Derby Win Planning. Internally, we will know it as:

ALYS001 - Kentucky Derby Win Planning

Now, since we track all our contract in this db, is it possible for Access to automatically know when to apply ALYS001, ALYS002 (for the next contract submitted) and so forth? Or is this something that will need to be tracked manually? So far, I've just changed the Contract ID # field from AutoNumber to Number.

Also, for the Contract ID # field, I'd like to make it mandatory to enter the data into that field in that format and nothing else.

So, would I put in the properties of Contract ID #
(AAAA111)? Or something....?

Thanks so much and I really hope this makes sense!!

Lynne

(Somehow I've become the Access SME here cuz now HR has asked me to re-build some of their db's too! Gebus! LOL!)

 

[VilaRestal]

I think you'd need to be doing that sort of thing as VBA code on the forms.

You'd have a table listing companies that submit contracts. A field in there shows their prefix to be used in their contracts' Contract IDs.
In a new contract, when the company is selected you'd have to open the contracts table as a recordset and loop through it noting down the numbers for all other contracts for that company (stripping the prefix from the Contract ID and converting to a number). When you've found the largest, add one to it, stick the prefix at the beginning et voila!

That's far more than can be done in the table design. In SQL Server you can create triggers that do that sort of thing but in Access its got to be VBA in the data entry form.

 

[tempestes]

I think you'd need to be doing that sort of thing as VBA code on the forms.

You'd have a table listing companies that submit contracts. A field in there shows their prefix to be used in their contracts' Contract IDs.
In a new contract, when the company is selected you'd have to open the contracts table as a recordset and loop through it noting down the numbers for all other contracts for that company (stripping the prefix from the Contract ID and converting to a number). When you've found the largest, add one to it, stick the prefix at the beginning et voila!

That's far more than can be done in the table design. In SQL Server you can create triggers that do that sort of thing but in Access its got to be VBA in the data entry form.

Hmmm...it sounds like I'm sort of there. I did create a new table to track and record each customer and its related shortform. But I have to admit, I'm a little lost when you start saying recordset and looping! Is it something I could learn so I could add this in or would it be easier to just do it manually? I'm also trying to think long term ... if we have to add new customers and shortforms I don't want to limit this method. Once in place, it would be nice if it worked and lasted unlike so many other things that need changing so shortly after implementation.

If manually, can you (or anyone) advise on how I can set the field I make to default to want this type of data entry:

ALSY001
ALSY002 and so forth...

Thanks a bunch!
Lynne