Automatically updating field value

Topic

New Reply

Hi,

I would like to be able to populate a field on a form with a particular value when a value in another values in another field on the same form are chosen.

The fwo fields I am referring to is ‘Portfolio’ and ‘Department’, each are drop down lists on the same form. The ‘Portfolio’ field displays values from the ‘Portfolio’ table (a single column table listing only portfolios). The ‘Department’ field displays values from the ‘Department’ table (again a single column table listing only departments).

I’m thinking of changing the ‘Portfolio’ table to 2 columns – the first column being the list of portfolios and the second column being the related department. When a user chooses a certain value from the ‘Portfolio’ field, I would like the ‘Department’ field to be filled in automatically based on values in the table. I would also like them to be able to change Department manually if they like, so I was thinking of keeping the Department field and table the way they are.

Hope this is possible and hope someone can help!

Cheers,
Jason

Reply | Quote

Replies

You could indeed add a Department column to the Portfolio table.
Then set the Column Count property of the Portfolio combo box on the form to 2.
If you want the user to see the department in the dropdown list, set the Column Widths property to (for example) 1″;1″
If you would prefere the department not to be displayed in the dropdown list, set the Column Widths property to (for example) 1″;0″
A column with width 0 will be hidden.

Create an event procedure for the After Update event of the Portfolio combo box:

Code:

Private Sub Portfolio_AfterUpdate()
  Me.Department = Me.Portfolio.Column(1)
End Sub

Columns in a combo box or list box are counted starting at 0, so Column(1) is the second column, corresponding to the Department field.

Reply | Quote

Thanks Hans for helping, that is exactly what I wanted to do.

Cheers,
Jason

You could indeed add a Department column to the Portfolio table.
Then set the Column Count property of the Portfolio combo box on the form to 2.
If you want the user to see the department in the dropdown list, set the Column Widths property to (for example) 1″;1″
If you would prefere the department not to be displayed in the dropdown list, set the Column Widths property to (for example) 1″;0″
A column with width 0 will be hidden.

Create an event procedure for the After Update event of the Portfolio combo box:

Code:

Private Sub Portfolio_AfterUpdate()
  Me.Department = Me.Portfolio.Column(1)
End Sub

Columns in a combo box or list box are counted starting at 0, so Column(1) is the second column, corresponding to the Department field.

Reply | Quote