Problem with cascading combos

[Guest]

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters". I've tried this over and over using
different strategies with no luck. Any idea what's going on??

 

[Marshall Barton]

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters".

The clients combo box's RowSource query need to use the
staff combo box as the criteria for the staff field in the
query. The criteria will liik like:
Forms![name of form].[name of 1st combo]

You may or may not have other issues in the query, but I
can't check that until you post a Copy of it.

Don't forget that the 1st combo box's AfterUpdate event
provedure needs to Requery the 2nd combo box.

 

[Guest]

Everything is set up right as far as the underlying query and AfterUpdate
event, I'm sure. If I manually run the underlying query and enter the
StaffID (numId) when it prompts me to, the query works fine. The only
problem seems to setting up the second combo. I have only tried it with the
wizard though, so maybe I'll try to set it up without the wizard to see what
happens. It shouldn't make a difference though, right?

Here's the SQL view of the query for the second combo (which uses 3 tables
instead of 2 like I said before):

SELECT TblClientCaseInfo.ClientCaseInfoID, [ClientLast] & ", " &
[ClientFirst] AS ClientName, TblClientCaseInfo.Program, TblCurrentStaff.numID
FROM TblClientContactInfo INNER JOIN (TblClientCaseInfo INNER JOIN
TblCurrentStaff ON TblClientCaseInfo.numID = TblCurrentStaff.numID) ON
TblClientContactInfo.numIDNum = TblClientCaseInfo.numIDNum
WHERE (((TblCurrentStaff.numID)=[Forms]![FrmContact2]![Staff]![numID]) AND
((TblClientCaseInfo.CloseDate) Is Null))
ORDER BY [ClientLast] & ", " & [ClientFirst];

Any help is appreciated!

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters".

The clients combo box's RowSource query need to use the
staff combo box as the criteria for the staff field in the
query. The criteria will liik like:
Forms![name of form].[name of 1st combo]

You may or may not have other issues in the query, but I
can't check that until you post a Copy of it.

Don't forget that the 1st combo box's AfterUpdate event
provedure needs to Requery the 2nd combo box.

 

[Marshall Barton]

Are you sure that query is used as the RowSource property of
the 2nd combo box?

If it is, the fact that you are being prompted for something
means that that something in the reference is not
resolvable. Perhaps it's as simple as a slight spelling
mistake or the name of the first combo box is not what you
thought it was. Double check the parameter prompt string
when it pops up, it tells you what it can't resolve.
--
Marsh
MVP [MS Access]

Everything is set up right as far as the underlying query and AfterUpdate
event, I'm sure. If I manually run the underlying query and enter the
StaffID (numId) when it prompts me to, the query works fine. The only
problem seems to setting up the second combo. I have only tried it with the
wizard though, so maybe I'll try to set it up without the wizard to see what
happens. It shouldn't make a difference though, right?

Here's the SQL view of the query for the second combo (which uses 3 tables
instead of 2 like I said before):

SELECT TblClientCaseInfo.ClientCaseInfoID, [ClientLast] & ", " &
[ClientFirst] AS ClientName, TblClientCaseInfo.Program, TblCurrentStaff.numID
FROM TblClientContactInfo INNER JOIN (TblClientCaseInfo INNER JOIN
TblCurrentStaff ON TblClientCaseInfo.numID = TblCurrentStaff.numID) ON
TblClientContactInfo.numIDNum = TblClientCaseInfo.numIDNum
WHERE (((TblCurrentStaff.numID)=[Forms]![FrmContact2]![Staff]![numID]) AND
((TblClientCaseInfo.CloseDate) Is Null))
ORDER BY [ClientLast] & ", " & [ClientFirst];

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters".

The clients combo box's RowSource query need to use the
staff combo box as the criteria for the staff field in the
query. The criteria will liik like:
Forms![name of form].[name of 1st combo]

You may or may not have other issues in the query, but I
can't check that until you post a Copy of it.

Don't forget that the 1st combo box's AfterUpdate event
provedure needs to Requery the 2nd combo box.

 

[Guest]

Yes, it's the correct query. There really isn't a prompt, just the message I
initially mentioned. I still haven't tried it without the wizard though.
I'll try that in a little bit and post again if it worked.

Are you sure that query is used as the RowSource property of
the 2nd combo box?

