Jonas,
What are you going to do with the data once you get it?

Kevin

dartcoach

what do you mean by which 1 is on both?

and the data is going on a report which displays the current status of a project. the current status is the other field in the sub form, and it corresponds with the date it was entered(which is why i need the latest date, or the "current status".

the reason i need to keep the old status changes is because the user will need to be able to continually update the Project status, and still be able to view past status changes and what days the were changed.

does that make sense? i can send the doc on monday if u would like...but thats basically the jist of the query i am making

Jonas,
What I meant is, which field is on both tables? I've got some code that should work for you, but need to know what the actual table names are and the field names you need for your report are.

Kevin

dartcoach

K, here are the names of all tables, forms and relevant fields.
the fields that will be on the report are:

1. "Project Name"(from the table "Project Management")
2. "Assigned To"(from the table "Project Management")
3. "Target Date"(from the table "Project Management")
4. "Status"( from the table "Status Updates Sub")...this will be the last updtated status.


The relevant fields for the query are:

Main form = Status>>>Table = Project Management...Relevant Field = Project Name
Sub form = Status Updates Subform>>>Table = Status Updates Sub...Relevent Field = "Date"

Jonas,

Put a button on a form, then put this code behind it:

   Dim rs As Recordset
   Dim rs1 As Recordset
   Dim db As Database

   Set db = CurrentDb
   Set rs = db.OpenRecordset("Select * from [Project Management]")
   With rs
      .MoveFirst
      Do While Not .EOF
          varName = ![Project Name]
          varSQL = "Select * from Sub where [Project Name] = " & """" & varName & """" & ";"
          Set rs1 = db.OpenRecordset(varSQL)
          With rs1
             If Not .EOF Then
                If Not .BOF Then
                   .MoveLast
                    MsgBox rs![Project Name] & " " & rs1!Date
                End If
             End If
          End With
          rs1.Close
          Set rs1 = Nothing
          .MoveNext
      Loop
    End With

Play with that and let me know if it helps.

Kevin

dartcoach

eh kevin, used the code on a button and it gave the message:
 "run-time error 3078: the microsoft jet database engine cannot find the input table or query 'sub'. make sure it exists and its name is spelled correctly. then the code highlights this:

"Set rs1 = db.OpenRecordset(varSQL)"

I changed 'sub' to read in the 'Status Updates Sub' which is the sub table. however that produces a msg saying invalid 'FROM' clause, so i dont think thats it.

Jonas,

Make sure that your put Status Updates Sub in brackets. it should look like:
[Status Updates Sub]

Kevin

dartcoach

Jonas,

Did that work for you?

Kevin

dartcoach

well i used the button, but it didnt do anything :S i am still having the same problems as before. when i click the button nothing happens, is there suppose to be some event i am missing?

however, i have tried something else out which seems to be a little more promising. i have used a sub datasheet in the query and that has allowed me to pull the one record from the main form, and all its corresponding sub records.

the problem i am having now is i again cant filter the subdatasheet to find only the most recent date for each project...

any thoughts?

Jonas,

Is the code not giving you a message box for each? I didn't add anything else to the code other than just retrieving the records.

Kevin

dartcoach