Field Validation

[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. 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

 

[79ssecca]

Based on the unique client identifier create a recordset that goes something like this...

function myfun(client_No as int)
Dim rs as recordset
dim strSQL as string

strSQL = "Select top 1 t.sesionNo" _
& " From tbl_mytable t" _
& " Where Unique_Client = {currently viewed client number}" _
& " Order By t.sessionNo desc"

set rs = currentdb.openrecordset("strSQL")

'Now verify that you found a record valid client
if rs.eof and rs.bof then
me!sessionno = 1
ELSE
me!sessionno = rs.sessionno.value + 1
end if

End Function
Further more I would consider placing this code on the click event of an "ADD" button or when the form is open in the add record mode. That will help eleiminate the posibility of a session number being entered and not being used. Also validate that information was entered that is aceptable on the closing of the form or on moving to a new record so that you can make corrections as nessasary.

Good Luck
79ssecca

 

[bogglebeats]

what i currently have is a listbox that counselors can choose their session number from. the session number is part of a table called counseling session and each client (which is from a table called clients) is connected to counseling session table via client id.

i copied and pasted your code into the visual basic code segment that i got directed to when clicking on the session number listbox's before update property. and have something like this:

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

am i renaming the functions/files correctly? i am still getting errors when i use this. please take a look for me. i really don't know much about this. thanks.

ameen

 

[WayneRyan]

ameen,

This is an ongoing discussion here:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40609

Wayne