If it is, the fact that you are being prompted for something
means that that something in the reference is not
resolvable. Perhaps it's as simple as a slight spelling
mistake or the name of the first combo box is not what you
thought it was. Double check the parameter prompt string
when it pops up, it tells you what it can't resolve.
--
Marsh
MVP [MS Access]

Everything is set up right as far as the underlying query and AfterUpdate
event, I'm sure. If I manually run the underlying query and enter the
StaffID (numId) when it prompts me to, the query works fine. The only
problem seems to setting up the second combo. I have only tried it with the
wizard though, so maybe I'll try to set it up without the wizard to see what
happens. It shouldn't make a difference though, right?

Here's the SQL view of the query for the second combo (which uses 3 tables
instead of 2 like I said before):

SELECT TblClientCaseInfo.ClientCaseInfoID, [ClientLast] & ", " &
[ClientFirst] AS ClientName, TblClientCaseInfo.Program, TblCurrentStaff.numID
FROM TblClientContactInfo INNER JOIN (TblClientCaseInfo INNER JOIN
TblCurrentStaff ON TblClientCaseInfo.numID = TblCurrentStaff.numID) ON
TblClientContactInfo.numIDNum = TblClientCaseInfo.numIDNum
WHERE (((TblCurrentStaff.numID)=[Forms]![FrmContact2]![Staff]![numID]) AND
((TblClientCaseInfo.CloseDate) Is Null))
ORDER BY [ClientLast] & ", " & [ClientFirst];

Tara wrote:

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters".


The clients combo box's RowSource query need to use the
staff combo box as the criteria for the staff field in the
query. The criteria will liik like:
Forms![name of form].[name of 1st combo]

You may or may not have other issues in the query, but I
can't check that until you post a Copy of it.

Don't forget that the 1st combo box's AfterUpdate event
provedure needs to Requery the 2nd combo box.

 

[Guest]

Well, I tried entering the rowsource without the wizard and still no luck.
Any ideas?? I'm just stumped!

Thanks!

Yes, it's the correct query. There really isn't a prompt, just the message I
initially mentioned. I still haven't tried it without the wizard though.
I'll try that in a little bit and post again if it worked.

Are you sure that query is used as the RowSource property of
the 2nd combo box?

If it is, the fact that you are being prompted for something
means that that something in the reference is not
resolvable. Perhaps it's as simple as a slight spelling
mistake or the name of the first combo box is not what you
thought it was. Double check the parameter prompt string
when it pops up, it tells you what it can't resolve.
--
Marsh
MVP [MS Access]

Everything is set up right as far as the underlying query and AfterUpdate
event, I'm sure. If I manually run the underlying query and enter the
StaffID (numId) when it prompts me to, the query works fine. The only
problem seems to setting up the second combo. I have only tried it with the
wizard though, so maybe I'll try to set it up without the wizard to see what
happens. It shouldn't make a difference though, right?

Here's the SQL view of the query for the second combo (which uses 3 tables
instead of 2 like I said before):

SELECT TblClientCaseInfo.ClientCaseInfoID, [ClientLast] & ", " &
[ClientFirst] AS ClientName, TblClientCaseInfo.Program, TblCurrentStaff.numID
FROM TblClientContactInfo INNER JOIN (TblClientCaseInfo INNER JOIN
TblCurrentStaff ON TblClientCaseInfo.numID = TblCurrentStaff.numID) ON
TblClientContactInfo.numIDNum = TblClientCaseInfo.numIDNum
WHERE (((TblCurrentStaff.numID)=[Forms]![FrmContact2]![Staff]![numID]) AND
((TblClientCaseInfo.CloseDate) Is Null))
ORDER BY [ClientLast] & ", " & [ClientFirst];


:

Tara wrote:

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters".


The clients combo box's RowSource query need to use the
staff combo box as the criteria for the staff field in the
query. The criteria will liik like:
Forms![name of form].[name of 1st combo]

You may or may not have other issues in the query, but I
can't check that until you post a Copy of it.

Don't forget that the 1st combo box's AfterUpdate event
provedure needs to Requery the 2nd combo box.

 

[Guest]

Okay, I think I've narrowed it down. It has something to do with the field
numID. I completely rebuilt the query, adding in each field individually and
then creating the combo after I entered a field. I had no problem until I
came to the numID field. At that point, the message "No value given for one
of more required parameters" came up. I'm not sure why though...Any ideas????

