problem changing code (Access97)

Topic

New Reply

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

Reply | Quote

Replies

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?

Reply | Quote

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.

Reply | Quote

>>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 http://www.speedferret.com for a 3rd party global find and replace utility.

Reply | Quote