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 Scott, 

Thanks for your quick reply. Let me clarify a bit. Below is a pic of the form. 

The CostCode is based on "Council" and I have two CostCode lists, one for Fall and one for Spring. Right now i can only get the Fall Code to populate using rowsouce query since i can get an "if/then" statement in there to work.  I also have two queries, one for the fall code and one for the spring codes. I'd like to be able to choose which list is looked in for the code based on the Season (that is, If season=Fall, use the Council field to find the costcode in the FallCostCode table aka use the fallcostcode query and the same for spring).

Does that lend some clarity? I do apologize, i'm very new to access and feel as if i jumbled my question. maybe i can edit it.

Answer 4

Yes, you can write one query with parameters that refer to either or both of the controls on the form.  Follow my example above, substituting your table name(s) and field name(s) as appropriate.

However, I am puzzled about the fact that this appears to be an unbound form. Typically, one of the great advantages of Access is that it DOES use bound forms. Why would we go the unbound form route? It's harder to implement and usually doesn't provide much benefit for a typical Access Relational Database Application.

Also, Please take a close look at Scott's comments on normalization.

Answer 5

I'm very new to this, how does one "bound" a form and what does "bound" and "unbound" mean in this context?

I'll do as you suggested otherwise :)