Query Question

[rhconley]

I have a form on which the user selects, via a list box, the name of a particular company to populate the Company field of the underlying EVAL table. The list of company names is retrieved from the SUBINFO table. In the Actions field on the same form, I would like to use that chosen company name as the parameter for a query that retrieves specific records from the SUBINFO table pertaining only to that company and displays the query results in a list box in the Actions field. The selection from the Actions field list box would then populate the Actions field in the EVAL table. From what I can tell, in a given form, I cannot use a value in a particular field in that form as a parameter for a query to populate another field in the same form. I hope this isn't too confusing, but this is driving me nuts. Simply put, I'd like the user to select, from a list, a particular company from Table A, and have actions associated with that company (also from Table A) displayed in the list box of another field on the same form. Both of these selections would populate fields in Table B. Please help.

[charlotte]

I'm getting confused by your explanation. You have a form based on the Eval table (by the way, change the table name. Eval is a built-in function, so the name is a reserved word and will cause you grief.) and you're using a combobox to populate the Company field. The combobox gets its rows from the SUBINFO table, right?

Then you also have a control on the form bound to the Actions field in the Eval table. You're using a listbox for that control and you want to populate the listbox with a query that uses the company name in the combobox as its criteria. You can do this, so you'll need to be more specific about the problems you're running into. You can't based fields in a table on other fields, but there is nothing to stop you from basing the contents of one control on a form on the contents of another control on the same form.

However, if you're trying to use the listbox to enter or display multiple values in the Actions field, don't do it. It's bad design and more suited to a spreadsheet or a product like FileMaker. Plus, although you can use a multiselect listbox to enter data, it won't display it as you might expect.

[rhconley]

Charlotte, thanks for your help and patience. Let me try to simplify:

On FORM X, the user selects a company from a combo box (the companies are stored in TABLE A). Based on the company they select, I want only the actions associated with that company (also stored in TABLE A) to display in another combo box on the same FORM X. Both the company and action selections will be stored in TABLE B which is associated with FORM X. I just can't make this happen! I hope this simplifies my conumdrum somewhat.

[Jim Bulger]

Where is the master table of companies? Usually, in a normalized db, this situation would have two tables with a 1:M relationship (i.e. TABLE A (companies) contains one company record per company; TABLE B (actions) would contain all action records for the companies in TABLE A). Selecting a company from listboxA would then populate listboxB with all records from TABLE B for that company. Why do you need to store this information in another table for a forms recordsource?

[charlotte]

OK, what you're describing is called various things, but what you're trying to do is synchronize the two comboboxes. You want the contents of combobox2 to be based on the choice in combobox1, right? Then there are two ways to do this.

1. Base combobox2 on a saved query. In the criteria of the query under CompanyID (or whatever the field name is), you would have something like [forms]![formX]![ComboBox1]. In the Afterupdate event of ComboBox1 on your form, put this line:

Me!CombBox2.Requery

2. In the AfterUpdate event of ComboBox1, build a SQL string that is the equivalent of the saved query and that will return only the records you need for combobox2 for this company. Set ComboBox2's controlsource property to that SQL string. The code would look something like this:

<pre>Private Sub ComboBox1_AfterUpdate()
Dim strSQL as String

strSQL = "SELECT [Actions] FROM TableA " _
& "WHERE TableA.Company=" & Me![ComboBox1]
ComboBox2.ControlSource = strSQL
ComboBox2.Requery
End Sub</pre>


However, Jim's question is a good one. And I'm uncomfortable with your earlier statement that the actions are stored with the company in TableA. Is TableA shaped something like: CompanyID, Action? Does it have one record for each possible action for each company where that action applies?

If not, or if it holds a bunch of actions in a single field, your design will never support what you're trying to do.