MS Access, Combobox, select fields bases on query, How do I weight the result fields

79 views Asked by At

I have a db where based on user input a series of comboboxes will populate.

Private Sub txtUserInput_AfterUpdate()
cbo1.RowSource = "Select txtValue from tbl1 where (txtValue=" & [Forms]![Form1]![txtValue] & ")"
cbo1.Requery
End Sub

Easy enough.

But I would like the order of the results to change based on weights I assign. For example, each instance I update blue is first 25% of the time, red is first 25% of the time and yellow is first 50% of the time.

EDIT 1:

For example (thank you Gene 2):

txtValue1    Blue
txtValue1    Red
txtValue1    Yellow
txtValue2    Ford
txtValue2    Dodge
txtValue3    Violet
txtValue3    Lilly
txtValue3    Poppy

When the user inputs txtValue1 into txtValue, the combo box reads:

Blue
Red
Yellow

I would like that order to change each time the box is activated based on probabilities I assign.

EDIT 2:

So John, the user goes to the form the first time.

Blue
Red
Yellow

The second time the user goes to the form.

Red
Blue
Yellow

Third time.

Yellow
Red
Blue

Fourth time.

Yellow
Red
Blue

Thank you!

1

There are 1 answers

1
John On BEST ANSWER

You have to add ORDER BY the weights you have assigned

cbo1.RowSource = "Select txtValue from tbl1 where (txtValue=" & [Forms]![Form1]![txtValue] & " ORDER BY weight)"