Update concat field on loop in form - With Resolution

[yellowfin7]

Good day,

I am currently running Windows 7 and Access 2007. I have created a form with a handfull of user input fields the will generate a new record in my table. My issue is that I am trying to implement an internal case number that is based on:

Code: Select all

 INT_CASE_NUM = "T" & DatePart("yyyy", [PT_DT_ADDED]) & [ID]

The [PT_DT_ADDED] field is locked and has a default value of =date(), the [ID] field is an auto number and currently a primary key.

I was hoping that I would have some luck here in finding the proper way to have the INT_CASE_NUM field that is also locked auto populate once the end user begins a new record. This field will become my primary key if I can get it to work.

As always I appreciate any help that you can provide.

yellowfin7

[HansV]

I'd keep ID as primary key and calculate INT_CASE_NUM in a query based on the table:

INT_CASE_NUM: "T" & Year([PT_DT_ADDED]) & [ID]

(INT_CASE_NUM should not be a field in the table since it is derived information)

[yellowfin7]

thank you for quick response. I guess I did not explain what I need to do with the int case num field, and that is it will be associated with other tables within subforms to link everything together.

That being said I would like it to become my primary key. once a new record has been started I do not want the field to change ever.

I do understand the concept of not storing derived information in a table when it has to do with calculations of numerical data but in this case there is just a concat fuction happening and the fields involved are static once a record is populated.

Is there a way achive my function without setting INT_CASE_NUM = "T" & DatePart("yyyy", [PT_DT_ADDED]) & [ID] as an on update fuction for every control.

Again thank you very much for the assistance.

[HansV]

Sorry to be obstinate, but an AutoNumber field is the perfect candidate for a primary key, for linking tables together and for linking subforms and subreports to their parent form/report. An AutoNumber field is guaranteed to be unique and unchangeable, it is available from the moment the user starts a new record and Access does all the maintenance for you.

Anyway, you could use the Before Update event of the form:

Code: Select all

Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Me.NewRecord Then
        Me.INT_CASE_NUM = "T" & Year([PT_DT_ADDED]) & [ID]
    End If
End Sub

but that means that the value won't be calculated until the new record is saved, either explicitly or because the user moves to a different record or closes the form.

[yellowfin7]

Thank you Hans, again exceptional assistance and haste.