Selecting a specific row from a query

[Damo10]

Hi,

I have a combo box that is using a query to populate the contents, what I would like to know is how to transfer the results of the selected row from the query to populate other text boxes on my userform as the query result will have multiple rows?

Regards,

 

[Machada2015]

I'm sure there's more than one way to do this, but the way I've handled it in the past is using a dynamic array. Start by declaring the array variable like 'Dim recordArray()'. The array would be two dimensional. Sounds like the number of columns would be fixed, but it seems like the number of rows could be different depending on your situation. What I do is create an integer variable for the array columns and a variable for the array rows. So something like; dim arrayColumns as integer and dim arrayRows as integer. Since the columns are a fixed in number, you could easily assign that value right away so assuming htere were 6 columns you could say arrayColumns = 6. Then the number of rows is dynamic so you'd need to create a function that calculates how many rows will be in your record set. So let's say you had Function calculateRows as Integer (you'd have to come up with this logic based on your situation). Then you could assign the result of the function to your arrayRows variable like 'arrayRows = calculateRows'. Let's assume your function found 10 rows that you need to grab. So now you know how many columns and rows your array will need so you need to resize your array like this 'Redim recordArray(arrayRows, arrayColumns). This would result in a an array that has 6 columns and 10 rows. Now that your array is properly sized you can then assign values from your spreadsheet either by assigning a range to the array or otherwise creating a function or a loop to cycle through the records and deposit the values into the array. So then lets say you want to assign the value that resides in the 2nd row, 3rd column of the array to a text box object named 'frm_Main.txt_firstValue' . You'd simply do ' frm_Main.txt_firstValue.value = recordArray(2,3)'. Hope this helps to get you started on a solution. Good luck.

 

[Micron]

Not sure why you mention this.

the query result will have multiple rows

If you want to propagate values from other fields of a combo box query based on what's showing in the combo box then on the After_Update event of the combo box, you could loop through the columns of your combo box and assign them to the text boxes. All you would need to do is ensure that the combo box has as many columns as the query has fields (you don't need to make all of them visible to the user). It would be something like

With Me
  .txtbox1 = cmbMyCombo.Column(0)
  .txtbox2 = cmbMyCombo.Column(1)
  .txtbox3 = cmbMyCombo.Column(2)
  .etc
End With

You may have to repaint the form after these changes.

I'm pretty sure that each time you change the combobox selection, the rest of the columns in the combobox will reflect this change.
Note that the combobox column property is zero based, so 0 is the first column. If there are too many fields to attack it this way, I'd try to use a counter with a loop and incorporate the number with the textbox name. The notion would be to assign combobox.Column to textbox .