Autofill Comboboxes Based on Another Combobox (Access 2007)

[sachuchem22]

I Have data entry form in which there are few comboboxes named as city,dist,state. so i want to autofill the dist, state comboboxes on adding value in city combobox. please help me.

[strive4peace]

Add the columns you want to automatically fill to the RowSource for the City combobox.

Change the ColumnCount and ColumnWidths properties

on the City AfterUpdate event, set the values in the other controls.

Here is some code you can modify:

with me.City_controlname
   if isnull(.value) then exit sub
   me.State_controlname = .column(#)
end with

WHERE
# is the column number -- column index starts with 0 (zero), so column(1) is really column 2

[zmbd]

How are your comboboxes setup for the row-source, bound-column, and control-source?

IF these are bound, and setup in a normalized database, in the after_update event (as S4P has mentioned) you need to set the [controlname].value to match the value of the related table field. You might also need to me.repaint to get the form to update.

[strive4peace]

I often do what the poster is doing ... have a table of 42,000+ zip codes that I use to fill city and state.

Reference Database with Zip Codes, Countries, States, Area Codes, Airlines, Airports, ... voltages, flowers, ...
http://www.AccessMVP.com/strive4peace/Reference.htm

I did try storing FKs instead of values for city , state, and zip but it was incredibly slow and made searching and filtering more difficult ... so now I store text and autofill on zip because my zips table is not always right. This saves me time entering addresses and lets me correct the looked-up values.

[sachuchem22]

Thanks you s4p N zmbd. iam new user of access so please explaine me in simpal way
When i select A or B or C in cmbcity then cmbDist autofill with L and cmbstate with X And onselect D or E or F IN cmbcity then autofill cmbdistcwith M and cmbstate with Y.

[strive4peace]

Hi,

lets just take this one step at a time.

First, set up the combobox to show the information you want to populate automatically.

Go to the design view of your form. If you already have a combobox, great. If not, then create one.

Turn on the Property Sheet if it is not showing. Press Alt-Enter.

Click on the combobox to select it.

On the Property Sheet, clickon the DATA tab.

click in the RowSource property.

To the right, you will see 3 dots ... -- this is called the Builder Button. click on it.

You will be taken to the designer and it looks like a query. Add the other fields you want to automatically populate and display.

Pick how you want it to be sorted.

Click on the Datasheet view to make sure this is what you want.

Count the columns that you have and remember this.

Also, make a note about how wide each column should be (I used NotPad to do this).

For instance, if you have 3 columns with City, District, and State, you might write:

1.5; 1.5; .5
(this is inches -- if you are using centimeters or another unit, your widths will be different).

Close the designer and click YES to save the changes.

On the Property Sheet, set:
ColumnCount (for instance, 3)
ColumnWidths -- this will be what you wrote into NotePad.

Save the form design and go to the form view.

When you drop the list of choices for your combo box, you should also see the other 2 columns.

Once you make it this far, post back and we will help you further.

[sachuchem22]

S4p! I do this as u say so what is next step...

[strive4peace]

Great! Now you see that when you pick something from the combo box, the information you want is known too. So after you update the combo by picking a value, you want code to push that information to other controls.

Select the City control
On the Property Sheet, click on the Event tab and then in the After Update event.

Click on the Builder Button ... off to the right.

This will put you into code between the Sub declaration and End Sub

Rather than telling you exactly what to do, I will give you some code you can modify:

    with me.City_controlname
       if isnull(.value) then exit sub
       me.District_controlname = .column(#)
       me.State_controlname = .column(#)
    end with

WHERE
City_controlname is the NAME property of the city controlname
District_controlname is the NAME property of the district controlname
State_controlname is the NAME property of the district controlname

# is the respective column number for the information (remember column numbering starts at 0 so 1 is really column 2

~~~~~~~~~ Add Option Explicit ~~~~~~~~~

if the top of your module does not have a statement that says Option Explicit, then add this:

Option Explicit  ' require variable declaration

~~~~~~~~~ Compile ~~~~~~~~~

Whenever you change code, references, or switch versions or operating environment, or modify objects with code behind them, you should always compile and save before executing.

from the menu in a VBE (module) window: Debug, Compile
(Alt-F11 to switch to the code window)

Fix any errors on the yellow highlighted lines.
Add needed references and remove missing references if necessary
(from the menu: Tools, References...)

keep compiling until nothing happens (this is good!) -- then Save


also be sure, before compiling, to use Option Explicit at the very TOP of each module so variables that are not declared or are misspelled will be picked up

~~~

if you made it to here without any problem, Save and test!

[zmbd]

... be sure, before compiling, to use Option Explicit at the very TOP of each module so variables that are not declared or are misspelled will be picked up

Recommended for anyone that uses VBA in any Office installation is to make this automatic.
IN Office 2013 installations (and this works for most other versions):

Open the VBE (<alt><F11> or even <ctrl><g>)
Once open: In the menu (no ribbon here :)
Tools>Options
Editor tab:
UNCHECK-Mark the "Auto Syntax Check" option

Your code will still flag as error (typically red) if there is malformed code - just that annoying message pop-up will go away here!

CHECK-Mark the "Require Variable Declaration" option

This will now insert the Option Explicit in every new module created. This means that the programmer will now be required to include the Dim variablenamehere As typecasthere for each variable used in the code.

[OK]

[sachuchem22]

Thanks S4P. I set code as u said but is not work. the set up of cmbcity as there are 3 column 0 for city 1 for dist and 3 for state and i used code exactly as u mention so what is wrong with this. AND When i used another way as i set control source of cmbDIST As =[cmbcity].[Column](1)
and for cmbstate as=[ cmbcity]. [column](2)
and its work but a problem with this way that in form cmbdist and cmbstate were autofills but in table bound with form are not updated.what i do now?

[strive4peace]

if you are showing values, as opposed to filling them with code so they can be edited, the ControlSource needs to be blank.

If you are simply echoing information that canNOT be edited, then you will assign the ControlSource using equations and NOT use code.