Cascading

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have the following code that I need to choose and item in the cboDepartment
and I need it to pull from specific tables based on what is chosen in the
department. The problem is that it is not populating the cboReportName. Here
is what I have:

Private Sub cboDepartment_AfterUpdate()
On Error Resume Next
Select Case cboDepartment.Value
Case "Business Cards"
cboReportName.RowSource = "tblManualWrkListBusCards"
Case "Canada CREW"
cboReportName.RowSource = "tblManualWrkListCanadaCREW"
Case "Canada FEW"
cboReportName.RowSource = "tblManualWrkListCanadaFEW"
Case "CITS"
cboReportName.RowSource = "tblManualWrkListCITS"
Case "CREW"
cboReportName.RowSource = "tblManualWrkListCREW"
Case "Identity Fraud"
cboReportName.RowSource = "tblManualWrkListIDFraud"
Case "RS"
cboReportName.RowSource = "tblManualWrkListRS"
Case "Smith Barney"
cboReportName.RowSource = "tblManualWrkListSmithBarney"
Case "Transaction Fraud"
cboReportName.RowSource = "tblManualWrkListTransFraud"
End Select
End Sub
 
I have the following code that I need to choose and item in the cboDepartment
and I need it to pull from specific tables based on what is chosen in the
department. The problem is that it is not populating the cboReportName. Here
is what I have:

Private Sub cboDepartment_AfterUpdate()
On Error Resume Next
Select Case cboDepartment.Value
Case "Business Cards"
cboReportName.RowSource = "tblManualWrkListBusCards"
Case "Canada CREW"
cboReportName.RowSource = "tblManualWrkListCanadaCREW"
Case "Canada FEW"
cboReportName.RowSource = "tblManualWrkListCanadaFEW"
Case "CITS"
cboReportName.RowSource = "tblManualWrkListCITS"
Case "CREW"
cboReportName.RowSource = "tblManualWrkListCREW"
Case "Identity Fraud"
cboReportName.RowSource = "tblManualWrkListIDFraud"
Case "RS"
cboReportName.RowSource = "tblManualWrkListRS"
Case "Smith Barney"
cboReportName.RowSource = "tblManualWrkListSmithBarney"
Case "Transaction Fraud"
cboReportName.RowSource = "tblManualWrkListTransFraud"
End Select
End Sub
Click to expand...

owwwwww.....

You're on the wrong track. Sorry, but you really are.

If you have a separate TABLE for each list, you're storing data in
tablenames. A much better solution might be to combine all these
ManualWrkLst tables into *ONE* table, with an additional field with
values "Business Cards", "Canada CREW", "Canada FEW", etc. - or
(perhaps better) with a numeric ID linked to a table with that number
as its primary key and these values as text strings (the Department
table).

You can then use a Query

SELECT <whatever fields>
FROM <this new table>
WHERE DepartmentID = Forms!yourformname!cboDepartment
ORDER BY <whatever is appropriate>

as the rowsource of cboReportName. Requery this combo in the
AfterUpdate event of cboDepartment... and you're done, and have a much
simpler database structure to manage as a bonus.

John W. Vinson[MVP]
 
Thank you for letting me know. I set it up the way that you suggested, with
all the Manual Work in one table. Now when I am trying to set up the form
this is what I have. Under the Department in the ROW SOURCE I have :

SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER BY
tblAllManWrkList.Deparment;

And in the After update of that combo box I have:

Private Sub cboDepartment_AfterUpdate()
On Error Resume Next
cboReportName.RowSource = "Select tblAllManWrkList.ReportName " & _
"FROM tblAllManWrkList " & _
"WHERE tblAllManWrkList.Department = '" &
cboDepartment.Value & "' " & _
"ORDER BY tblAllManWrkList.ReportName;"

When I attempt to choose something from the Department combo box I receive
the following error:

ORDER BY clause (tblAllManWrkList.Department) conflicts with DISTINCT

I'm not sure exactly what I need to change. Can you please help me out?
Thank you.
 
SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER BY
tblAllManWrkList.Deparment;

And in the After update of that combo box I have:

Private Sub cboDepartment_AfterUpdate()
On Error Resume Next
cboReportName.RowSource = "Select tblAllManWrkList.ReportName " & _
"FROM tblAllManWrkList " & _
"WHERE tblAllManWrkList.Department = '" &
cboDepartment.Value & "' " & _
"ORDER BY tblAllManWrkList.ReportName;"

When I attempt to choose something from the Department combo box I receive
the following error:

ORDER BY clause (tblAllManWrkList.Department) conflicts with DISTINCT

I'm not sure exactly what I need to change. Can you please help me out?
Click to expand...

It may be as simple as a typo: you have ORDER BY
tblAllManWrkList.Deparment - no "t" in Department!

You can actually simplify your code considerably; you don't need to
keep changing cboReportName's Row Source. It can be permanently

"SELECT ReportName FROM tblAllManWrkList WHERE Department = '" _
& [Forms]![YourFormName]![cboDepartment] & "' ORDER BY ReportName;"

and in the AfterUpdate event of cboDepartment, simply requery it:

Me.cboReportName.Requery

John W. Vinson [MVP]
 
I want to start off by saying I appreciate all your help. I took your advice
and set the cboReportName's rowsource permanently, this is what I have

SELECT ReportName FROM tblAllManWrkList WHERE Department = '" &
[Forms]![frmManualWorkInput]![cboDepartment] & "' ORDER BY ReportName;"

In the cboDepartment I have the following as the rowsource

SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER BY
tblAllManWrkList.Department

and in the After Update I have

[event procedure] and in the code I have

Me.cboReportName.Requery

I have my list that shows up in the department, but there is nothing in the
report name combo box.






John W. Vinson said:
SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER BY
tblAllManWrkList.Deparment;

And in the After update of that combo box I have:

Private Sub cboDepartment_AfterUpdate()
On Error Resume Next
cboReportName.RowSource = "Select tblAllManWrkList.ReportName " & _
"FROM tblAllManWrkList " & _
"WHERE tblAllManWrkList.Department = '" &
cboDepartment.Value & "' " & _
"ORDER BY tblAllManWrkList.ReportName;"

When I attempt to choose something from the Department combo box I receive
the following error:

ORDER BY clause (tblAllManWrkList.Department) conflicts with DISTINCT

I'm not sure exactly what I need to change. Can you please help me out?
Click to expand...

It may be as simple as a typo: you have ORDER BY
tblAllManWrkList.Deparment - no "t" in Department!

You can actually simplify your code considerably; you don't need to
keep changing cboReportName's Row Source. It can be permanently

"SELECT ReportName FROM tblAllManWrkList WHERE Department = '" _
& [Forms]![YourFormName]![cboDepartment] & "' ORDER BY ReportName;"

and in the AfterUpdate event of cboDepartment, simply requery it:

Me.cboReportName.Requery

John W. Vinson [MVP]
Click to expand...
 
Exactly what is being returned by
[Forms]![frmManualWorkInput]![cboDepartment]: is it the name of the
department, or the Id of the department? (Check by putting MsgBox
[Forms]![frmManualWorkInput]![cboDepartment] into your code). Is that the
value that's stored as Department in tblAllManWrkList?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Welthey said:
I want to start off by saying I appreciate all your help. I took your
advice
and set the cboReportName's rowsource permanently, this is what I have

SELECT ReportName FROM tblAllManWrkList WHERE Department = '" &
[Forms]![frmManualWorkInput]![cboDepartment] & "' ORDER BY ReportName;"

In the cboDepartment I have the following as the rowsource

SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER BY
tblAllManWrkList.Department

and in the After Update I have

[event procedure] and in the code I have

Me.cboReportName.Requery

I have my list that shows up in the department, but there is nothing in
the
report name combo box.






John W. Vinson said:
SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER
BY
tblAllManWrkList.Deparment;

And in the After update of that combo box I have:

Private Sub cboDepartment_AfterUpdate()
On Error Resume Next
cboReportName.RowSource = "Select tblAllManWrkList.ReportName " & _
"FROM tblAllManWrkList " & _
"WHERE tblAllManWrkList.Department = '" &
cboDepartment.Value & "' " & _
"ORDER BY tblAllManWrkList.ReportName;"

When I attempt to choose something from the Department combo box I
receive
the following error:

ORDER BY clause (tblAllManWrkList.Department) conflicts with DISTINCT

I'm not sure exactly what I need to change. Can you please help me out?
Click to expand...

It may be as simple as a typo: you have ORDER BY
tblAllManWrkList.Deparment - no "t" in Department!

You can actually simplify your code considerably; you don't need to
keep changing cboReportName's Row Source. It can be permanently

"SELECT ReportName FROM tblAllManWrkList WHERE Department = '" _
& [Forms]![YourFormName]![cboDepartment] & "' ORDER BY ReportName;"

and in the AfterUpdate event of cboDepartment, simply requery it:

Me.cboReportName.Requery

John W. Vinson [MVP]
Click to expand...
Click to expand...
 
I have that code written in the RowSource for the cboReportName

