Text box, Cascading Comb, & Parameter Query

[pfm721]

I am trying to set up a form to run a parameter query. On the form
frmAPSSearch the user will enter a 5 digit code in the text box
txtFilterCPTCode, then select a state from the first combo box
cboFilterState, finally a county from the last combo box cboFilterLocality.
Then I have a command button to run the parameter query. When the query runs
I get no results.

The query is set up to reference two tables. tblAPCLocality08, and
tblAPCPrices08. In the query there are three fields I would like to set are
parameters (this might be the problem as don't even know if access will
accept that.) Field HCPCS has this code in it
Forms![frmAPCSearch]![txtFilterCPTCode]. When I use only that parameter the
query runs fine. The secod field is State with this code
Forms![frmAPCSearch]![cboFilterState] . The last field is Locality with this
code
Forms![frmAPCSearch]![cboFilterLocality] . When either of the last two is in
the query it does not return any results.

Any suggestions on what I might be doing wrong or a more efficient way to do
this?

Thanks in advance..

 

[pfm721]

Disregard this question. I figured out what the problem was. It was an
improper reference in bound columns.

 

[Al Campagna]

pfm721,
Make sure your combos are setup properly. You may be "displaying" one
value, but really storing another in that field.
And the query is actually using that value, not the one "displayed".
Place an unbound text control on the form with a ControlSource of...
= cboFilterState
Is that the correct value you want to pass to the Query?
Do the same for cboFilterLocality... is that value correct?

When referring to a value in any combo column, Access uses 0,1,2,3,4...
When referring to the Bound Column of a combo Access uses 1,2,3,4,5
A combo always "displays" the first non-zero column, but "stores" the
Bound Column value.

If you're still having problems, describe your combo setup...
1. Combo columns and sample values
2. Column Count, Column Widths, and Bound Column
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Ken Sheridan]

Two possibilities spring to mind:

1. You are 'optionalizing' the parameters, i.e. you want to be able to
select values in one or both, but not necessarily in both of them, e.g. you
might select a state but not a locality. If so then you need to test each
for OR IS NULL. In the 'criteria' row of the Locality column in query design
view for instance you'd put:

Forms![frmAPCSearch]![cboFilterLocality] OR
Forms![frmAPCSearch]![cboFilterLocality] IS NULL

This should be entered as a single line; it might have been split over two
by your newsgroup reader. Do the same for the State column to optionalize
that parameter also. If you do this and save the query in design view you'll
find that Access has moved things around if you open it again in design view.
It will work just the same however.

2. Are the state and locality columns in tblAPCLocality08 of numeric data
types referencing numeric primary key columns of states and localities
tables? This could be the case if you've used the 'lookup wizard' when
setting the data types for the columns in tblAPCLocality08, or if you've set
the display control to a combo box in the columns' properties sheets. In
either case you would see the text values, but the underlying values would be
numeric.

If so then you need to set up the cboFilterState and cboFilterLocality combo
boxes so that their values are hidden numeric values , but you see the text
values. In the case of the cboFilterState control for instance it would be
set up along these lines:

RowSource: SELECT StateID, State FROM tblStates ORDER BY State;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

Where StateID is the numeric primary key of tblStates, and State the text
column containing the state names. If your units of measurement are
imperial rather than metric Access will automatically convert the last one
above. The important thing is that the first dimension is zero to hide the
first column and that the second is at least as wide as the combo box.

BTW, if this is the cause of the problem, most developers recommend against
using the so called 'lookup fields' facility. For reasons why see:


http://www.mvps.org/access/lookupfields.htm


