Value list that changes depending on other field

[Guest]

I have a form to enter University staff details. There are 5 faculties and each faculty has a number of departments. Rather than having one large value list of all the departments. I would like (if possible) the department value list to change depending on which faculty is selected

Not sure if this is possible and I have very limited coding knowledge so speak slowly. Thanks

 

[Michel Walsh]

Hi,


In the afterupdate event of the control that holds the "faculty", say it
is control Combo1, and assuming the departments list is in the list control
listBox1, then, in AfterUpdate of Combo1:

Dim str as String
str = "SELECT department FROM Departments WHERE faculty='" &
Me.combo1 & "'"
' Debug.Print str
Me.ListBox1.RowSource=str



Note that str should be, after concatenations, should look like (if you
remove the comment in front of Debug.Print)

SELECT department FROM departments WHERE faculty='Administration'



Hoping it may help,
Vanderghast, Access MVP

I have a form to enter University staff details. There are 5 faculties

and each faculty has a number of departments. Rather than having one large
value list of all the departments. I would like (if possible) the department
value list to change depending on which faculty is selected.

Not sure if this is possible and I have very limited coding knowledge so

speak slowly. Thanks

 

[Bandit]

-----Original Message-----
I have a form to enter University staff details. There

are 5 faculties and each faculty has a number of
departments. Rather than having one large value list of
all the departments. I would like (if possible) the
department value list to change depending on which faculty
is selected.

Not sure if this is possible and I have very limited

coding knowledge so speak slowly. Thanks

.

Hi,
You need to have the user choose the first value and
then set the select box based upon that value.

cboStaff (A, B, C, D, E)
cboDepartments

cboStaff afterUpdate Event
me.cboDepartments.recordsource = "Select
Departments from tblDepartments Where tblDepartments.staff
= ' & [cboStaff] & '"

me.cboDepartments.requery



Hope this helps,
Bandit

 

[Guest]

Yes is possible, but not too of how your form is setup.

You might have to work with to list boxes - 1 for faculty and one for department.

In the AfterUpdate() event for the Faculty list box you have to assign a query (can be one for each faculty) or put together an SQL statement and assign this as recordsource to the department listbox.

Example:

Private Sub FacultySelect_AfterUpdate()

dim vlcFilter as string

' Select statement for departments
vlcFilter = "SELECT blablabla"
vlcFilter = vlcFilter & " FROM <departments or something>"
vlcFilter = vlcFilter & " HAVING ((<department>)='" & Me.FacultySelect & "')"

Me.FacultySelect.RowSource = vlcFilter
Me.FacultySelect.SetFocus ' Optional so that listbox auto
Me.FacultySelect.Dropdown ' dropdown


You can also make seperate queries for each department, but is a bit more work.

Success
Eric W

----- Clare wrote: -----

I have a form to enter University staff details. There are 5 faculties and each faculty has a number of departments. Rather than having one large value list of all the departments. I would like (if possible) the department value list to change depending on which faculty is selected.

Not sure if this is possible and I have very limited coding knowledge so speak slowly. Thanks

 

[Nikos Yannacopoulos]

Clare,

