View report selector error? (1 Viewer)

[tonycicc20]

When i click on view reports it should pop up with a prompt for me to select the correct report page.. instead it does this 'There was an error executing the command.' anyone know/want to fix this for me? $

 

[Ranman256]

is there code in the report? (there shouldn't, put the code in the form to tell how to open the report)
are there functions in the query? (could be a missing reference in VBE)

we'd really have to see the example to debug.

 

[tonycicc20]

is there code in the report? (there shouldn't, put the code in the form to tell how to open the report)
are there functions in the query? (could be a missing reference in VBE)

we'd really have to see the example to debug.

Heres the DB. you would go to switch board then 'view reports' and click any report type they all have the same error....

 

[MajP]

You need to get rid of the line after opening the report.

' Open a report.
        Case conCmdOpenReport
            DoCmd.OpenReport rst![argument], acPreview
            'delete next line. It does nothing
            'Reports(rst![argument]).setfocus
        ' Customize the Switchboard.

 

[tonycicc20]

You need to get rid of the line after opening the report.

' Open a report.
        Case conCmdOpenReport
            DoCmd.OpenReport rst![argument], acPreview
            'delete next line. It does nothing
            'Reports(rst![argument]).setfocus
        ' Customize the Switchboard.

So i dont get the error anymore but when i click view the report it should pop up a box to select the specific table in that report and it is not, its going to the default 1H report...?

 

[MajP]

So i dont get the error anymore but when i click view the report it should pop up a box to select the specific table in that report and it is not, its going to the default 1H report...

I do not know what any of that means. There is no code in any report that would pop open anything. There is no report called 1H. You will need to describe this better. Also your front end appears corrupt. You have modules listed that do not exist. I would create a new front end and import your objects into it. Also consider a decompile. This is just normal house cleaning to keep it all working clean.

 

[tonycicc20]

Sorry 1H is this cable which is the first one.. i should be able to search the report by cable# and i cant...

 

[MajP]

Currently the reports open in print preview so there is not interactive capability. You would need to open in report view to use any built in search features.
DoCmd.OpenReport rst![argument], acViewReport

Not sure if you really use this database, and if it is working for you. If so great, but man this is a big mess. You have 217 tables that mostly look the same. I think you could wipe out 90% of the tables, forms, and reports if this was better designed. That would make it far simpler simpler. The database seems corrupt. The backend will not compact (no surprise), and the Front End is showing some very strange behavior. If this is something you plan to continue to use and develop, I would seriously consider refactoring the whole database. This does not seem like it would be maintainable at this point. If you have the interest there are plenty here that can assist in rebuilding.

 

[tonycicc20]

Currently the reports open in print preview so there is not interactive capability. You would need to open in report view to use any built in search features.
DoCmd.OpenReport rst![argument], acViewReport

Not sure if you really use this database, and if it is working for you. If so great, but man this is a big mess. You have 217 tables that mostly look the same. I think you could wipe out 90% of the tables, forms, and reports if this was better designed. That would make it far simpler simpler. The database seems corrupt. The backend will not compact (no surprise), and the Front End is showing some very strange behavior. If this is something you plan to continue to use and develop, I would seriously consider refactoring the whole database. This does not seem like it would be maintainable at this point. If you have the interest there are plenty here that can assist in rebuilding.

This is exactly what i would like to do.. the database has been converted through 3 versions of access.. i inherited it from someone that retired last year and am supposed to maintain it.. i have no problem paying someone to make it work properly as i have very little experience with Access... If you or someone you can recommend to do it i would be very appreciative.

 

[MajP]

It looks to me that at one time this was pretty well designed, and then people started to add on top of it without cleaning anything out. It is hard to tell what is useable and what was either a copy or temporary thing. I do not think really there is that much here, it does not look like the database does that much. I think the trick is figuring out what is really used and what is just left over. Do you understand the business process? Can you look at the tables and know what the data represents, how it is gathered and entered, and where it is used?. That would not solve all the problems but would allow for a massive cleanup, by knowing what needs to stay and what goes.
If I wanted to do this right, I would have to start from scratch. Redesign all the tables and import old data into the new table design. Once the backbone is done everything else would be simple.
But I think in the short term you could do a lot of cleaning up with the ability in the future to then do this correct restructuring.
Do you understand why there are so many tables for Cables but it appears only Cable10E is used?

 

[tonycicc20]

It looks to me that at one time this was pretty well designed, and then people started to add on top of it without cleaning anything out. It is hard to tell what is useable and what was either a copy or temporary thing. I do not think really there is that much here, it does not look like the database does that much. I think the trick is figuring out what is really used and what is just left over. Do you understand the business process? Can you look at the tables and know what the data represents, how it is gathered and entered, and where it is used?. That would not solve all the problems but would allow for a massive cleanup, by knowing what needs to stay and what goes.
If I wanted to do this right, I would have to start from scratch. Redesign all the tables and import old data into the new table design. Once the backbone is done everything else would be simple.
But I think in the short term you could do a lot of cleaning up with the ability in the future to then do this correct restructuring.
Do you understand why there are so many tables for Cables but it appears only Cable10E is used?

That's the problem, i have no idea, other than knowing what the database is supposed to do... i have a guide i can email you for reference

 

[MajP]

So I have gone through the database, and I see only a fraction of the tables are used. The biggest question I have is there are a ton of Cable tables
like Cable10E or Cable1H. These tables are all the same as far as I can tell.

And only 10E or cable1h are tied to any forms or reports as far as I can tell.
This seems crazy, but it is as if the developer did not know how to make queries so they made separate tables. Then when they wanted to work on a current cable they redesigned all of the forms and reports to point at a specific table. Do you work on one Cable and then archive that data once complete? Or do you need to get to any cable for its data? Currently it looks as if you work on one Cable and then when complete you archive it.

Normally (and I mean everyone) would have a single Cables table with all data for all your cables. Yes it would be long, but not a problem. Then you can add, edit, and report on any Cable. I think that is what this needs.

For the report you show it should be grouped by CableNo. I would likely have a form on the front end to allow you to pick one or more cables to show in the report. As far as I can tell, the way it is designed now is that the whole database can only work on or report on 1 cable no at a time without someone going in there to redesign the forms and reports. By redesign I mean change in design view the data source.

 

[arnelgp]

inherited it from someone that retired last year and am supposed to maintain it.

 

[tonycicc20]

So I have gone through the database, and I see only a fraction of the tables are used. The biggest question I have is there are a ton of Cable tables
like Cable10E or Cable1H. These tables are all the same as far as I can tell.
View attachment 92225
And only 10E or cable1h are tied to any forms or reports as far as I can tell.
This seems crazy, but it is as if the developer did not know how to make queries so they made separate tables. Then when they wanted to work on a current cable they redesigned all of the forms and reports to point at a specific table. Do you work on one Cable and then archive that data once complete? Or do you need to get to any cable for its data? Currently it looks as if you work on one Cable and then when complete you archive it.

Normally (and I mean everyone) would have a single Cables table with all data for all your cables. Yes it would be long, but not a problem. Then you can add, edit, and report on any Cable. I think that is what this needs.

For the report you show it should be grouped by CableNo. I would likely have a form on the front end to allow you to pick one or more cables to show in the report. As far as I can tell, the way it is designed now is that the whole database can only work on or report on 1 cable no at a time without someone going in there to redesign the forms and reports. By redesign I mean change in design view the data source.

only known back up i have the others were all corrupt. no idea what MDB is.. I can send it to you if you think it will help

 

[tonycicc20]

From Switchboard to Database editor - Locked - Edit Cable Segment Data there's the view report button... it does not work.. when it should this is the view report button that should post a popup so we can select the proper Cable for the report

 

[MajP]

It seems like this could be an old version of the database. There are several buttons like this that do not even have any code or a macro associated to it, but looks like they were meant to do things. I think I have made some good work cleaning this up. Then once cleaned up can add some features like this back in.

 

[tonycicc20]

https://www.access-programmers.co.uk/forums/attachments/db-zip.91363/ here is it before when the switchboard was inoperable maybe that has the code? i havent used the DB at all this week as i figured i would holdoff until i can get it somewhat fixed so we should be OK

 

[GinaWhipp]

So, I got a chance to review the file and ALL the code is gone, so I'm agreeing that this file appears to have suffered some corruption. Fixing a corrupted file is not really an option, a rewrite is, IMHO, the only only option.

 

[MajP]

So, I got a chance to review the file and ALL the code is gone, so I'm agreeing that this file appears to have suffered some corruption. Fixing a corrupted file is not really an option, a rewrite is, IMHO, the only only option

Having the code is by far the least of the problems. The capability that was there was extremely basic and could be replicated easily. I have been trying to understand the data, but without any relationships or queries it is pretty tough. I took a shot at that to see how far back you would have to peal the onion, but the amount of time would be significant. I focused on trying to fix the tables and get some of the key forms working.
The original developer did not seem to understand how to make queries, use comboxes, but had a small idea on normalization. There are foreign keys, but often the primary key is an auto iD that is not used anywhere. Fixing any code would be a bandaid on a gaping wound. Here is my shot at fixing some of the tables. The biggest was moving the hundreds of separate Cable tables into one table. However, the more I dug I see that there appears to be far more non normalized tables, and there needs to be more redesign.

 

[tonycicc20]

Having the code is by far the least of the problems. The capability that was there was extremely basic and could be replicated easily. I have been trying to understand the data, but without any relationships or queries it is pretty tough. I took a shot at that to see how far back you would have to peal the onion, but the amount of time would be significant. I focused on trying to fix the tables and get some of the key forms working.
The original developer did not seem to understand how to make queries, use comboxes, but had a small idea on normalization. There are foreign keys, but often the primary key is an auto iD that is not used anywhere. Fixing any code would be a bandaid on a gaping wound. Here is my shot at fixing some of the tables. The biggest was moving the hundreds of separate Cable tables into one table. However, the more I dug I see that there appears to be far more non normalized tables, and there needs to be more redesign.

Yeah i cant speak on the person that made it, it was probably 15-20 years ago.. i like what you did there already..