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 11th, 2007, 12:03 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jonas,
Also, please post the complete code behind the button so we can take a look at it.

Thanks,

Kevin

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

hey, here is the code attached to the button. does it look right?

Private Sub Command2_Click()
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 [Status Updates 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


End Sub


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

Jonas,
It looks right to me. Are the table names and field names correct?
Are you getting a message box with the project name and a date?

Kevin

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

i double checked the spelling of all the fields and tables. and no message box comes up, nothin happens when i click the button. is there any other reason this wouldnt work?

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

Jonas,

Does the [Project Management] table have any records, also, based on what you posted earlier, there should always be at least 1 record in the [Status Updates Sub] table - is this true?

Kevin

Kevin

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

kevin, i think i got it! finally it is doing what i want, and yes there is always a record in the subform.

oh theres just one more thing, is there anyway format the subform to pull all the records, as i need some data for calculations, but only show the top record? which in my case the most recent record. but that is just aesthetics, the more important aspects are done. just cant seem to make the report look exactly like the form..

thanks a bunch for all the help =)
 
Old June 11th, 2007, 03:11 PM
Friend of Wrox
 
Join Date: Jan 2005
Posts: 471
Thanks: 0
Thanked 1 Time in 1 Post
Default

Jonas,
Glad you've got it. In the code you're using, you can work thru the records of rs1.

          With rs1
             If Not .EOF Then
                If Not .BOF Then
                   .MoveLast
                    MsgBox rs![Project Name] & " " & rs1!Date
                   .movefirst
                   do while not .eof
                      """ Calculations here """
                      .movenext
                   loop
                End If
             End If
          End With
          rs1.Close
          Set rs1 = Nothing


Hth,

Kevin

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

Jonas,
Did you get the loop processing working?

Kevin

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

hey Kevin,

sorry for the late response, got tied up in meetings. I did not try the second code, as I have everything working as it should. but thanks all the same!

Jonas






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.