Using query to populate combo box

[dug2k]

I am trying to use a combo box to show a list of values from a table where the SysID is the same as the IDCode of a different table (of the record currently being displayed). What is the best way to do this? I tried using the following query:

SELECT Table2.Description
FROM Table2
WHERE (((Table2.SysID)=[Table1].[IDCode]));

Then I set the row source of the combo box to run the query. When I try to look at the combo box, the list is always blank. I want it to look at the CURRENT value of IDCode and list all records in Table2 that have the same SysID. Since it does not prompt me for parameter value Table1.IDCode what other info would it need to fill this list? What could be wrong and should I take a different approach? I want the user to look at this list created in the combo box, choose 1 of these based on the description, and see additional information about that record from table 2. Thanks!

 

[Terpsfan]

I think what you want to do is run your query against the ID record on your form that is from table1. I'm using Me!IDCode as the reference to your form and control using IDCode.

SELECT Table2.Description
FROM Table2
WHERE Table2.SysID = Me!IDCode;

 

[dug2k]

Thanks Omega, but I still could not get it to work. I am still running the query from the combo box RowSource. The query now reads:

SELECT Table2.Description
FROM Table2
WHERE Table2.SysID = Me!Text0;

Text0 is a text box whose ControlSource is IDCode. Now when I click on the new combo box, I am prompted to enter parameter value for Me!Text0. This value should already be known! Even if I type in a valid IDCode, the combo box is still blank. Any ideas? Is there something I could do in the properties of Text0 to run this query correctly? Thanks.

 

[Terpsfan]

Ok, I figured it out. You want to take the sql statement out of the row source property of the combo box and run it as an inline sql statement in the form's current event. I'm arbitrarily using combo4 and assuming IDCode is a number.

Private Sub Form_Current()
Combo4.RowSource = "SELECT Table2.Description FROM Table2 WHERE Table2.SysID = " & Me.IDCode & ""
End Sub

 

[Terpsfan]

I need to slap myself, you're using Text0...

Private Sub Form_Current()
Combo4.RowSource = "SELECT Table2.Description FROM Table2 WHERE Table2.SysID = " & Me.Text0 & ""
End Sub

 

[dug2k]

Now I am not getting prompted for Me.Text0. However, this is still giving me a blank list. I am wondering if it is a syntax thing for Table2.SysID. When I tried to debug, I add Table2.SysID as a watch and it only says &quot;<Expression not defined in context>&quot; and the type is empty. In this case it will never equal Me.Text0. I am not sure what the syntax should be. Or is there something else I need to do since I am comparing values from 2 different tables? Please help. Thanks.

 

[hennep]

after setting the combo4.rowsource you might need a combo4.requery

 

[dug2k]

Thanks hennep, but the requery did not seem to help.

I will try to explain exactly what I am trying to do so there may be a different approach I could take in my form structure, etc. to reach the same result. I have a table Table1, from which various information is displayed on the form. Each IDCode is unique, let's say about 50 records. Each record represents a system. Some of these systems require things to be fixed, some do not. Several of them have multiple things to be fixed. I would like to browse to a certain system ID, be able to have a combo box updated when I change records to display descriptions of all of that system's fixes (if any), and once I get that working, be able to select a description and have more information about only the selected fix displayed (estimated time, open/closed, etc). All of this information is in Table2 (Description, SysID, EstTime, etc.) The less complicated the design, the better i.e. if there are some things I may have to enter myself that would be better than nothing. I am trying to keep the 2 tables separate, that seems like the simplest way for right now. Any suggestions would be a huge help. Thanks!

 

[dug2k]

I finally solved the first problem. The combo was updating fine, but I couldn't see it. I had 2 columns set up in the combo, the first of which was 0&quot; wide. However, I have another problem. I would like the combo to continue updating based on a query, but also to show a blank combo every time I change records. Then the user needs to click the combo to see the new list. As of now, the old combo value is still showing even when I move to a new record. I have tried a requery on both the form's current event and Combo_AfterUpdate, and a Combo.Rowsource = &quot;&quot; and Combo.Requery in the current event but nothing seems to work. Any thoughts?

I was wondering if anybody cared to look at my sample database and show me what might be wrong.

http://bryan.mybravenet.com/

Just click on &quot;Favorite Links Page&quot; and then click on &quot;combobox&quot; to download the sample file.

Thanks!