HELP with Cascading Combo

[Guest]

I know this subject has been written about to death but I am just plain stuck
with an error in the AfterUpdate Event I cannot get rid of.

The RowSource of CBO_Issue:

SELECT DISTINCT Reasons.Issue FROM Reasons;

Then

Private Sub CBO_Issue_AfterUpdate()
Cbo_Reason.RowSource = "SELECT Reasons.Reason" & _
"FROM Reasons" & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Me.Cbo_Reason.Requery
End Sub

And I would like the values chosen to populate the underlying table
[Issue].[Emp_Issue] and [Issue].[Reason]

Could someone help me with this please?

Thank you

 

[Douglas J. Steele]

What's the error you're getting?

What's the data type of Reasons.Issue? If it's numeric, you don't need the
single quotes you've got there.

Are you sure that CBO_Issue.Value is returning the value you think it is?

 

[Guest]

Thanks for helping Doug:

The data type for all is Text.

The error I am getting:

"A problem occurred while the database was communicating with the OLE Server
or Active X Control"

I have no clue
--
Jeff C
Live Well .. Be Happy In All You Do

What's the error you're getting?

What's the data type of Reasons.Issue? If it's numeric, you don't need the
single quotes you've got there.

Are you sure that CBO_Issue.Value is returning the value you think it is?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I know this subject has been written about to death but I am just plain
stuck
with an error in the AfterUpdate Event I cannot get rid of.

The RowSource of CBO_Issue:

SELECT DISTINCT Reasons.Issue FROM Reasons;

Then

Private Sub CBO_Issue_AfterUpdate()
Cbo_Reason.RowSource = "SELECT Reasons.Reason" & _
"FROM Reasons" & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Me.Cbo_Reason.Requery
End Sub

And I would like the values chosen to populate the underlying table
[Issue].[Emp_Issue] and [Issue].[Reason]

Could someone help me with this please?

Thank you

 

[Douglas J. Steele]

Hold on. I just noticed your SQL is incorrect: you're missing spaces between
words.

What you've got is going to come out as:

SELECT Reasons.ReasonFROM ReasonsWHERE Reasons.Issue ...

Try:

Cbo_Reason.RowSource = "SELECT Reasons.Reason " & _
"FROM Reasons " & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for helping Doug:

The data type for all is Text.

The error I am getting:

"A problem occurred while the database was communicating with the OLE
Server
or Active X Control"

I have no clue
--
Jeff C
Live Well .. Be Happy In All You Do

What's the error you're getting?

What's the data type of Reasons.Issue? If it's numeric, you don't need
the
single quotes you've got there.

Are you sure that CBO_Issue.Value is returning the value you think it is?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I know this subject has been written about to death but I am just plain
stuck
with an error in the AfterUpdate Event I cannot get rid of.

The RowSource of CBO_Issue:

SELECT DISTINCT Reasons.Issue FROM Reasons;

Then

Private Sub CBO_Issue_AfterUpdate()
Cbo_Reason.RowSource = "SELECT Reasons.Reason" & _
"FROM Reasons" & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Me.Cbo_Reason.Requery
End Sub

And I would like the values chosen to populate the underlying table
[Issue].[Emp_Issue] and [Issue].[Reason]

Could someone help me with this please?

Thank you

 

[Guest]

I have never been so frustrated with an Access issue as this. I cannot count
the times I have deleted the things and started over only to continue getting
the same error. Doug, I pasted your SQL into the AfterUpdate Property and
still got the same error.

This is not supposed to be this difficult.
--
Jeff C
Live Well .. Be Happy In All You Do

Hold on. I just noticed your SQL is incorrect: you're missing spaces between
words.

What you've got is going to come out as:

SELECT Reasons.ReasonFROM ReasonsWHERE Reasons.Issue ...

Try:

Cbo_Reason.RowSource = "SELECT Reasons.Reason " & _
"FROM Reasons " & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thanks for helping Doug:

The data type for all is Text.

The error I am getting:

"A problem occurred while the database was communicating with the OLE
Server
or Active X Control"

I have no clue
--
Jeff C
Live Well .. Be Happy In All You Do

What's the error you're getting?

What's the data type of Reasons.Issue? If it's numeric, you don't need
the
single quotes you've got there.

Are you sure that CBO_Issue.Value is returning the value you think it is?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I know this subject has been written about to death but I am just plain
stuck
with an error in the AfterUpdate Event I cannot get rid of.

The RowSource of CBO_Issue:

SELECT DISTINCT Reasons.Issue FROM Reasons;

Then

Private Sub CBO_Issue_AfterUpdate()
Cbo_Reason.RowSource = "SELECT Reasons.Reason" & _
"FROM Reasons" & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Me.Cbo_Reason.Requery
End Sub

