Combo Box Value Bound Problem

I am using Access 2000. I would like to fill up a combo box with values dynamically from a table when user click the combo box only (not at the form load time).

I have tried the click and mouse down event, however, none of them work (my combo box still empty). Any advice ? Below is the sample code

Private Sub cboDefectGroup_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
cboDefectGroup.RowSource = "select DefectCode, Description from Defect"
cboDefectGroup.ColumnWidth = "0cm;2.54cm"
cboDefectGroup.BoundColumn = 1
End Sub

Private Sub cboDefectGroup_Click()
cboDefectGroup.RowSource = "select DefectCode, Description from Defect"
cboDefectGroup.ColumnWidth = "0cm;2.54cm"
cboDefectGroup.BoundColumn = 1
End Sub

Just curious why you are wanting to do this instead of just setting the rowsource of the combo box at design time. I don't see any WHERE clause which would limit it based on anything so it would be fine to just set the row source and the column widths and all at design time and if you want the latest data just use this in the click event of the combo:

Me.cboDefectGroup.Requery

Another variation on Bob's reply.
Set the column widths and BoundColumn in your design view
Use the Got Focus of the cboDefectGroup

Private Sub cboDefectGroup_GotFocus()
cboDefectGroup.RowSource = "select DefectCode, Description from Defect"
End Sub

This will give you the latest data when you move to the drop down.

Poppa Smurf said:

This will give you the latest data when you move to the drop down.

Click to expand...

So will requerying the combo box and is actually slightly more efficient than setting the rowsource again.

boblarson said:

Just curious why you are wanting to do this instead of just setting the rowsource of the combo box at design time. I don't see any WHERE clause which would limit it based on anything so it would be fine to just set the row source and the column widths and all at design time and if you want the latest data just use this in the click event of the combo:

Me.cboDefectGroup.Requery

Click to expand...

I notice with or without the requery method, the combo box still can show the latest data when user click on the combo box.

However, I am wondering why user have to type in something inside the combo box before VBA can fire the click events under the combo box? Anyone know the reason or any alternative way to write the VBA ?

Poppa Smurf said:

Another variation on Bob's reply.
Set the column widths and BoundColumn in your design view
Use the Got Focus of the cboDefectGroup

Private Sub cboDefectGroup_GotFocus()
cboDefectGroup.RowSource = "select DefectCode, Description from Defect"
End Sub

This will give you the latest data when you move to the drop down.

Click to expand...

Thanks. Exactly it works as expected. Something new to me as I work too long in the VB and Vb.Net. For knowledge sharing, VB and VB.Net use the click event to populate the value whereas VBA use the GotFocus event. I notice the requery method do not required here.

Again, thanks for kind assist.

As you've found out, All variations of Visual Basic are not created equal! QuickBasic 4.5 begat Visual Basic and Visual Basic begat Visual Basic for Applications, and so they share many, many function/features/properties, but they also have many function/features/properties that are not shared! To make this even more confusing, many of these non-shared functions do share the same name, it's simply that that they don't do the same thing!

One often confusing Property for VB/VB.Netters moving to Access is the Text Property. In latter two languages this refers to the value of a textbox, for example. In Access VBA, however, it only refers to the characters currently in a textbox, which may or may not be the value stored in the field in the underlying table. It can only be accessed thru code when the textbox actually has focus, and so, is of very little in Access VBA. In Access, the Value Property would be used, which doesn't require the textbox to have focus when it is referenced.

To add to the muddle, the same function/features/properties in VBA can vary depending on which application you're using it in. All Excel VBA function/features/properties are not natively available thru Access VBA!

The bottom line is that if you try to use something from VB/VB.Net and it doesn't work as you expected in Access, go to Access Help to see if there's a differnce.