Perplexing forms issue

[Wildanessdude]

I have an MS Access 2007 database that I ported from MS Access 2003 to 2007 a few years ago. I have been trying to fix a VBA bug ever since I ported this database to 2007 and I finally decided to try asking for help...

My issue occurs when I open one form from another. "Form A" contains a combo-box control with a recordID. When I double click on the combo-box I run a function that is supposed to open a second form, "Form B", and find the appropriate record. In Access 2003 everything worked just fine. When I ported to 2007 I started having issues and they are different depending on whether or not Form B is already open.

If Form B is not open my code will open the form then not complete the rest of the function. If the Form B is already open it will sometimes complete the rest of the code and sometimes not complete it.


Here is my code:
...
DoCmd.OpenForm "Company Entry" ' Form B
' Code always executes to here

' From here it sometimes works and sometimes doesn't
msgbox "Got here?"
DoCmd.SelectObject acForm, "Company Entry"
DoCmd.GoToControl "[CompanyIDText]"
DoCmd.FindRecord lRecordID, acAnywhere, False, , False, acCurrent, True
...

There is nothing unusual in this code and I know it should work just fine. What seems to be happening is, Form B opens but the "focus" (not sure if this the exact term) either remains with Form A or switches back to Form A after Form B is opened so the code can't complete on Form B.

I'm suspecting a bug in Access but I thought I'd better ask to see if I'm missing something. I've been writing code in Access for years so I'm pretty knowledgeable but this one has me stumped. Writing Access code is not my main function at work but I do know quite a bit about Access and VBA (obviously not everything). Can anyone help? I've spent hours on this and I'd hate to go back and write a macro to see if that works but I don't know what else to do here.

[RuralGuy]

See if one of the 1st 2 in this list help: http://www.baldyweb.com/

[Missinglinq]

This type of thing comes under what I call Strange, Odd and Curious Behavior, and S/O/C/B, especially when coupled with the phrase 'sometimes it works/sometimes it doesn't,' usually translates into corruption, in one form or another.

Although we usually think of Forms, and even entire Databases, when we speak of corruption, Controls, such as Command Buttons and Comboboxes, can and do become corrupted, and seem particularly susceptible to this during app development or when porting an app from one version to another. The test/cure for this is short and simple...delete and then re-create the Control.

Linq ;0)>

[Wildanessdude]

Thanks for the replies. I did forget to mention that both forms were recreated a few years ago in the hope that it would fix the problem. I may have to give that another try though.

Keep those suggestions coming. I'd really like to get to the bottom of this.

[RuralGuy]

You could try *importing* your db into a fresh new db and see if the problem follows: http://www.btabdevelopment.com/ts/impnew
Importing tends to leave corruption behind. You should also do a Compact and Repair.

[Wildanessdude]

You could try *importing* your db into a fresh new db and see if the problem follows: http://www.btabdevelopment.com/ts/impnew
Importing tends to leave corruption behind. You should alsodo a Compact and Repair.

I tried this (again, though it's been a couple of years) and it didn't help. It was a good thought though. I appreciate the ideas. Thanks.

[Wildanessdude]

If anyone has any more suggestions I'm very open to them. Thanks.

[redbull]

A couple questions.. (you answered some of them)
"they are different depending on whether or not Form B is already open."

What is the behavior of this click if Form B is already open?
"If the Form B is already open it will sometimes complete the rest of the code and sometimes not complete it"

What is the behavior of this click if Form B is Not Open?
"If Form B is not open my code will open the form then not complete the rest of the function."

After looking at all of it.. Nothing is jumping out at me instantly.. Another question though..

Doesn't this "DoCmd.OpenForm "Company Entry" ' Form B" and "DoCmd.SelectObject acForm, "Company Entry"" do the same thing? Meaning, when Docmd.OpenForm runs, it should open FORM B over FORM A, meaning it should already be selected correct?

Two Suggestions, Grasping at straws here...
1.If in fact the statement above is the case, comment the DoCmd.SelectObject out and see what happens... IF that piece of the code is doing more then I see then uncomment out.

2.Try also placing the code below (your code) in the form load or current event of Form B. It could be that the code IS truly running, however the load time is causing the selection and FindRecord to be missed..

DoCmd.SelectObject acForm, "Company Entry"
DoCmd.GoToControl "[CompanyIDText]"
DoCmd.FindRecord lRecordID, acAnywhere, False, , False, acCurrent, True

With that being said, when you manually f8 thru this code, does it seem to work? The more I look at it I think it could be an issue of the code running faster then the form load. When Form B is already open... that wouldn't be an issue.

--- Side note ---

Windows xp 64bit Access 2003 > Windows 7 64bit Access 2007
Everyday lol

[redbull]

If you don't want to move your code, and it does turn into a timing issue... you could also call a pause app function..


Put this somewhere, then you can call it in your code...

Public Sub PauseApp(PauseInSeconds As Long)
    
    Call AppSleep(PauseInSeconds * 1000)
    
End Sub

AppSleep .5
DoCmd.SelectObject acForm, "Company Entry"
DoCmd.GoToControl "[CompanyIDText]"
DoCmd.FindRecord lRecordID, acAnywhere, False, , False, acCurrent, True

Just to leave a .5 second sleeper on this piece.

[redbull]

Status update?