i have some trouble with queries and combo boxes.

[Guest]

i had another post, but only one answer , from John Vinson, and if he reads
this post, i hope that the information that i`ll put here will be of any help
to understand what i`m trying to say..
i`m rather new to access and vba, so please indulge me.

I`ll try to be a little more specific.
I have several departaments, in which diferent people have diferent jobs.
the first combo box is the selection of the departament.
the second one selects the job, and the third one, are the employees.
I use tables to store the data.

what i want to do, is something like this:
1st -> 2nd -> 3rd or
1st -> 3rd.

I`ll explain.

the first combo box gives us the jobs, and the jobs gives us the people.
But, in some cases i don`t need to select a job, so, what i want to do is,
that if I don`t select a job, in the third combo box i would have all the
people in the departament selected in the first combo.

The problem is, that i can make the selection, in VBA, but i don`t know how
to do it for 2 critereas.
the first case: 1st -> 2nd -> 3rd, i did it, but i don`t know how to do this
thing: if a don`t select a job, i want all the people in the departament.

the vba code for this selection is:

Private Sub First_combo_AfterUpdate()
Me![Third_combo].Requery
End Sub

As i said, i`m a newbie..

 

[[MVP] S.Clark]

In the After Update of the first combo, you would use something like:

strSQL = "Select * from tablename where fieldname = " & cbo1
cbo2.rowsource = strSQL
cbo2.requery
cbo3.rowsource = strSQL
cbo3.requery

---

In the After Update of the 2nd combo...

if not isnull(cbo1) then
strW = "Fieldname1 = " & cbo1
end if
if not isnull(cbo2) then
if len(strW) > 0 then strW = strW & " AND " 'single line if statement
strW = strW & "Fieldname2 = " & cbo2
end if

strSQL = "Select * from tablename"
if len(strW) > 0 then strSQL = strSQL & " WHERE " & strW
cbo3.rowsource = strSQL
cbo3.requery

Simple, huh?

 

[John Spencer]

What is the row source for the third combobox? You may need to change it
with VBA to solve your problem or you may be able to make a simple change to
it to solve the problem.

Also, I find you explanation a bit confusing with First, Second, and Third
comboboxes. You say in one line that the First combo box selects Department
and then later your say the first combo gives us the jobs. I suggest you
refer consistently to the comboboxes as DeptCombo, JobCombo, and
EmployeeCombo to keep down the confusion.

Somy understanding is

If you select an item in DeptCombo and JobCombo is blank, you want
EmployeeCombo to show all employees for the selected item in DeptCombo.

If you select an item in DeptCombo and JobCombo, you want the EmployeeCombo
to filter the list to only employees that meet both criteria.

What you haven't told us is the relationships between the tables. Does an
employee belong to only one department - there is ONE field in the employee
table for department? Does an employee have only one job - there is ONE
field in the employee table for job?

Probably most of this can be reasoned out if you post the rowsource for the
EmployeeCombo.

 

[Guest]

i`m using different tables...
does this code still aply??

In the After Update of the first combo, you would use something like:

strSQL = "Select * from tablename where fieldname = " & cbo1
cbo2.rowsource = strSQL
cbo2.requery
cbo3.rowsource = strSQL
cbo3.requery

---

In the After Update of the 2nd combo...

if not isnull(cbo1) then
strW = "Fieldname1 = " & cbo1
end if
if not isnull(cbo2) then
if len(strW) > 0 then strW = strW & " AND " 'single line if statement
strW = strW & "Fieldname2 = " & cbo2
end if

strSQL = "Select * from tablename"
if len(strW) > 0 then strSQL = strSQL & " WHERE " & strW
cbo3.rowsource = strSQL
cbo3.requery

Simple, huh?

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

i had another post, but only one answer , from John Vinson, and if he reads
this post, i hope that the information that i`ll put here will be of any
help
to understand what i`m trying to say..
i`m rather new to access and vba, so please indulge me.

I`ll try to be a little more specific.
I have several departaments, in which diferent people have diferent jobs.
the first combo box is the selection of the departament.
the second one selects the job, and the third one, are the employees.
I use tables to store the data.

what i want to do, is something like this:
1st -> 2nd -> 3rd or
1st -> 3rd.

I`ll explain.

the first combo box gives us the jobs, and the jobs gives us the people.
But, in some cases i don`t need to select a job, so, what i want to do is,
that if I don`t select a job, in the third combo box i would have all the
people in the departament selected in the first combo.

The problem is, that i can make the selection, in VBA, but i don`t know
how
to do it for 2 critereas.
the first case: 1st -> 2nd -> 3rd, i did it, but i don`t know how to do
this
thing: if a don`t select a job, i want all the people in the departament.

the vba code for this selection is:

Private Sub First_combo_AfterUpdate()
Me![Third_combo].Requery
End Sub

As i said, i`m a newbie..

 

[[MVP] S.Clark]

The tables would be referenced properly in the SQL string.


--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

i`m using different tables...
does this code still aply??

In the After Update of the first combo, you would use something like:

strSQL = "Select * from tablename where fieldname = " & cbo1
cbo2.rowsource = strSQL
cbo2.requery
cbo3.rowsource = strSQL
cbo3.requery

---

In the After Update of the 2nd combo...

if not isnull(cbo1) then
strW = "Fieldname1 = " & cbo1
end if
if not isnull(cbo2) then
if len(strW) > 0 then strW = strW & " AND " 'single line if statement
strW = strW & "Fieldname2 = " & cbo2
end if

strSQL = "Select * from tablename"
if len(strW) > 0 then strSQL = strSQL & " WHERE " & strW
cbo3.rowsource = strSQL
cbo3.requery

Simple, huh?

--
Steve Clark, Access MVP
http://www.fmsinc.com/consulting
*FREE* Access Tips: http://www.fmsinc.com/free/tips.html

i had another post, but only one answer , from John Vinson, and if he
reads
this post, i hope that the information that i`ll put here will be of
any
help
to understand what i`m trying to say..
i`m rather new to access and vba, so please indulge me.

I`ll try to be a little more specific.
I have several departaments, in which diferent people have diferent
jobs.
the first combo box is the selection of the departament.
the second one selects the job, and the third one, are the employees.
I use tables to store the data.

what i want to do, is something like this:
1st -> 2nd -> 3rd or
1st -> 3rd.

I`ll explain.

the first combo box gives us the jobs, and the jobs gives us the
people.
But, in some cases i don`t need to select a job, so, what i want to do
is,
that if I don`t select a job, in the third combo box i would have all
the
people in the departament selected in the first combo.

The problem is, that i can make the selection, in VBA, but i don`t know
how
to do it for 2 critereas.
the first case: 1st -> 2nd -> 3rd, i did it, but i don`t know how to do
this
thing: if a don`t select a job, i want all the people in the
departament.

the vba code for this selection is:

Private Sub First_combo_AfterUpdate()
Me![Third_combo].Requery
End Sub

As i said, i`m a newbie..