Results 1 to 10 of 10
  1. #1
    schwabe is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    San Francisco Bay Area
    Posts
    50

    Update Label Texts with Query Results

    Hello everyone,



    I am trying to create a form in which the user selects a group of records with a combobox which runs a query. The result of the query looks like

    Configuration ID Color
    6 red
    9 green
    18 blue
    22 cyan

    On the from, I have four labels in which I'd like to show the text "red", "green", "blue" and "cyan"?

    I know, that I have to work on a VBA module for the combobox after update.
    However, I have no idea how to run the query when the value is selected?
    How do I access the single "cells" of the query in VBA?

    Thanks,
    Benjamin

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Do you want this form able to edit records?

    Why do you have 4 labels? Sounds like to me should just have textbox bound to the Color field.

    If you want to rearrange the records to display horizontally with the colors as field names, that would be a CROSSTAB query or emulate CROSSTAB with expressions http://www.datapigtechnologies.com/f.../crosstab.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    schwabe is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    San Francisco Bay Area
    Posts
    50
    Hi June7,

    that is a good idea, but it also leaves a question open:
    - How do I trigger the query when I made the selection with the combobox?

  4. #4
    schwabe is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    San Francisco Bay Area
    Posts
    50
    OK, I found it myself. I just need to use Me.Requery and Me.SubForm.Requery and that's it.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    Not sure what you mean by 'trigger the query'. What do you want to happen when item is selected? Do you want to open a query object or a form/report bound to query?

    What is selected in the combobox? Does query reference the combobox as filter parameter?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    schwabe is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    San Francisco Bay Area
    Posts
    50
    It looks like it is not exactly what I need or I am missing something. Anyways, I need to explain a little more what I am trying to do:


    I have a table like this which contains the data of cars that I have on stock:

    ID, refManufacturer, txtModel, refColor, refFuelType, refCarType

    All columns that start with ref are references that reference to another table where each number is assigned to the Name and possibly other properties.

    The user has a combobox in which she/he can select the manufacturer and a second one in which she/he can select the models that are produced by the manufacturer.
    Now, I wanted to show a list of the colors available (that is done) and next to the color comboboxes with the available fueltypes and CarTypes (like sedan, sportswagon,...).

    The query result that is given by the selection of the comboboxes looks like
    Color Fuel CarType
    green diesel compact
    green gas compact
    green diesel cabrio
    green gas cabrio

    So, I only want to show one record in the subform with "green"
    and in the first combobox: "diesel", "gas"
    and the second combobox: "compact", "cabrio"

    So, how can I do that?

    Thanks,
    Benjamin

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    You are using cascading (dependent) comboboxes for the Manufacturer and Model and Color? Why not also for FuelType and CarType?

    I don't really understand the subform. What is main form bound to? What is subform bound to? What is purpose of this form/subform arrangement - data entry?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    schwabe is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    San Francisco Bay Area
    Posts
    50
    The purpose of the form is to select three or four cars that go onto a truck and create a report with all the information of each of the cars and their summary, e.g. total weight of the cars.

    Yes, I am using the the cascading comboboxes for Manufacturer and Model. The main form is connected to the main table described above and it contains the two comboboxes + a cancel and an OK button with which I want to trigger the generation of the report.

    The subform is bound to the query which provides the cars selected by manufacturer and model and I am using the subform to provide the different colors and I'd also like to add the other selection per color in the subform. The trick is, that I'd like to have a car of each color on one of the trucks.

    The truck/car model I am describing is not exactly real situation, but I cannot go into describing the real problem publicly... So, please excuse if it doesn't make sense in real life.

    Benjamin

  9. #9
    schwabe is offline Advanced Beginner
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2012
    Location
    San Francisco Bay Area
    Posts
    50
    I should also mention, some of the cars don't come in cyan, some of the do. If there is no cyan available, I only want to have three cars on the truck.

    The alternative I see is:
    Having four queries - for four each color and have cascading comboboxes for the other settings.
    For the cyan cars however, the querie might return no result in which case the corresponding elements should become invisible in the GUI.

    Does that explain the situation well enough?

    Thanks for your support,
    Benjamin

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    The subform should be bound to a table where you save the selected cars else you need a yes/no field in the table to allow selection of records for output to that running of report and afterwards set all the records back to no.

    I will use the model of a purchase order and products as example. Each order can have multiple products. Three tables are involved.

    Main form bound to Orders, subform bound to OrderDetails. OrderDetails has a combobox for selecting products.

    In your scenario I picture Shipment, ShipmentDetails, Cars as the 3 tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Update Label from Text Box
    By rcdeck in forum Forms
    Replies: 2
    Last Post: 01-30-2013, 11:22 AM
  2. Replies: 1
    Last Post: 07-20-2012, 09:48 AM
  3. Replies: 3
    Last Post: 03-11-2012, 08:24 PM
  4. Query Results are Clickable to Update Form
    By chasemhi in forum Forms
    Replies: 0
    Last Post: 03-24-2011, 08:01 PM
  5. Replies: 2
    Last Post: 11-25-2010, 11:01 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums