Generating SQL w/2 "WHERE" Arguments from cbo

[John D]

I wrote this up before - but when I tried to Post the system asked me to log
in (which I had already done), and 30 minutes later the post has not shown
up. If it shows up after this post - take your choice as to which post to
read - but they are the same question. ---

I have a Master Form [FA1_OrgMaster_All] that has a Combo Box [cboOrgs]. I
have a subform [FA1s7_FinRpts] that has another Combo Box [cboIS_BySrv_List].
The user selects a nonprofit organization in the first cbo, and the
After_Update Event Procedure populates the second cbo with a list of services
provided by that nonprofit.

I want the user to be able to select one of the services listed in the
second cbo and have that cbo's On_Click Event display an Income Statement for
that service on screen.

My approach is to have the On_Click Event first generate a new SQL query
that will be saved to a query that will "drive" the generation of the report
I want to be previewed. I will then Call the report preview. But I can't get
past the SQL generation. In similar situations I am able to do so, BUT THIS
SITUATION HAS TWO "WHERE" ARGUMENTS INSTEAD OF ONE, which is the case in
those other situations. The report to be displayed is for a specific
nonprofit and a specific service provided by that nonprofit.

Here's the vb code:

Dim strSQL As String

strSQL = "SELECT DISTINCT TAaaOrg.OrgID, (and lots more fields selected)

"FROM TYaf_FYEnd RIGHT JOIN (and lots more joins - that aren't the problem)

(Here's the problem -->)

"WHERE (TAaaOrg.OrgID) = " & [Forms]![FA1_OrgMaster_All]![cboOrgs] & vbCrLf

strSQL = strSQL & " " & _

"AND (QXR_1As3_BottomLineAccts.Actvty) = " &
[Forms]![FA1s7_FinRpts]![cboIS_BySrv_List] & vbCrLf

strSQL = strSQL & " " & _

"ORDER BY TAaaOrg.OrgName;"

I admit I added the ORDER BY clause to try to solve a problem I was having
finishing the statement. It isn't necessary to have an ORDER BY because only
one organization is being selected. I'd prefer to close out the SQL
generating code after the "WHERE" clause.

The error message is - "Run-time error '2450': ... can't find the form
'FA1s7_FinRpts' ...".

When I click Debug this code is highlighted:

strSQL = strSQL & " " & _

"AND (QXR_1As3_BottomLineAccts.Actvty) = " &
[Forms]![FA1s7_FinRpts]![cboIS_BySrv_List] & vbCrLf

What the H!**$%!?! am I doing wrong?

Thanks - John D

 

[Cheese_whiz]

"AND (QXR_1As3_BottomLineAccts.Actvty) = " &
[Forms]![FA1s7_FinRpts]![cboIS_BySrv_List] & vbCrLf "

You can't refer to a subform like that. I think it needs to be something
like this:

[Forms]![YourSubFormControlName].[Form]![cboIS_BySrv_List]

I don't get the whole & vbCrLf in the string query, but that's beside the
point I suppose. Just try fixing the reference to the control in the subform
as laid out.

PS: The [Form] part is typed just like that: [Form], but you need to plug in
the name of your subform control.

CW

I wrote this up before - but when I tried to Post the system asked me to log
in (which I had already done), and 30 minutes later the post has not shown
up. If it shows up after this post - take your choice as to which post to
read - but they are the same question. ---

I have a Master Form [FA1_OrgMaster_All] that has a Combo Box [cboOrgs]. I
have a subform [FA1s7_FinRpts] that has another Combo Box [cboIS_BySrv_List].
The user selects a nonprofit organization in the first cbo, and the
After_Update Event Procedure populates the second cbo with a list of services
provided by that nonprofit.

I want the user to be able to select one of the services listed in the
second cbo and have that cbo's On_Click Event display an Income Statement for
that service on screen.

My approach is to have the On_Click Event first generate a new SQL query
that will be saved to a query that will "drive" the generation of the report
I want to be previewed. I will then Call the report preview. But I can't get
past the SQL generation. In similar situations I am able to do so, BUT THIS
SITUATION HAS TWO "WHERE" ARGUMENTS INSTEAD OF ONE, which is the case in
those other situations. The report to be displayed is for a specific
nonprofit and a specific service provided by that nonprofit.

Here's the vb code:

Dim strSQL As String

strSQL = "SELECT DISTINCT TAaaOrg.OrgID, (and lots more fields selected)

"FROM TYaf_FYEnd RIGHT JOIN (and lots more joins - that aren't the problem)

(Here's the problem -->)

"WHERE (TAaaOrg.OrgID) = " & [Forms]![FA1_OrgMaster_All]![cboOrgs] & vbCrLf

strSQL = strSQL & " " & _

"AND (QXR_1As3_BottomLineAccts.Actvty) = " &
[Forms]![FA1s7_FinRpts]![cboIS_BySrv_List] & vbCrLf

strSQL = strSQL & " " & _

"ORDER BY TAaaOrg.OrgName;"

I admit I added the ORDER BY clause to try to solve a problem I was having
finishing the statement. It isn't necessary to have an ORDER BY because only
one organization is being selected. I'd prefer to close out the SQL
generating code after the "WHERE" clause.

The error message is - "Run-time error '2450': ... can't find the form
'FA1s7_FinRpts' ...".

When I click Debug this code is highlighted:

strSQL = strSQL & " " & _

"AND (QXR_1As3_BottomLineAccts.Actvty) = " &
[Forms]![FA1s7_FinRpts]![cboIS_BySrv_List] & vbCrLf

What the H!**$%!?! am I doing wrong?

Thanks - John D

 

[Josh D]

Refer to the subForm as its role on the main form when the action is taking
place and not as the form name -- me.child(childName)![cboIS_BySrv_List]

"AND (QXR_1As3_BottomLineAccts.Actvty) = " &
[Forms]![FA1s7_FinRpts]![cboIS_BySrv_List] & vbCrLf "

You can't refer to a subform like that. I think it needs to be something
like this:

[Forms]![YourSubFormControlName].[Form]![cboIS_BySrv_List]

I don't get the whole & vbCrLf in the string query, but that's beside the
point I suppose. Just try fixing the reference to the control in the subform
as laid out.

PS: The [Form] part is typed just like that: [Form], but you need to plug in
the name of your subform control.

CW

I wrote this up before - but when I tried to Post the system asked me to log
in (which I had already done), and 30 minutes later the post has not shown
up. If it shows up after this post - take your choice as to which post to
read - but they are the same question. ---

I have a Master Form [FA1_OrgMaster_All] that has a Combo Box [cboOrgs]. I
have a subform [FA1s7_FinRpts] that has another Combo Box [cboIS_BySrv_List].
The user selects a nonprofit organization in the first cbo, and the
After_Update Event Procedure populates the second cbo with a list of services
provided by that nonprofit.

I want the user to be able to select one of the services listed in the
second cbo and have that cbo's On_Click Event display an Income Statement for
that service on screen.

My approach is to have the On_Click Event first generate a new SQL query
that will be saved to a query that will "drive" the generation of the report
I want to be previewed. I will then Call the report preview. But I can't get
past the SQL generation. In similar situations I am able to do so, BUT THIS
SITUATION HAS TWO "WHERE" ARGUMENTS INSTEAD OF ONE, which is the case in
those other situations. The report to be displayed is for a specific
nonprofit and a specific service provided by that nonprofit.

Here's the vb code:

Dim strSQL As String

strSQL = "SELECT DISTINCT TAaaOrg.OrgID, (and lots more fields selected)

"FROM TYaf_FYEnd RIGHT JOIN (and lots more joins - that aren't the problem)

(Here's the problem -->)

"WHERE (TAaaOrg.OrgID) = " & [Forms]![FA1_OrgMaster_All]![cboOrgs] & vbCrLf

strSQL = strSQL & " " & _

"AND (QXR_1As3_BottomLineAccts.Actvty) = " &
[Forms]![FA1s7_FinRpts]![cboIS_BySrv_List] & vbCrLf

strSQL = strSQL & " " & _

"ORDER BY TAaaOrg.OrgName;"

I admit I added the ORDER BY clause to try to solve a problem I was having
finishing the statement. It isn't necessary to have an ORDER BY because only
one organization is being selected. I'd prefer to close out the SQL
generating code after the "WHERE" clause.

The error message is - "Run-time error '2450': ... can't find the form
'FA1s7_FinRpts' ...".

When I click Debug this code is highlighted:

strSQL = strSQL & " " & _

"AND (QXR_1As3_BottomLineAccts.Actvty) = " &
[Forms]![FA1s7_FinRpts]![cboIS_BySrv_List] & vbCrLf

What the H!**$%!?! am I doing wrong?

Thanks - John D

 

[John D]

Gosh Josh (D) - And Mr Cheese_too

Actually, it turned out much simpler. When Josh said "...as its role on the
main form when the action is taking place ...", my "mind" (what's left of it
these days) bounced off that and realized - hey, I didn't need to "name" the
subform because I was ON the subform when the cbo gets clicked. (DUHHHH) So
- turned out simple:

& Me![cboIS_BySrv_List]