Auto field update on combo selection

  • Thread starter Thread starter Noel
  • Start date Start date
N

Noel

Hi. Ive posted something similar to this recently and had
some help from Larry Linson but the thread got messy (my
fault) I thought a repost with clarification would be
best. In very general terms the question is this, is it
possible for Access to automatically take a copy of the
value in one records field and use it to update another
records field, based on the users selection in a Combo
box.?

Heres more detail. Say I have a form with a control
source as tblStudents and a combo box in a subform that
allows the users to select a School from tblSchools.
This puts the SchoolID in tblPlacements. Each record in
tblPlacements therefore records a Student placement in a
School. Now say I have a field called PlacementStartYear
in tblSchools and one with the same name in
tblPlacements. What I want is, once the user selects a
particular School in the combo box, access automatically
goes to that record in tblSchools, takes note of the
value in field PlacementStartYear and uses that value to
update the PlacementStartYear field in the current
tblPlacements record. The operative word here is
automatic. I know I could just provide a txt field in the
subform for the user to enter the PlacementStartYear in
the Placement record but I think Access could be made to
do this automatically. Also, this automatic updating of
the PlacementStartYear field should happen when the
School is either first selected or when it is reselected
(the users often have to change the placement school).
Lastly, another major aspect is that the
PlacementStartYear value in tblSchools changes from one
year to the next so the idea is to ensure that the value
automatically entered in the Placements record is the
value current at the time the Placements record was
either created or updated. The
tblPlacements.PlacementStartYear value must not track the
changes to the tblSchools.PlacementStartYear field.

Is this possible? Could this be done by building some
sort of Event code associated with the combo boxes
AfterUpdate or On Change property? Are there any
examples anywhere showing this type of thing? Sorry if
this has been very longwinded. Thanks for your time.
Cheers, Noel
 
yes, try the following idea:


Create the combo box on the tblSchools. Make it return the following 3
columns

SchooldId SchoolName SchoolYear

When you select the combo box, the SchooldId is stored just the way you
want. To grab the SchoolYear, you simply add the following line of code to
your after update event of the combo box:

me!PlacementStartYear = me.ComboBoxName.Column(2)

Note that the column function will gab the column from the current combo box
setting.

However, what you are asking to do kind of violate relational design, since
you already have the SchoolID, and can use a relation join to get the
SchoolYear. If there is NO need to change or the PlacementStartYeer will NOT
be different from the SchooYear field, then you SHOULD NOT copy the data. By
no copying the data, you are free to change the SchoolYear value and NOT
have to go around and update all the records. this is really nice when the
person who entered the School record made a mistake, or in fact if you need
to change the year, and have all records show this change. if you use a
relation, then no update code is needed, since you did no copy that year
over and over.

So, the above one line of code will do the trick, but think very hard about
this, as really, you don't need to copy that year data, since you can ALWAYS
get the year by looking at the SchoolId. You can even place a un-bound text
box next to the combo box, and have it display the year data like:

=([comboBoxName].[collumn(2)])

So, once again, it seems you have little reason to copy the year data.
 
Thanks for this Albert. It looks like this could be the
answer but I need to clarify a couple of points. First,
you mention a SchoolYear field but I didnt mention one.
Ive got two fields called PlacementStartYear, one in
tblSchools and one in tblPlacements. So I presume you
mean my three combo columns should be
SchoolID SchoolName PlacementStartYear.

Also your line of code reads:
me!PlacementStartYear = me.ComboBoxName.Column(2)
should that be
me!PlacementStartYear = me.ComboBoxName.Column(3)

As for your last point, perhaps I need to explain this
better. Yes I take your point about being able to always
see the PlacementStartYear by looking at the SchoolID but
thats the whole point. I dont want my Placements records
to reflect the current value of PlacementStartYear at all
times. I want them to reflect the value as it was the
last time that that school was selected in the combo. I
specifically dont want the value in the
PlacementsStartYear field in tblPlacements to change when
the tblSchools.PlacementStartYear value changes.

Anyway. Ill have a play with your suggestion and will get
back to you. Thanks again.
-----Original Message-----
yes, try the following idea:


Create the combo box on the tblSchools. Make it return the following 3
columns

SchooldId SchoolName SchoolYear

When you select the combo box, the SchooldId is stored just the way you
want. To grab the SchoolYear, you simply add the following line of code to
your after update event of the combo box:

me!PlacementStartYear = me.ComboBoxName.Column(2)

Note that the column function will gab the column from the current combo box
setting.

However, what you are asking to do kind of violate relational design, since
you already have the SchoolID, and can use a relation join to get the
SchoolYear. If there is NO need to change or the PlacementStartYeer will NOT
be different from the SchooYear field, then you SHOULD NOT copy the data. By
no copying the data, you are free to change the SchoolYear value and NOT
have to go around and update all the records. this is really nice when the
person who entered the School record made a mistake, or in fact if you need
to change the year, and have all records show this change. if you use a
relation, then no update code is needed, since you did no copy that year
over and over.

So, the above one line of code will do the trick, but think very hard about
this, as really, you don't need to copy that year data, since you can ALWAYS
get the year by looking at the SchoolId. You can even place a un-bound text
box next to the combo box, and have it display the year data like:

=([comboBoxName].[collumn(2)])

So, once again, it seems you have little reason to copy the year data.

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn


.
Click to expand...
 
Hi again Albert, Ive tried this and it doesnt do quite
what Id expected. I really need the After Update Event to
take the combo value and auto inject it into the
PlacementStartYear field in the current tblPlacements
record. The way it works at present is that the combo
value is being put in the field in the form thats being
used to create or modify the record.

Can the Event code put the combo value directly into the
records field? If so what would it be?

Again, thanks for the help, Noel
 
Hi Albert. Sorry to mess you about but Ive now got this
working, so no need to respond. I had got my control
sources mixed up. Thank you for the help - it does
exactly what I want. Cheers, Noel
 
Back
Top