Auto-Populating Fields based on a combo box in another field.

[sao]

I am currently using Access 2000.

In my table it is the following fields that are set up:

1 - Staff Name
2 - Department

This table is already populated with 100+ records (staff along with
department.)

I have a form set up that has a combo box that allows the user to
select the "Staff Name" and I have managed to get it to automatically
complete the "Department" field of the form with the relevant staff
member's department.

This was done by changing the "control source" property to the below:

=[Staff Name].Column(1)

....where "Staff Name" is column "0" of the data table, and Department
is Column 1 (as above). This results in corresponding "Department" to
whichever "Staff Name" is chosen from the table.

THE PROBLEM:
************

When selecting a member of staff from the drop down list of the combo
box, although the "Department" field shows that staff member's
Department, it does not show in the new table I created. In other
words, anything that is generated automatically based on the selection
of the combo box is not being written to the table.

I am experiencing the same problem with a different field that I have
where the date for that field is calculated automatically, the date
that shows up in the form is not saved to the table so there's no date
data when it comes to reporting!
If this doesn't make sense, its probably my wording, apologies in
advance for that!

Nevertheless, any help would be very gratefully recieved!!

[Ed Marzan]

Hello,

For new records, try using the AfterUpdate event of the combo to set
the value of the control on the subform

Something like

Sub MyCombo_AfterUpdate()

MyControl =[Staff Name].Column(1)

End Sub

For existing records you'll probably need to write an update query

HTH

Ed Marzan
NYC
sa*@kha.org.uk (sao) wrote in message news:<5d**************************@posting.google. com>...

I am currently using Access 2000.

In my table it is the following fields that are set up:

1 - Staff Name
2 - Department

This table is already populated with 100+ records (staff along with
department.)

I have a form set up that has a combo box that allows the user to
select the "Staff Name" and I have managed to get it to automatically
complete the "Department" field of the form with the relevant staff
member's department.

This was done by changing the "control source" property to the below:

=[Staff Name].Column(1)

...where "Staff Name" is column "0" of the data table, and Department
is Column 1 (as above). This results in corresponding "Department" to
whichever "Staff Name" is chosen from the table.

THE PROBLEM:
************

When selecting a member of staff from the drop down list of the combo
box, although the "Department" field shows that staff member's
Department, it does not show in the new table I created. In other
words, anything that is generated automatically based on the selection
of the combo box is not being written to the table.

I am experiencing the same problem with a different field that I have
where the date for that field is calculated automatically, the date
that shows up in the form is not saved to the table so there's no date
data when it comes to reporting!
If this doesn't make sense, its probably my wording, apologies in
advance for that!

Nevertheless, any help would be very gratefully recieved!!

[sao]

Thanks very much for your advice, I have tried what you suggested with
no luck. Still no data in the field when looking at the table.

I would be grateful if you would give me more info on the update
statement you mentioned?

Thanks
sao

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Ed Marzan]

Well, since I generally have no life send me a copy of your db with
the name of the form in question and I'll have a look. :)

It will be easier for both of us. If I solve your problem I will post
the code to the group.

HTH

EM
sao <sao@_NO_SPAM_kha.org.uk> wrote in message news:<3f***********************@news.frii.net>...

Thanks very much for your advice, I have tried what you suggested with
no luck. Still no data in the field when looking at the table.

I would be grateful if you would give me more info on the update
statement you mentioned?

Thanks
sao

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!