Update Label Texts with Query Results

[schwabe]

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

[June7]

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

[schwabe]

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?

[schwabe]

OK, I found it myself. I just need to use Me.Requery and Me.SubForm.Requery and that's it.

[June7]

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?

[schwabe]

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

[June7]

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?

[schwabe]

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

[schwabe]

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

[June7]

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.