Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2008
    Posts
    3

    Unanswered: Dynamically changing control caption based on recordset values.

    I have a form that returns 100 items. All of the items have have a
    different value in their Category field.

    EXAMPLE:
    ----------------
    A
    B
    C

    How can I change the value of the caption in the field label on the
    form based on the category? I attempted a series of Case statements
    but it didn't seem to work when records were returned from different categories (one record from category a, one from category b).

    EXPECTATION: If I return 3 records with my query that are in 3 different categories, I expect that the label control for the respective fields/controls would reflect the category returned in the query. If the first item returned is from Category A...the label on the form should read category A...If the second item returned is from Category B...the label on the form should read category B. I have a form that is set to continuous forms so I can show multiple records on the same form.

    Select Case PriceCat
    Case "A"
    MsgBox ([tblProduct.PriceType])
    Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "PriceTypeA"
    Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "PriceTypeB"
    Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "PriceTypeC"
    Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "A"
    Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "B"
    Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "C"
    Case Else
    Me![frmSubSearch].Form.prdPrice1Gl.ControlSource = "1"
    Me![frmSubSearch].Form.prdPrice5Gl.ControlSource = "2"
    Me![frmSubSearch].Form.prdPrice5GlCF.ControlSource = "3"
    Me![frmSubSearch].Form.lblItemSubPrice1.Caption = "1"
    Me![frmSubSearch].Form.lblItemSubPrice5.Caption = "2"
    Me![frmSubSearch].Form.lblItemSubPrice5CF.Caption = "3"
    End Select

  2. #2
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Ummm... why not just use your Category field itself AS the label?
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  3. #3
    Join Date
    Jan 2008
    Posts
    3
    Because the Category name is not the name of the field label.

    For example: if the record is from category a, i need the label to say Price in pounds. If the record is from category b, i need the label to say Price in ounces, if the record is from category c, i need the label to say Bulk Price.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Have you considered adding a field such as CategoryCaption.

    If it's on a continuous form, you may want to consider either adding a CategoryCaption field to the table or just create another table where you have 2 fields, one which matches the Category value in the main data table, and the other field reflecting what caption to show. Then link the 2 tables together and bring the caption field to show on the form (or create a combobox or do a dlookup).

    For example with the dlookup (and a table called LookupCategoryCaption with Category and CategoryCaption fields)
    Create a blank Textbox.
    For the sourceobject put this in
    =Dlookup("[CategoryCaption]","LookupCategoryCaptionTable","[Category] = ' " & Forms!MyForm!MySubForm!CategoryField & " ' ") Note spaces put in between " and ' to show syntax.
    or
    You can make it a combobox and set it up to show the value of CategoryCaption.

    Being a continuous form, you need to have some value in the table for the continous form to match against and return a value for the caption. Ideally this would be in the actual table for the continous form or in a "translation" type table where you do a dlookup or something to that effect.
    Last edited by pkstormy; 01-16-08 at 21:25.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Create a look up table and then use a query as your row source for the continuous sheet!

    Bish, bash, bosh
    George
    Home | Blog

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    And of course, if the number of categories is finite and doesn't change often, you can use a control that looks like a label with a series of conditional if's built in.

    Or, if the number/name of categories does change, improve efficiency by using a function as the control source (DLookups can come back to haunt you).

    You can also add the field at the query level, even use a function in the query if you don't want a linked table (although

    No matter how you do it, your "label" will actually be a control, cause a label caption has to be the same for all records in a continuous form.

    In any case, your "label caption" is actually part of the data as you've described it.

    have fun,
    tc

  7. #7
    Join Date
    Jan 2008
    Posts
    3
    Any ideas of what the function may look like? There are 14 categories and they will not change as they are core product.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Have you considered adding a field such as CategoryCaption.
    That's how I would do it. All over in 10 minutes, fast operation, no coding required.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It would look something like this

    Code:
    Function CategoryLabel(strCategory as String) as String
      Select Case strCategory
        Case = "Category A"
          CategoryLabel = "Price In Pounds"
        Case = "Category B"
          CategoryLabel = "Price in Ounces"
        Case = "Category C"
          CategoryLabel = "Bulk Price"
        ... etc ...
        Case Else
          CategoryLabel = "Unknown Category"
      End Select
    End Function
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  10. #10
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    With 14 categories, I would not use a conditional iif in the field or the query. A linked table is probably your best option, but the Select function StarTrekker shows is just as good.

    The function code goes in the module window; the function can go in the fields control source as: =CategoryLable([CategoryField])

    have fun,
    tc
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Oh forgoodness sake people; I know we're working in Access but can we at least encorage good design?

    As with all systems; start from the ground up. Guess what the foundations for good database design..?
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •