Beginner question regarding generating fields

[Willump]

Hi, Please forgive me as I am new to these forums and access. I am trying to generate an ID field within a form. The format of the ID is 01TomSmi (ID number one higher than the last, followed by the first 3 letters the letter of the first name and the first 3 letters of the last name). I want the ID to automatically generate in my form. I have used the following code. =Left([Firstname],3) & Left([Lastname],3) & Max(Left([IDnumber],2)+1), although I am unsure where to put it. If I put the code in the control source, I get an error saying the primary key is null, although I can see it has generated correctly. If i put it in the default value it does not update when I change the name in the form.



Any help would be appreciated, thanks.

[pbaldy]

It would be handled like a calculated field:

http://allenbrowne.com/casu-14.html

[Willump]

The field I am trying to calculate is a primary key, and access wont allow me to change it to a calculated field. There is already values in the field, I just want it to work out a new ID when submitting a form. I'm not sure if this should be done in the form, the table or a query .

[pbaldy]

Sorry, I meant the second section, "you want to store a calculated result anyway".

[Willump]

I think the formula I used is correct, I just don't know what to do with it. Do I need to create a new calculated field and then change the ID to this field? If I do this then the Primary key is temporarily null and so I can't save any records. Sorry if I'm not making myself clear. I think it would be easier to use a default value, but then the name part of the ID does not work as there is no name when I first open the form, and it does not update.

[pbaldy]

No, you'd use code from the link like:

Private Sub Quantity_AfterUpdate()   
  Me.Fee = Round(Me.Quantity * Me.UnitPrice * 0.1, 2) 
End Sub

But using your formula. In other words, you're placing your value into a bound textbox using code rather than having the formula as the control source.