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

Hi Stephanie, Well your first mistake is having 2 Cost Code lists. One of the first rules of relational database design is to not use object names to identify data. You should have ONE costcode table and each cost code should be identified by a field that indicates the season. That solves your problem by building the comboboxes as I suggested.

I'm also wondering why you are using an unbound form? While there are good reasons to do so, I don't see any of those reasons. In a bound form, Access handles the I/O between the form and the table for you. In an unbound form, you have to write code for the I/O yourself.

Answer 4

Ah i see. how do i bound the form? I'll get to fixing the table issue right away!