2 different recordsource for the same form

-----------------------------------------------------------------------

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 [tblSessionId].[SessionId]" & _
"FROM [tblSessionId]" & _
"WHERE ((([tblSessionId].[SessionId]) Is Null));"
Me.RecordSource = RecSrcFrmSessIdOpnSql

'***************
'The new session id is pasted here into the database
Dim txtsessYrString As String, txtSessNoStrg As String
If Forms!frmSessionID.txtCountREcord.Value = "0" Then
Me!txtSessionIdno.Value = Year(Date) & "-" & "00001"
Else
txtsessYrString = Year(Date)
txtSessNoStrg = Format((Val(DCount("*", "tblSessionId")) + Val("1")), "00000")
Me!txtSessionIdno.Value = txtsessYrString & "-" & txtSessNoStrg
End If
DoCmd.RunCommand acCmdSaveRecord
DoCmd.Requery
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
Dim RecSrcFrmSessIdLoadSql As String

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

End Function

-----------------------------------------------------------------------

Private Sub cmdViewLastSessID_Click()
'here the form will show the last id pasted
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

End Sub
 

-----------------------------------------------------------------------