Help with queries within 2 ComboBoxes

[Guest_imported]

I have a problem that needs sorting out.

I have 2 combos on a Form. I want the 1st combo to select an Equipment Group (STD, Vacuum, High Pressure etc). From this first combobox, i want the second combobox to return with records that contain the same value within the field "equip group".
Therefore, the second combobox contains all the records (models) that have "STD" for example, with the field "equip group".

I have tryed using requery macros, but i'm unsure of how to use them properly. Any help or enlightnement will be grateful.

Thanks
J. Mott

 

[Boorgy]

Hi

I would try something like this: I would have an SQL statement ready, like "SELECT Model FROM yourTable WHERE [Equip Group]= ".
Then program your combobox so when you click on it, it adds its selected field to the string, so the string should be some like:
"SELECT Model FROM yourTable WHERE [Equip Group]=DTD"
Once this is done, your program should run that query and from the recordset use getRows or getString. Once you have the String or the array, clear combobox2 and add all your elements (easier with an array since you can add the array directly using addRange...)

This should work with a bit of fiddling around.
Good Luck

 

[Guest_imported]

thanx boorgy for the advice, i managed to get the first cbobox working, but now, i have to get the 2nd combobox working.

I think you don't get the use of the combobox. It needs to select a product from a list. Then, when the Product is chosen, the 2nd combo box will update and display all the models that the Product relates to.

E.g.

|Select "STD" from cbo box|
| STD |
| Vacuum |
| 2 Stage |
THEN the next cbo box shows all records with STD within the "equip list"

|e3 h 100 |
|e5 g 200 | <<<< These records are linked with STD products with a Query beforehand.

I'm poor with SQL, is there any way it can be done with Queries and filters?

Any advice will be much appreicated. I can always e-mail a copy of my work if these ramblings make no sense.

J. Mott

 

[Boorgy]

Hi again,

Yeah, I understand, but I wasn't really clear the first time.
All I mentioned (the query, clearing the second combobox and updating it) should be encoded in your combo1_click module, so the 2nd combobox gets updated each time.
I think the code would look like (using ADO):

Private sub combo1_click()
Dim query As String
query=&quot;Select Model From yourTable Where [Equip Group]='&quot;
query=query & combo1.text &&quot;'&quot;
Command.CommandType=adCmdText 'use your Command name...
Command.CommandText=query
Set Recordset=Command.execute 'use your RS name...
Dim Arr() As Variant
Arr=Recordset.getRows
combo2.clear
For i=0 To Ubound(Arr)
combo2.addItem Arr(i)
next

Set recordset to Nothing
end sub

This should get you the list of all models in combo2.
If it doesn't work, get in touch by email...
Good luck,
boorgy

 

[Chyld]

Hi,

Create a query based on the main table (i.e. Products)
i.e.

Field: Product Range
Table: Products
Total: Group
Sorted: Ascending
Criteria :

Save the query as something like &quot;Type selection&quot;

Base the first combo box on the query &quot;Type Selection&quot; and call it 'ProdName'

Now, create another query with exactly the same as above but in the 'criteria' section, enter the following:
Like [Forms]![Your_Forms_Name]![ProdName]
then take the sort order off.

and add another column to the query as below:

Field: Products Associated
Table: Products
Total: Group
Sorted: Ascending
Criteria :

Save this query as 'List_Associated_Products'

Create a second combobox **without the wizard on**.
Go into the forms design view and select the new combobox. In the 'Name' section call the combobox 'ProdType', in the 'Row Source' part select the 'list_associated_products' query that you just created, in 'column count' type in '2' (as your query has two columns in it!!), in 'Bound Column' type '2' and in 'column widths' type in '0cm;5cm' making the first column invisible whilst displaying the second column.

Now select the first combobox 'ProdName' and in the event 'afterupdate' select the three '...' that appears on the end and select 'code - blah blah'.
all you have to do here is type in the following:
Docmd.requery &quot;ProdType&quot;

Save and exit the code window, save the form, go to normal view and voila, your combo boxes should be working.

Sorry about the extended typing, but being able to understand what I'm saying is much easier than all that codey stuff....

Chyld

Now in design view of your form, select the 'ProdName'combobox and

 

[Guest_imported]

Thanx Chyld!

That walkthru really helped and did the trick! i'm not that up with the Access jargon sometimes, but that walkthru was very concise!

can this method be applied to other function within the same form? as i shall try and make it so that when the model has been selected, the price of the model (which data is held in the same table as all the other data i have used) will appear in the text box next to the model combobox.

Would repeating this code i used do the same trick as last time, or would it need re-working so that only one value per model would be shown within the textbox?

I can e-mail you a copy of the database if u need to look at it.

Many thanks
J. Mott