Solved

Putting Circle or Bracket, around the correct answer in a Report.

Posted on 2004-10-05
11
576 Views
Last Modified: 2012-08-13
I have a combo box contaiing the following:

VType combo

Car / PickUp,
Bus / Trailer,
Mobile Home / Hotel

In designing the "Details" section of my report, I listed these three combo options in a strainght line and separating them with a Comma and as using "unbound text" as follows:

(1). Car / PickUp,   (2).  Bus / trailer,   (3). Mobile Home ' Hotel  (under VType Combo)

I have a Master Table called "tblMyData" as follows:

ID       Service                      VType                  Charge         Date
1        CarWash                    Car / PickUp          $2.00          10/01/04
2        Engine Oil Chane         Bus / Trailer          $15.00        10/02/04
3        TuneUp                      Car / PickUp           $12.99        10/02/04
4        Car Wash                   Car / PickUp           $2.00          10/06/04

In Printing out a record of Service, I want a VBA function that will indicate the type of VType (from the VType Combo Box)
and CIRCLE IT OR AT BEST put a bracket arond the correct VType that was serviced (from the list of VType Combo Box)rvice that was performed say on 10/06.04 (from the VType list above).

I have already designed the report and it's working fine with dates and everything EXCEPT CIRCLE or  a BRACKET AN UNDERLINE, BOLD TEXT etc., around the choice of VType answer as shown below assuming that Car / Pickup is the correct answer that was serviced as a :CarWash" on 10/06/04. (see below as I wanted it).

(Car / PickUp),   Bus / trailer,   Mobile Home ' Hotel  

NOTE: There is a BRACKET around Car / PickUp in BOLD AND UNDERLINE TEXT.

Just something to indicate the correct answer in my report using tblMyData table above.

I will appreciate any suggestion.
Thanks.
Bill

0
Comment
Question by:billcute
11 Comments
 
LVL 26

Expert Comment

by:Alan Warren
ID: 12234009
Hi Bill

wondering if an option group bound to ID might do the trick here?

Should display as  a circle with a dot in correct option

Alan
0
 
LVL 16

Expert Comment

by:GreymanMSC
ID: 12234108
I presume that you have three labels in your report's detail section for each of the possible VTypes, with appropriate names.  For reference I'll call these lblCar, lblBus, and lblMobile.  Just substitute whatever name you actually used.
 
First make sure that the TAG property of each label exactly matches the possible VType (as you wish to change the caption).
 
Second add a textbox with the ControlSource property set to VType, the Name set to txtVType and the Visible property set to False.  This box will not appear on the report, it's simply to be used to compare it's value to the labels' tag properties.  VType should, presumably, be a field returned in the report's RowSource query.
 
Now you need to add the following code to the Detail line's On Format event.  Simply switch to the report's code module and paste the following snippet (changing control names where appropriate).

  Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim Ls as Variant, Iter as Long, L as Label, T as TextBox
    Ls=Array(Me.lblCar, Me.lblBus, Me.lblMobile)
    Set T=Me.txtVType
    For Iter = LBound(Ls) To UBound(Ls)
      Set L = Ls(Iter)
      If Nz(T.Value) = L.Tag Then
       '<--It's a match -->
        L.Caption = "(" & L.Tag & ")"
        L.FontBold = True
        L.FontUnderline = True  
      Else
        '<--It's not a match -->
        L.Caption = L.Tag
        L.FontBold = True
        L.FontUnderline = True  
      End If
      Set L=Nothing
    Next Iter
    Set T=Nothing
    Ls=Null
  End Sub

Now, if you wish to put a circle around the labels, you would simply need a picture images placed behind each one (and make sure the labels have a transparent background), to be made visible or invisible as required.
0
 
LVL 16

Accepted Solution

by:
GreymanMSC earned 150 total points
ID: 12234130
Doh! Cut and paste error!  Sorry about that.  The false case of the if statement should set the font effects to false, of course.

      Else
        '<--It's not a match -->
        L.Caption = L.Tag
        L.FontBold = False
        L.FontUnderline = False
      End If
0
 
LVL 4

Author Comment

by:billcute
ID: 12235809
Dear GreymanMSC,

Thanks for helping. Your suggestion is quite appealing but I am not that sophisticated yet..you guys are "Gurus" already.

Please provide the step by step design. Are you saying that from the toolbox, I should daw three labels and name them: lblCar, lblBus, and lblMobile before going with the design?

There are three options in my VType combo box which are:
(1). Car / PickUp,   (2).  Bus / trailer,   (3). Mobile Home / Hotel

I assume my label should be "lblCar / PickUp", "LblBus / Trailer" and "LblMobile Home / Hotel" respectively.

The only bound table to the Report Design is my "tblMyData". From here content of the "VType" field will be pasted to the REPORT. ONLY one category can show at a time.

So, assuming that I place three labels in the "Page Header" or the "Detail section" of the Report as you've suggested:
"lblCar / PickUp", "LblBus / Trailer" and "LblMobile Home / Hotel" as well as the text box also in the "Detail section".

