Results 1 to 4 of 4
  1. #1
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    problem changing code (Access97)

    I copied a table and report from a database that allows you to show a timeline in the report. It worked fine in my database after replacing the existing data with my own.

    My problem occurs when I tried to rename the table and fields. I renamed Projects to tblProjects and for fields Start Date to StartDate (also End Date to EndDate). The report will no longer open. It asks if I want to debug, then highlights

    the line that starts with Set rs =

    Private Sub Report_Open(Cancel As Integer)
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT Min([Start Date]) AS MinOfStartDate " _
    & " FROM Projects", dbOpenSnapshot)
    If rs.RecordCount > 0 Then
    mdatEarliest = rs!MinOfStartDate
    End If
    Set rs = db.OpenRecordset("SELECT Max(IIf(IsDate([End Date]),CDate([End Date]),Null)) " _
    & "AS MaxOfEndDate FROM Projects", dbOpenSnapshot)
    If rs.RecordCount > 0 Then
    mdatLatest = rs!MaxOfEndDate
    End If

    I tried changing the Table and Field names in the code, but cannot get it to accept the changes. Can anyone suggest what I am doing wrong?

    Thanks <img src=/S/help.gif border=0 alt=help width=23 height=15>

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: problem changing code (Access97)

    Hi,
    Are you changing the code from within debug mode? (i.e. while the error line is still highlighted) If so, make sure you click on the Save button before you close the VBEditor otherwise your changes will be lost. Alternatively, open the report in design view, go into the OnOpen event on the report properties tab and edit the code directly from there.
    Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: problem changing code (Access97)

    >>Set rs = db.OpenRecordset("SELECT Min([Start Date]) AS MinOfStartDate " _
    & " FROM Projects", dbOpenSnapshot)<<


    You say you changed the name of your Projects table to tblProjects? Yet look at the code above. It still is looking at "Projects".

    Merely changing a table name doesn't automatically change the name everywhere in your code; as a matter of fact, it doesn't change it anywhere! And have you opened a query yet that was based on Projects? You will be in for a nasty surprise.

    Go to <A target="_blank" HREF=http://www.speedferret.com>http://www.speedferret.com</A> for a 3rd party global find and replace utility.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Posts
    163
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: problem changing code (Access97)

    Thanks Rory, I think it must have been the saving that was causing my problem. It's working now. I made the changes, then saved, but when I tried to close it again got the message box about "This action will reset the current code in break mode." That window does not allow me to choose NO. Before whenever I chose Yes, then rechecked the code it was back at the original. This time with saving before getting that message seemed to do the trick.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •