Sunday January 21st 2018

Categories

Insider

Archives

Using Combo Box and List Boxes to fill in other controls

I was asked by someone in the Healthcare field to provide them an easy way of looking up some information based what he or she type in look up field, so that they dont have to manually look into the documents. In other words they will store the information in table and this information can find by using form. There are several ways to do that however solution below was very helpful and was found in a book Access 2002 Desktop Developer’s Handbook by Paul Litwin, Ken Getz and Mike Gunderloy.

Pulling data into unbound conrols

1- Fill the combo box; create a query (or use an existing table) that contains the data you want to present, plus any other fields you want filled in automatically once the user makes a choice. Later steps will be simpler if you make sure the value to be displayed in the combo box is the first field in the table or query, but doing so is’nt umperative.

2-Prepare the combo box; set the column widths property so that the correct column is visible and the othe columns are invisible. If the first column you want displayed and you have 5 columns total, your column widths setting would be :0:0;0;0

This value tells Access to use the default width for the first column and 0 width (hidden) for the next 4 columns.

3-Prepare the other controls: Set the controlsource property for each of the controls into which you want data pulled. In each case, set the control source property to

YourCombo.column(n)

where yourcombo is the control name property of the combo and n is the column number(Starting at 0) you want pulled into the control.

Once you set up the form Access take care of the rest. Anytime the Combo Box changes, the other controls on the form will recalculate and pull the value they need from combo box. It takes absolutely no programming.

Pushing Data into bound controls

If you need to fill in controls that are bound, their control source property is not empty; therefore, you can’t use the pull method described in the previous section. Instead you’ need to push data into controls you’ve madea choice from the combo or list box. That is also simple but would require very little programming. The steps are same as above but you need to leave the controlsource property of the text bound to data fields. To apply push method, you attach code to the afterupdate eventof the Combo or list box, which will fill the appropriate controls. In the example form,frm push text, the code just loop through all the columns and send out data to each of the four conveniently named text boxes;

dim intI as integer

for intI = 1to 4

Me.controls(“txtbox”&intI)=1st.column(intI)