Need Equal Length Columns - HELP !!! (1 Viewer)
- Thread starter dVESTERN
- Start date
Hi Isladogs,
Corrected all the Compile Errors. Primarily all were instances of where I had copied a form and modified it for another purpose. There were apx. 5-6 instances where an On_Click command was trying to set an undefined control field.
Cleared all errors, however, the original problem still exists.
Dave S.
Corrected all the Compile Errors. Primarily all were instances of where I had copied a form and modified it for another purpose. There were apx. 5-6 instances where an On_Click command was trying to set an undefined control field.
Cleared all errors, however, the original problem still exists.
Dave S.
isladogs
MVP / VIP
- Local time
- Today, 21:14
- Joined
- Jan 14, 2017
- Messages
- 18,219
Check whether Require Variable Declaration is ticked in the VBE Options.
Then make sure you have Option Explicit as the second line is every code module.
Then compile again.
No promises it will help but should be done anyway.
If you use macros and/or embedded macros, they may need checking but I can't advise as I don't use them
If still stuck try uploading your dB but as I use Win10 I may not get your issue.
Then make sure you have Option Explicit as the second line is every code module.
Then compile again.
No promises it will help but should be done anyway.
If you use macros and/or embedded macros, they may need checking but I can't advise as I don't use them
If still stuck try uploading your dB but as I use Win10 I may not get your issue.
Isladogs,
1) Set the Require Variable Declaration Option
2) Added Option Explicit to each module
No Change . . .
Have attached a Zip'd copy of the database. Set a breakpoint where the code fails in Windows 7.
After all recommended changes, code still runs perfectly on W10 but fails on W7.
Any thoughts?
Dave S.
1) Set the Require Variable Declaration Option
2) Added Option Explicit to each module
No Change . . .
Have attached a Zip'd copy of the database. Set a breakpoint where the code fails in Windows 7.
After all recommended changes, code still runs perfectly on W10 but fails on W7.
Any thoughts?
Dave S.
isladogs
MVP / VIP
- Local time
- Today, 21:14
- Joined
- Jan 14, 2017
- Messages
- 18,219
Very odd.
The report runs perfectly on my Windows 10 tablet (A2010)
But on my desktop PC. I got the same error 3197 as you but in Windows 10 as well as Windows 7 on a VM
And its getting worse...
On my desktop, I've also had errors 3251, 3040 (Disk I/O error during read) and error 3020
Opening the report after bypassing the recordset loop works fine though the layout obviously then isn't what you want.
I'll try & look into the flawed recordset code later.
As I believe JHB wrote most of this, he may see the problem faster than me.
Also happy if anyone else wishes to take a look in the meantime
The report runs perfectly on my Windows 10 tablet (A2010)
But on my desktop PC. I got the same error 3197 as you but in Windows 10 as well as Windows 7 on a VM
And its getting worse...
On my desktop, I've also had errors 3251, 3040 (Disk I/O error during read) and error 3020
Opening the report after bypassing the recordset loop works fine though the layout obviously then isn't what you want.
I'll try & look into the flawed recordset code later.
As I believe JHB wrote most of this, he may see the problem faster than me.
Also happy if anyone else wishes to take a look in the meantime
ISLADOGS,
I, too, began to see other errors like you describe after adding the "Option Explicit" to each module. They were not consistant, rather hit and miss.
Dave S.
P.S.
HAVE KEPT A COPY OF THE DATABASE BEFORE MAKING ANY COMPILE CORRECTIONS OR ADDING THE EXPLICIT OPTION. WILL TRY TO SEE IF THERE IS ANY DIFFERENCE TOMORROW. OFF THEN.
I, too, began to see other errors like you describe after adding the "Option Explicit" to each module. They were not consistant, rather hit and miss.
Dave S.
P.S.
HAVE KEPT A COPY OF THE DATABASE BEFORE MAKING ANY COMPILE CORRECTIONS OR ADDING THE EXPLICIT OPTION. WILL TRY TO SEE IF THERE IS ANY DIFFERENCE TOMORROW. OFF THEN.
Last edited:
isladogs
MVP / VIP
- Local time
- Today, 21:14
- Joined
- Jan 14, 2017
- Messages
- 18,219
Yup. Same here. But it always works on my tablet which is odd.
BTW there are still two compilation error on another form
You have an issue both with setting warnings incorrectly and doing so twice on the badly named form Add/UpdateAttendanceByDate.
BTW there are still two compilation error on another form
You have an issue both with setting warnings incorrectly and doing so twice on the badly named form Add/UpdateAttendanceByDate.
isladogs
MVP / VIP
- Local time
- Today, 21:14
- Joined
- Jan 14, 2017
- Messages
- 18,219
Well that was an absolute pain in the ....
For a long time, I was fairly convinced this was all due to corruption.
However, after a lot of trial & error with different code, I changed the recordset code from OpenDynaset to OpenTable - this fixed the errors! No idea why as I've NEVER had to do that before
I then modified the recordset code to allow for unequal column lengths as that didn't seem correct previously
e.g. 61 records = 21.20,20 ; 62 records =21,21,20, 63 records = 21,21,21
Previously the report wouldn't run on certain dates such as 21/03/2019 though data existed. It now works for all dates with data
However the code is less 'neat' than previously.
It should be possible to tidy it up with more effort but I've already spent too long on it
I've replaced the code where you deleted & made the table repeatedly.
Instead the table is emptied & repopulated each time
This should make the database more stable ...but the file size will still increase each time you run your report. You will need to compact occasionally
I also added error handling to that procedure
Tested the report in both Win10 & Win7 - no further issues in either OS as far as I can tell
There are lots of issues elsewhere in the database but I'll leave those to you to fix. Good luck!
For a long time, I was fairly convinced this was all due to corruption.
However, after a lot of trial & error with different code, I changed the recordset code from OpenDynaset to OpenTable - this fixed the errors! No idea why as I've NEVER had to do that before
I then modified the recordset code to allow for unequal column lengths as that didn't seem correct previously
e.g. 61 records = 21.20,20 ; 62 records =21,21,20, 63 records = 21,21,21
Previously the report wouldn't run on certain dates such as 21/03/2019 though data existed. It now works for all dates with data
However the code is less 'neat' than previously.
It should be possible to tidy it up with more effort but I've already spent too long on it
I've replaced the code where you deleted & made the table repeatedly.
Instead the table is emptied & repopulated each time
This should make the database more stable ...but the file size will still increase each time you run your report. You will need to compact occasionally
I also added error handling to that procedure
Tested the report in both Win10 & Win7 - no further issues in either OS as far as I can tell
There are lots of issues elsewhere in the database but I'll leave those to you to fix. Good luck!
isladogs
MVP / VIP
- Local time
- Today, 21:14
- Joined
- Jan 14, 2017
- Messages
- 18,219
Phew! That's a relief.
I don't understand why the recordset errored or why the change to dbOpenTable fixed them though TBH I haven't researched what that does.
You should be able to work out how it splits the 3 columns correctly by testing for values like 61, 62, 63 and stepping through the code.
I left an aggregate query- query6 - if you want to do some testing.
As I said there's lots you should deal with. Lots of similarly named tables and queries. Some may be duplicates. Also duplicated and superfluous code for switching warnings on/off ...etc.
I don't understand why the recordset errored or why the change to dbOpenTable fixed them though TBH I haven't researched what that does.
You should be able to work out how it splits the 3 columns correctly by testing for values like 61, 62, 63 and stepping through the code.
I left an aggregate query- query6 - if you want to do some testing.
As I said there's lots you should deal with. Lots of similarly named tables and queries. Some may be duplicates. Also duplicated and superfluous code for switching warnings on/off ...etc.
