datediff() using fields from different records

[tdb]

I did what you suggest - test the code on a simple query without the combobox filtering for user - still getting the same runtime error 2455 - you entered an expression that has an invalid reference to the property RecordSource. When I debug, this line is highlighted -

strRS = Forms!sessionfo rm!subformdate. Form.RecordSour ce

I tried putting the brackets around sessionform, subformdate, separate, simultaneously, still getting the same error. I tried using ! in various positions, same error.

Yes, the code works on a table, but my info is in a Form - not sure what you mean by simply assign your result query in your subform

Thank you for trying to help...

Hi tdb,

Before trying o vizualize your data using subform, it seems logical that we do a test of the code and all using a simple query?

If it works than we should assign this query as a record source of a subform and the problem will be finished Am i right?

So it will be better to assign the table with sessions as recordsource as it was by default described by mmcarthy, if there is problems in execution of this query tell us!

If not simply assign your result query in your subform and there isn't need of any comboboxes, users and so on... I think the link of your user Id with your subform data is a subject of a new thread! Do you agree with me?

[MMcCarthy]

subformdate may be the name of the subform but not necessarily the name of the subform object.

On the main form in design view click on the frame around the subform and open the properties. Under the other tab check what the value is in the Name property. Is it subformdate or something else?

The other point is whether the sessionform form is actually open when this code is executing.

If both of these are OK, try changing your code to

strRS = Forms!sessionfo rm!subformdate. Form.RecordsetC lone

and see if this will work.

If none of the above solve your problem let me know.

I did what you suggest - test the code on a simple query without the combobox filtering for user - still getting the same runtime error 2455 - you entered an expression that has an invalid reference to the property RecordSource. When I debug, this line is highlighted -

strRS = Forms!sessionfo rm!subformdate. Form.RecordSour ce

I tried putting the brackets around sessionform, subformdate, separate, simultaneously, still getting the same error. I tried using ! in various positions, same error.

Yes, the code works on a table, but my info is in a Form - not sure what you mean by simply assign your result query in your subform

Thank you for trying to help...

[tdb]

OK can you post the full sql for the Record Source of both tables and tell me what you are using for the Master/Child relationship link.

I do not see where there is sql for a table, but this is what I have for the queries -

SQL for username query that is used in the combobox:
SELECT DISTINCT Computerinfo.Us er
FROM Computerinfo
WHERE (((Computerinfo .User) Is Not Null));

SQL for datebyuser query that is used in the subform:
SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

I am using the field [User] for the CHILD LINK
and the field [Users] for the MASTER LINK (Users is the name of the combo box in the form, that way the name that is active in box is the filter for what populates the subform)

[tdb]

I checked the name and it is subformdate.
Yes, the form is open when executing.
I changed the code to RecordsetClone - now get "run-time error '31' - Type mismatch" and on debug the VB highlights same line. What a bugger!

subformdate may be the name of the subform but not necessarily the name of the subform object.

On the main form in design view click on the frame around the subform and open the properties. Under the other tab check what the value is in the Name property. Is it subformdate or something else?

The other point is whether the sessionform form is actually open when this code is executing.

If both of these are OK, try changing your code to

strRS = Forms!sessionfo rm!subformdate. Form.RecordsetC lone

and see if this will work.

If none of the above solve your problem let me know.

[MMcCarthy]

I do not see where there is sql for a table, but this is what I have for the queries -

SQL for username query that is used in the combobox:
SELECT DISTINCT Computerinfo.Us er
FROM Computerinfo
WHERE (((Computerinfo .User) Is Not Null));

SQL for datebyuser query that is used in the subform:
SELECT [Computerinfo].[User], [Computerinfo].[ID], [Computerinfo].[LogonhostDate], [Computerinfo].[LogoffhostDate]
FROM Computerinfo
ORDER BY [Computerinfo].[User], [Computerinfo].[ID];