One further point: Having columns for both locality and state in the
'referencing' table introduces redundancy. As the locality implies the state
(assuming localities don't span state boundaries) having a state column is
unnecessary and, more importantly, leaves the door open to inconsistent data
being entered. You can find a demo of how to handle this sort of scenario at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


In this case the hierarchy is of three levels, using the local
administrative units of county, district and parish in my neck of the woods,
but the principle is exactly the same.

Ken Sheridan
Stafford, England

I am trying to set up a form to run a parameter query. On the form
frmAPSSearch the user will enter a 5 digit code in the text box
txtFilterCPTCode, then select a state from the first combo box
cboFilterState, finally a county from the last combo box cboFilterLocality.
Then I have a command button to run the parameter query. When the query runs
I get no results.

The query is set up to reference two tables. tblAPCLocality08, and
tblAPCPrices08. In the query there are three fields I would like to set are
parameters (this might be the problem as don't even know if access will
accept that.) Field HCPCS has this code in it
Forms![frmAPCSearch]![txtFilterCPTCode]. When I use only that parameter the
query runs fine. The secod field is State with this code
Forms![frmAPCSearch]![cboFilterState] . The last field is Locality with this
code
Forms![frmAPCSearch]![cboFilterLocality] . When either of the last two is in
the query it does not return any results.

Any suggestions on what I might be doing wrong or a more efficient way to do
this?

Thanks in advance..

 

[pfm721]

Thanks to both Ken and Al. I had solved the problem but the advice you gave
is very useful. I actually went back and changed a couple of things to make
it run better. Thanks a bunch. You are the reason this is such a great forum.

Two possibilities spring to mind:

1. You are 'optionalizing' the parameters, i.e. you want to be able to
select values in one or both, but not necessarily in both of them, e.g. you
might select a state but not a locality. If so then you need to test each
for OR IS NULL. In the 'criteria' row of the Locality column in query design
view for instance you'd put:

Forms![frmAPCSearch]![cboFilterLocality] OR
Forms![frmAPCSearch]![cboFilterLocality] IS NULL

This should be entered as a single line; it might have been split over two
by your newsgroup reader. Do the same for the State column to optionalize
that parameter also. If you do this and save the query in design view you'll
find that Access has moved things around if you open it again in design view.
It will work just the same however.

2. Are the state and locality columns in tblAPCLocality08 of numeric data
types referencing numeric primary key columns of states and localities
tables? This could be the case if you've used the 'lookup wizard' when
setting the data types for the columns in tblAPCLocality08, or if you've set
the display control to a combo box in the columns' properties sheets. In
either case you would see the text values, but the underlying values would be
numeric.

If so then you need to set up the cboFilterState and cboFilterLocality combo
boxes so that their values are hidden numeric values , but you see the text
values. In the case of the cboFilterState control for instance it would be
set up along these lines:

RowSource: SELECT StateID, State FROM tblStates ORDER BY State;

BoundColum: 1
ColumnCount: 2
ColumnWidths 0cm;8cm

Where StateID is the numeric primary key of tblStates, and State the text
column containing the state names. If your units of measurement are
imperial rather than metric Access will automatically convert the last one
above. The important thing is that the first dimension is zero to hide the
first column and that the second is at least as wide as the combo box.

BTW, if this is the cause of the problem, most developers recommend against
using the so called 'lookup fields' facility. For reasons why see:


http://www.mvps.org/access/lookupfields.htm


One further point: Having columns for both locality and state in the
'referencing' table introduces redundancy. As the locality implies the state
(assuming localities don't span state boundaries) having a state column is
unnecessary and, more importantly, leaves the door open to inconsistent data
being entered. You can find a demo of how to handle this sort of scenario at:


http://community.netscape.com/n/pfx...yMessages&tsn=1&tid=23626&webtag=ws-msdevapps


In this case the hierarchy is of three levels, using the local
administrative units of county, district and parish in my neck of the woods,
but the principle is exactly the same.

Ken Sheridan
Stafford, England

I am trying to set up a form to run a parameter query. On the form
frmAPSSearch the user will enter a 5 digit code in the text box
txtFilterCPTCode, then select a state from the first combo box
cboFilterState, finally a county from the last combo box cboFilterLocality.
Then I have a command button to run the parameter query. When the query runs
I get no results.

The query is set up to reference two tables. tblAPCLocality08, and
tblAPCPrices08. In the query there are three fields I would like to set are
parameters (this might be the problem as don't even know if access will
accept that.) Field HCPCS has this code in it
Forms![frmAPCSearch]![txtFilterCPTCode]. When I use only that parameter the
query runs fine. The secod field is State with this code
Forms![frmAPCSearch]![cboFilterState] . The last field is Locality with this
code
Forms![frmAPCSearch]![cboFilterLocality] . When either of the last two is in
the query it does not return any results.

Any suggestions on what I might be doing wrong or a more efficient way to do
this?

Thanks in advance..

 

[Nicholas Meyer]

great