Field Validation Using Visual Basic Even Procedure

[bogglebeats]

I am trying to validate a field located on one of my subforms using the Beforeupdate property with Visual Basic. The field is called session number and it represents the current session number of each client. Because the field is based on a couple of important queries, i need to validate it as such that duplicate session numbers cannot be entered. For instance if session number 3 is taken, session number 1, 2, or 3 cannot be entered. Ideally, i would like each session number to be automatically assigned to each client session. I don't think autonumber would work because it would keep going on a linear basis for all clients, when i need it to go on a linear basis for EACH client. I would need the session number to start at 1 and increase each time a new session is added. I could toy with the beforeupdate property and validate based on what each counselor would enter for the counseling session, but i would rather have the session number assigned to each session as information is entered. What do you suggest i do? thanks.

ameen

i have been kindly given the following code, but it does not seem to work because i don't know how to make it work. what would i need to rename to make it compile?

Private Sub Session_Number_Listbox_BeforeUpdate(Cancel As Integer)
Function myfun(Client_ID As Integer)
Dim rs As Recordset
Dim strSQL As String

strSQL = "Select top 1 t.Session_Number" _
& " From tbl_counseling_session t" _
& " Where Client_ID = {currently viewed client number}" _
& " Order By t.Session Number desc"

Set rs = Clients.openrecordset("strSQL")

'Now verify that you found a record valid client
If rs.EOF And rs.BOF Then
Me!Session_Number = 1
Else
Me!Session_Number = rs.Session_Number.Value + 1
End If

End Function
End Sub