Populating a field from a query based on a combo box.

[halabis]

I have a form in which I am attempting to populate a field based on two combo boxes and am having difficulty getting the field to populate. My setup is as follows.

Tables with Fields A, B & C



Query 1
Pulls A, B, & C. A has a filter on it from Combo Box 1

Query 2
Pulls B & C B has a filter on it from Combo Box 2

Form has

Combo Box 1: Unbound Combo box With a Value List row source. The values in this list match all the possible options from Field A. On Change it requeries Combo box 2

Combo Box 2: Unbound Combo box with a Row source of Field B from Query 1

Text Box: Control Source Query 2 Field C


Everything except the text box works perfectly. Selecting a value in Combo box 1 limits the values in combo box 2 to those that appear in Field B that Match Field A as selected in combo box 1. If I then select a value for Combo Box 2 and Open Query 2 it displays the exact line I want that contains only the Field B that matches

However the Text box does not display Field C from Query 2. I know query 2 is pulling the correct data as I have opened it manually and looked. However the text box only displays "#Name?"

What am I doing wrong, and how do I get the text box to display Field C from Query 2?


Thanks!

[ssanfu]

On Change it requeries Combo box 2

I would use the after update event of combo box 1, not the change event.

However the text box only displays "#Name?"

This means Access does not know how to interpret the control source/does not know where to get the value.

The text box probably has a control source of something like "= [Query2].[FieldC]".
You cannot refer to a field in a query like that (or a field in a table). The only way to get the value is to execute the query. but you cannot just open the query.
So I would use VBA in the combo box 2 after update event to open the query (as a recordset) in code, then "push" the value of field C into the text box, then close the query (recordset).


Haven't tried it, but you might be able to use DLookup() to get the value for the text box. (= DLookup("[Field C]", Query2)

[halabis]

The text box probably has a control source of something like "= [Query2].[FieldC]".

Thats exactly what I was trying. It obviously wasn't working.

So I would use VBA in the combo box 2 after update event to open the query (as a recordset) in code, then "push" the value of field C into the text box, then close the query (recordset).

Ok, I've figured out how to insert the VBA code to open the query and to close the query. Do you have an example of code I could use to push the value to the text box? I found the following, but quite frankly it doesn't make any sense to me. (My experience coding VBA is very limited, I use if in excel every once in a while, but not with any regularity)

http://stackoverflow.com/questions/1...a-in-ms-access

[John_G]

From what you have described, the combination of selections from Combo box 1 and Combo box 2 serve to identify a unique record in Query 2. You can use that information in the After Update event of Combo box 2 to populate your textbox, using DLookup:

me![textbox] = DLookup("FieldC","Query2","criteria")

Criteria is a text string that specifies the conditions that uniquely identify the record in Query2 - the same as an SQL Where clause, but without the "Where".

It would be made up of the filters specified by Combo box 1 and combo box 2, and would look something like this:

"FieldA = '" & [Combo box 1] & "' AND FieldB = '" & [Combo Box 2] & "'"

FieldA and FieldB both have to be in query2 for this to work (from your description I think they are), and the syntax I have shown assumes that both are text.

[ssanfu]

Do you have an example of code I could use to push the value to the text box?

Since you asked.....

you could try this

Private Sub Combo2_AfterUpdate()

    Dim rs As DAO.Recordset

    Set rs = Currentdb.OpenRecordset("Query2")

    If Not rs.BOF And Not rs.EOF Then   'check to make sure records met the criteria
        Me.Text_Box_Name = rs!FieldC  'push the value of "FieldC" in the recordset into the text box control.
    Else
        Me.Text_Box_Name = "Oops"
    End If

    rs.Close
    Set rs = Nothing

End Sub

The two lines in blue are optional. The message "Oops" means no records are returned from Query2. You can remove the two lines or change the message to whatever you want.




BTW, it really helps if you use actual control names, field names, form names, table names, etc.......

[halabis]

You guys are amazing! Thanks!