Query parameter - getting this from a subform, having problems!

[FattyFatty]

Hi All,

I'm come across a brick wall and am after some help! I'm wanting to use
the value of a control (a combo box) on a subform in my query as the
criteria for my query.

The problem is that I'm getting this error message when running the
query (yes, the form is open): "The expression is typed incorrectly, or
it is too complex to be evaluated...." etc.

I'm using access 2003.

Here's what I have:
The main form is called: frm_C01_MaintainTeamsAndStaff
The subform is called (i.e. the actual form):
frm_C05_Sub_MaintainTeamDetails_DeleteTeam
The name of the subform on the main form is called (i.e. the name of
the subform "control" on the main form):
subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam
The name of the control on the subform I'm wanting to reference in the
query is called: cmb_TeamName

My query is only returning 3 fields:
Staff_Name_Full
Staff_TeamNumber
Staff_ActiveIndicator

The Staff_TeamNumber field criteria is:
[Forms]![frm_C01_MaintainTeamsAndStaff]![subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam].[Form]![cmb_TeamName]

According to the Microsoft website, I'm using the correct syntax:
http://support.microsoft.com/?kbid=209099#appliesto
"To refer to a control on a subform, use the following syntax:
Forms![main form name]![subform control name].Form![control name]"

Can anyone direct me in the right direction??

Thanks

 

[Ken Snell \(MVP\)]

Tell us what the SQL statement is for the query, and what type of data are
in the subform's control that you're using. The error you're getting often
results when the data value is incomplete (e.g., a date of 1//2006), or a
Null value is being passed to a function that cannot handle a Null value, or
the query is dividing a number by zero, or...

 

[FattyFatty]

Okie dokie, here's some more guff:

The end result I'm wanting is just a count of the number of records
that the query brings back.

If I remove the criterion that looks at the control, it works perfectly
(except that it gives me the count based on all teams, as opposed to
being limited to team by the control criteria like I need).

Here's the SQL:
SELECT Count(tbl_A_StaffDetails.Staff_Name_Full) AS
CountOfStaff_Name_Full
FROM tbl_A_StaffDetails
HAVING
(((tbl_A_StaffDetails.Staff_TeamNumber)=[Forms]![frm_C01_MaintainTeamsAndStaff]![subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam].[Form]![cmb_TeamName])
AND ((tbl_A_StaffDetails.Staff_ActiveIndicator)=Yes));

Table fields being used in the query:
Staff_Name_Full - Text
Staff_TeamNumber - Number (Byte format)
Staff_ActiveIndicator - Yes/No (Yes/No format)

The control on the subform (cmb_TeamName) is a combobox based on a
table with 5 columns. Here's some properties of the combobox that
might be helpful:
Column Count - 5
Column Widths - 0cm;3cm;0cm;0cm;0cm
Bound Column - 1
The visible column is text format and the bound column is Number (Byte
format)

Hope this is more what you were wanting - thanks for looking at this by
the way!

 

[Ken Snell \(MVP\)]

Two thoughts for the error cause:

1) If tbl_A_StaffDetails.Staff_ActiveIndicator is a boolean (Yes/No) field,
then the Yes in the query should be replaced by True.

2) If the combo box on the subform can have "no value" (Null) when the query
is run, then the query can have the error you see.

Try this for your query:

SELECT Count(tbl_A_StaffDetails.Staff_Name_Full) AS
CountOfStaff_Name_Full
FROM tbl_A_StaffDetails
WHERE
(((tbl_A_StaffDetails.Staff_TeamNumber)=Nz([Forms]![frm_C01_MaintainTeamsAndStaff]![subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam].[Form]![cmb_TeamName],
""))
AND ((tbl_A_StaffDetails.Staff_ActiveIndicator)=True));


I've changed Yes to True, wrapped the combo box value with Nz function to
replace Null with empty string if the combo box has a Null value, and
changed HAVING to WHERE so that the query runs faster and more efficiently
(WHERE filters the records before doing the counting; HAVING selects all the
records, then filters out the ones that you don't want and then does the
counting).
--

Ken Snell
<MS ACCESS MVP>

Okie dokie, here's some more guff:

The end result I'm wanting is just a count of the number of records
that the query brings back.

If I remove the criterion that looks at the control, it works perfectly
(except that it gives me the count based on all teams, as opposed to
being limited to team by the control criteria like I need).

Here's the SQL:
SELECT Count(tbl_A_StaffDetails.Staff_Name_Full) AS
CountOfStaff_Name_Full
FROM tbl_A_StaffDetails
HAVING
(((tbl_A_StaffDetails.Staff_TeamNumber)=[Forms]![frm_C01_MaintainTeamsAndStaff]![subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam].[Form]![cmb_TeamName])
AND ((tbl_A_StaffDetails.Staff_ActiveIndicator)=Yes));

