Wrox Programmer Forums
|
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old June 8th, 2007, 01:37 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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

Kevin

dartcoach
 
Old June 8th, 2007, 02:09 PM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old June 8th, 2007, 02:16 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 8th, 2007, 02:24 PM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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"


 
Old June 8th, 2007, 02:45 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
 
Old June 8th, 2007, 03:08 PM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.


 
Old June 8th, 2007, 03:12 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jonas,

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

Kevin

dartcoach
 
Old June 11th, 2007, 09:29 AM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jonas,

Did that work for you?

Kevin

dartcoach
 
Old June 11th, 2007, 11:44 AM
Authorized User
 
Join Date: Jul 2006
Posts: 26
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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?

 
Old June 11th, 2007, 12:00 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
Query Problem. rupen Access 3 April 27th, 2007 07:43 AM
Query Problem bundersuk VB Databases Basics 0 December 30th, 2006 07:50 AM
problem with query harpua Classic ASP Databases 1 January 24th, 2005 12:36 PM
Problem in query leo_vinay Classic ASP Databases 5 January 21st, 2005 06:32 AM
query problem mateenmohd SQL Server 2000 7 September 9th, 2003 11:58 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.