General discussion

Locked

Access - combo box

By pstempien ·
I am creating a database which has two fields among many. One field is Town and the second field is Zip. I have a table of certain town zip combinations the ky to which is town. I want ot be able to enter the town name via a comb box drop down and have the corisponding zip be filled in. Corspondingly I want to be able to fill in the zip and have the coresponding town be filled in I can get the town to be filled in but the zip will not fill in what Am I doing wrong.

This conversation is currently closed to new comments.

16 total posts (Page 1 of 2)   01 | 02   Next
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Access - combo box

by Peleg In reply to Access - combo box

A combo box is a way to lookup a row by using one of the columns as a index. When you select the row, the other columns in the Row Source are available. You refer to the columns as me!cboCombo.column(x) where 0 is what shows in the combo, and, moving from left to right, 1, 2, 3... reference the rest of the columns in the Row Source. So, you can only lookup data in a combo by one and only one index. But when you close the combo, you can display anything in the Row Source in another text box by using "=cboCombo.Column(x)" as the Text Box's Control Source. Now, if you want to search by either of two columns, you have to have two combo boxes. These when you select from one of them, you can force the other to display by setting it's value in the After Update event handler:

me!cboCombo1 = me!cboCombo2.column(1)

and in the other combo:

me!cboCombo2 = me!cboCombo1.colummn(1)

Collapse -

Access - combo box

by pstempien In reply to Access - combo box

This seems to be an answer to the question howevver I am sure there are easier ways of getting this accomplished. This is not a very unsusual request or am I completely off.

All I want to do is have the entry of the zip populate the town field or the entry of the town populate the zip field

Please advise

Collapse -

Access - combo box

by donq In reply to Access - combo box

build 2 queries:

1 named "qcTown" (for query/combo town) based on your table and sort on Town. Run the query and if it produces what you want swich to SQL view and copy the text into the clipboard. Open the combo boxes properties sheet and paste the clipboard into the "Row Source" field.

repeat the process building "qcZip" but sort on zip. If everything works repeat your copy and paste to the appropriate combo boxes Row Source property field.

Good luck - Don Quigg

Collapse -

Access - combo box

by pstempien In reply to Access - combo box

Too comlicated. This is simply a table with four entries and it is being entered into the database directly with about twenty-five other items. to do what you call for would cause too much confusion and could be don maually faster

Collapse -

Access - combo box

by jonhouston In reply to Access - combo box

I am fairly new to access programming but maybe this will help

In your town combo boxes change event you could requery the database to fill in the zip combo with just the correct zip
ex.
zipComb.SetFocus
zipComb.RowSourceType = "Query/Table"
zipComb.RowSource = "SELECT zipField FROM Table where (town='" +townComb.Value+ "')"

This statement would then fill the zip combo with the zip that matches the current selection in the town combo box.

You could likewise do the same process for the other combo.

I hope this makes sense.

Collapse -

Access - combo box

by pstempien In reply to Access - combo box

The question was auto-closed by TechRepublic

Collapse -

Access - combo box

by SanKulPune In reply to Access - combo box

Hello pstempien,

Use the Bound Column property of the combo box to assign appropriate value (Town or ZIP) from the selected row, to the underlying field.

Suppose column 1 of your drop down list is town and column 2 is zip.

To assign Town tothe underlying field, set Bound Column property to 1.
To assign ZIP to the underlying field, set Bound Column property to 2.

I hope this solves your problem.

You select a row from the drop down list of the combo box and not a field like Town or ZIP. Value from which column of that row is assigned to the underlying field is controlled by the column number assigned to the Bound Column property.

The value you see in the form and the value assigned to the underlying field can be different.

The relationship between the no. of columns in the row source object (query / table), the columns displayed in the drop down list of the combo box, the value displayed in the combo box itself and the value assigned to the underlying field (control source of the combo box) is a very complex. It depends on the values assigned to a number of properties of the combo box.

If you need detailed explanation on this, I would be glad to forward it to you.

with regards,

SanganakSakha.
SimpleAnd Effective Solutions To Small Problems, No QuickFixes.

Collapse -

Access - combo box

by pstempien In reply to Access - combo box

The question was auto-closed by TechRepublic

Collapse -

Access - combo box

by francodacosta In reply to Access - combo box

I beleave the easiest way to solve your problem is to get someone who understands access to code it for you.

You have rejected two acceptable awnsers with the excuse "To complicated".

Why do you say that a two lines of code solution is complicated????????

Was your brain initiated?????

Collapse -

Access - combo box

by pstempien In reply to Access - combo box

This is typical of the type of Computer snobs that answer these questions. The question posed is a simple question and inorder to work out the answer it necessary to understand what is going on and why that particular answer will work.Also then that answer can then be applied to other similar situations. When you are beginning to learn the code common snes requeires an understand of what is happening. This is what is wrong with today's educational system and the answer that this person supplied is what I term part of the dumbing of America

Back to Web Development Forum
16 total posts (Page 1 of 2)   01 | 02   Next

Related Discussions

Related Forums