I tried to add the MsgBox
[Forms]![frmManualWorkInput]![cboDepartment] to the end of that but nothing
different happened



Douglas J. Steele said:
Exactly what is being returned by
[Forms]![frmManualWorkInput]![cboDepartment]: is it the name of the
department, or the Id of the department? (Check by putting MsgBox
[Forms]![frmManualWorkInput]![cboDepartment] into your code). Is that the
value that's stored as Department in tblAllManWrkList?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Welthey said:
I want to start off by saying I appreciate all your help. I took your
advice
and set the cboReportName's rowsource permanently, this is what I have

SELECT ReportName FROM tblAllManWrkList WHERE Department = '" &
[Forms]![frmManualWorkInput]![cboDepartment] & "' ORDER BY ReportName;"

In the cboDepartment I have the following as the rowsource

SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER BY
tblAllManWrkList.Department

and in the After Update I have

[event procedure] and in the code I have

Me.cboReportName.Requery

I have my list that shows up in the department, but there is nothing in
the
report name combo box.






John W. Vinson said:
On Tue, 6 Feb 2007 05:50:01 -0800, Welthey

SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER
BY
tblAllManWrkList.Deparment;

And in the After update of that combo box I have:

Private Sub cboDepartment_AfterUpdate()
On Error Resume Next
cboReportName.RowSource = "Select tblAllManWrkList.ReportName " & _
"FROM tblAllManWrkList " & _
"WHERE tblAllManWrkList.Department = '" &
cboDepartment.Value & "' " & _
"ORDER BY tblAllManWrkList.ReportName;"

When I attempt to choose something from the Department combo box I
receive
the following error:

ORDER BY clause (tblAllManWrkList.Department) conflicts with DISTINCT

I'm not sure exactly what I need to change. Can you please help me out?

It may be as simple as a typo: you have ORDER BY
tblAllManWrkList.Deparment - no "t" in Department!

You can actually simplify your code considerably; you don't need to
keep changing cboReportName's Row Source. It can be permanently

"SELECT ReportName FROM tblAllManWrkList WHERE Department = '" _
& [Forms]![YourFormName]![cboDepartment] & "' ORDER BY ReportName;"

and in the AfterUpdate event of cboDepartment, simply requery it:

Me.cboReportName.Requery

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
If that SQL is the RowSource, try simply putting

SELECT ReportName FROM tblAllManWrkList WHERE Department =
[Forms]![frmManualWorkInput]![cboDepartment] ORDER BY ReportName

What you had would be appropriate if you were setting the RowSource of the
control in the AfterUpdate module for cboDepartment:

Private Sub cboDepartment_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT ReportName FROM tblAllManWrkList " & _
"WHERE Department = '" & _
[Forms]![frmManualWorkInput]![cboDepartment] & _
"' ORDER BY ReportName;"
Me.cboReportName.RowSource = strSQL

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Welthey said:
I have that code written in the RowSource for the cboReportName

I tried to add the MsgBox
[Forms]![frmManualWorkInput]![cboDepartment] to the end of that but
nothing
different happened



Douglas J. Steele said:
Exactly what is being returned by
[Forms]![frmManualWorkInput]![cboDepartment]: is it the name of the
department, or the Id of the department? (Check by putting MsgBox
[Forms]![frmManualWorkInput]![cboDepartment] into your code). Is that the
value that's stored as Department in tblAllManWrkList?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Welthey said:
I want to start off by saying I appreciate all your help. I took your
advice
and set the cboReportName's rowsource permanently, this is what I have

SELECT ReportName FROM tblAllManWrkList WHERE Department = '" &
[Forms]![frmManualWorkInput]![cboDepartment] & "' ORDER BY ReportName;"

In the cboDepartment I have the following as the rowsource

SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER
BY
tblAllManWrkList.Department

and in the After Update I have

[event procedure] and in the code I have

Me.cboReportName.Requery

I have my list that shows up in the department, but there is nothing in
the
report name combo box.






:

On Tue, 6 Feb 2007 05:50:01 -0800, Welthey

SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList
ORDER
BY
tblAllManWrkList.Deparment;

And in the After update of that combo box I have:

Private Sub cboDepartment_AfterUpdate()
On Error Resume Next
cboReportName.RowSource = "Select tblAllManWrkList.ReportName " &
_
"FROM tblAllManWrkList " & _
"WHERE tblAllManWrkList.Department = '" &
cboDepartment.Value & "' " & _
"ORDER BY tblAllManWrkList.ReportName;"

When I attempt to choose something from the Department combo box I
receive
the following error:

ORDER BY clause (tblAllManWrkList.Department) conflicts with DISTINCT

