A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

populate field from combo box (again)



 
 
Thread Tools Display Modes
  #1  
Old April 14th, 2010, 01:06 AM posted to microsoft.public.access.forms
deb
external usenet poster
 
Posts: 898
Default populate field from combo box (again)

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
--
deb
  #2  
Old April 14th, 2010, 01:40 AM posted to microsoft.public.access.forms
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default populate field from combo box (again)

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.


"deb" wrote in message
...
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
--
deb



  #3  
Old April 14th, 2010, 01:42 AM posted to microsoft.public.access.forms
Douglas J. Steele[_3_]
external usenet poster
 
Posts: 3,143
Default populate field from combo box (again)

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"deb" wrote in message
...
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
--
deb


  #4  
Old April 14th, 2010, 03:07 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default populate field from combo box (again)

On Tue, 13 Apr 2010 17:06:01 -0700, deb wrote:

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.
--

John W. Vinson [MVP]
  #5  
Old April 14th, 2010, 03:16 AM posted to microsoft.public.access.forms
deb
external usenet poster
 
Posts: 898
Default populate field from combo box (again)

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


"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"deb" wrote in message
...
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
--
deb


.

  #6  
Old April 14th, 2010, 03:20 AM posted to microsoft.public.access.forms
deb
external usenet poster
 
Posts: 898
Default populate field from combo box (again)

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


"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"deb" wrote in message
...
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
--
deb


.

  #7  
Old April 14th, 2010, 03:28 AM posted to microsoft.public.access.forms
John W. Vinson
external usenet poster
 
Posts: 18,261
Default populate field from combo box (again)

On Tue, 13 Apr 2010 19:16:01 -0700, deb wrote:

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?
--

John W. Vinson [MVP]
  #8  
Old April 14th, 2010, 03:39 AM posted to microsoft.public.access.forms
deb
external usenet poster
 
Posts: 898
Default populate field from combo box (again)

yay, fixed it

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

thanks!
--
deb


"Douglas J. Steele" wrote:

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)



"deb" wrote in message
...
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
--
deb


.

  #9  
Old April 14th, 2010, 04:11 AM posted to microsoft.public.access.forms
deb
external usenet poster
 
Posts: 898
Default populate field from combo box (again)

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)


--
deb


"John W. Vinson" wrote:

On Tue, 13 Apr 2010 19:16:01 -0700, deb wrote:

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?
--

John W. Vinson [MVP]
.

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:35 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.