Slew of bizzare bugs...

[Kaisha]

I come from a C++/asm background and have written a number of programs with the Win32 API (ie. I'm quite familiar with Windows programming, just having issues with VBA's/Access's 'quirks') and have been trying to help a friend 'fix' (ie. re-write from practically scratch) their access database. Its mostly done but I've come across a number of very strange bugs. I've spent 10-12 hours over the last 2-3 days scouring forums and everything google could find and have come up with absolutely nothing of use. I just thought I'd throw these out there in case anyone had any insight.



I tried to design a form that could be used both as a standard form, and as a pop-up window using DoCmd.OpenForm and acWindowNormal/acDialog. The acWindowNormalmethod seems to work but the 'acDialog' is all sorts of quirky. Initially the form when opened with 'acDialog' would show up in strange locations around the screen, often on a completely different monitor or under the windows task bar. Even when moved to a proper location the form would return to its bizarre initial position upon re-opening. Even when closed with DoCmd.CloseForm with acSaveYes the position was not saved and upon next opening it would be back hiding under a taskbar or on another monitor.

The next idea was to use the Win32 API (as suggested on some forums) to reposition the dialog on load. Being familiar ground I thought this was a wonderful idea. Alas to no avail. The code did work, and the window was moved to the proper location but now the Access program itself caused a weird problem. Simply adding the following Win32 API function definitions to a code module:

Private Declare PtrSafe Function apiGetClientRect Lib "user32" Alias "GetClientRect" (ByVal hwnd As Long, lpRect As RECT) As Long
Private Declare PtrSafe Function apiGetWindowRect Lib "user32" Alias "GetWindowRect" (ByVal hwnd As Long, lpRect As RECT) As Long
Private Declare PtrSafe Function apiSetWindowPos Lib "user32" Alias "SetWindowPos" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, ByVal x As Long, ByVal y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long
Private Declare PtrSafe Function apiShowWindow Lib "user32" Alias "ShowWindow" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

caused access to hang. What would happen is after a code change (ie. as I was debugging) and after switching from design to form view both the access window and the vba editor would hang with the access status bar stuck on 'calculating...'. This wouldn't happen every time though, only once every 3-5 attempts. The time hanging would vary from 2-20min. It would also cause Internet Explorer to run slowly, and the task manager showed one of the 4 processors working at max. If I were to hazard an uneducated guess it seemed very similar to a mutex bug in the Win32 API I came across years ago... But the MS devs would surely be aware of a bug in their own software right?? I can't be the only one who's tried to run Win32 functions from within VBA? So in the end even though the functions technically worked (when access would stop hanging and run the forms did open in the proper locations), I couldn't work with it as the constant hanging made editing difficult, also I worry that the same issues might affect the DB after deployment.

My next attempt was to abandon the idea of having forms in both as a normal report and a dialog, and just use them as dialogs exclusively. Now I had tested them extensively as normal forms, and they worked fine. But code that ran completely without issue previously would crash if either the 'Pop Up' or 'Modal' properties were set to true. I had code set to run on form unload (checks if changes are made, asks if the user wants to save, pretty standard stuff). When 'Pop Up' and 'Modal' are set to false it runs fine. If either 'Pop Up' or 'Modal' are set to true, the code will crash when switching from form to design view. Now it doesn't crash when closing the window outright. And of course I can go from closed to design view fine. But for some reason, despite the exact same code being run in the same way, switching from form view to design view causes errors, while closing the form does not. And of course this only occurs when 'Pop Up' or 'Modal' are set to true. Even when opening the form with a DoCmd.OpenForm with acDialog, but 'Pop Up' and 'Modal' set to false, it will STILL run fine. It seems to only occur when opened from the editor.

While playing around I also found a few other things that have left me baffled. The 'Auto Center' property has no effect when the form is opened from DoCmd.OpenForm. No matter what size I set my form in the properties menu or design editor, when opened as a pop-up it will not be the proper size. The 'Auto Resize' property never works, constantly making the dialog far too big and leaving bands on the bottom. The 'Border Style' property has no effect that I can tell.

On top of all this I've had a particularly aggravating issue with errors and OnUnload events. It seems that if an error occurs when the form is running, access or vba likes to simply lose all its global variables (occasionally warning me that the project will be reset, but not always). Which means the OnUnload event (which uses these globals) of course would error out. This would leave me unable to close or unload a form, seemingly permanently stuck in form view, requiring me to comment out code just to get back to design view to re-enable the code to try another fix. It got particularly annoying when this occurred with a form in dialog mode, as there was no way to close it short of using the task manager to kill the process.

No one who was worked with C++ is unfamiliar with odd errors and hard to track bugs. But nothing I've seen has prepared me for the absolutely mindboggling screwyness of access/vba. I actually started to miss template metaprogramming errors at one point.

The big problem here is that no one on the various forums I've seen seems to have any clue. Over 90% of the solutions suggested have just flat out not worked. Apparently somewhere between VB 6 and VB.net, VBA got lost. I taught myself the D3D9 and D3D11 APIs almost entirely from scouring the MSDN reference's, so I'm not unfamiliar with the format, but the VB references are in many cases incorrect, and the rest of the time have so little to as to be completely useless.

At this point I'm at a loss at where to go next...

[RuralGuy]

I'm afraid it sounds like Windows 8 issues. Do you have a Win7 machine you can try it on or maybe post enough of the db to demonstrate the issue that one of us can try on a different machine?

[Xipooo]

Have you performed a decompile/recompile on your database?

[Kaisha]

The Windows 7 machine we were originally using has died, which is why we switched to the Windows 8 machine in the intern. I'll have the it back up and running in a few days but until then the Windows 8 machine is the only one that has Office 2013. And no, I don't even know how to decompile/recompile an access database. When I get the Windows 7 machine working, and if I'm still having the issues, I'll trim the database down and post a copy for you guys to play with.

[RuralGuy]

Meanwhile, this link will help here: http://www.granite.ab.ca/access/decompile.htm

[Xipooo]

I suggest you do the decompile/recompile method that RuralGuy points to. Many "bugs" can be solved by doing this.

[Kaisha]

I did both a compact/repair and a decompile on the database (as far as I can tell both do the same thing?). I then did a debug -> compile and it found 2 small typo errors. I fixed those easily. The size of the database was reduced to 1/3 which was nice. Nothing else was fixed though.

[Xipooo]

They don't do the same thing and I really want to make sure you were able to do what exactly it is we were suggesting.

Open Access with the /decompile command. Then open your database. Then open your VBA window and run a "Compile".

A compact and repair work on the tables mostly.... decompile/compile are working with the VBA code.

[Kaisha]

I did a compact/repair, saved and closed. I then opened the file in access with /decompile as the link said. I then ran a compile in VBA.

[Kaisha]

Ok well its mostly done. But the problems still persist, and there are a few new issues. So I posted the file here: http://www.fileswap.com/dl/4UKNzYTQQ...1.4.accdb.html . Most of the data has been removed from the tables, but otherwise its identical to what I'm using. Issues I'd like resolved are:

- I'd like the forms/dialog boxes to be the size I designed them in the editor. As of right now they are a little too long, and have a grey band at the bottom. In the middle of the screen to if possible.

- When converted to .accde format, it won't run on another system (Windows 7 x64) with the 2013 access run time. The Windows 7 system will open the full .accdb though. When the .accdb is run with the access run time some controls exhibit graphical errors. I think this is because the run time isn't as 'up to date' as the full version (this was happening before they released a 2013 update last week or so). It'd be nice to be able to deploy this as a .accde and not require the full access 2013.

- Is there anyway to separate the back end (tables) from the front end with a relative path? I can split the database fine, but as soon as I move the files to a new computer it all falls apart.

[ItsMe]

When using DoCmd.OpenForm you can overide property settings of the form by including arguments in the DoCmd.

Not sure why you would need a Windows API to set the X, Y of a form. The form's properties can be adjusted using the property sheet while in Design View. Some of these legacy properties can be overwritten with global settings in the Current DB options and Client Settings options. For instance, the "Allow Overlapping Windows" (or something like that) setting should allow the oldschool forms and X, Y coordinates vs. the new Forms with Tabs thing.

Rather than guessing numbers I usually size and place the form on the screen in an area to my likeing (using similar resolution to the user’s screen) before saving the form's design.

EDIT: I may be wrong about Modal Settings. My memory is foggy on this
Docmd will not overide the Pop Up or Modal properties of a form. There is not a way to adjust these properties, via VBA or another, while the form is loading or after it is loaded.


You need to develop Access applications in a way that they can be sustained 100% during RunTime. Any RunTime exception will cause global variables and other memory to be abolished. Extensive testing and error trapping is mandatory. There is not a debugger in RunTime. If the user does not have a full version of Access, the App will close during a RunTime exception.

[Kaisha]

When using DoCmd.OpenForm you can overide property settings of the form by including arguments in the DoCmd.

Not sure why you would need a Windows API to set the X, Y of a form. The form's properties can be adjusted using the property sheet while in Design View. Some of these legacy properties can be overwritten with global settings in the Current DB options and Client Settings options. For instance, the "Allow Overlapping Windows" (or something like that) setting should allow the oldschool forms and X, Y coordinates vs. the new Forms with Tabs thing.

Rather than guessing numbers I usually size and place the form on the screen in an area to my likeing (using similar resolution to the user’s screen) before saving the form's design.

EDIT: I may be wrong about Modal Settings. My memory is foggy on this
Docmd will not overide the Pop Up or Modal properties of a form. There is not a way to adjust these properties, via VBA or another, while the form is loading or after it is loaded.


You need to develop Access applications in a way that they can be sustained 100% during RunTime. Any RunTime exception will cause global variables and other memory to be abolished. Extensive testing and error trapping is mandatory. There is not a debugger in RunTime. If the user does not have a full version of Access, the App will close during a RunTime exception.

I appreciate the suggestions but... I covered all that you mentioned in the OP. I tried to use the Windows API because every other method I had tried previously failed. Setting the form properties did not work, at design time or run time.

I've learned the hard way that access likes to forget globals and I'm ok with the app closing if there is an error.

[ItsMe]

Similar to the suggestions here you can rebuild a form. If it is only a couple or one form that is causing me trouble I will save the VBA to text and then remove the form's module by going to its property’s (Other tab) and changing "Has Module" to equal No.

Compile VBA from the IDE: Debug>Compile DB

Then rebuild the form's class module using the pulldowns at the top of the IDE to create the appropriate sub procedures; pasting the guts of the code blocks into the newly created procedures.


My apologies for not understanding what has been tried already.

[Kaisha]

Recreating the forms won't help. I can reproduce the behavior with a blank form on a new database. Create a form. Put a colored rectangle control on it so you can see where the boundaries are supposed to be. Resize the form to your desired size in design view. Turn off 'auto center', ' auto resize', and 'fit to screen'. Turn on 'pop up' and 'modal'. Go to form view. And voila... a pop-up form that is the wrong size.

[ItsMe]

Using W7 Pro 64bit and Access 2010 32bit I was not able to reproduce that behavior.

Using Overlapping Windows, New DB, Blank Form, Pop Up = Yes, Modal = Yes

When I open the form directly to Design View and drag it to another location on the screen, then save, then close; it will then open, in form view or design view, in the position it was in when saved.

When I resize the form, with the window larger than the form boundaries, the form opens with the window the size of the form boundaries.

The only guess I can make is maybe the window boundaries are not beyond the form boundaries at the time the form is saved, causing the "form/window" resize not to function as expected (untested).

The following JPG shows the form boundary at 5 inches. This is where the window boundary appears when I open it in form view until after I change the size of the form and save.


.