Any help is greatly appreciated!

Well, I tried entering the rowsource without the wizard and still no luck.
Any ideas?? I'm just stumped!

Thanks!

Yes, it's the correct query. There really isn't a prompt, just the message I
initially mentioned. I still haven't tried it without the wizard though.
I'll try that in a little bit and post again if it worked.

Are you sure that query is used as the RowSource property of
the 2nd combo box?

If it is, the fact that you are being prompted for something
means that that something in the reference is not
resolvable. Perhaps it's as simple as a slight spelling
mistake or the name of the first combo box is not what you
thought it was. Double check the parameter prompt string
when it pops up, it tells you what it can't resolve.
--
Marsh
MVP [MS Access]


Tara wrote:

Everything is set up right as far as the underlying query and AfterUpdate
event, I'm sure. If I manually run the underlying query and enter the
StaffID (numId) when it prompts me to, the query works fine. The only
problem seems to setting up the second combo. I have only tried it with the
wizard though, so maybe I'll try to set it up without the wizard to see what
happens. It shouldn't make a difference though, right?

Here's the SQL view of the query for the second combo (which uses 3 tables
instead of 2 like I said before):

SELECT TblClientCaseInfo.ClientCaseInfoID, [ClientLast] & ", " &
[ClientFirst] AS ClientName, TblClientCaseInfo.Program, TblCurrentStaff.numID
FROM TblClientContactInfo INNER JOIN (TblClientCaseInfo INNER JOIN
TblCurrentStaff ON TblClientCaseInfo.numID = TblCurrentStaff.numID) ON
TblClientContactInfo.numIDNum = TblClientCaseInfo.numIDNum
WHERE (((TblCurrentStaff.numID)=[Forms]![FrmContact2]![Staff]![numID]) AND
((TblClientCaseInfo.CloseDate) Is Null))
ORDER BY [ClientLast] & ", " & [ClientFirst];


:

Tara wrote:

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters".


The clients combo box's RowSource query need to use the
staff combo box as the criteria for the staff field in the
query. The criteria will liik like:
Forms![name of form].[name of 1st combo]

You may or may not have other issues in the query, but I
can't check that until you post a Copy of it.

Don't forget that the 1st combo box's AfterUpdate event
provedure needs to Requery the 2nd combo box.

 

[Marshall Barton]

Something strange is going on here, I don't remember ever
seeing that error message before. I thought you were
paraphrasing the usual parameter prompt message.

Let's at least we have our nomenclature correct. I can't
tell if your use of the word "field" is precise enough. A
"field" is a column in a table or query. A "Control" is an
object (text box, combo box, etc) on a form or report. When
you say the field numID is causing a problem (in the query's
WHERE clause?) I can't tell if you mean the field in the
TblCurrentStaff table or the combo box on the form or
something else.

Wait a minute, hold on to everything. I just noticed that
the part of the WHERE clause:
TblCurrentStaff.numID=Forms!FrmContact2!Staff!numID
the reference to the combo box Forms!FrmContact2!Staff!numID
is garbled. If the name of the combo box is Staff, then the
!numID is incorrect and should be removed. Try this
instead:
TblCurrentStaff.numID=Forms!FrmContact2!Staff
Depending on the combo box's BoundColumn property (which
should correspond to the numID field in the table), the
value of the combo box is directly available without extra
qualifications.

Double check the Staff combo box properties ColumnCount,
BoundColumn and ColumnWidths to make sure they agree with
the RowSource: if the ColumnCount is off, nothing about the
combo box will work as expected; if the BoundColumn is not
set properly, the query will not compare to the right value;
and if the ColumnWidths are out of whack, you won't see the
list's data or the box will display the wrong column.
--
Marsh
MVP [MS Access]

Okay, I think I've narrowed it down. It has something to do with the field
numID. I completely rebuilt the query, adding in each field individually and
then creating the combo after I entered a field. I had no problem until I
came to the numID field. At that point, the message "No value given for one
of more required parameters" came up. I'm not sure why though...Any ideas????

Well, I tried entering the rowsource without the wizard and still no luck.
Any ideas?? I'm just stumped!

Thanks!

Yes, it's the correct query. There really isn't a prompt, just the message I
initially mentioned. I still haven't tried it without the wizard though.
I'll try that in a little bit and post again if it worked.

:

