Query to Text Box

[Igawa29]

I have two queries that I want to output to two different text boxes. Query 8 should run first, since Query 9 is dependent on Query 8 results. I am trying to do this by a button click, but right now I am stuck with how to modify this code. Any help would be appreciated.

Private Sub Command41_Click()
On Error GoTo Err_Command41_Click

    Dim stDocName As String

    stDocName = "Query8_inv"
    DoCmd.OpenQuery stDocName, acNormal, acEdit
    
    stDocName2 = "Query9_inv"
    DoCmd.OpenQuery stDocName2, acNormal, acEdit

Exit_Command41_Click:
    Exit Sub

Err_Command41_Click:
    MsgBox Err.Description
    Resume Exit_Command41_Click
    
End Sub

 

[dhookom]

Are either of these action queries? Can you share the SQL view of the queries?

Do both queries return a single field and a single record? If not, what value(s) would you expect to display in a text box?

Typically you can use DLookup() to display a single value from a query or table.

Duane
Hook'D on Access
MS Access MVP

 

[Igawa29]

Thanks for your response.

Basically there is a list box that I will click on to select a value, then I have my Query 8 pulling the value from that list box and displaying one item. After that Query 9 pulls one item from what was displayed on Query 8.

I have the query going to (2) list boxes, which are hidden and I have the text box pulling the value from the list box.

Here is my SQL code that I used:

Query 8

SELECT INVENTORY.[Metric Calculation]
FROM dbGYY1 INNER JOIN INVENTORY ON dbGYY1.[Report Number] = INVENTORY.[Report Number]
WHERE (((INVENTORY.[Metric Calculation Description])=[FORMS]![FORM2]![list10]) AND ((INVENTORY.[Metric Calculation]) Is Not Null) AND ((dbGYY1.[Report Number])=[FORMS]![FORM1]![number]))
GROUP BY INVENTORY.[Metric Calculation];

Query 9


SELECT INVENTORY.Comments
FROM dbGYY1 INNER JOIN INVENTORY ON dbGYY1.[Report Number] = INVENTORY.[Report Number]
WHERE (((INVENTORY.Comments) Is Not Null) AND ((dbGYY1.[Report Number])=[FORMS]![FORM1]![number]) AND ((INVENTORY.[Metric Calculation])=[FORMS]![FORM2]![list12]))
GROUP BY INVENTORY.Comments;

 

[dhookom]

When you state "there is a list box" please provide the name of it and its form so we can tie it to your SQL statements.

I don't understand "have the query going to (2) list boxes". Is the query the Row Source or does the list box value filter the query?

It looks like your question involves two forms with generic names, two list boxes with generic names, and a text box.

I'm confused.

Duane
Hook'D on Access
MS Access MVP

 

[Igawa29]

Sorry for confusing you, I am horrible at explaining code lol. Anyway here is the SQL for my main list box. What I am doing is linking it from "FORM 1" and that data pulls over:

SELECT INVENTORY.[Metric Calculation Description]
FROM dbGYY1 INNER JOIN INVENTORY ON dbGYY1.[Report Number] = INVENTORY.[Report Number]
WHERE (((INVENTORY.[Metric Calculation Description]) Is Not Null) AND ((dbGYY1.[Report Number])=[FORMS]![FORM1]![number]))
GROUP BY INVENTORY.[Metric Calculation Description];

What happens now when I open this form (FORM 2) it gathers the "Report Number" from (FORM 1) and pushes the information into the list box (normally 3 - 5 items).

Ultimatly what I need is to have a command button to press that will generate the next two queries that I need and output into a text box (Since I need the words wrapped).

Think of it like this.

Main List box (numerous choices)
1st Text box (One choice based on main list box)
2nd text box (Once choice based on the tree of Main List Box and 1st text box)

I hope that makes sense.

 

[dhookom]

I would create and save a query based off dbGYY1 and INVENTORY. You can then use DLookup("[some field]","[Your New Query]","Your where condition here")


Duane
Hook'D on Access
MS Access MVP

 

[Igawa29]

I am not quite sure I follow you, since I have never used the Dlookup before. Could you give me an example to follow?

 

[dhookom]

Did you look in Help for DLookup?

Duane
Hook'D on Access
MS Access MVP