How do I go about adding a "PICTURE IMAGE" of a CIRCLE that would fit around each label Option.

Are you suggesting that I draw a Circle with Ms. Paint Program, save it as "bitmap" or "jpeg" an then place it around each label and at the same time set the transparency such that each label would show through?

I understood your suggestion on adding a text field named txtVType and set it to a "Non-Visible" field.

As for the "Detail lines on format event". Are you suggestting I place the above code in between the sub routine shown below?
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

End Sub

I quite appreciate your input, hoping to hear soon from you.
Thanks
Bill
0
 
LVL 8

Expert Comment

by:JonoBB
ID: 12236688
Isnt this what conditional formatting is meant to handly

In design mode of your report, select the controls that you want to format, click Format>Conditional Formatting
Then set your expression and the format that you want

If the value of the control matches the expression, then the control will be formatted as you have specified
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 16

Expert Comment

by:GreymanMSC
ID: 12244047
  There are three options in my VType combo box which are:
   (1). Car / PickUp,   (2).  Bus / trailer,   (3). Mobile Home / Hotel

   I assume my label should be "lblCar / PickUp", "LblBus / Trailer" and "LblMobile Home / Hotel" respectively.

Each label has a name and a caption property.  You will find these by double cliking on a control while in design view.

The name property is used to reference the control in code.  The caption property is the displayed text.  They do not have to be related in any way, though a well named control indicates what it is used for.   Names should contain only alphanumeric characters, and not spaces, punctuation marks or other special characters.   (Although MS Access allows it, it's kludgy to use in code). Thus lblCar can display "Car / Pickup".

    So, assuming that I place three labels in the "Page Header" or the "Detail section" of the Report as you've suggested:
    "lblCar / PickUp", "LblBus / Trailer" and "LblMobile Home / Hotel" as well as the text box also in the "Detail section".

Place the labels and the text box in the same section, and the code to control their display in the format event of that section.

   How do I go about adding a "PICTURE IMAGE" of a CIRCLE that would fit around each label Option.

    Are you suggesting that I draw a Circle with Ms. Paint Program, save it as "bitmap" or "jpeg" an then
    place it around each label and at the same time set the transparency such that each label would show through?

Yes. Exactly.  Though it's simpler if you place it behind the label, with the label's background set to transparent so the image shows through.

   As for the "Detail lines on format event". Are you suggestting I place the above code in between the sub routine
    shown below?
   Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

   End Sub

Yes.  In the design view of the report, focus on the Detail bar (the bar above the detail section), double clicking to bring up the properties dialog if it is not already showing.  Then go to the Events tab and select the On Format event.  Change the text to read [Event Procedure], then click the elipsis button (...) to open the code view.  It will provide the above wrapper for the code (as you've found).  Paste the code into the sub routine.
0
 
LVL 4

Author Comment

by:billcute
ID: 12244121
Hi GreymanMSC,
Thanks again, your code did not work with list with spaces and a slash in between. It gave me a sybtax error.
When I use you particular example with Car, Bus and Mobile, it worked. What can I do to get around this and my tables contained thousands of records from a combo list with slash in between.
Please help.
Thanks
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 12244460
GreymanMSC,
I had just posted my last question only to see that you had replied my previous question. Anyway, I did an extensive work on your code and after reading your correction on labelling few minutes ago. I was able to fix the problem. I didn't want to bother about the circles anymore. The bracket bold and  underline was more than enough.

Before I accept your answer which is likely to close this question, please click on my username "billcute" and click the "VIEW QUESTION HISTORY" and you will find 3 other questions worth 250 points each that are related to this one you just answered; yet NOT as difficult as this one.

Please let me know the ones you would like to take up for the points. After I have heard from you for the last time, I will ACCEPT your answer, award you full points and close this question.
I dont know if I would have an opportunity of getting in touch with you except by luck that's why I am communicating this info to you through this medium.
Thanks for everything.
Bill
0
 
LVL 4

Author Comment

by:billcute
ID: 12299333
Dear Greymann,
I really need your help. I have seen some of your other solution especially in VBA CODES.

shanesuebsahakarn and stevbe are both helping me with an AfterUpdate Event but they are having some difficulties with.

Please click on my username to take a look at the Title" Problems with an AfterUpdate Codes.

I will appreciate whatever you can do to help solve this problem.
0
 
LVL 4

Author Comment

by:billcute
ID: 12329242
Greyman,

NEW POSTER: - worth (500 points)

I know that you are vey good at this - the reason why I am inviting you to this question.

I have just posted a new question and it is related to the current one you are helped me with previously. The Title of the Poster is "Calculate Formular Using VBA Codes / SQL - For Form UPDATES:.

Please click on my "Username" (billcute) it will take you there. Have a shot at the question to see if you can handle it.
____________________________________________________________________________________________________
0
 
LVL 4

Author Comment

by:billcute
ID: 12332229
Try to see my 2 new Posters, each worth 500 Points for a total of 1000 points. It's been summarized
Bill
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now