Ghost behavior in Access...it's spooky!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a sub routine in a form module that executes some code on the click
event of a command button. The first few lines of code verify that the user
entered in all of the required fields and displays a message box to the user
asking if they are sure they want to complete the task. After they click ok,
a call is made to a function in a standard code module, where criteria is
established and records get appended. From there, the control returns back
to the original form code click event procedure where it finishes a few lines
of code. It creates a new querydef and uses the function value as criteria
to show the user the records they just created and sets the form controls
back to null.

What doesn't make sense to me is that I had to remove the line that makes a
call to the function because it was running twice, giving me duplicate
results. I don't understand how Access would know the appropriate time in
sequence to make the function call. What is going on? Is this an
unexplained phenomena in Access?
The only thing I can think of is that because I initially had the made the
function call in the code, Access has since gotten smart and knows that's
when it's supposed to execute. I've also since added a few lines of code
before the function executes and after it and it still executes at the right
time. So wierd!
 
There's several possibilities here.

The first is that the compiled version of the code (what actually executes)
is out of sync with the text version (what you view and edit.) A decompile
will address this.

Steps:
1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database to get rid of this junk:
Tools | Database Utilities | Compact/Repair

3. Close Access. Make a backup copy of the file. Decompile the database by
entering something like this at the command prompt while Access is not
running. It is all one line, and include the quotes:
"c:\Program Files\Microsoft office\office\msaccess.exe" /decompile
"c:\MyPath\MyDatabase.mdb"

4. Open Access (holding down the Shift key if you have any startup code),
and compact again.

Another option is that your code is being called twice. For now at least,
make sure the command button's AutoRepeat is set to No. You also need to
take extra precautions if any of the code contains a DoEvents.

If that doesn't fix the problem, add this line to the top of your procedure:
Debug.Print "Called at " & Now()
You can look in the Immeidate Window (Ctrl+G) to see if the routine is being
called 2ce in a row.

Next trick would be to add the line:
Stop
to the top of the procedure. When it is called, Access will stop and
highlight the line in yellow. Press F8 repeatedly to trace where the code is
going.

You may find that you have called the code from multiple events. For
example, if you placed some validation in Form_BeforeUpdate as well as in
the Click of your button.
 
Thank you Allen.

I have not yet tried to turn off NameAutoCorrect because the Access program
does not appear to be on my local pc as I can only find shortcuts to it (even
in the program files\Microsoft office folder)

I tried your other suggestions and have confirmed that the form subroutine
is making a call to the module function twice. First call occurs because my
code explicitly calls. All good there. After the function executes, and
control returns to the form, when it executes this line:

DoCmd.OpenQuery "Assigned Batches", acViewNormal, acReadOnly

It goes back to the function and runs it over again. This is where it is
getting duplicated. Now the above query contains the function value as
criteria:

like "Where MyFunction() Like '*,' & MyTable.MyField & ',*'; "

Should I not be using a docmd event for this?


Thanks for your help!
 
So I've done more thinking about it, I guess it's not spooky after all. The
function is simply being called by the docmd.open query event.

I really appreciate your tip on using the Stop method at the top of my
procedure. I didn't know you could do that! I've been copying and pasting
the form code into a standard code module and running it from there which is
a big pain in the rear because I have to make full references all the
controls.

If there is a better way to approach it, I'm all ears. Otherwise I'll keep
it the way it is as it appears to be working well.

Thanks again!
AA
 
Back
Top