Auto populating fields

[Guest]

Hi,

I have a look-up table with data fields X, Y, and Z. I have a form where I
want a drop-down box to select a value X, and then auto-populate Y and Z
based on what's selected for X. X, Y, and Z values should all be written to
my main table.

Thanks for your help!
Heather

 

[luanhoxung]

see the answer from here.
http://www.mvps.org/access/forms/frm0028.htm
hope this help.
Luan from VietNam.

 

[luanhoxung]

see the answer from here.
http://www.mvps.org/access/forms/frm0028.htm
hope this help.
Luan from VietNam.

 

[John Vinson]

Hi,

I have a look-up table with data fields X, Y, and Z. I have a form where I
want a drop-down box to select a value X, and then auto-populate Y and Z
based on what's selected for X. X, Y, and Z values should all be written to
my main table.

Thanks for your help!
Heather

Generally you should NOT need to do this (there are exceptions, such
as storing a current price with the expectation that the price might
change in the future). Storing data redundantly is almost always a Bad
Idea.

What are these fields?

John W. Vinson[MVP]

 

[John Vinson]

I got it working... thanks!

If the job Family, Code and Title are all interrelated, then you
should store only the most stable of them (the job code presumably).
The Family and Title can simply be looked up as needed from a table of
Jobcodes, can they not? Is it important to permanently record what the
JobTitle of a given job code WAS at the time an employee was entered
into the table, even if that job title subsequently changes?

Just FWIW, you could simplify this to:

varCode = DLookup("JobCode", "JobTitleLookup", _
"[JobTitle] = '" & [JobTitle] & "'")

Your previous code with [JobTitle] = [JobTitle] was merely looking up
the first record in the table where the job title was equal to itself
(and of course all the rcords in the table would qualify, unless the
Jobtitle were NULL!)

John W. Vinson[MVP]

 

[Guest]

I have found a simple way of doing it.
Place the code in the After Update [Event Procedure] of your drop down box

Me![X field] = Me![drop-down box ].Column(0)
Me![Y field] = Me![drop-down box ].Column(1)
Me![Z field] = Me![drop-down box ].Column(2)

Column (0) eguals the first field, Column(1) eguals the second field,
Column(2) equals the third field that you have in your drop-down box.

The "row source" would be name of your "drop down box". Culumn count equals
"3"
Column Widths could be set at 0.5";0";0";


sandrao

 

[antman142]

Hi,

I have a look-up table with data fields X, Y, and Z. I have a form where I
want a drop-down box to select a value X, and then auto-populate Y and Z
based on what's selected for X. X, Y, and Z values should all be written to
my main table.

Thanks for your help!
Heather

Hello, to build on what Sandrao suggested your code could also look like this:


Private Sub Combo_AfterUpdate ()

Me.Xfield.Value = Me.Combo.Column(0) ' value of X
Me.Yfield.Value = Me.Combo.Column(1) ' value of Y
Me.Zfield.Value = Me.Combo.Column(2) ' value of Z

end sub

by using the Me. (dot), it allows you to take advantage of intellisense.

Antman