datediff() using fields from different records

[MMcCarthy]

I just need a couple more things.

What is the record source of the form subform. Is it a table, saved query or select statement. If it's a saved query or select statement I'll need the full sql.

The problem is you can't use recordsetclone the way you're using it. If you give me the above info I'll be able to work out the correct approach.

Mary

[tdb]

I just need a couple more things.

What is the record source of the form subform. Is it a table, saved query or select statement. If it's a saved query or select statement I'll need the full sql.

The problem is you can't use recordsetclone the way you're using it. If you give me the above info I'll be able to work out the correct approach.

Mary

It is a saved query - here it is.....thankyou , hope this helps.

SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

[MMcCarthy]

SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

This statement already has an Order By you can't impose another one in the code. Therefore you cannot use RecordsetClone.

I 've changed the code to reflect this and to correct errors in the recordset assignment.

 
Private Function buildSessions()
 
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strRS As String
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
 
  strRS = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo ORDER BY [Computerinfo].[LogonhostDate];"
  Set rs1 = db.OpenRecordset(strRS)
 
  strSQL = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo ORDER BY [Computerinfo].[LogoffhostDate];"
  Set rs2 = db.OpenRecordset(strSQL)
 
  rs1.MoveFirst
  Do Until rs1.EOF
	If Not IsNull(rs1!LogonhostDate) Then
	  rs2.MoveFirst
	  Do Until rs2.EOF
		If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
		  slogoff = rs2!LogoffhostDate
		  rs2.MoveLast
		End If
		If Not rs2.EOF Then
		  rs2.MoveNext
		End If
	  Loop
	  DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
		  "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
	End If
	rs1.MoveNext
  Loop
 
  rs1.Close
  rs2.Close
  Set rs1 = Nothing
  Set rs2 = Nothing
  Set db = Nothing
 
End Function

[tdb]

Mary - you did it! Kudos! Thank you sooo much for all your help! This is great. I will study what you have done here to learn more about sql.

SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

This statement already has an Order By you can't impose another one in the code. Therefore you cannot use RecordsetClone.

I 've changed the code to reflect this and to correct errors in the recordset assignment.

 
Private Function buildSessions()
 
Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strRS As String
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
 
  strRS = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo ORDER BY [Computerinfo].[LogonhostDate];"
  Set rs1 = db.OpenRecordset(strRS)
 
  strSQL = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo ORDER BY [Computerinfo].[LogoffhostDate];"
  Set rs2 = db.OpenRecordset(strSQL)
 
  rs1.MoveFirst
  Do Until rs1.EOF
	If Not IsNull(rs1!LogonhostDate) Then
	  rs2.MoveFirst
	  Do Until rs2.EOF
		If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
		  slogoff = rs2!LogoffhostDate
		  rs2.MoveLast
		End If
		If Not rs2.EOF Then
		  rs2.MoveNext
		End If
	  Loop
	  DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
		  "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
	End If
	rs1.MoveNext
  Loop
 
  rs1.Close
  rs2.Close
  Set rs1 = Nothing
  Set rs2 = Nothing
  Set db = Nothing
 
End Function

[MMcCarthy]

Mary - you did it! Kudos! Thank you sooo much for all your help! This is great. I will study what you have done here to learn more about sql.

Great, I'm glad it's working.

If you've any questions on what I've done or why just let me know.

Mary

[tdb]

Is there a way to automatically update the table when the DoCmd.RunSQL part of the function runs?

[NeoPa]

Is there a way to automatically update the table when the DoCmd.RunSQL part of the function runs?

I'm not sure what you're asking, as DoCmd.RunSQL does update the table.

[tdb]

I'm not sure what you're asking, as DoCmd.RunSQL does update the table.

When I run the build sessions function, for each record the user has to click OK on a popup window to append the table. This is for over 1000 records.

Is there a way to skip this step and just let the function run?

[MMcCarthy]

When I run the build sessions function, for each record the user has to click OK on a popup window to append the table. This is for over 1000 records.

Is there a way to skip this step and just let the function run?

You just need to add a line at the beginning of the code to turn the warnings off and a line at the end to turn them back on as follows:

 
Private Function buildSessions()

Dim db As Database
Dim rs1 As DAO.Recordset
Dim rs2 As DAO.Recordset
Dim strRS As String
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb

  DoCmd.SetWarnings False 

  strRS = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo ORDER BY [Computerinfo].[LogonhostDate];"
  Set rs1 = db.OpenRecordset(strRS)

  strSQL = "SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo ORDER BY [Computerinfo].[LogoffhostDate];"
  Set rs2 = db.OpenRecordset(strSQL)

  rs1.MoveFirst
  Do Until rs1.EOF
If Not IsNull(rs1!LogonhostDate) Then
  rs2.MoveFirst
  Do Until rs2.EOF
If rs2!User = rs1!User And rs2!LogoffhostDate > rs1!LogonhostDate Then
  slogoff = rs2!LogoffhostDate
  rs2.MoveLast
End If
If Not rs2.EOF Then
  rs2.MoveNext
End If
  Loop
  DoCmd.RunSQL "INSERT INTO tblSessions (user, logonTime, logoffTime) " & _
  "VALUES ('" & rs1!User & "',#" & rs1!LogonhostDate & "#,#" & slogoff & "#);"
End If
rs1.MoveNext
  Loop

  rs1.Close
  rs2.Close
  Set rs1 = Nothing
  Set rs2 = Nothing
  Set db = Nothing
  
  DoCmd.SetWarnings True
 
End Function

[tdb]

Works great! This forum is a valuable resource. Thank you all for your help!

[tdb]

Something I just noticed is that the table appends to the end every time I run the function so that there are duplicate sets of sessions - is there a way to first remove all the records in the table, then build it again when the function is run?

[NeoPa]

Use it all the time :

DELETE *
FROM [YourTable]

Often with a WHERE clause too.

[MMcCarthy]

Use it all the time :

DELETE *
FROM [YourTable]

Often with a WHERE clause too.

As this is an action query it can be run with DoCmd.RunSQL

[tdb]

As this is an action query it can be run with DoCmd.RunSQL

I have tried putting this into the function in various places, tried changing the * to the [table].[field] for all fields, and tried putting it in another DoCmd.RunSQL statement. Nothing seems to work. Is there a specific place to put this statement in a function?

[MMcCarthy]

I have tried putting this into the function in various places, tried changing the * to the [table].[field] for all fields, and tried putting it in another DoCmd.RunSQL statement. Nothing seems to work. Is there a specific place to put this statement in a function?

Ok, just under DoCmd.SetWarnin gs False

DoCmd.RunSQL "DELETE * FROM tblSessions;"