[SOLVED] How to create a custom autonumber in ms access form using VBA?

How to create a custom autonumber in ms access form using VBA?

I have a primary key field named "Code" in my Products table. I also have a form for that table. I need to auto generate the "Code" in the form like GK01, GK02 and so on. I have tried the Dmax function but it didn't work maybe because the "Code" field is in Text (got runtime error 94: Invalid use of Null).

I have also tried the standard autonumber in access through formatting (field properties format option) the autonumber like Gk'00'. It works but this field is related to other tables where "Code" is in number format. So it clashes with the relationship.

Now I want to use VBA to create autonumber in the format mentioned above in the form. Please Help...

Solution

Hurray... I have got a solution. I created an autonumber field named ID in my Products table. Then in the forms products name field I added a afterupdate event procedure like this:

Private Sub ProductName_AfterUpdate()
    Me.Code.Value = "GK" & Me.ID.Value
End Sub

It is working perfectly. Thanks to everyone who tried to solve it.