I'm not sure exactly what I need to change. Can you please help me
out?

It may be as simple as a typo: you have ORDER BY
tblAllManWrkList.Deparment - no "t" in Department!

You can actually simplify your code considerably; you don't need to
keep changing cboReportName's Row Source. It can be permanently

"SELECT ReportName FROM tblAllManWrkList WHERE Department = '" _
& [Forms]![YourFormName]![cboDepartment] & "' ORDER BY ReportName;"

and in the AfterUpdate event of cboDepartment, simply requery it:

Me.cboReportName.Requery

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
I have updated everything and when I choose the department the only thing
that comes in the reportname drop down box is reportname. I have checked to
make sure that I have everything spelled right. Do you know what direction
to point me in?

Douglas J. Steele said:
If that SQL is the RowSource, try simply putting

SELECT ReportName FROM tblAllManWrkList WHERE Department =
[Forms]![frmManualWorkInput]![cboDepartment] ORDER BY ReportName

What you had would be appropriate if you were setting the RowSource of the
control in the AfterUpdate module for cboDepartment:

Private Sub cboDepartment_AfterUpdate()
Dim strSQL As String

strSQL = "SELECT ReportName FROM tblAllManWrkList " & _
"WHERE Department = '" & _
[Forms]![frmManualWorkInput]![cboDepartment] & _
"' ORDER BY ReportName;"
Me.cboReportName.RowSource = strSQL

End Sub

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Welthey said:
I have that code written in the RowSource for the cboReportName

I tried to add the MsgBox
[Forms]![frmManualWorkInput]![cboDepartment] to the end of that but
nothing
different happened



Douglas J. Steele said:
Exactly what is being returned by
[Forms]![frmManualWorkInput]![cboDepartment]: is it the name of the
department, or the Id of the department? (Check by putting MsgBox
[Forms]![frmManualWorkInput]![cboDepartment] into your code). Is that the
value that's stored as Department in tblAllManWrkList?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I want to start off by saying I appreciate all your help. I took your
advice
and set the cboReportName's rowsource permanently, this is what I have

SELECT ReportName FROM tblAllManWrkList WHERE Department = '" &
[Forms]![frmManualWorkInput]![cboDepartment] & "' ORDER BY ReportName;"

In the cboDepartment I have the following as the rowsource

SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList ORDER
BY
tblAllManWrkList.Department

and in the After Update I have

[event procedure] and in the code I have

Me.cboReportName.Requery

I have my list that shows up in the department, but there is nothing in
the
report name combo box.






:

On Tue, 6 Feb 2007 05:50:01 -0800, Welthey

SELECT DISTINCT tblAllManWrkList.Department FROM tblAllManWrkList
ORDER
BY
tblAllManWrkList.Deparment;

And in the After update of that combo box I have:

Private Sub cboDepartment_AfterUpdate()
On Error Resume Next
cboReportName.RowSource = "Select tblAllManWrkList.ReportName " &
_
"FROM tblAllManWrkList " & _
"WHERE tblAllManWrkList.Department = '" &
cboDepartment.Value & "' " & _
"ORDER BY tblAllManWrkList.ReportName;"

When I attempt to choose something from the Department combo box I
receive
the following error:

ORDER BY clause (tblAllManWrkList.Department) conflicts with DISTINCT

I'm not sure exactly what I need to change. Can you please help me
out?

It may be as simple as a typo: you have ORDER BY
tblAllManWrkList.Deparment - no "t" in Department!

You can actually simplify your code considerably; you don't need to
keep changing cboReportName's Row Source. It can be permanently

"SELECT ReportName FROM tblAllManWrkList WHERE Department = '" _
& [Forms]![YourFormName]![cboDepartment] & "' ORDER BY ReportName;"

and in the AfterUpdate event of cboDepartment, simply requery it:

Me.cboReportName.Requery

John W. Vinson [MVP]
Click to expand...
Click to expand...
Click to expand...
 
I have updated everything and when I choose the department the only thing
that comes in the reportname drop down box is reportname. I have checked to
make sure that I have everything spelled right. Do you know what direction
to point me in?
Click to expand...

Does *a* report name field value come up in the combo box? or the
listeral text string "reportname"?

Is the RowSource of the combo box in fact a Query:

SELECT ReportName FROM tblAllManWrkList
WHERE Department = [Forms]![frmManualWorkInput]![cboDepartment]
ORDER BY ReportName;

which is what Douglas and I were both suggestiong?

If so, does tblAllManWrkList contain fields named ReportName and
Department?

John W. Vinson [MVP]
 
You must log in or register to reply here.
Back
Top