forms and combo boxes (1 Viewer)

GRM-kenyette

Miss Kenyette
Local time
Today, 18:49
Joined
Mar 10, 2005
Messages
15
Hi,

How do i use a combo box to list all the records from a particular field so that when you select a item from the list it then populates other text boxes on the form relating to that particular record?

For example, if i have a customer table and i have 100 records. I want to put all the names in a combo box and when you select one it displays all the other information in text boxes (such as address, tel No, email address...etc).


thanzs in advance
 
J

JammyG

Guest
I would have though you could do that by having the Customer ID in a drop down box, but displaying the Customer ID as the Customers name. How you dislplay the ID as another field I don't know, been told it's possible but I need to find out too !
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 22:49
Joined
Jul 9, 2003
Messages
16,245
If you have a look at the example posted here there is a combo box on a form and a text box that displays information from the query within the combo box.

The combo box is called [Combo0]
and the SQL of the query the combo box is based on is:

SELECT tblTownSTD.ID, tblTownSTD.Town, tblTownSTD.STD
FROM tblTownSTD
ORDER BY tblTownSTD.Town;

You may prefer to view this query in the query grid, you will see that the query displays three fields from the table "tblTownSTD" the three fields are:

tblTownSTD.ID
tblTownSTD.Town
tblTownSTD.STD

However the combo box [Combo0] only displays two of the fields,

tblTownSTD.Town
tblTownSTD.STD

This is desirable as the ID field is not required for display purposes. this is achieved by setting The combo box [Combo0] column widths to :0cm;2.54cm;2.54cm. notice that the column widths still cater for the three fields provided for by the query, but the length of the display of the first field is 0cm, thus hiding the field from view.

The reason I am pointing this out is that to display the third field in the text box "text6" you use the following code:

=[Combo0].[Column](2)

and the (2) does not refer to the second field displayed, but it refers to the third field in the query this is because the query fields are numbered 0, 1 and 2 not 1, 2 and 3 As you might expect. This is an important point to remember and a big trip up if you are not careful!

The other got you with combo boxes is that the column count must be set to the number of fields provided for by the query, in this case there are three fields so the column count must be set to three, even though the combo box only displays two of them.

Now using the above information you should be able to place on your form as many text boxes as required to return the contents of the query behind the combo box. You just need to place this code: =[Combo0].[Column](x) where "x" is the column in the query you want the text box to display. (don't forget that they start at zero not one.)
 

Users who are viewing this thread

Top Bottom