Subform Fields

[Beany]

Hi,

Possibly a simple question..

I have a table called User with fields: User, Department and Location..

In a subform, i have the same three fields..

in the subform, i would like to select a user from the user field and it automatically populates the department and location fields (info from the User Table)?? can you please let me know if this can be done?

thanks in advance for any info

 

[Dennisk]

Are you using a sub form instead of a combo box to populate controls.

 

[Beany]

ive got a subform, and within the subform i have three comboboxes, user, department and location.

how do i get info into the fields department and location when i select a user from User combobox (in subform)?

data can be extracted from the tblUser.

 

[Dennisk]

create a query that links the user to the Dept and display the Department and location. In the after update event of the combo box set the control to the required column of the combo box.

 

[Beany]

do you have an example i can look at?

for some reason i cant get it to work... im relatively new to access..

thanks

 

[Dennisk]

in the after update event of the combo box

me!YourControlNameHere = me!yourComboBoxHere.Column where n is the number of the column.
If the control you want to populate is on another form then include the full reference i.e.

Forms!YourFormName!YourSubFormName!YourControlName

 

[Beany]

your info is so correct but when im working with it, it aint gona work...

ive attached my database, it would be nice of you to have a look and see what im doing wrong...

thanks

 

[Dennisk]

sorry cannot download databases. Dept. policy.

 

[Beany]

Dennisk, i understand mate, youve been excellent help mate..

Is there anyone that can have a look at my database? please

thanks

 

[Velosai]

Ok, this should do it

Step one
Change the row source for the user combo to:

SELECT User.User, User.Department, User.Location
FROM [User];

Step two
Change Department and Location to text boxes

Step three
Source for Department should be "=Me.User.Column(1)"
Source for Location should be "=Me.User.Column(2)"

That should hopefully work

 

[Rich]

Me. won't work in the control source, it should be just = [User].Column(1)

 

[Beany]

Velosai and Rich -----------> thank you

User combo now populates Department but for some reason it will not populate my location textbox... why is that?

ive checked all steps you told me to carry out..

 

[Velosai]

Can you just post what you put in the control for location

 

[Beany]

ive got the following in my control for location:

=User.Column(2)

 

[Velosai]

Sorry my bad, you need to change column count field in properties for the user combo box from 1 to 3 and hey presto, it will work

 

[Beany]

Velosai,

its working... thank you