populate field from combo box (again)

[deb]

sorry, can't seem to wrap my mind around this and i know its simple

i have an input form for property details - PropertyInputFrm

i have a table with suburb, state and postcode - PostCodesTbl

i want to be able to select the suburb with a combo box and have the state
and postcode populate automatically

i'm thinking its Dlookup

 

[Jeff Boyce]

Are you absolutely certain that the same postal code is not used by more
than one suburb? (I seem to recall running into a local postal code that
applied to two "cities"...)

If you are trying to do this so that the table underlying your
PropertyInputFrm stores the state and postcode, stop now! You don't need to
redundantly store those if you already have a one-for-one connection between
the suburb and the state/postcode (but see above!).

Instead, you could add two unbound textboxes on your form, and in the
AfterUpdate event of the combobox, you could "push" the values of the
selected suburb's state and postcode into those two textboxes. No, you
wouldn't be storing them, just displaying them.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[Douglas J. Steele]

Easiest way is to have the state and postcode fields in the combo box and
simply grab their values in the combo box's AfterUpdate event. Assuming your
combo box is named cboSuburb, state is in column 2 and postcode is in column
3, you'd use something like:

Private Sub cboSuburb_AfterUpdate()

Me!txtState = Me!cboSuburb.Column(1)
Me!txtPostcode = Me!cboSuburb.Column(2)

End Sub

Note that the Column collection starts numbering at 0.

It's not necessary to have the state and postcode visible in the combo box
(you can control this by setting the combo box's ColumnWidths property
appropriately), but it is critical that the ColumnCount property is set
correctly.

 

[John W. Vinson]

i want to be able to select the suburb with a combo box and have the state
and postcode populate automatically

You almost certainly do NOT want to do this.

Copying the state and the postcode from the table of suburbs into some other
table wastes disk space, and more importantly risks invalid data. Those fields
should exist in the Suburbs table - the rowsource of your combo box - and in
NO OTHER TABLE.

You can *display* them on a form by using textboxes with a control source like

=comboboxname.Column(n)

where n is the zero based index of the state or postcode field; and you can
display them on a report by basing the report on a query joining this table to
the table of suburbs, by suburb.

 

[deb]

yep, this is the way i want to do it, to answer both of you, no i'm not
wanting to story the state and postcode, just display

Douglass - the code isnt working and i cant figure out why

column count is 3
bound column is 1
column widths 2.54cm;0cm;0cm

and i've put the code in the after update event procedure of the combo box

its selecting the suburb fine just not updating the other fields

 

[deb]

i've got no primary key in the postcode table, is that an issue?

 

[John W. Vinson]

its selecting the suburb fine just not updating the other fields

It shouldn't "update" any fields.

It should DISPLAY the field values in other textboxes.

Is it not doing so?

 

[deb]

yay, fixed it

i just redid it all and created all the fields again and it worked

thanks!

 

[deb]

is now

i realised what was wrong was the name of the boxes - mine didnt have txt in
the name

all fixed

thanks for your help (again, i think you helped me last time too)