Are you sure that query is used as the RowSource property of
the 2nd combo box?

If it is, the fact that you are being prompted for something
means that that something in the reference is not
resolvable. Perhaps it's as simple as a slight spelling
mistake or the name of the first combo box is not what you
thought it was. Double check the parameter prompt string
when it pops up, it tells you what it can't resolve.
--
Marsh
MVP [MS Access]


Tara wrote:

Everything is set up right as far as the underlying query and AfterUpdate
event, I'm sure. If I manually run the underlying query and enter the
StaffID (numId) when it prompts me to, the query works fine. The only
problem seems to setting up the second combo. I have only tried it with the
wizard though, so maybe I'll try to set it up without the wizard to see what
happens. It shouldn't make a difference though, right?

Here's the SQL view of the query for the second combo (which uses 3 tables
instead of 2 like I said before):

SELECT TblClientCaseInfo.ClientCaseInfoID, [ClientLast] & ", " &
[ClientFirst] AS ClientName, TblClientCaseInfo.Program, TblCurrentStaff.numID
FROM TblClientContactInfo INNER JOIN (TblClientCaseInfo INNER JOIN
TblCurrentStaff ON TblClientCaseInfo.numID = TblCurrentStaff.numID) ON
TblClientContactInfo.numIDNum = TblClientCaseInfo.numIDNum
WHERE (((TblCurrentStaff.numID)=[Forms]![FrmContact2]![Staff]![numID]) AND
((TblClientCaseInfo.CloseDate) Is Null))
ORDER BY [ClientLast] & ", " & [ClientFirst];


:

Tara wrote:

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters".


The clients combo box's RowSource query need to use the
staff combo box as the criteria for the staff field in the
query. The criteria will liik like:
Forms![name of form].[name of 1st combo]

You may or may not have other issues in the query, but I
can't check that until you post a Copy of it.

Don't forget that the 1st combo box's AfterUpdate event
provedure needs to Requery the 2nd combo box.

 

[Guest]

Thanks for keeping up with this Marshall...
By field, I mean in the underlying query for the 2nd combo. As for the
WHERE clause, yeah, I knew that was in there. I've tried it several
different ways and that was just one of those ways. I know you normally just
ask for the value of a particular combo box (Staff), but after exhausting all
other ideas, I decided to try to pinpoint it more exactly. I figured I
didn't have anything to lose since nothing else seemed to be working. I've
tried it with just Forms!FrmContact2!Staff, but I still get the same message.
I've also checked and rechecked the bound column, column widths, and coulmn
count many times, just to be sure. Everything is good there. I'm soooo
frustrated by this! Thanks for the continued feedback...if you have any more
ideas, they would be greatly appreciated.

Something strange is going on here, I don't remember ever
seeing that error message before. I thought you were
paraphrasing the usual parameter prompt message.

Let's at least we have our nomenclature correct. I can't
tell if your use of the word "field" is precise enough. A
"field" is a column in a table or query. A "Control" is an
object (text box, combo box, etc) on a form or report. When
you say the field numID is causing a problem (in the query's
WHERE clause?) I can't tell if you mean the field in the
TblCurrentStaff table or the combo box on the form or
something else.

Wait a minute, hold on to everything. I just noticed that
the part of the WHERE clause:
TblCurrentStaff.numID=Forms!FrmContact2!Staff!numID
the reference to the combo box Forms!FrmContact2!Staff!numID
is garbled. If the name of the combo box is Staff, then the
!numID is incorrect and should be removed. Try this
instead:
TblCurrentStaff.numID=Forms!FrmContact2!Staff
Depending on the combo box's BoundColumn property (which
should correspond to the numID field in the table), the
value of the combo box is directly available without extra
qualifications.

Double check the Staff combo box properties ColumnCount,
BoundColumn and ColumnWidths to make sure they agree with
the RowSource: if the ColumnCount is off, nothing about the
combo box will work as expected; if the BoundColumn is not
set properly, the query will not compare to the right value;
and if the ColumnWidths are out of whack, you won't see the
list's data or the box will display the wrong column.
--
Marsh
MVP [MS Access]

Okay, I think I've narrowed it down. It has something to do with the field
numID. I completely rebuilt the query, adding in each field individually and
then creating the combo after I entered a field. I had no problem until I
came to the numID field. At that point, the message "No value given for one
of more required parameters" came up. I'm not sure why though...Any ideas????

