linking fields

[bleep]

Hiya, Ive been trying to link two fields so that when i select a valu
from one, the value in the second field should automatically appear.

I have two tables: Lessons and Actionee Details.

The actionee details table has four fields but i am only interested i
two of them: Name and department. Data in the fields for the Actione
details is manually entered by the user (department can b
automatically selected by a subform combo box). The values of Name an
department are to be used in the Lessons table. I've set up a looku
column for the Name (read in from the Actionee Details table) I a
trying to make it so that when a Name in the Lessons table is selecte
the department related to that name should automatically appear i
another field (department). I;ve been trying for ages but cant seem t
get it to work. Is it possible to do this?

Please could someone help me on this as im really struggling

Thanks
Fay

 

[Polly]

I'm going to try and reply, I hope this helps you...

1) On the lookup field that you've set up for [Name] in the Lesson
table, set it so that it shows the name and department when selected
(I don't know that this needs to be done, but it's how I have it i
mine and I haven't tried it without...)

2) On the form where you want the [Name] and [Department] informatio
to be entered:

Go to design view. Select the [Name] control.
Right click, and select 'Properties'.
On the 'Event' tab, click in the 'After Update' box.
Now select the three dots button (it has got a name, but I can'
remember, looks like '...'
Choose 'Code Builder' from the 3 options.

Underneath 'Private Sub Name_AfterUpdate()' write:
Me![Department] = Me![Name].Column2()

Save and return to Access.

Column 2 is if your department field is the second column, and name i
in the first column.

Hope this helps your struggle, know the feeling (craftily worked thi
out from one of the Access samples, but it took a while)

Poll

 

[bleep]

Thank u so much!!! :

 

[SteveS]

Hiya, Ive been trying to link two fields so that when i select a value
from one, the value in the second field should automatically appear.

I have two tables: Lessons and Actionee Details.

The actionee details table has four fields but i am only interested in
two of them: Name and department. Data in the fields for the Actionee
details is manually entered by the user (department can be
automatically selected by a subform combo box). The values of Name and
department are to be used in the Lessons table. I've set up a lookup
column for the Name (read in from the Actionee Details table) I am
trying to make it so that when a Name in the Lessons table is selected
the department related to that name should automatically appear in
another field (department). I;ve been trying for ages but cant seem to
get it to work. Is it possible to do this?

Please could someone help me on this as im really struggling

Thanks
Faye

Faye,

If by lookup column you mean a combo box, check out the .Columns() property.

Basically, in the Rowsource for the comob box, you need (at least) two columns -
Name and Department.

BTW, using 'Name' as a field name (if you are) is a bad choice because it is a
reserved word (as is Date) in Access. FirstName, txtName, Act_Name, etc would be
better choices.

OK, back to the matter at hand.

After the two fields are in the rowsource, in the AfterUpdate event of the combo
box, add the line:

Me.txtDepartment = me.comboName.columns(1)

The columns are zero based, so the second column is column 1. If Department is
in the 4th column, then it would be .Columns(3)

Change txtDepartment and comboName to the names you used. Also change the column
number to what ever the column 'Department' is in the combo rowsource.

Whenever you select a name, the afterUpdate event fires and stuffs the
Department into the text box.


If you still have trouble, post the SQL of the combo box rowsource and the names
of the combo box and the department text box.