Help needed to Generate Next Number (1 Viewer)

[shery1995]

Hi Everyone, I have two tables, Client and Matters. Based on those two table I have two Forms Client and Matters. There is a button on client form when I clik this button it open up the matter form with ClientID. What I want to achieve is when it open matter form like it copies clientID at the same time it should automatically generate next matterID for the same client. I mean one client can have many matters if matter for the same client eist should generate the next matterID for the same client. If clientId is change should start the sequence from number one again. Please answer with example. thanks in advance.

 

[Khalid_Afridi]

Hi Shery!
Its not easy to generate automatically the next matter number for the specific client.
you have to do some programmatically.

considers these steps:
Create a client ID in your client table
create a client Mater ID in your matter table (this should be a text field)
combined the client ID and mater id in the mater ID i.e (client1-1, client1-2,....)

to get these you have to open a recordset for specific client, then count the records for that client and increment +1 to it and save the generated Id to the client mater id.
(a little tricky huh.???? )

you will get the following ids
client1-1
client1-2
client1-3
.........

 

[shery1995]

Thank you very much K Afridi for your quick response. Can you give some example. thanks

 

[Khalid_Afridi]

you are welcome shery!

here are some coding in my database where I can get the next Work-Orders number for a specific Contract No.

        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Dim strSQL As String
        
        Set db = CurrentDb
        strSQL = "SELECT count(tblSOF.ContractNo) as TotRec FROM tblSOF WHERE (tblSOF.ContractNo)= '" & Me.cboContractNo & "'"
        
        Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
        

            Me!txtWONo = "WO-" & rs!TotRec + 1
            Me!WONo = rs!TotRec + 1
             
       Set rs = Nothing

 

[highandwild]

Use DMax("[ContractNo]","tblSOF","")+1 to get the next number
and set it up as the default value for the ContractNo text box.

 

[pbaldy]

In response to the email from A.M. regarding why this would return a type mismatch error:

strSQL = "SELECT max(MatterID) + 1 from tblClientMatter WHERE ClientID = '" & Me.ClientID & "'"

The most likely reason would be ClientID being a numeric field, in which case the code would need to be (note I've also aliased the field so it can be referred to):

strSQL = "SELECT max(MatterID) + 1 AS NextNumber from tblClientMatter WHERE ClientID = " & Me.ClientID

 

[shery1995]

still unable to achieve the required result. Can someone check the attached db file and advise where I'm wrong.

 

[shery1995]

My apology, I missed to attached the file.

 

[Khalid_Afridi]

My apology, I missed to attached the file.

Shery!

your tables structures for Client and Matters are not fulfilling the required concept of Database Foreign and Primary keys
and you should be also aware of the Relational Database Normalization.

In your table tblClientMater the ClientID should be a foreign key to accept multiple entries of ClientID.

Why not you use the subform on the same main form which will record the ClientMater on one screen instead of a button to open another form?