I found sometimes is really difficult to choose which one to use, e.g
I want to fill up the data to a list box, you can do it by select data and build a query for it, also you can build a VBA code and put it under the control event.
So, which one is better or we say, if we have choice which one is the best?
Thanks
I have always used queries and hardly think of a reason not to do so. Except maybe if you want to add custom items (like 'empty') to the listbox or when speed is an issue. After all, there is absolutely no need to have a listbox with 10.000+ items in it. Custom events to fill the listbox are better in that case.
I try not to make any code more complicated that it needs to be. If I'm simply binding the entire contents of 1 table from 1 column to a list box, then I'll use the control properties. If it's more complicated than that, a query would do it.
(You should also remember that the more code you make, the more code you have to maintain, so more isn't necessarily better.)
The question can be put this way: What's the best way to set the control source of a list box?
The best question to ask next: Do I want the control source declared with a static method or a dynamic method?
Static would be what your VBA wizard generates for a new list box. Static is simple, and simple is best unless your needs say otherwise. The usual needs are (as birger said) solving a problem with a slow-loading form OR sophisticated forms automation.
If you are using a dynamic method then you are using VBA. If the need you are addressing is speed, you can set your control source when the form loads. If the need is automation, you may or may not take action when the form loads and you will be using triggers like AfterUpdate or command button selections.
Query if at all possible as that is much shorter development time than using a call back function. But sometimes you have to such as if displaying a list of drives, files or other data determined using API calls or such.
I tend to always use a Select Query for the row source of a List/Combo Box even if I want a list of file names I would stuff them into a temp table first and then select from that.
Keep all SQL code in one place and as 'close' to the data as possible. For me, this means VIEWs and PROCEDUREs in the 'back end' called using data access technology in the 'front end'. Consider if one of your tables' columns' names changed and you had to search all controls on all properties on all forms in every front end application that uses the 'back end', then all the VBA code in every object in every in every front end application, etc.