And I would like the values chosen to populate the underlying table
[Issue].[Emp_Issue] and [Issue].[Reason]

Could someone help me with this please?

Thank you

 

[Douglas J. Steele]

Try

Dim strSQL As String

strSQL = "SELECT Reasons.Reason " & _
"FROM Reasons " & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Debug.Print strSQL

Cbo_Reason.RowSource = strSQL

Once the code's run, go to the Immediate Window (Ctrl-G) and see what's been
written there. Does it look correct? Copy it to the clipboard, then go to
create a new query. Rather than selecting any tables for the query, shut
down the selection dialog and go into the SQL view (you can get to it
through the View menu). Paste the SQL there and try to run the query. Does
it work?

The RowSourceType for Cbo_Reason is Table/Query I hope.

 

[Guest]

Doug:
I cut and pasted what you wrote into the AfterUpdate Event of cbo_Issue:
Dim strSQL As String

strSQL = "SELECT Reasons.Reason " & _
"FROM Reasons " & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Debug.Print strSQL

Cbo_Reason.RowSource = strSQL


The Immediate Window is blank

 

[Douglas J. Steele]

That implies that your AfterUpdate event isn't firing. Look at the
properties of cbo_Issue. Does the AfterUpdate event say [Event Procedure]?
(The property can accidentally get turned off. One reason is if you cut and
paste the control.)

 

[Guest]

Yes, when looking in the properties tab at the AfterUpdate Event

[Event Procedure] is listed in the blank

when opening the code window, underneath "Option Compare Database":


Private Sub cbo_Issue_AfterUpdate(Cancel As Integer)

Dim strSQL As String

strSQL = "SELECT Reasons.Reason " & _
"FROM Reasons " & _
"WHERE Reasons.Issue = '" & cbo_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Debug.Print strSQL

Cbo_Reason.RowSource = strSQL


End Sub



--
Jeff C
Live Well .. Be Happy In All You Do

That implies that your AfterUpdate event isn't firing. Look at the
properties of cbo_Issue. Does the AfterUpdate event say [Event Procedure]?
(The property can accidentally get turned off. One reason is if you cut and
paste the control.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Guest]

Doug:

I began one more time with a NEW form

Your solution works now.

I amagine I corrupted the thing with all the cutting and pasting I did, as
you thought.

Thanks for all your time and help this morning
--
Jeff C
Live Well .. Be Happy In All You Do

That implies that your AfterUpdate event isn't firing. Look at the
properties of cbo_Issue. Does the AfterUpdate event say [Event Procedure]?
(The property can accidentally get turned off. One reason is if you cut and
paste the control.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

 

[Douglas J. Steele]

That makes no sense to me at all.

Put a breakpoint in there, and see if you can single-step through it.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Yes, when looking in the properties tab at the AfterUpdate Event

[Event Procedure] is listed in the blank

when opening the code window, underneath "Option Compare Database":


Private Sub cbo_Issue_AfterUpdate(Cancel As Integer)

Dim strSQL As String

strSQL = "SELECT Reasons.Reason " & _
"FROM Reasons " & _
"WHERE Reasons.Issue = '" & cbo_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Debug.Print strSQL

Cbo_Reason.RowSource = strSQL


End Sub



--
Jeff C
Live Well .. Be Happy In All You Do

That implies that your AfterUpdate event isn't firing. Look at the
properties of cbo_Issue. Does the AfterUpdate event say [Event
Procedure]?
(The property can accidentally get turned off. One reason is if you cut
and
paste the control.)

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Doug:
I cut and pasted what you wrote into the AfterUpdate Event of
cbo_Issue:
Dim strSQL As String

strSQL = "SELECT Reasons.Reason " & _
"FROM Reasons " & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Debug.Print strSQL

Cbo_Reason.RowSource = strSQL


The Immediate Window is blank


--
Jeff C
Live Well .. Be Happy In All You Do


:

Try

Dim strSQL As String

strSQL = "SELECT Reasons.Reason " & _
"FROM Reasons " & _
"WHERE Reasons.Issue = '" & CBO_Issue.Value & "' " & _
"ORDER BY Reasons.Reason;"

Debug.Print strSQL

Cbo_Reason.RowSource = strSQL

Once the code's run, go to the Immediate Window (Ctrl-G) and see
what's
been
written there. Does it look correct? Copy it to the clipboard, then go
to
create a new query. Rather than selecting any tables for the query,
shut
down the selection dialog and go into the SQL view (you can get to it
through the View menu). Paste the SQL there and try to run the query.
Does
it work?

The RowSourceType for Cbo_Reason is Table/Query I hope.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have never been so frustrated with an Access issue as this. I
cannot
count
the times I have deleted the things and started over only to
continue
getting
the same error. Doug, I pasted your SQL into the AfterUpdate
Property
and
still got the same error.

This is not supposed to be this difficult.