Querying Access.

[gowans07]

Hi,
I am trying to query a database to retrieve information about a product. Firstly the user must select a product from a Combo box, and what i want it to do is when they have selected a product for it to search for the items that are within the product. I have a timer in which will search for the product items but may use a command button instead, but that's irrelevant. What makes this different from a normal query is that a product can only have 8 items or less and what i want it to do is to display each individual item into a separate label (array).

Below is the code i have to add the records into the combo box
Do Until Data1.Recordset.EOF                          'Stops when there are no more records to be added
CmbProduct.AddItem Data1.Recordset("Product Name")    'Loads all the Products from the database and adds them to the combo box
Data1.Recordset.MoveNext                              'Moves onto the next record after the previous has been added
Loop

Tbl_Item_Product is the table name
The records i want from the table is "Item Name" and there will be > 1 but < 8 records that match the product. The label array i want to add each record into is label9(0 to 7). Hope i've supplied enough information for you, can provide more if required. Thanks :)

[hkdani]

You can dynamically load a label, but you must have at least one on the form to begin with.

What you probably want to do is load the values into your combo box and assign the label value at the same time within your given Do loop. You will also have to add code--if you want to dynamically add labels--to position the labels so that you can see them. If you want to make it simpler, create and place an array of 8 labels on the form to begin with.

If you are going to use the same labels for each search, then you'll have to initialize the labels to a vbNullString value before entering your Do Loop so that you won't have any left over data from a previous query.

Dim i as Integer 
i = 0

' Clear Label values
for i = 0 to 7
   label1(i).Caption = vbNullString
next i

Do Until Data1.Recordset.EOF 'Stops when there are no more records to be added
CmbProduct.AddItem Data1.Recordset("Product Name") 'Loads all the Products from the database and adds them to the combo box
label1(i).Caption = CmbProduct.Text
Data1.Recordset.MoveNext 'Moves onto the next record after the previous has been added
i = i + 1
Loop

[gowans07]

Hi hkdani, thanks for the quick reply, although your code is correct it is not quite what i am looking for. sorry :/ i require it to see what is in cmbproduct.text and then search in the database for that product and then retrieve the items that make up the product. I may not of made myself clearer earlier. Is loading straight from a database the way to do this or should i have it that when cmbproduct.text <> "" then it will search for the .text product, retrieve the items for that product and load into a listbox / combo box? Thus then copying everything from the list box into the labels, via .index ??

Thanks again.
TG

[hkdani]

Well, you have to have some text in the Combo Text to begin with. I don't know if you explained how your were going to do that. You can type it in directly.

But there are several events you could use to trigger a search into your database: Check the Events listed in the right column in your code window of the Integrated Development Environment (IDE) for the Combo Control. You could use the click event, Double Click Event, Key up, etc. Write code to do your search and fill your labels inside that event.

Or you could add a command button to click on using the value in the combo box to use a search.