RE: Crosstab Query Subform - Criteria from Combo Box on Main Form?

Tech-Archive recommends: Fix windows errors by optimizing your registry

"Duane Hookom" wrote:

You might want to just set the SourceObject of the subform control to the
query name. There is a sample of how this is done at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

The significant code is:
Me.subformctrl.SourceObject = "Query.qxtbYourCrosstabName"

--
Duane Hookom
Microsoft Access MVP

Thanks Duane. I think I'm getting there, but I've run into a roadblock. I
get an error message "Run-time error '3265'" Item not found in this
collection".

I've got two questions. The first and major issue is - what do I need to
change to get the code to execute? The second is why couldn't I put a WHERE
clause in the code without putting the "If ... Then ..." statement around it
(see below)?

I've entered (as best I can) appropriate code in the AfterUpdate event for
my 2nd Combo Box. When I open the main form, I select a county in the first
Box [cboCounties]. That limits the organization names available in the 2nd
box to those in that County. So far, so good. But when I select an
organization in [cboOrgs] I get the run time error.

When I click "debug" the VB editor opens and highlights this line:

Set qd = db.QueryDefs(strXTabQueryName)

Below is the code I've entered in [cboOrgs_AfterUpdate]. I saw 3 different
aspects of your code I had to modify:

The major change (I think) I had to make - You have an If...Then SQL
statement built around Year such that if Year <> 0 (that is if the user has
chosen an individual year), then you add a WHERE clause to select the Year
chosen by the user. Then, you PIVOT by that Year as the column. My columns
are also years, but I don't want the user to choose which year to display,
but rather to choose which organization for which the crosstab will be run.
Therefore, I always want a WHERE clause to define the Organization, but
Organization Name or ID# will not appear in the results - rows are services
reported and columns are years.

But when I tried to put the WHERE clause in without the "If ... Then ..."
surrounding it I kept getting error messages in the Editor. I finally tried
it with the "If ... Then ..." clause and it "worked". Fact is under my logic
there will never be a case when the " ... Then ..." part of the clause will
"rule" because there will always be an organization chosen. (Don't know why I
couldn't just do a WHERE, and don't know if this has anything to do with my
debug problem. Do you see why?)

The other two changes, I think, are minor (I think). First, I don't need the
"Me.txtSQL = ..." statement because I don't have a field showing the code -
so I 'commented' that line out of the executing code. Second, whereas your
example is showing how to control a dynamically generated crosstab in a
subform, you don't actually have a subform - you have a field on the form
named [subformctrl]. You set the SourceObject of that field. So I tried in my
code to refer to my subform when I set the SourceObject and not a field on my
form. (I don't know if this is relevent because the code has not executed
down to that line yet.)

Here's the code I've got at this point. I'm producing my crosstab on the
results of a saved query - QOrg_S1_AllYrs_ServNumSort. I'm putting the
symbols <???> above and below the lines I think are involved in my questions.

Do you see something I need to change? - thanks.

Private Sub cboOrgs_AfterUpdate()
Dim db As DAO.Database
Dim qd As DAO.QueryDef
Dim strSQL As String
Dim strXTabQueryName As String
strXTabQueryName = "qxtbOrgServVsYr"
Set db = CurrentDb
<???>
Set qd = db.QueryDefs(strXTabQueryName)
<???>
strSQL = "TRANSFORM First(QOrg_S1_AllYrs_ServNumSort.Y_N) AS FirstOfY_N"
& vbCrLf & _
"SELECT QOrg_S1_AllYrs_ServNumSort.Service" & vbCrLf & _
"FROM QOrg_S1_AllYrs_ServNumSort" & vbCrLf
<???>
If Me.cboCounties <> Null Then
strSQL = strSQL & "WHERE (QOrg_S1_AllYrs_ServNumSort.OrgID) = " &
[Forms]![FA1_OrgMaster_All]![cboOrgs] & vbCrLf
End If
<???>
strSQL = strSQL & " " & _
"GROUP BY QOrg_S1_AllYrs_ServNumSort.Service " & vbCrLf & _
"PIVOT QOrg_S1_AllYrs_ServNumSort.Year;"
qd.SQL = strSQL
' Me.txtSQL = strSQL - this was in Duane's code, but it was populating a
field on his form to show the code - not necessary for me
Set qd = Nothing
Set db = Nothing
' Me.subformctrl.SourceObject = "Query." & strXTabQueryName - also in
Duane's code, but it was for a form field named
' "subformctrl" - whereas I need to define the SourceObject of a subform
on a subform
<???>
Me.[FA1s5b_SCOSrvcs].SourceObject = "Query." & strXTabQueryName
<???>
End Sub
.

Relevant Pages

  • RE: Help with Insert Into
    ... and now I get an error message "Invalid use of Null" ... I have the subform set up with the Record_ID and Oblig_ID from Tbl_JUNCTION ... you could change str back to strSQL. ... Dim ObligID as Long ...
    (microsoft.public.access.modulesdaovba)
  • RE: Help with Insert Into
    ... perhaps you could get your values from it rather than the form/ subform. ... and now I get an error message "Invalid use of Null" ... you could change str back to strSQL. ... Dim ObligID as Long ...
    (microsoft.public.access.modulesdaovba)
  • RE: Crosstab Query Subform - Criteria from Combo Box on Main Form?
    ... "Duane Hookom" wrote: ... case the "test" results in False, resulting in the WHERE clause not being put ... subformctrl is a field; in mine it's a subform (actually a subform on a ... Dim db As DAO.Database ...
    (microsoft.public.access.formscoding)
  • RE: Crosstab Query Subform - Criteria from Combo Box on Main Form?
    ... control on my form named "subformctrl" is a subform. ... get an error message "Run-time error '3265'" Item not found in this ... but rather to choose which organization for which the crosstab will be run. ... Dim db As DAO.Database ...
    (microsoft.public.access.formscoding)
  • RE: Work hours by Ken Sheridan.
    ... "Duane Hookom" wrote: ... Dim strMinutesSeconds As String ... Dim dtmDay As Date ... ' if start time after lunch subtract lunch time ...
    (microsoft.public.access.queries)