N
Newbie
HELP! I have been getting lost in the forum - trying all the different ways
to create two cascading combo boxes in the header of my form that also find a
record in the details of my form depending on the results of the two
choices-I think I have the various ways all-mixed-up.
I want the first Combo box [ComboStatus] to filter out the choices of
various types of contracts/contract prospects, using a table of possibilities
created from a make table query of this column into a table of possibilities
and then eliminated the duplicates.
I want the second Combo box [ComboClient] to allow you to select a Client
from a list of results of the contract/prospect type choice chosen from the
of the first Combo box [ComboStatus]. The second combo box should only show
the choices appropriate for the choice made in the first combo box
[ComboStatus]. After making a choice in the second combo box, I want the
form to display the information from the query in the details section of the
form for the record chosen (which is using the same query to display this).
I’ve done this before, at another company for which I worked, and know it is
possible. I remember having a similar problem, I am afraid that I am going
through the same wrong steps that I went through before and may need to
reprogram my mind to not do this in the future if I ever figure out what the
heck I am doing. I have spent way too much time trying to figure this out
(about three days) for something that I have done before. I feel guilty
asking “how†when I know that I should remember. It was several years ago
though and I have not had the opportunity to use it since then.
(from one of the posts that I read)
I had this code in the rowsource of the second combo box
{ SELECT Agenda and RFQ Details Query_Client FROM Agenda and RFQ Details
Query WHERE Agenda and RFQ Details Query_Client=ComboStatus.value }
This was after trying another way…that failed.
At that time, before the code above (from another post) I thought the first
combo box was suppose to take care of the rowsource of the second box. I
then removed that text and blanked-out the row source of the second combo
box.
THIS IS WHAT I HAVE NOW
This is the code in the rowsource of the first combo box [ComboStatus]:
SELECT DISTINCT [Agenda and RFQ COMBO MAKE TABLE].[Type of Contract] FROM
[Agenda and RFQ COMBO MAKE TABLE];
In the after_update of the first combo box I have this code:
Private Sub ComboStatus_AfterUpdate()
On Error Resume Next
ComboClient.RowSource = "Select Agenda and RFQ Details Query_Client " & _
"FROM Agenda and RFQ Details Query " & _
"WHERE Agenda and RFQ Details Query.Contract Status Text = '" &
ComboStatus.Value & "' " & _
"ORDER BY Agenda and RFQ Details Query_Client"
End Sub
The first combo works fine, of course. But the second is blank and I get
the syntax error (I think from the AfterUpdate of the first combo) of “Syntax
error (missing operator) in query expression ‘Agenda and RFQ Details
Query_Client’â€
At one time, one of the iterations did have the whole list of clients – but
not filtered by the first box
Another time, I only got one item on the list, the first.
At this time, I don’t even know how I got the results from “At one time…â€
or ever “Another time…â€
Do you think I’m lost???
I know this isn’t that hard – what am I doing wrong – I think it is
something simple.
to create two cascading combo boxes in the header of my form that also find a
record in the details of my form depending on the results of the two
choices-I think I have the various ways all-mixed-up.
I want the first Combo box [ComboStatus] to filter out the choices of
various types of contracts/contract prospects, using a table of possibilities
created from a make table query of this column into a table of possibilities
and then eliminated the duplicates.
I want the second Combo box [ComboClient] to allow you to select a Client
from a list of results of the contract/prospect type choice chosen from the
of the first Combo box [ComboStatus]. The second combo box should only show
the choices appropriate for the choice made in the first combo box
[ComboStatus]. After making a choice in the second combo box, I want the
form to display the information from the query in the details section of the
form for the record chosen (which is using the same query to display this).
I’ve done this before, at another company for which I worked, and know it is
possible. I remember having a similar problem, I am afraid that I am going
through the same wrong steps that I went through before and may need to
reprogram my mind to not do this in the future if I ever figure out what the
heck I am doing. I have spent way too much time trying to figure this out
(about three days) for something that I have done before. I feel guilty
asking “how†when I know that I should remember. It was several years ago
though and I have not had the opportunity to use it since then.
(from one of the posts that I read)
I had this code in the rowsource of the second combo box
{ SELECT Agenda and RFQ Details Query_Client FROM Agenda and RFQ Details
Query WHERE Agenda and RFQ Details Query_Client=ComboStatus.value }
This was after trying another way…that failed.
At that time, before the code above (from another post) I thought the first
combo box was suppose to take care of the rowsource of the second box. I
then removed that text and blanked-out the row source of the second combo
box.
THIS IS WHAT I HAVE NOW
This is the code in the rowsource of the first combo box [ComboStatus]:
SELECT DISTINCT [Agenda and RFQ COMBO MAKE TABLE].[Type of Contract] FROM
[Agenda and RFQ COMBO MAKE TABLE];
In the after_update of the first combo box I have this code:
Private Sub ComboStatus_AfterUpdate()
On Error Resume Next
ComboClient.RowSource = "Select Agenda and RFQ Details Query_Client " & _
"FROM Agenda and RFQ Details Query " & _
"WHERE Agenda and RFQ Details Query.Contract Status Text = '" &
ComboStatus.Value & "' " & _
"ORDER BY Agenda and RFQ Details Query_Client"
End Sub
The first combo works fine, of course. But the second is blank and I get
the syntax error (I think from the AfterUpdate of the first combo) of “Syntax
error (missing operator) in query expression ‘Agenda and RFQ Details
Query_Client’â€
At one time, one of the iterations did have the whole list of clients – but
not filtered by the first box
Another time, I only got one item on the list, the first.
At this time, I don’t even know how I got the results from “At one time…â€
or ever “Another time…â€
Do you think I’m lost???
I know this isn’t that hard – what am I doing wrong – I think it is
something simple.