datediff() using fields from different records

[tdb]

Is there a way to pull the data in the "FROM" clause from the subform (instead of a table)? Here's what I have tried to do with the code you provided, but when I click on the button to run the function, getting a "run-time error" 3131 that the FROM statement is incorrect:
.........
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordse t("SELECT * FROM Forms!sessionfo rm.subformdate. Form! ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordse t("SELECT * FROM Forms!sessionfo rm.subformdate. Form! ORDER BY LogoffhostDate" )
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul

[MMcCarthy]

Is there a way to pull the data in the "FROM" clause from the subform (instead of a table)? Here's what I have tried to do with the code you provided, but when I click on the button to run the function, getting a "run-time error" 3131 that the FROM statement is incorrect:
.........
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordse t("SELECT * FROM Forms!sessionfo rm.subformdate. Form! ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordse t("SELECT * FROM Forms!sessionfo rm.subformdate. Form! ORDER BY LogoffhostDate" )
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul

Ok that won't work. However, it can be done under certain circumstances. Is the record source of subformdate a table?

[tdb]

The record source is a query - This is the way I am trying to get the date to run the function/module and get the result:

I made a form and put a combo box on it - the box is linked to a query that lets me select a user, then this is linked to a subform to populate the subform with the ID, and date fields. The subform is based on a query also, so that I could use an ordered list instead of the original table.

I hope this makes sense, maybe the info needed to make the function work could be achieved another way, but I need to be able to select a user and get the results in the right order so the function and vb code will work.

Thanks for your time...

Ok that won't work. However, it can be done under certain circumstances. Is the record source of subformdate a table?

[MMcCarthy]

OK, try this:

Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordse t("SELECT * FROM " & Forms![sessionform]![subformdate].Form.RecordSou rce & " ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordse t("SELECT * FROM " & Forms![sessionform]![subformdate].Form.RecordSou rce & " ORDER BY LogoffhostDate" )
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul

[tdb]

Thank you so much for your quick reply......
I am getting runtime error 2455 - "you entered an expression that has an invalid reference to the property RecordSource" - all of these fields are in the table and query that are part of this equation.

[tdb]

The subform does not show the [User] field since that is what is used to link the combo box and the subform. Could this be causing a problem? If so, is there a way to show the field that is used to link the subform?

[MMcCarthy]

OK, try this:


Dim strRS As String
Dim strSQL As String

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

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

[tdb]

Same error, your code looks like it should work...I am wondering if there is anyway it is related to the subform not having the User field to work on - is there a way I can send you and image of the form?

[MMcCarthy]

Same error, your code looks like it should work...I am wondering if there is anyway it is related to the subform not having the User field to work on - is there a way I can send you and image of the form?

Sorry I just realised what you've been saying.

Yes the subform would need to have the field that is being used by the combobox.

[tdb]

Thank you so much for all your help - Is there a way to get the linked field to show up?

[tdb]

I added another field to the record source of the combo box and the record source of the subform - they are duplicate USER fields and automatically got the name Expr1000. I then reference these in the module in place of "User" but still get the Run-time error 2455. At this point it seems like I have an inherent design flaw perhaps that is preventing this report from succeeding. Any additional ideas greatly appreciated.

[MMcCarthy]

I added another field to the record source of the combo box and the record source of the subform - they are duplicate USER fields and automatically got the name Expr1000. I then reference these in the module in place of "User" but still get the Run-time error 2455. At this point it seems like I have an inherent design flaw perhaps that is preventing this report from succeeding. Any additional ideas greatly appreciated.

OK

The rules

A form and subform are linked in one of two ways.

Using a child/master relationship. This means that the field on which they are linked (usually the primary key on the main form) is present in the recordsource and in a control on the main form and also (usually a foreign key on the subform) is present in the recordsource and in a control on the subform.

OR

Using a filter on the subform. Turning it on and off and changing its value based on certain criteria on the main form. This is a little more complicated but can be done in VBA code.

Which way is most appropriate for you?

[tdb]

I have the combo box and the subform linked by child and master fields. So this way would probably be best at this point.

[PEB]

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?

Is there a way to pull the data in the "FROM" clause from the subform (instead of a table)? Here's what I have tried to do with the code you provided, but when I click on the button to run the function, getting a "run-time error" 3131 that the FROM statement is incorrect:
.........
Dim strSQL As String
Dim slogoff As Date
Set db = CurrentDb
Set rs1 = db.OpenRecordse t("SELECT * FROM Forms!sessionfo rm.subformdate. Form! ORDER BY LogonhostDate")
Set rs2 = db.OpenRecordse t("SELECT * FROM Forms!sessionfo rm.subformdate. Form! ORDER BY LogoffhostDate" )
rs1.MoveFirst
Do Until rs1.EOF
If Not IsNul

[MMcCarthy]

I have the combo box and the subform linked by child and master fields. So this way would probably be best at this point.

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.