Don't worry, no code needed. Assuming the form's name is Form1, the
faculty's combo name is cboFaculty and the department combo's name is
cboDept:
1. Modify the query being cboDept 's rowsource, adding the following
criterion under the Faculty field (the field has to be there, even if you
don't show it):
Forms![Form1]![cboFaculty]
2. While in form design, select cboFaculty and display properties. On tab
Events, place the cursor in the box next to event Before Update, and then
click on the little button with the three dots that appears on the right;
select Macro Builder. You will be taken to a macro design screen; add an
action Requery, and in the box next to Control Name in the lower part of the
screen, type in the name of the department combo (cboDept). Save giving a
meaningful name. You will be taken back to the form design view, save and
you're done.

Basically, what you did is: in step 1, you added a filter on the department
combo's rowsource query, so it only returns values pertaining to the
current faculty selected in the first combo. In step 2, you created a macro
that forces the dept combo to refresh and display relevant departments only,
everytime the user changes the faculty selection in the first combo.

HTH,
Nikos

I have a form to enter University staff details. There are 5 faculties

and each faculty has a number of departments. Rather than having one large
value list of all the departments. I would like (if possible) the department
value list to change depending on which faculty is selected.

Not sure if this is possible and I have very limited coding knowledge so

speak slowly. Thanks

 

[Nikos Yannacopoulos]

Clare,

Sorry, I must have been absentminded when I read your post, my reply applies
to combo boxes, not lists. I see there are already some replies on your
question in the meanwhile, though, so I don't need to try again!

Nikos

Clare,

Don't worry, no code needed. Assuming the form's name is Form1, the
faculty's combo name is cboFaculty and the department combo's name is
cboDept:
1. Modify the query being cboDept 's rowsource, adding the following
criterion under the Faculty field (the field has to be there, even if you
don't show it):
Forms![Form1]![cboFaculty]
2. While in form design, select cboFaculty and display properties. On tab
Events, place the cursor in the box next to event Before Update, and then
click on the little button with the three dots that appears on the right;
select Macro Builder. You will be taken to a macro design screen; add an
action Requery, and in the box next to Control Name in the lower part of the
screen, type in the name of the department combo (cboDept). Save giving a
meaningful name. You will be taken back to the form design view, save and
you're done.

Basically, what you did is: in step 1, you added a filter on the department
combo's rowsource query, so it only returns values pertaining to the
current faculty selected in the first combo. In step 2, you created a macro
that forces the dept combo to refresh and display relevant departments only,
everytime the user changes the faculty selection in the first combo.

HTH,
Nikos

I have a form to enter University staff details. There are 5 faculties

and each faculty has a number of departments. Rather than having one large
value list of all the departments. I would like (if possible) the department
value list to change depending on which faculty is selected.

Not sure if this is possible and I have very limited coding knowledge so

speak slowly. Thanks

 

[Sandra Dee]

I actually have the same issue that Clare does(but with
combo boxes) and I tried your suggestion but it did not
work for me. Any other suggestions? Thanks - Sandy

-----Original Message-----
Clare,

Sorry, I must have been absentminded when I read your post, my reply applies
to combo boxes, not lists. I see there are already some replies on your
question in the meanwhile, though, so I don't need to try again!

Nikos

Clare,

Don't worry, no code needed. Assuming the form's name is Form1, the
faculty's combo name is cboFaculty and the department combo's name is
cboDept:
1. Modify the query being cboDept 's rowsource, adding the following
criterion under the Faculty field (the field has to be there, even if you
don't show it):
Forms![Form1]![cboFaculty]
2. While in form design, select cboFaculty and display properties. On tab
Events, place the cursor in the box next to event Before Update, and then
click on the little button with the three dots that appears on the right;
select Macro Builder. You will be taken to a macro design screen; add an
action Requery, and in the box next to Control Name in

the lower part of
the

screen, type in the name of the department combo (cboDept). Save giving a
meaningful name. You will be taken back to the form design view, save and
you're done.

Basically, what you did is: in step 1, you added a

filter on the
department

combo's rowsource query, so it only returns values pertaining to the
current faculty selected in the first combo. In step 2,

you created a
macro

that forces the dept combo to refresh and display

relevant departments
only,

everytime the user changes the faculty selection in the first combo.

HTH,
Nikos

There are 5 faculties
and each faculty has a number of departments. Rather than having one large
value list of all the departments. I would like (if

possible) the
department

value list to change depending on which faculty is selected. coding knowledge so
speak slowly. Thanks

.

 

[Nikos Yannacopoulos]

Sandy,

You may have a wrong reference (even a spelling mistake) somewhere. If you
want, post your design details (form name and controls, undelying table
name(s) and fields, relationships) and what you are trying to do, and I'll
give you the exact syntax so you can check.

Nikos

I actually have the same issue that Clare does(but with
combo boxes) and I tried your suggestion but it did not
work for me. Any other suggestions? Thanks - Sandy

-----Original Message-----
Clare,

Sorry, I must have been absentminded when I read your post, my reply applies
to combo boxes, not lists. I see there are already some replies on your
question in the meanwhile, though, so I don't need to try again!

Nikos

Clare,

Don't worry, no code needed. Assuming the form's name is Form1, the
faculty's combo name is cboFaculty and the department combo's name is
cboDept:
1. Modify the query being cboDept 's rowsource, adding the following
criterion under the Faculty field (the field has to be there, even if you
don't show it):
Forms![Form1]![cboFaculty]
2. While in form design, select cboFaculty and display properties. On tab
Events, place the cursor in the box next to event Before Update, and then
click on the little button with the three dots that appears on the right;
select Macro Builder. You will be taken to a macro design screen; add an
action Requery, and in the box next to Control Name in

the lower part of
the

screen, type in the name of the department combo (cboDept). Save giving a
meaningful name. You will be taken back to the form design view, save and
you're done.

Basically, what you did is: in step 1, you added a

filter on the
department

combo's rowsource query, so it only returns values pertaining to the
current faculty selected in the first combo. In step 2,

you created a
macro

that forces the dept combo to refresh and display

relevant departments
only,

everytime the user changes the faculty selection in the first combo.

HTH,
Nikos

news:42034C7B-C2C2-4446-A78C- (e-mail address removed)...
I have a form to enter University staff details. There are 5 faculties
and each faculty has a number of departments. Rather than having one large
value list of all the departments. I would like (if

possible) the
department

value list to change depending on which faculty is selected.

Not sure if this is possible and I have very limited coding knowledge so
speak slowly. Thanks

.