Populating a text box from query results

[dsebaucom]

I am new to Access programming and would consider my skills intermediate.

Here's my problem:

I have a form with three dropdown boxes that display data from different
tables, a text box and a button. The form is going to be used to display some
information from a query that I have built (the query works).

I need to populate the form's text box with data from the query. The data
from the query will be dependent upon what the user enters into the dropdown
boxes.

I know that I will probably have to use some VB programming in which I am
novice. I have looked all over the web and can't find anything to help me out.


Does anyone have any ideas on how I can accomplish this?

 

[Al Campagna]

ds,
Not sure what you mean by "using a query to fill in a textcontrol."
It would seem that the 3 combos should be all that are needed to "drill" down to a
value that you wnat enetered in the text control.
Since you haven't indicated what the combos are doing, I'll use a "simple" example of
drilling down a library to a specific book
Three combos...
cboGenre cboAuthor cboTitle
Fiction Dickens Hard Times
Non-Fiction Scott Ivanhoe
etc... Foote Civil War
etc.... etc...
cboAuthor relies on the selected value of cboGenre to filter the selections it
displays, and cboTitle uses cboGenre and cboAuthor to filter it's results.
On the AfterUpdate event of cboTitle, you could set the value of your text control to
the value in cboTitle, or any of the columns assocaited with it.

Don't take my example "literally", but the concept is acoomon method to drill down
(using the combo RowSource queries) to a final value.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

 

[dsebaucom via AccessMonster.com]

Thanks for your response Al. Maybe I'll explain what I'm donig better.

My three combo boxes are exactly how you described. They are based off of
functional areas in my business. Example: Projects / Billing / Payments
Received. We do an audit process of every area so there are numerous audits
for the different areas (Projects / Billing / Payments Received might have 20
different audits).

I have a form that populates our auditTable. The form has the drill downs for
the area and then scores for the different audit questions.

There are four different sections of our audit (Section1, Section2, Section3,
Section4) each section has about 10 questions associated with it. In my
auditTable I do not distinguish between the sections. Instead, I just run a
query that calculates the section scores.

I also have a query called qrySection that calculates the average of each
section by area (it can give me the average score in Section1, Section2, etc..
.. for Projects / Billing / Payments Received). The fields in qrySection are:
area1(criteria = 1st combo from form), area2(criteria = 2nd combo from form),
area3(criteria = 3rd combo from form), avgSection1, avgSection2, avgSection3
and avgSection4.

Now comes my problem.....

We want to do reporting from a form. A user can come into this form, select
the area (Projects / Billing / Payments Received) from the three combos and
then click the submit button. When the user clicks the submit button, I want
to populate four text boxes with the average scores from qrySeciton. So,
textBox1 would display avgSection1 from my query, etc...

In the buttons OnClick Event I have been successful calling qrySection with
DoCmd.OpenQuery "qrySection" but it opens up a new window with the query
results. I can't seem to figure out how to get the values of avgSection1,
avgSection2, etc... separated. I thought I could maybe do something like this:
Me.textBox1.value = qrySection.avgSection1 but that doesn't work

Any ideas?

ds,
Not sure what you mean by "using a query to fill in a textcontrol."
It would seem that the 3 combos should be all that are needed to "drill" down to a
value that you wnat enetered in the text control.
Since you haven't indicated what the combos are doing, I'll use a "simple" example of
drilling down a library to a specific book
Three combos...
cboGenre cboAuthor cboTitle
Fiction Dickens Hard Times
Non-Fiction Scott Ivanhoe
etc... Foote Civil War
etc.... etc...
cboAuthor relies on the selected value of cboGenre to filter the selections it
displays, and cboTitle uses cboGenre and cboAuthor to filter it's results.
On the AfterUpdate event of cboTitle, you could set the value of your text control to
the value in cboTitle, or any of the columns assocaited with it.

Don't take my example "literally", but the concept is acoomon method to drill down
(using the combo RowSource queries) to a final value.

I am new to Access programming and would consider my skills intermediate.

[quoted text clipped - 12 lines]

Does anyone have any ideas on how I can accomplish this?

 

[Al Campagna]

I think I understand...
You have a query that uses the values from combo 1-3 for it's criteria to deliver the 4
calculated fields (1 record w/AvgSection1-4) you need to see.

Create a subform on your form, with qrySection as the RecordSource, and the four fields
place on it.
Link the combo/s (Parent) to your subform (Child) according to their relationship. (I
would think that the PaymentsReceived key field would be all you need, but "link" to the
combos as necessary)
The combos can even be unbound...
You could make the subform Viisble = No until the AfterUpdate event of cbo3.
Basically, that should work.
--
hth
Al Campagna . Candia Computer Consulting . Candia, NH USA
Microsoft Access MVP
http://home.comcast.net/~cccsolutions

"Find a job that you love, and you'll never work a day in your life."

Thanks for your response Al. Maybe I'll explain what I'm donig better.

My three combo boxes are exactly how you described. They are based off of
functional areas in my business. Example: Projects / Billing / Payments
Received. We do an audit process of every area so there are numerous audits
for the different areas (Projects / Billing / Payments Received might have 20
different audits).

I have a form that populates our auditTable. The form has the drill downs for
the area and then scores for the different audit questions.

There are four different sections of our audit (Section1, Section2, Section3,
Section4) each section has about 10 questions associated with it. In my
auditTable I do not distinguish between the sections. Instead, I just run a
query that calculates the section scores.

I also have a query called qrySection that calculates the average of each
section by area (it can give me the average score in Section1, Section2, etc..
for Projects / Billing / Payments Received). The fields in qrySection are:
area1(criteria = 1st combo from form), area2(criteria = 2nd combo from form),
area3(criteria = 3rd combo from form), avgSection1, avgSection2, avgSection3
and avgSection4.

Now comes my problem.....

We want to do reporting from a form. A user can come into this form, select
the area (Projects / Billing / Payments Received) from the three combos and
then click the submit button. When the user clicks the submit button, I want
to populate four text boxes with the average scores from qrySeciton. So,
textBox1 would display avgSection1 from my query, etc...

In the buttons OnClick Event I have been successful calling qrySection with
DoCmd.OpenQuery "qrySection" but it opens up a new window with the query
results. I can't seem to figure out how to get the values of avgSection1,
avgSection2, etc... separated. I thought I could maybe do something like this:
Me.textBox1.value = qrySection.avgSection1 but that doesn't work

Any ideas?

ds,
Not sure what you mean by "using a query to fill in a textcontrol."
It would seem that the 3 combos should be all that are needed to "drill" down to a
value that you wnat enetered in the text control.
Since you haven't indicated what the combos are doing, I'll use a "simple" example of
drilling down a library to a specific book
Three combos...
cboGenre cboAuthor cboTitle
Fiction Dickens Hard Times
Non-Fiction Scott Ivanhoe
etc... Foote Civil War
etc.... etc...
cboAuthor relies on the selected value of cboGenre to filter the selections it
displays, and cboTitle uses cboGenre and cboAuthor to filter it's results.
On the AfterUpdate event of cboTitle, you could set the value of your text control to
the value in cboTitle, or any of the columns assocaited with it.

Don't take my example "literally", but the concept is acoomon method to drill down
(using the combo RowSource queries) to a final value.

I am new to Access programming and would consider my skills intermediate.

[quoted text clipped - 12 lines]

Does anyone have any ideas on how I can accomplish this?