Questions? Search our site or call us at 1-800-722-6004

Duplicate Message when Concatenating into the Comment Property in Access

Message Appears more than once when Concatenating the Message into the Comment Property in an MS Access Report

You have written a short VBA subroutine in the OnFormat event of the Detail Section of your report with the intention of setting the Comment Property of the ActiveX Control to reflect the both a comment and the message encoded in the barcode. The code takes the value of the comment property and concatenates it with the value of the Message property, then resets the comment property to the concatenated string. E.g:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Dim strTest
  strTest = Me.ActiveXCtl0.Comment
  Me.ActiveXCtl0.Message = 12345
  Me.ActiveXCtl0.Comment = strTest & " " & Me.ActiveXCtl0.Message
End Sub

When you preview the report in Access everything looks great, but when you print the report it prints the comment and repeats the message:

i.e. Comment Message Message

This happens regardless of which printer you print to.

Solution

We do not know why this happens, it seems that Access sends the Message value twice when it is sent to the printer: inserting a msgbox or debug.Print statement to display the value of the concatenated string proves that when sent to Preview or Print Preview the comment is set correctly, but when sent to the printer the Message is repeated in the concatenated string.

The Solution is to hardcode your comment, or set the take the comment string from somewhere other than the Comment Property of the ActiveX Control. For instance, if you create a form with a text box on it, you type the comment into the text box and push a button to run the report, your report can take the value of the text box and concatenate it with the message property. The following code uses a hardcoded string variable as a comment rather than the value of the Comment property and will not cause the problem:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
  Dim strTest
  strTest = "Test"
  Me.ActiveXCtl0.Message = 12345
  Me.ActiveXCtl0.Comment = strTest & " " & Me.ActiveXCtl0.Message
End Sub

Categories: Troubleshooting, Barcode ActiveX

Last Updated: 2011.07.06

Need more help?

Don't hesitate to call or email us with your questions

Our office is open 9AM - 5PM Monday Through Friday (E.S.T.)

Technical Support: 215-496-0202

Toll-Free: 1 (800) 722-6004
Skype: taltech1 (Voice only)
Email: support@TALtech.com