Hi
Can anybody tell me why the VBA code below works fine in Access 2003
but doesn't work in Access 2007? I do not get any error message, even
if I put a deliberate error into the code. Also, if I put a debug
break point in the code, nothing happens. If I add the line msgbox
"ok" it displays the message box, so I know that it is reading the
code. This is a frightening problem, because if this doesn't display
errors, then what other bits of code are not working? This is
happening on every PC which has Access 2007.

Here's what should happen - when the user clicks on a list box, it
takes the value from the item he has clicked on and goes off and finds
the record which corresponds with the value.

Private Sub lstCompany_Click()
On Error GoTo err_handler

DoCmd.GoToRecord acActiveDataObject, , acFirst
txtNAME.Locked = False
txtNAME.SetFocus
DoCmd.FindRecord lstCompany.Column(0), acAnywhere, False, , False,
acCurrent, False
txtFindCompany.SetFocus
txtNAME.Locked = True
txtFindCompany.SetFocus
Exit Sub

err_handler:
MsgBox Err.Number & " " & Err.Description

Exit Sub

End Sub

Thanks a lot for any help

Colin

I'm not sure why that would cause an error, however, you can check your vba
settings to help find the error message.

Open your code module, then in vba, click on tools, options. Verify that
error handling is set to "break on all errors". Recompile, and then when you
run you should at least get an error number / description.

Hope this helps,

Andy
"Bobby" <bobby2 (AT) blueyonder (DOT) co.uk> wrote

On 6 Mar, 15:00, "ARC" <PCES... (AT) PCESoft (DOT) invalid> wrote:
I've just followed your advice. Unfortunately even with "break on all
errors" set, I still don't get an error from the code.

Colin

Try this standard recovery sequence (in order):

1. Uncheck the boxes under:
Tools | Options | General | Name AutoCorrect
In Access 2007, it's:
Office Button | Access Options | Current Database | Name AutoCorrect
Explanation of why:
http://allenbrowne.com/bug-03.html

2. Compact the database:
Tools | Database Utilities | Compact/Repair
or in Access 2007:
Office Button | Manage | 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.

5. Open a code window.
Choose References from the Tools menu.
Uncheck any references you do not need.
For a list of the ones you typically need in your version of Access, see:
http://allenbrowne.com/ser-38.html

6. Still in the code window, choose Compile from the Debug menu.
Fix any errors, and repeat until it compiles okay.

7. Still in the code window, choose Options on the Tools menu. On the
General tab, make sure Error Trapping is set to:
Break on Unhandled Errors
and the Compile on Demand is unchecked.

At this point, you should have a database where the name-autocorrect errors
are gone, the indexes are repaired, inconsistencies between the text- and
compiled-versions of the code are fixed, reference ambiguities are resolved,
the code syntax is compilable, and the VBA options are set to show errors
and avoid this kind of corruption.

If it is still a problem, the next step would be to get Access to rebuild
the database for you. Follow the steps for the first symptom in this
article:
Recovering from Corruption
at:
http://allenbrowne.com/ser-47.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Bobby" <bobby2 (AT) blueyonder (DOT) co.uk> wrote

Interestingly on my PC I get and error message, which is "2142 The
FindRecord action requires a Find What argument". All other PCs I have
tried do not display a message. The only difference is, I have both
2007 and 2003 on my PC.

So, I have two issues - The error above and more alarmingly (I think!)
the fact that nobody apart from me is getting error messages. Have we
just forgotten to tick a box when we setup Access 2007?

Colin

Hmm.... sorry to keep replying to myself. I've fixed the error with
the FindRecord. The error was in the line:

DoCmd.FindRecord lstCompany.Column(0), acAnywhere, False, , False,
acCurrent, False

It didn't like lstCompany.Column(0). To get around the problem, I
assigned a variable and gave it the value lstCompany.Column(0), as
below:

MyVariable = lstCompany.Column(0)
DoCmd.FindRecord MyVariable, acAnywhere, False, , False, acCurrent,
False

This now works in 2007, though it seems crazy to me.

The reason why I got the error but nobody else was because I opened
the database in design view. So why do the errors get displayed in
design view, but not when we open the database by double clicking?
This surely is a tick box somewhere???

Thanks for the help so far. Any further help will be much appreciated,

Colin