Well, I tried entering the rowsource without the wizard and still no luck.
Any ideas?? I'm just stumped!

Thanks!

:

Yes, it's the correct query. There really isn't a prompt, just the message I
initially mentioned. I still haven't tried it without the wizard though.
I'll try that in a little bit and post again if it worked.

:

Are you sure that query is used as the RowSource property of
the 2nd combo box?

If it is, the fact that you are being prompted for something
means that that something in the reference is not
resolvable. Perhaps it's as simple as a slight spelling
mistake or the name of the first combo box is not what you
thought it was. Double check the parameter prompt string
when it pops up, it tells you what it can't resolve.
--
Marsh
MVP [MS Access]


Tara wrote:

Everything is set up right as far as the underlying query and AfterUpdate
event, I'm sure. If I manually run the underlying query and enter the
StaffID (numId) when it prompts me to, the query works fine. The only
problem seems to setting up the second combo. I have only tried it with the
wizard though, so maybe I'll try to set it up without the wizard to see what
happens. It shouldn't make a difference though, right?

Here's the SQL view of the query for the second combo (which uses 3 tables
instead of 2 like I said before):

SELECT TblClientCaseInfo.ClientCaseInfoID, [ClientLast] & ", " &
[ClientFirst] AS ClientName, TblClientCaseInfo.Program, TblCurrentStaff.numID
FROM TblClientContactInfo INNER JOIN (TblClientCaseInfo INNER JOIN
TblCurrentStaff ON TblClientCaseInfo.numID = TblCurrentStaff.numID) ON
TblClientContactInfo.numIDNum = TblClientCaseInfo.numIDNum
WHERE (((TblCurrentStaff.numID)=[Forms]![FrmContact2]![Staff]![numID]) AND
((TblClientCaseInfo.CloseDate) Is Null))
ORDER BY [ClientLast] & ", " & [ClientFirst];


:

Tara wrote:

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters".


The clients combo box's RowSource query need to use the
staff combo box as the criteria for the staff field in the
query. The criteria will liik like:
Forms![name of form].[name of 1st combo]

You may or may not have other issues in the query, but I
can't check that until you post a Copy of it.

Don't forget that the 1st combo box's AfterUpdate event
provedure needs to Requery the 2nd combo box.

 

[Guest]

I just reread your post...as for the "field" issue...the problem occurs if I
add numID to the underlying query. The query still works (prompts me for the
value) but when I then try to create the combo based on that query, I get the
error message.

Thanks for keeping up with this Marshall...
By field, I mean in the underlying query for the 2nd combo. As for the
WHERE clause, yeah, I knew that was in there. I've tried it several
different ways and that was just one of those ways. I know you normally just
ask for the value of a particular combo box (Staff), but after exhausting all
other ideas, I decided to try to pinpoint it more exactly. I figured I
didn't have anything to lose since nothing else seemed to be working. I've
tried it with just Forms!FrmContact2!Staff, but I still get the same message.
I've also checked and rechecked the bound column, column widths, and coulmn
count many times, just to be sure. Everything is good there. I'm soooo
frustrated by this! Thanks for the continued feedback...if you have any more
ideas, they would be greatly appreciated.

Something strange is going on here, I don't remember ever
seeing that error message before. I thought you were
paraphrasing the usual parameter prompt message.

Let's at least we have our nomenclature correct. I can't
tell if your use of the word "field" is precise enough. A
"field" is a column in a table or query. A "Control" is an
object (text box, combo box, etc) on a form or report. When
you say the field numID is causing a problem (in the query's
WHERE clause?) I can't tell if you mean the field in the
TblCurrentStaff table or the combo box on the form or
something else.

Wait a minute, hold on to everything. I just noticed that
the part of the WHERE clause:
TblCurrentStaff.numID=Forms!FrmContact2!Staff!numID
the reference to the combo box Forms!FrmContact2!Staff!numID
is garbled. If the name of the combo box is Staff, then the
!numID is incorrect and should be removed. Try this
instead:
TblCurrentStaff.numID=Forms!FrmContact2!Staff
Depending on the combo box's BoundColumn property (which
should correspond to the numID field in the table), the
value of the combo box is directly available without extra
qualifications.