Table fields being used in the query:
Staff_Name_Full - Text
Staff_TeamNumber - Number (Byte format)
Staff_ActiveIndicator - Yes/No (Yes/No format)

The control on the subform (cmb_TeamName) is a combobox based on a
table with 5 columns. Here's some properties of the combobox that
might be helpful:
Column Count - 5
Column Widths - 0cm;3cm;0cm;0cm;0cm
Bound Column - 1
The visible column is text format and the bound column is Number (Byte
format)

Hope this is more what you were wanting - thanks for looking at this by
the way!

Tell us what the SQL statement is for the query, and what type of data
are
in the subform's control that you're using. The error you're getting
often
results when the data value is incomplete (e.g., a date of 1//2006), or a
Null value is being passed to a function that cannot handle a Null value,
or
the query is dividing a number by zero, or...

 

[FattyFatty]

Looks good - I'm off home now so I'll try your suggestions first thing
tomorrow morning and get back to you.
Thanks for all your help so far Ken

 

[FattyFatty]

Okay, I've...
1) Changed the Staff_ActiveIndicator critera to TRUE.
2) Changed the HAVING clause to WHERE.
Still the same problem.

The combobox can never be null, but I've added the Nz function in there
anyway - same problem. I've never used the Nz function - but I have
done complicated workarounds before!! I'll remember that for later -
shouldn't be too hard seeing as I'm from New Zealand (NZ)!

To triple check that I have all the form names and control correct,
with the main form open, I did a vb watch (I think that's what it's
called? - CTRL+G) using:
?
Forms!frm_C01_MaintainTeamsAndStaff!subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam.Form!cmb_TeamName
It returned "MATTS TEAM" which is what was SELECTED in the combobox,
but it didn't return the bound column, which is what I need.... so
that's a problem - My guess is that it's probably this that's causing
the problem?

I could add another combobox and change the visible column so that the
team number is shown instead of the team name, but I'd prefer to learn
how to do it properly! Is there any particular way I should be
referencing the combobox to get the bound column instead of the visible
column?

Two thoughts for the error cause:

 

[FattyFatty]

Hmmm...

I've found the code to refer the a specific column, but It doesn't seem
to be working for me?? I need to add ".Column(X)" to the end.

I did another vb watch:

?
Forms!frm_C01_MaintainTeamsAndStaff!subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam.Form!cmb_TeamName
Returns... MATTS TEAM

?
Forms!frm_C01_MaintainTeamsAndStaff!subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam.Form!cmb_TeamName.Column(1)
Returns ... MATTS TEAM
?
Forms!frm_C01_MaintainTeamsAndStaff!subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam.Form!cmb_TeamName.Column(2)
Returns ... nothing, it's blank??

?
Forms!frm_C01_MaintainTeamsAndStaff!subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam.Form!cmb_TeamName.Column(3
Returns ... nothing, it's blank??

?
Forms!frm_C01_MaintainTeamsAndStaff!subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam.Form!cmb_TeamName.Column(4)
Returns ... nothing, it's blank??

?
Forms!frm_C01_MaintainTeamsAndStaff!subfrm_C05_Sub_MaintainTeamDetails_DeleteTeam.Form!cmb_TeamName.Column(5)
Returns ... NULL

None of them return the team number??

I thought it may have something to do with the column being visible or
not, so I changed the visible columns so that both the Team Number and
Team Name were visible (cols 1 & 2), but then all columns returned
nothing??? Not even NULL.

 

[Ken Snell \(MVP\)]

What is the RowSource query's SQL statement for the combo box? Note that a
query can read only the Bound Column from the combo box, which is the Value
of the combo box. You cannot read any other column from the combo box
directly from the query -- you either need to call a function that will read
that other column and then return the value to the query, or you need to put
an invisible textbox on the form that has an expression that reads that
value, and then have the query read the value of the combo box.

 

[FattyFatty]

I'm not 100% sure what you mean by RowSource query's SQL - but I'm
guessing it's what's in the Row Source property of the combo box?

If so, then it's just: tbl_B_LISTof_Team_NameLeaderDepartmentCentre

The row source is just looking at a table that has a list of team
numbers, names and team leaders etc.

 

[Ken Snell \(MVP\)]

Yes, the RowSource property. When a table name is there, ACCESS actually
creates a query under the covers:

SELECT * FROM tbl_B_LISTof_Team_NameLeaderDepartmentCentre;

So, let's see a list of the field names (from design view, in the order of
top to bottom).

 

[FattyFatty]

Hmmm. One of your other comments gave me an idea - I deleted the
combobox and created a new one.

Now it works. One of the properties must have been set wrong - but for
the life of me I couldn't figure out which one!

Thanks for all your help Ken - got there in the end!

 

[Ken Snell \(MVP\)]

Glad you got it to work!