Refereshing the value of the selected combobox based on the values of other 4 combo box

[Jose]

Please help to resolve this issue.

In Microsoft access form I am having 5 combo boxes each seperately
grouping values from a table "Jobs". The first one groups "Area",second
combo box groups the "Supervisors", Third one groups "Priority", Forth
one Groups "Jobtype" and last one groups the "Planner". I have to
filter the form based on the values selected in these combo box.

The issue is the user may select any of the five combo box and all
other combo boxed shall be updated based on this selection. Similarly
the user will select any other combo box from the remaining four. Now
the rest of the como boxes has to update based on these two values.
This will continue till he selected the last combo. Also the user may
select only one or two combo box and then when they press the command
button for Filter Jobs, The Form shall filter for those selected values
only.

 

[Marshall Barton]

In Microsoft access form I am having 5 combo boxes each seperately
grouping values from a table "Jobs". The first one groups "Area",second
combo box groups the "Supervisors", Third one groups "Priority", Forth
one Groups "Jobtype" and last one groups the "Planner". I have to
filter the form based on the values selected in these combo box.

The issue is the user may select any of the five combo box and all
other combo boxed shall be updated based on this selection. Similarly
the user will select any other combo box from the remaining four. Now
the rest of the como boxes has to update based on these two values.
This will continue till he selected the last combo. Also the user may
select only one or two combo box and then when they press the command
button for Filter Jobs, The Form shall filter for those selected values
only.

The second and later combo box's RowSource should be a query
that has a criteria that refers to the previous combo box.
Each query will be something like:
SELECT supervisorID, supervisor
FROM Supervisors
WHERE supervisorarea = Forms!theform.cboArea
(change all the names to the ones you are using)

Then add code to each combo box's AfterUpdate event like
this:
Sub cboArea_AfterUpdate()
cboSupervisor = Null
cboPriority = Null
cboJobtype = Null
cboPlanner = Null
cboSupervisor.Requery
End Sub

Sub cboSupervisor_AfterUpdate()
cboPriority = Null
cboJobtype = Null
cboPlanner = Null
cboPriority .Requery
End Sub

. . .

 

[Jose]

Thanks for this . The only concern is that i can not fix any combo box
as the first one . User may select any of the five. It may be area or
supurvisor etc. Once he selected any of the 5 combo box all others has
to filter the value to the first one.

 

[Marshall Barton]

Thanks for this . The only concern is that i can not fix any combo box
as the first one . User may select any of the five. It may be area or
supurvisor etc. Once he selected any of the 5 combo box all others has
to filter the value to the first one.

I am going to take some time to think about this. But first
I have some questions. What does it mean to select an entry
from a dependent list when nothing is specified for the
parent list? Maybe the dependent list is not filtered so it
displays all items?? Or do you have an arrangement where
each list is not really dependent on a parent list???

It almost sounds as if you don't really have dependent
lists. Instead you seem to have one set of records with 5
independent fields and you want to filter the records by any
combination of the fields.

 

[Jose]

Yes, that is right,

I am having one set of records with 5 independent fields and I want to
filter the records by any
combination of the fields.

 

[Marshall Barton]

Yes, that is right,

I am having one set of records with 5 independent fields and I want to
filter the records by any
combination of the fields.

First, make sure that the search/filter combo boxes (in the
form's header section?) are unbound (i.e. blank
ControlSource).

I will assume that the form's RecordSource is just a table
or query name. If you have an SQL statement as the record
source, this suggestion will have to be modified
appropriately. The code in your button's Click event should
have this kind of structure:

Dim strSQL As String
Dim strWhere As String
If Not IsNull(Me.combo1) Then
strWhere = strWhere & " AND field1=" & Me.combo1
End If
If Not IsNull(Me.combo2) Then
strWhere = strWhere & " AND field2=" & Me.combo2
End If
. . .
strSQL = "SELECT * FROM table WHERE " & Mid(strWhere, 6)
Me.RecordSource = strSQL

That code assumes all of the table fields are numeric. if a
field is Text:
. . . & " AND fieldT=""" & Me.comboT & """"
and Date fields:
. . . & " AND fieldD=" & FORMAT(Me.comboD,
"\#m\/d\/yyyy\#")

be sure to change all the field and control names to the
ones you are using.

 

[Jose]

Thank you very much for your help