PK in combo box automatically populates two other text boxes

[CoddFish]

I have a form that contains a combo-box where the user selects an AreaID (the bound PK for tblArea). This primary key is associated with records that contain three other columns of data:
County, ZipCode, and FacilityType.

There are two text boxes on the same form that I would like to automatically populate with the County and ZipCode data that correspond with the AreaID chosen in the above-described combo box.

How can I do this?

thanks...

 

[Calvin]

This is very cool but it will take a few steps, and I will try to cover everything.

first - change the RowSource of the combobox to somthing like this

SELECT [AreaID], [County], [ZipCode], [FacilityType] FROM MyTable ORDER BY [County], [ZipCode]

change MyTable to the appropriate table name

second - on the combo box properties window set the following:
Column Count = 4
Column Widths = 0,1,.5,1
Bound Column = 1
List Width = 2.5

i like setting the following also
List Row = 12
Limit to List = Yes

now that should take care of the combo box displayed data

next lets handle the AfterUpdate event
it should look something like this:

Private Sub Combo1_AfterUpdate()
If Me.Combo1 = "" Then Exit Sub

Me.Text1 = Me.Combo1.Column(1) 'County
Me.Text2 = Me.Combo1.Column(2) 'ZipCode
Me.Text3 = Me.Combo1.Column(3) 'FacilityType

End Sub

 

[CoddFish]

This works beautifully! Thanks Calvin!

-CoddFish

 

[maya]

calvin,

i tried ur suggestion but it doesnt seem to work for me...if i use ur statement for the combobox it appear to have a few columns in it which is not what im looking for...my form basically looks like this

Country: (combobox)
State: (Combobox)
.
.
.
other information in textbox...

so how do i insert the countries name into the combobox without repeating any of the name coz as u know in the database, a countries name will appear more than once for each states that relates to it....hope u could give me another way...thanx

best regards
Maya

 

[Calvin]

Insert DISTINCT into the select statement for the Country combobox, like so:

SELECT DISTINCT [Country] FROM MyTable ORDER BY [Country]

and add an AfterUpdate event to the Country combobox that will set the rowsource of the state combobox, like so:

Private Sub cboCountry_AfterUpdate()

me.cboState.rowsource = "SELECT DISTINCT [State],[Data1],[Data2] FROM MyTable ORDER BY [State]"

End Sub

change the combobox properties to something similar to the following:
Column Count = 3
Column Widths = 1,.5,1
Bound Column = 1
List Width = 2.5

and in the AfterUpdate event for the State combobox, do like so:

Private Sub cboState_AfterUpdate()

me.Text1=me.cboState.Column(1) 'Data1
me.Text2=me.cboState.Column(2) 'Data2

End Sub

if you editing the row source in the query builder, inserting DISTINCT into the SQL statement is the same as setting the query property "Unique Values" = Yes

 

[maya]

don undersatnd

why are u inserting the Data1 and Data2 in the combobox as well? cant it just appear in the textbox? Thanx

 

[maya]

And for ur information, my database has 18 columns, two of which represents the country and state and the others is in textboxes....should be a better way rite?? Thanx...

 

[Calvin]

You insert multiple columns in a combobox and when you select something like State the two columns of data next to is is related and you can, through code, auto insert it into another field.

Ok, for example, I use this one a lot, you have a list of zip codes

97223
97008
97228
98035

now when you select one of those zip codes you want the County, City and State to auto populate so there is no need for user input or error

so you temporarily store and populate your dropdown list, and would look like this (essentially)

97223|Washington|Tigard|OR
97008|Washington|Beaverton|OR
97228|Multnohma|Portland|OR
98035|Clark|Vancouver|WA

now lets say you select 97008 from the drop down list, the list will close and the only visible data is the 97008 zipcode, and I want to see or automatically instert the related data into other fields, and since the 97008 is currently selected we've basically selected Washington County, the City of Tigard and the State of Oregon but that data is not currently stored in the datatable only the zipcode is. so we have to write a small amount of code to do that for us.

As you asked, the Data1 and Data2 have to come from somewhere and the easiest place to temporarily store it and capture it is in the combobox.

I hope this helps.

 

[maya]

ok

yes, i understand what u are saying, but cant we refer the information for each zipcode straight from the table....that means the information for each zipcode need not be displayed in the combo so that the user would not get confused with what the other columns r doing in the combobox....

anyway im still having problem linking the country combo with the state....whenever i click a country, say US, all the states of other countries is displayed as well....i think something wrong with my SQL statement...is there any other way? thanx for helping me...

 

[Calvin]

Sorry, you mentioned this earlier and I over looked it

....whenever i click a country, say US, all the states of other countries is displayed

Use a WHERE condition in the SQL statement

me.cboState.rowsource = "SELECT DISTINCT [State],[Data1],[Data2] FROM MyTable WHERE [Country]='" & me.cboCountry & "' ORDER BY [State];"

if you think the multiple columns is confusing to the user then
keep them but set the following properties:
Column Count = 3
Column Widths = 1,0,0
Bound Column = 1
List Width = 1

by changing the column widths to 1,0,0 you are basically telling the combo box that there is data in columns 1 and 2 but you want a width of zero inches so noone can see the data

if you want to auto populate other textboxes on the form with data relating to the State you select in your combobox there are other ways but I feel this is the simplest.

 

[Calvin]

Hope you get it worked out, if you have any more questions they will have to be answered by someone else or wait till morning.

I'm off to bed.

good (yaaaawn) night...

 

[maya]

wow...it works perfectly...thanx a lot calvin...now i just have to load all the text and i wish there would be a more effective way coz im dealing with 16 Textboxes and that means the information in the combobox will have 16 columns as well....hope u could suggest something more efficient...Thanx anyway

 

[maya]

good nite...hope u or other angel programmers around could me again...

 

[Calvin]

not gone yet...

dang, you caught me...

well you have to load and select and temporarily store the data somewhere before you pass it on and other ways are more code intensive and you will basically be doing twice what loading up the combobox lets you do once.

now I'm off to slee...

 

[maya]

Hi Calvin and everyone...its me again
im doing fine with the combobox but now i have problem in autopopulating the texboxes....the way that u gave me doesnt seem to work....any other ideas? Thanx

 

[Calvin]

Hi Maya, welcome back,

I have to ask you to be more specific, what does your code look like? what action method are you using from the combo box to invoke the sub that auto populates?

 

[maya]

method

im using the country_afterupdate method

is it correct or should i put it in the state_afterupdate or state_change method?

Thanx

 

[Calvin]

yes, the only thing that your Country_AfterUpdate should be doing is setting state.rowsource = sqlstatement

your State_AfterUpdate is what you need to trigger the autopopulate action

 

[Calvin]

Like I already posted above

Private Sub cboState_AfterUpdate() 

me.Text1=me.cboState.Column(1) 'Data1 
me.Text2=me.cboState.Column(2) 'Data2 

End Sub

 

[maya]

Thanx a thrillion...it works absolutely well now...
all the best to you and Take Care!!