Double check the Staff combo box properties ColumnCount,
BoundColumn and ColumnWidths to make sure they agree with
the RowSource: if the ColumnCount is off, nothing about the
combo box will work as expected; if the BoundColumn is not
set properly, the query will not compare to the right value;
and if the ColumnWidths are out of whack, you won't see the
list's data or the box will display the wrong column.
--
Marsh
MVP [MS Access]

Okay, I think I've narrowed it down. It has something to do with the field
numID. I completely rebuilt the query, adding in each field individually and
then creating the combo after I entered a field. I had no problem until I
came to the numID field. At that point, the message "No value given for one
of more required parameters" came up. I'm not sure why though...Any ideas????


:

Well, I tried entering the rowsource without the wizard and still no luck.
Any ideas?? I'm just stumped!

Thanks!

:

Yes, it's the correct query. There really isn't a prompt, just the message I
initially mentioned. I still haven't tried it without the wizard though.
I'll try that in a little bit and post again if it worked.

:

Are you sure that query is used as the RowSource property of
the 2nd combo box?

If it is, the fact that you are being prompted for something
means that that something in the reference is not
resolvable. Perhaps it's as simple as a slight spelling
mistake or the name of the first combo box is not what you
thought it was. Double check the parameter prompt string
when it pops up, it tells you what it can't resolve.
--
Marsh
MVP [MS Access]


Tara wrote:

Everything is set up right as far as the underlying query and AfterUpdate
event, I'm sure. If I manually run the underlying query and enter the
StaffID (numId) when it prompts me to, the query works fine. The only
problem seems to setting up the second combo. I have only tried it with the
wizard though, so maybe I'll try to set it up without the wizard to see what
happens. It shouldn't make a difference though, right?

Here's the SQL view of the query for the second combo (which uses 3 tables
instead of 2 like I said before):

SELECT TblClientCaseInfo.ClientCaseInfoID, [ClientLast] & ", " &
[ClientFirst] AS ClientName, TblClientCaseInfo.Program, TblCurrentStaff.numID
FROM TblClientContactInfo INNER JOIN (TblClientCaseInfo INNER JOIN
TblCurrentStaff ON TblClientCaseInfo.numID = TblCurrentStaff.numID) ON
TblClientContactInfo.numIDNum = TblClientCaseInfo.numIDNum
WHERE (((TblCurrentStaff.numID)=[Forms]![FrmContact2]![Staff]![numID]) AND
((TblClientCaseInfo.CloseDate) Is Null))
ORDER BY [ClientLast] & ", " & [ClientFirst];


:

Tara wrote:

I have a combo that I'm trying to limit based on the value selected in
another combo. In the 1st combo, the user will select a staff members name
(with StaffID as the bound column). I want the 2nd combo to then populate
with only those clients associated with the staff member. The second combo
is based on a query that pulls information from 2 different tables. One
table holds client contact information (Name, Address, Etc.) and the 2nd
table holds client case information, which also includes StaffID. When I
start to put the 2nd combo together, I get the error message "No value given
for one or more required parameters".


The clients combo box's RowSource query need to use the
staff combo box as the criteria for the staff field in the
query. The criteria will liik like:
Forms![name of form].[name of 1st combo]

You may or may not have other issues in the query, but I
can't check that until you post a Copy of it.

Don't forget that the 1st combo box's AfterUpdate event
provedure needs to Requery the 2nd combo box.

 

[Marshall Barton]

I just reread your post...as for the "field" issue...the problem occurs if I
add numID to the underlying query. The query still works (prompts me for the
value) but when I then try to create the combo based on that query, I get the
error message.

A parameter prompt when you run the query clearly indicates
that the query can not resolve the name it's prompting you
for a value. This dramatically narrows the problem area so
we can forget the form for now and concentrate our efforts
on the query and/or the table.

Are you sure the field is spelled numID in the table's
design view? (Looking at the table in datasheet view is
**not** sufficient to see the exact field names.)

 

[Guest]

The query only prompts me because of the Forms!FrmContact2!Staff criteria.
The form and control it references aren't open/active, so it has to prompt me
for the value. Obviously, if I take the criteria out, there's no prompt.
Either way, the query works.

 

[Marshall Barton]

Gee, I keep misreading your situation. Ok, the query works
fine all by itself when the form is open. But, even when
the form is open you are prompted(?) or get some kind of
error when you Requery the clients combo box. The only
logical reason for that is because the name of the form or
staff combo box in the query is wrong.

 

[Guest]

