Generating Autonumbering

Option Compare Database
Function fctGenerateSessId() As Integer
'***************
'opening the form with a query that takes isnull as criteris
'this query will open the sessionid table
Dim RecSrcFrmSessIdOpnSql As String

RecSrcFrmSessIdOpnSql = "SELECT [BackGrdTblSessionId].[SessionId]" & _
"FROM [BackGrdTblSessionId]" & _
"WHERE ((([BackGrdTblSessionId].[SessionId]) Is Null));"
Me.RecordSource = RecSrcFrmSessIdOpnSql

'***************
'The new session id is pasted here into the database
Dim txtsessYrString As String, txtSessNoStrg As String, strSessionIdtopaste As String
If Forms!BackGrdFrmSessionID.txtCountREcord.Value = "0" Then
Me!txtSessionIdno.Value = Year(Date) & "-CI-" & "001"
strSessionIdtopaste = Year(Date) & "-CI-" & "001"
Else
txtsessYrString = Year(Date)
txtSessNoStrg = Format((Val(DCount("*", "BackGrdTblSessionId")) + Val("1")), "000")
Me!txtSessionIdno.Value = txtsessYrString & "-CI-" & txtSessNoStrg
strSessionIdtopaste = txtsessYrString & "-CI-" & txtSessNoStrg
End If
DoCmd.RunCommand acCmdSaveRecord
End Function
Function fctviewresultSessId() As Integer
'the query opens the session id table
'only the highest value entered in the sessionid table is shown here
'this value will be used for the future operations
'Load BackGrdFrmSessionID
Dim RecSrcFrmSessIdLoadSql As String

RecSrcFrmSessIdLoadSql = "SELECT TOP 1 [BackGrdTblSessionId].[SessionId]" & _
"FROM [BackGrdTblSessionId]" & _
"ORDER BY [BackGrdTblSessionId].[SessionId] DESC;"
Forms!BackGrdFrmSessionID.RecordSource = RecSrcFrmSessIdLoadSql

End Function
Private Sub cmdGenerateSessID_Click()
'cancelled
'here the form will show the last id pasted
DoCmd.Requery
fctviewresultSessId
'DoCmd.Requery

End Sub


Private Sub Form_Open(Cancel As Integer)
' Before the form is previwed the new session id to be used is pastecd
'into the session table here
fctGenerateSessId
Forms!BackGrdFrmSessionID.Visible = False
DoCmd.OpenForm "BackGrdFrmDocumentID"
DoCmd.OpenForm "frm001DocumentCheckIn"
End Sub