Nest Sequential Number (1 Viewer)

C

checkbock

Guest
Got a Statement table
Field
Statementno companyname companystatementnos

The companystatementnos should be sequential according to the company name
eg
statementno companyname companystatementnos
1 a 1
2 b 1
3 a 2
4 a 3

Suppose you need somekind of procedure.

Any idea.
 

Travis

Registered User.
Local time
Today, 00:20
Joined
Dec 17, 1999
Messages
1,332
Here's an idea:

Create a Function like this

Public Function GetCompanyStatementNos(byVal stCompanyName as String) as Long
Dim rst as Recordset
Set rst = currentdb.OpenRecordset("Select Top 1 companystatementnos From tblStatements Where companyname=" & stCompanyName & " Order by companystatementnos Desc;")
GetCompanyStatementNos=rst.Fields("companystatementnos" + 1
Set rst = Nothing
End Function

On your form set AfterUpdate property of CompanyName to this:

Private companyname_AfterUpdate ()
Me.Companystatementnos = GetCompanyStatementNos(Me.Companyname)
end sub

This way everytime you enter the company it will retrieve the last value and add 1 to it and also add it to you new record.
 

Users who are viewing this thread

Top Bottom