I am using the field [User] for the CHILD LINK
and the field [Users] for the MASTER LINK (Users is the name of the combo box in the form, that way the name that is active in box is the filter for what populates the subform)

OK this doesn't work. It only works if the Main form is also bound to a table.

Remove the values in the master child boxes and set the following in the filter instead.

[User]=[Forms]![sessionform]![Users]

Does this help?

[tdb]

I can set the filter for the form, it didn't work. Do not see how to set a filter for a subform in the properties toolbox.

OK this doesn't work. It only works if the Main form is also bound to a table.

Remove the values in the master child boxes and set the following in the filter instead.

[User]=[Forms]![sessionform]![Users]

Does this help?

[MMcCarthy]

I can set the filter for the form, it didn't work. Do not see how to set a filter for a subform in the properties toolbox.

You have to set the filter on the subform itself not the frame in the main form. Close the main form. Open the subform separately in design view and you can set it there. Then close the subform and open the main form again.

[MMcCarthy]

BTW I'm sorry I explained it badly in the first place. The filter only goes on the subform. Not on the main form.

[tdb]

BTW I'm sorry I explained it badly in the first place. The filter only goes on the subform. Not on the main form.

I am using Access 2000 and I do not see any option to open only the subform. I tried double clicking on it. Sorry for yhe hassle, I really appreciate you trying to help me out.

[MMcCarthy]

I am using Access 2000 and I do not see any option to open only the subform. I tried double clicking on it. Sorry for yhe hassle, I really appreciate you trying to help me out.

Check your list of forms. When you create a subform, even using the wizard it should be saved on its own as a form separately on the list.

[tdb]

Check your list of forms. When you create a subform, even using the wizard it should be saved on its own as a form separately on the list.

Ok, I figured out how to set the filter, still getting error "type mismatch" - Could it be that we are using "str" and the values are both alpha and numerical. Is there another way to define the variable?

Dim strRS As String
....
strRS = Forms![sessionform]![subform].Form.Recordset Clone
...

[MMcCarthy]

Ok, I figured out how to set the filter, still getting error "type mismatch" - Could it be that we are using "str" and the values are both alpha and numerical. Is there another way to define the variable?

Dim strRS As String
....
strRS = Forms![sessionform]![subform].Form.Recordset Clone
...

Sorry I made an assumption you had declared a recordset. I should have noticed the Set command was missing.

[code]

Dim strRS As Recordset

Set strRS = Forms![sessionform]![subform].Form.Recordset Clone

/code]

[tdb]

Sorry I made an assumption you had declared a recordset. I should have noticed the Set command was missing.

[code]

Dim strRS As Recordset

Set strRS = Forms![sessionform]![subform].Form.Recordset Clone

/code]

This seems to be working - but now gettin another error at

Set rs1 = db.OpenRecordse t(strSQL)

"type mismatch" at the ".OpenRecordset "
I tried OpenDynaset, didn't work.
I changed the strSQL to be like the strRS.

[MMcCarthy]

This seems to be working - but now gettin another error at

Set rs1 = db.OpenRecordse t(strSQL)

"type mismatch" at the ".OpenRecordset "
I tried OpenDynaset, didn't work.
I changed the strSQL to be like the strRS.

OK

The code is all mixed up. You shouldn't have that line at all.

Can you post the code IN FULL

Mary

[tdb]

Private Function buildSessions()

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

Set strRS = Forms![sessionform]![subform].Form.Recordset Clone
Set strSQL = Left(strRS, "Len(strRS) " - 2) & " ORDER BY LogonhostDate"
Set rs1 = db.OpenRecordse t(strSQL)

Set strSQL = Left(strRS, "Len(strRS) " - 2) & " ORDER BY LogoffhostDate"
Set rs2 = db.OpenRecordse t(strSQL)

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

rs1.Close
rs2.Close
Set rs1 = Nothing
Set rs2 = Nothing
Set db = Nothing
End Function