Autopopulate fields based on value in one field in the same form

[muddasirmasood]

I am trying to make database for my company. Was just working on makingdatabase entry easier by autopopulating fields in form based on the selectionof a value in one field in the same form? I have City, State and Post code inmy main table to be filled out for a particular costumer. What I wanna do in myform is select city from a combo box named “city” and it should autopopulatethe fields “state” and “post code” in the same form. The code that I havewritten in afterchange event of “city” is below


Private Sub cboCity_Change()
Me.txtState.Value = Me.cboCity.Column(1)
Me.txtPCode.Value = Me.cboCity.Column(2)
End Sub

But when I run this code and select a value from thedropdown list of “city” the field of "state" autopopulates but "Post code" doesnotfor some reason I don’t know why? I have tried everything but couldn’t work itout.




Please help, thanks and regards,

Muddasir Masood.

[Bob Fitz]

Can you show us the SQL statement used as the Row Source property of the combo box and also tell us what you have for its Column Widths and Column Count properties.

[ranman256]

You cant populate state and zip based on city. 1 city has many zip codes (and states)
You CAN popuplate city,state based on Zip.
Youd need the 40000 zip codes to to this. (i have this)

Unless you are talking about filling in the city,state,zip for an existing customer in the db.
Then you just filter the recordset based on CO id.

[muddasirmasood]

Hello Ranman:

Thanks for reply. I am filling in information for new costumers. Why I want to populate other fields based on city is it's mostly known and can be selected easily. I am trying to do for Australian areas. I have a table with the whole list of cities, state and postal codes, just couldn't get post code selected when I select city, surprisingly it's showing up state but not the post code... :-(

[ranman256]

Youd have to have the combo based on a query that looks at the city value.
As the city changes, the zip code query would too and give new zip codes based on that.

[muddasirmasood]

Sorry Bob Fitz: saw your message only today



The sql statement for the combo box is as below





SELECT TBL_Pcode.Locality, TBL_Pcode.State, TBL_Pcode.Pcode,TBL_Pcode.PCodeID FROM TBL_Pcode ORDER BY TBL_Pcode.Locality;





Column count is 1 and there is no column width… it doesn’tshow anything… thanks, hope you help me come out with this glitch… thanks onceagain. J

[Bob Fitz]

Try this:
Change the Column Count property to 3
Set the Column Widths property to 1;0;0;
Set the Control Source property of your text box "txtState" to:
= cboCity.Column(1)
Set the Control Source property of your text box "txtPCode" to:
= cboCity.Column(2)

[muddasirmasood]

Thanks Bob Fitzpatrick :-)

That works :-) just a little question, what does column count and column width has to do with it.. just for information... :-)

[Bob Fitz]

Thanks Bob Fitzpatrick :-)

That works :-) just a little question, what does column count and column width has to do with it.. just for information... :-)

Good. Glad that worked for you
The Column Count determines how many columns will be taken from the query or SQL statement used in the Row Source property and used in the Combo/Listbox.
The Column Widths property determines how wide each of the columns should be. If a column width is set to 0; then the column will be there but it can not be seen. In yours, the first column is now set to 1; and is visible. This figure should be adjusted to display a column wide enough to display the longest value in the list. The other two columns are set to o; and are therefore not visible to users when looking at the list, but the values are still in the column so they can be referenced by other controls or VBA code. For example:
Your control "txtState" is now references the second column with = cboCity.Column(1)
Please note that the column index starts with 0 so the first column is .Column(0) and the second is .Column(1)
Hope this helps.