Populate list box from a table in vba

Question

I am developing a vba form for employee database, in that there is a search criteria for userid and employees name with the userid should be displayed in a list box control which comes from a single table

I need to populate a list box with value from a table according to a value in a text box which act as a search box (eg: userid)

Please help me how to do this

Answer 1

You question is hard to answer because it depends on some things like data types of the search field etc. So this answer is going to be vague on some of those points...

First off you need to create your listbox with search criteria that will look on the form for the search value and filter accordingly.

You do this by setting the the RowSource property of the listbox. HEre is an example rowsource for a a listbox that looks for a textbox on a form for its filter value...

SELECT tblAgencies.AgencyID, tblAgencies.OrganizationName
FROM tblAgencies
WHERE (((tblAgencies.OrganizationName) 
          Like "*" & nz([Forms]![frmMainMenu2]![txtSearchAgencies],"") & "*"))
ORDER BY tblAgencies.OrganizationName;

The key part is the Like... line. A couple of things about it...notice that the query looks to the form for some criteria. You see that in the [Forms]![frmMainMenu2]![txtSearchAgencies] part of the query. So there is a search textbox on frmMainMenu2 that is called txtSearchAgencies.

Notice also that I am using NZ function to ensure that the peek onto that textbox returns at least an empty string. Finally notice that is uses the Like operator with wild cards at both ends so that the user can type a partial string.

Finally...next to your search box put a command button to execute the filter/search. All that it has to do is REQUERY the listbox like this...

Me.lstAgencies.Requery.

You could also try to Requery at the OnChange event which would filter as they type. But if your query is slow this may not work well.

Seth

Answer 2

Let's say you have a table TABLE1 that has fields userid, employee. You should create a form that has a combobox(named boxid) and textbox(named EdtEmployee). Define a rowsource value of combobox like

SELECT table1.userid FROM table1 WHERE employee like EdtEmployee & "*";

Then define a lostfocus event of a textbox like this

Private Sub EdtEmployee_LostFocus()
 BoxId.Requery
End Sub

I hope, this works for you

Answer 3

I agree with mik. I just would use an AfterUpdate event instead of the LostFocus which I've neber used.