CODEWITHSUNDEEP
ms-accessreportms-access-2010
WhatEvil
WhatEvil

Reputation: 481

Access 2010 - strange behaviour when using expressions as control sources in reports

I'm producing a report from a table in Access 2010, but I'm seeing some strange behaviour when I try to use an expression as a controlsource for a textbox or ActiveX control.

If I have a textbox where controlsource is JOB_ID then it works fine, displays correctly etc. but if I have a textbox where controlsource is =[JOB_ID] & " " & [PART_ID] or even just =[JOB_ID] then in layout view and report view that textbox shows up as blank, until I click on it and put the cursor in that box, then it shows what it should for as long as the cursor is in that textbox (i.e. the result of the expression).

It also seems to show up just fine in print preview for the report.

It's the same for ActiveX controls - I need to print a barcode with each row of the report so I have inserted a Microsoft Barcode ActiveX control and if using just JOB_ID then it shows up fine, but if I use =[JOB_ID] then each barcode on the page flickers when viewed in layout view or report view.

Also probably related - at the bottom left of the screen when using expressions in the control source it says "Calculating..." which doesn't seem to go away. It's like it's constantly recalculating the value it needs to display and I can't figure out why.

Any ideas?

Upvotes: 0

Views: 548

Answers (1)

scottmcd9999
scottmcd9999

Reputation: 214

With a report, you're much better off created those calculated fields in a query, and then using that query to drive your report.

To create a calculated column n the QBE, use this syntax:

MyCalculatedField: Job_ID & " " & Part_ID

This will add a column to the query named "MyCalculatedField", and you can then use that on your report. Obviously you'd have to add all other relevant columns to the query as well.

Many ActiveX controls are not compatible with Access, even if those controls come from Microsoft. You are far better off using native Access controls, or using a control that is certified for use with Access. There are some compliant controls from IDAutomation (http://www.officebarcode.com/access-barcoding.html), and others.

Regarding the Calcuating ... issue - have you performed maintenance on your database lately? You should make a backup of your database and then try to Compact it. If that doesn't work, try importing everything into a new, blank database, and compact again.

Some have had good luck using the undocument Decompile method. To do that, create a shortcut with this as the Target:

"full path to msaccess.exe" "full path to your db" /decompile

Run that, then compact the database again. Next, Compile your VBA code - from the VBA Editor click Debug - Compile and fix any errors you run across. Keep doing that until the menuitem is disabled. Now Compact again.

As before, please make a backup before trying any of these things.

Upvotes: 1

Related Questions