No problem with misreading Marshall...I probably haven't been very clear.

Let me try to clarify...

I can't create the combo at all using the wizard if I include the field
numID in the underlying query. It gives me the error message when I'm
creating it. If I create the combo manually, nothing at all happens when I
requery. In other words, if the form is open, nothing at all occurs. The
combo is empty. If I just run the query (with the form closed), it works
fine, with or without the criteria included.

As for logical conclusions, I'm beginning to think there aren't any in this
situation. I know something odd is happening, but I can't find it. I've
looked everywhere for anything that might be causing this. Are there any
other options for trying to accomplish what I need that I haven't thought of?

Thanks for all your help. I really apprecate it...

Gee, I keep misreading your situation. Ok, the query works
fine all by itself when the form is open. But, even when
the form is open you are prompted(?) or get some kind of
error when you Requery the clients combo box. The only
logical reason for that is because the name of the form or
staff combo box in the query is wrong.
--
Marsh
MVP [MS Access]

The query only prompts me because of the Forms!FrmContact2!Staff criteria.
The form and control it references aren't open/active, so it has to prompt me
for the value. Obviously, if I take the criteria out, there's no prompt.
Either way, the query works.

 

[Marshall Barton]

No wonder I've never heard of that message if it's coming
from the create a combo box wizard. I do find it odd that
the wizard complains about the numID field while the query
can include that field and run fine. Then when you create
the combo box manually, it has nothing in its dropdown list.
Combining that infomation, it sure seems that something
about numID is out of whack.

I think you need to focus your attention there and try some
different scenarios on a simple test form to see if you can
provoke some additional clues into coming out of the
shadows. Also look at the field in the table's design and
see if there is anything you've done that is different from
other fields (e.g. set its lookup to combo, given it a
different name and caption, or ??)

When logic goes out the window, the bail out position is to
point at some kind of corruption as the culprit. If that's
what's causing all this confusion, then that's what should
be pursued, not some oddball way of doing things that works
in spite of it. Before going any further make sure you are
workin in a copy of the database, any action in a corrupt
mdb file might make things worse so you need a way to get
back to the original problem. If you can't find anything in
the table and you continue to get strange behavior in new,
simple forms, then try using Compact/Repair. If that
doesn't help, the next on the list is to create a new blank
mdb file, set all its options and references and then try to
import everything from the problem mdb. The last resort is
to restore from a working backup file.

 

[Guest]

Thanks for all your help Marshall. I'll try your suggestions and pray that I
can resolve it. I appreciate everything...

No wonder I've never heard of that message if it's coming
from the create a combo box wizard. I do find it odd that
the wizard complains about the numID field while the query
can include that field and run fine. Then when you create
the combo box manually, it has nothing in its dropdown list.
Combining that infomation, it sure seems that something
about numID is out of whack.

I think you need to focus your attention there and try some
different scenarios on a simple test form to see if you can
provoke some additional clues into coming out of the
shadows. Also look at the field in the table's design and
see if there is anything you've done that is different from
other fields (e.g. set its lookup to combo, given it a
different name and caption, or ??)

When logic goes out the window, the bail out position is to
point at some kind of corruption as the culprit. If that's
what's causing all this confusion, then that's what should
be pursued, not some oddball way of doing things that works
in spite of it. Before going any further make sure you are
workin in a copy of the database, any action in a corrupt
mdb file might make things worse so you need a way to get
back to the original problem. If you can't find anything in
the table and you continue to get strange behavior in new,
simple forms, then try using Compact/Repair. If that
doesn't help, the next on the list is to create a new blank
mdb file, set all its options and references and then try to
import everything from the problem mdb. The last resort is
to restore from a working backup file.
--
Marsh
MVP [MS Access]

No problem with misreading Marshall...I probably haven't been very clear.

Let me try to clarify...

I can't create the combo at all using the wizard if I include the field
numID in the underlying query. It gives me the error message when I'm
creating it. If I create the combo manually, nothing at all happens when I
requery. In other words, if the form is open, nothing at all occurs. The
combo is empty. If I just run the query (with the form closed), it works
fine, with or without the criteria included.

As for logical conclusions, I'm beginning to think there aren't any in this
situation. I know something odd is happening, but I can't find it. I've
looked everywhere for anything that might be causing this. Are there any
other options for trying to accomplish what I need that I haven't thought of?