How to Display the Result of Query in MS Access Form Text Box

Question

I need to display the results of a query in a text box based on the value of another field in a form.

I can get one of the choices (Fall Codes) to show up using a list box and a SELECT statement in the row source.

In plain English, what I need to happen is that the "CostCode" use different queries depending on the "Season" field, either "Fall" or "Spring". That is, "If Season=Fall, then display results of qryFallCostCodes. If Season=Spring, then display the results of qrySpringCostCodes.

These aforementioned queries are based on another field "Council". That is the query for the fall codes is "look in the form field "councils" and find the corresponding cost code" which works great. Same for spring. the issue I'm having is getting the form to choose which query to use based on the Season.

Hopefully this makes sense. I'm happy to provide more answers if you have questions. 

PS- I'm very new to Access but not averse to VBA or other "advanced" solutions.

Answer 1

A bound form has it's Recordsource set to a table (or query). The controls then have their ControlSource set to a field in the Recordsource. I always use the Form Wizard to initially build my forms. it walks you through the process of selecting a table and fields for your form. It then places the controls on your form. You can then go into Design Mode to move the controls around and make changes to the look of the form. there would then be no need for a Save button.

Answer 2

One "binds" forms to recordsets. When that happens, the form is considered "bound." Here's an example.

This form's record source is a query called "qryMeals_frm" In other words, this form is bound to this query. This process is called binding, as in tying or linking. In the form, some controls are also bound to specific fields in the query, and some are not.

Answer 3

It sounds like what you need is a parameterized query that filters on a "Season" field in one table. It doesn't sound like you actually should have different queries for each season.

In a general sense, that query would be something like this:

SELECT Field1, Field2, Field3

FROM YourTableNameGoesHere

WHERE YourTableNameGoesHere.Season = Forms!YourFormNameGoesHere.YourListBoxNameGoesHere

Answer 4

Hi Stephanie, I'm an independent adviser and will try to help.

Since a query can return multiple rows and multiple columns you can't display the results in a text box. However, I'm not 100% clear what you are trying to do. As I understand you, you need to select a Cost Code, but you want to Cost Codes to be filtered based on a season. This is a standard technique called Cascading Comboboxes. It is well documented.

Basically, the Rowsource of your combobox is filtered by the selection in the previous combo (Season). However, I'm not sure if your tables are constructed properly. In a properly normalized database your Cost Code table would look like this:

CostCodeID (PK Autonumber)

CostCode

SeasonID (FK)

You would also have a table for seasons like:

SeasonID (PK Autonumber)

Season

So you have a combo for Seasons (cboSeason) with a RowSource of:

SELECT SeasonID, Season FROM Seasons;

The Bound Column is 1 and the first column width is set 0.

The Cost Code column has a Rowsource of:

SELECT CostCodeID, CostCode FROM CostCodes WHERE SeasonID = Forms!formname!cboSeason.

If you need further clarification on this please feel free to ask.

Answer 5

It sounds like what you need is a parameterized query that filters on a "Season" field in one table. It doesn't sound like you actually should have different queries for each season.

In a general sense, that query would be something like this:

SELECT Field1, Field2, Field3

FROM YourTableNameGoesHere

WHERE YourTableNameGoesHere.Season = Forms!YourFormNameGoesHere.YourListBoxNameGoesHere

I do have two queries, one for Fall Codes and one for Spring Codes which are based on another field, "Council" (probably should have mentioned that, sorry i didn't).

However, are you saying that a single query that looks in a specific table for the data based on the Season rather than trying to do what i asked above? It sounds simpler and i love simpler. Just trying to understand. :) 

I very much appreciate your quick response!

I've attached an image to show my form and tables etc.