Auto generation of sl no

[mavtcr]

Friends,I have made a programme where master datas are entered and updated in an Access database. In the database there are several Branches with three digit codes.Under each Branch comes several families with a unique number.I want to generate this uniqe number automatically.. To explain further BranchA with code 100,BranchB with code 200 The families coming under BranchA will have number like 1001,1002,1003 and so on.The familis coming under BranchB will have numbers like 2001,2002,2003 and so on I have written some codes It works But the problem is all the families are serially numbered ie When I enter a family under BrancgB afer entering one under BranchA The Number is generated as 2002 instead of 2001 Can any body help me
Inline Code Example Here
Private Sub CmdSubmit_Click()
Dim FSN As Single
Set FRS = New ADODB.Recordset
FRS.ActiveConnection = conn
FRS.CursorLocation = adUseClient
FRS.CursorType = adOpenDynamic
FRS.LockType = adLockOptimistic
FRS.Source = "SELECT * FROM Family"
FRS.Open
FSN = (FRS.RecordCount)
If FSN = 0 Then
FSN = 1
Else
FSN = (FSN + 1)
End If
FRS.AddNew
FRS("Fno") = BRN & FSN
FRS("Fname") = TxtFname.Text
FRS("Brname") = CmbBranch.Text
FRS.Update
TxtFno = BRN & FSN
FRS.Requery
ClearAll
CmdSubmit.Enabled = False
TxtMode.SetFocus
End Sub

Here the BRN is the Branch code

[gusano79]

Without details, I can't give you a specific answer, but this is where to fix your immediate problem:

FRS.Source = "SELECT * FROM Family"

You could add a WHERE condition to this query to only count entries for a specific branch.

This is a little fragile, though. You might instead select the highest branch number in a particular branch and add one to that instead of relying on the numbers to match the record count.

[mavtcr]

I have tried the WHERE but failed
Can you give me the code for the second method

[rishif2]

would you put here your table structure with some data in it ?
then we will try to solve the issue .

[gusano79]

would you put here your table structure with some data in it

^^ This. We can't help you if we don't know what we're working with.

[mavtcr]

I give below the table structure with data. Table name = Family
Fno=1011000-101 is the branch code,1000 Family sl no In my code the variable BRN
Fname=Mynatty
Faddress=Nellankara,Thrissur
Brno=101
Branch=Mannuthy
If I add one more family under this branch I should get Family sl no 1001
and If I add another family under a new branch with brno=102 I should get the Fno=1021000 Instead I am getting 1021003

[mavtcr]

Friends I got it myself.I inserted this just before FSN=(FRS.RecordCount)

    If FRS.State = adStateOpen Then FRS.Close
    `FRS.Open "SELECT * FROM Family WHERE Brname = '" & CmbBranch & "'", conn, adOpenStatic, adLockOptimistic     `

Now I am getting continuous sl no under a branch,and another continuous sl no for a different Branch

Thanks

[mavtcr]

Question solved by myself