Get Serial Number

[Loek]

Dear all,

I was created a form with two tables and I want get a serial number for each my collections.

Table1 say as My Category
Table2 say as My Collection

In the table1 I have :
Id | Category
-----------------
01 | Computer
02 | Religious

And in the table 2 I have :
Category | SerialNumber | Collection
------------------------------------------- -
01 | 0100001 | A
01 | 0100002 | B
02 | 0200003 | AA
02 | 0200004 | BB
01 | 0100005 | C

In the table 2 above, I want get a serial number like this :
Category | SerialNumber | Collection
------------------------------------------- -
01 | 0100001 | A
01 | 0100002 | B
02 | 0200001 | AA
02 | 0200002 | BB
01 | 0100003 | C

So that for each collection get generate number from category, not all category.
[FONT=&quot]I try this one :
[/FONT]

 Public Function GetSerialNum()
  Dim strCategory As String, strSerialNum As String
  Dim strLastSerialNum As String, strNextSerialNum As String
   
  strCategory = Me![cbo_categori]  [COLOR=SeaGreen]‘combo box in a form for category[/COLOR]
  strSerialNum = DLast(“[SerialNumber]”, “Table2”)  [COLOR=SeaGreen]‘serial number in a field name in the table2[/COLOR]
  strLastSerialNum = Right(strSerialNum, 5) [COLOR=SeaGreen]‘ five digit number to generate[/COLOR]
  strNextSerialNum = Format(Val(strLastSerialNum) +1, “00000”)
   
  GetSerialNum = strCategory & strNextSerialNum
  End Function

Anyone help me….