Take Parameter from Record Selected on Continuous Form to Run Quer

I have a database for tracking items and money given to needy clients. I
have a Search form I designed based on Allen Browne's tips with entry text
boxes on top, and a filtered continuous form below. Each record in the
continuous form has an "Open" button which opens the Client's record,
frmClientsSearch. My frmClientsSearch is based on the Clients table, with a
subform based on the AddKids table and another subform based on a Referrals
query (First, money from each church is summed by referral, then a second
query pulls all the Refferal data with the total included).

Everything was working fine until I installed it on a wireless network on
Sunday, and I noticed the query was running very slowly. It turns out the
query is going through all the records in the database to sum the amounts,
and then selecting the records to display. I would like to limit the query
to search based on Client_Number, but I can't figure out how to refer to the
Client Number on the selected record on the continuous form of the search
record. In my Open Form button, it uses "[Client_Number] = " &
Me.Client_Number. I tried putting Me.Client_Number in the Criteria field for
the queries, but it does not recognize it (I get a parameter request). I
also tried adding the Client Number as OpenArgs in the OpenForm procedure,
but I can't get the query to link to that either. What is the proper way to
do this?

Thank you,
Valerie Terbush
--
Valerie

On Tue, 22 Sep 2009 20:30:01 -0700, Valerie
wrote:

I have a database for tracking items and money given to needy clients. I
have a Search form I designed based on Allen Browne's tips with entry text
boxes on top, and a filtered continuous form below. Each record in the
continuous form has an "Open" button which opens the Client's record,
frmClientsSearch. My frmClientsSearch is based on the Clients table, with a
subform based on the AddKids table and another subform based on a Referrals
query (First, money from each church is summed by referral, then a second
query pulls all the Refferal data with the total included).

Everything was working fine until I installed it on a wireless network on
Sunday, and I noticed the query was running very slowly. It turns out the
query is going through all the records in the database to sum the amounts,
and then selecting the records to display. I would like to limit the query
to search based on Client_Number, but I can't figure out how to refer to the
Client Number on the selected record on the continuous form of the search
record. In my Open Form button, it uses "[Client_Number] = " &
Me.Client_Number. I tried putting Me.Client_Number in the Criteria field for
the queries, but it does not recognize it (I get a parameter request). I
also tried adding the Client Number as OpenArgs in the OpenForm procedure,
but I can't get the query to link to that either. What is the proper way to
do this?

Thank you,
Valerie Terbush

Correct the error in your query. If you would like help doing so please post
the SQL of the query.

I am guessing that your criteria are in the query's HAVING clause (which is
applied after totalling) rather than the WHERE clause. You can fix it in the
grid by changing the Group By operation under Client_Number to "Where".
--

John W. Vinson [MVP]

I didn't have criteria before when it was running slowly. Now when I am
adding criteria I don't think I have the references right since it is asking
me for a parameter instead of using the information I thought I told it in
code.

Here is the code I am currently using to open the form. Note, I am trying
to pass through the Client_Number as OpenArgs (CN):

Private Sub CmdOpenRecord_Click()
If Not IsNull(Me.Client_Number) Then
Dim CN As Long
CN = Me.Client_Number
MsgBox (CN)
DoCmd.OpenForm "frmClientsSearch", acNormal, "", "[Client_Number] =
" & Me.Client_Number, acFormReadOnly, acNormal, CN
End If
End Sub

Here are the queries:
1) Sums amount for each referral. I just tried changing HAVING to WHERE,
but it then tells me I have a syntax error in my expression.

SELECT Referrals.Client_Number, Referrals.Referral_ID, Sum(Money.Amount) AS
SumOfAmount
FROM Referrals LEFT JOIN [Money] ON Referrals.Referral_ID = Money.Referral_ID
GROUP BY Referrals.Client_Number, Referrals.Referral_ID
HAVING ((Referrals.Client_Number)=[Me].[OpenArgs]);

2) Returns all information for each referral plus the total amount given for
that refferal (summed in #1)

SELECT Referrals.*, qryMoneyByClient.SumOfAmount
FROM Referrals INNER JOIN qryMoneyByClient ON (Referrals.Client_Number =
qryMoneyByClient.Client_Number) AND (Referrals.Referral_ID =
qryMoneyByClient.Referral_ID)
WHERE (((Referrals.Client_Number)=[Me].[OpenArgs]));


Right now when I run these, it prompts me for [Me].[OpenArgs]

Thank you for your help!

--
Valerie


"John W. Vinson" wrote:

On Tue, 22 Sep 2009 20:30:01 -0700, Valerie
wrote:

I have a database for tracking items and money given to needy clients. I
have a Search form I designed based on Allen Browne's tips with entry text
boxes on top, and a filtered continuous form below. Each record in the
continuous form has an "Open" button which opens the Client's record,
frmClientsSearch. My frmClientsSearch is based on the Clients table, with a
subform based on the AddKids table and another subform based on a Referrals
query (First, money from each church is summed by referral, then a second
query pulls all the Refferal data with the total included).

Everything was working fine until I installed it on a wireless network on
Sunday, and I noticed the query was running very slowly. It turns out the
query is going through all the records in the database to sum the amounts,
and then selecting the records to display. I would like to limit the query
to search based on Client_Number, but I can't figure out how to refer to the
Client Number on the selected record on the continuous form of the search
record. In my Open Form button, it uses "[Client_Number] = " &
Me.Client_Number. I tried putting Me.Client_Number in the Criteria field for
the queries, but it does not recognize it (I get a parameter request). I
also tried adding the Client Number as OpenArgs in the OpenForm procedure,
but I can't get the query to link to that either. What is the proper way to
do this?

Thank you,
Valerie Terbush

Correct the error in your query. If you would like help doing so please post
the SQL of the query.

I am guessing that your criteria are in the query's HAVING clause (which is
applied after totalling) rather than the WHERE clause. You can fix it in the
grid by changing the Group By operation under Client_Number to "Where".
--

John W. Vinson [MVP]

OK, I understand what you meant about changing from Group By to Where on the
grid, and I changed that. That will help the speed, but it is still asking
for a parameter. I have tried the following as criteria:

[Me].[OpenArgs]
[frmClientsSearch].[OpenArgs]
[frmSearchClients].[Client_Number] (The previous search form is named

frmSearchClients)
All ask me for a parameter. What am I doing wrong in the naming?

Thank you!
--
Valerie


"Valerie" wrote:

I didn't have criteria before when it was running slowly. Now when I am
adding criteria I don't think I have the references right since it is asking
me for a parameter instead of using the information I thought I told it in
code.

Here is the code I am currently using to open the form. Note, I am trying
to pass through the Client_Number as OpenArgs (CN):

Private Sub CmdOpenRecord_Click()
If Not IsNull(Me.Client_Number) Then
Dim CN As Long
CN = Me.Client_Number
MsgBox (CN)
DoCmd.OpenForm "frmClientsSearch", acNormal, "", "[Client_Number] =
" & Me.Client_Number, acFormReadOnly, acNormal, CN
End If
End Sub

Here are the queries:
1) Sums amount for each referral. I just tried changing HAVING to WHERE,
but it then tells me I have a syntax error in my expression.

SELECT Referrals.Client_Number, Referrals.Referral_ID, Sum(Money.Amount) AS
SumOfAmount
FROM Referrals LEFT JOIN [Money] ON Referrals.Referral_ID = Money.Referral_ID
GROUP BY Referrals.Client_Number, Referrals.Referral_ID
HAVING ((Referrals.Client_Number)=[Me].[OpenArgs]);

2) Returns all information for each referral plus the total amount given for
that refferal (summed in #1)

SELECT Referrals.*, qryMoneyByClient.SumOfAmount
FROM Referrals INNER JOIN qryMoneyByClient ON (Referrals.Client_Number =
qryMoneyByClient.Client_Number) AND (Referrals.Referral_ID =
qryMoneyByClient.Referral_ID)
WHERE (((Referrals.Client_Number)=[Me].[OpenArgs]));


Right now when I run these, it prompts me for [Me].[OpenArgs]

Thank you for your help!

--
Valerie


"John W. Vinson" wrote:

On Tue, 22 Sep 2009 20:30:01 -0700, Valerie
wrote:

I have a database for tracking items and money given to needy clients. I
have a Search form I designed based on Allen Browne's tips with entry text
boxes on top, and a filtered continuous form below. Each record in the
continuous form has an "Open" button which opens the Client's record,
frmClientsSearch. My frmClientsSearch is based on the Clients table, with a
subform based on the AddKids table and another subform based on a Referrals
query (First, money from each church is summed by referral, then a second
query pulls all the Refferal data with the total included).

Everything was working fine until I installed it on a wireless network on
Sunday, and I noticed the query was running very slowly. It turns out the
query is going through all the records in the database to sum the amounts,
and then selecting the records to display. I would like to limit the query
to search based on Client_Number, but I can't figure out how to refer to the
Client Number on the selected record on the continuous form of the search
record. In my Open Form button, it uses "[Client_Number] = " &
Me.Client_Number. I tried putting Me.Client_Number in the Criteria field for
the queries, but it does not recognize it (I get a parameter request). I
also tried adding the Client Number as OpenArgs in the OpenForm procedure,
but I can't get the query to link to that either. What is the proper way to
do this?

Thank you,
Valerie Terbush

Correct the error in your query. If you would like help doing so please post
the SQL of the query.

I am guessing that your criteria are in the query's HAVING clause (which is
applied after totalling) rather than the WHERE clause. You can fix it in the
grid by changing the Group By operation under Client_Number to "Where".
--

John W. Vinson [MVP]

I just got it to work! I used [Clients].[Client_Number], which is how the
Referrals subform is linked to the frmClientsSearch main form. I thought I
had tried this yesterday, but there must have been something else wrong at
that point that is fixed now.

Thank you for your help.
--
Valerie


"Valerie" wrote:

OK, I understand what you meant about changing from Group By to Where on the
grid, and I changed that. That will help the speed, but it is still asking
for a parameter. I have tried the following as criteria:

[Me].[OpenArgs]
[frmClientsSearch].[OpenArgs]
[frmSearchClients].[Client_Number] (The previous search form is named

frmSearchClients)
All ask me for a parameter. What am I doing wrong in the naming?

Thank you!
--
Valerie


"Valerie" wrote:

I didn't have criteria before when it was running slowly. Now when I am
adding criteria I don't think I have the references right since it is asking
me for a parameter instead of using the information I thought I told it in
code.

Here is the code I am currently using to open the form. Note, I am trying
to pass through the Client_Number as OpenArgs (CN):

Private Sub CmdOpenRecord_Click()
If Not IsNull(Me.Client_Number) Then
Dim CN As Long
CN = Me.Client_Number
MsgBox (CN)
DoCmd.OpenForm "frmClientsSearch", acNormal, "", "[Client_Number] =
" & Me.Client_Number, acFormReadOnly, acNormal, CN
End If
End Sub

Here are the queries:
1) Sums amount for each referral. I just tried changing HAVING to WHERE,
but it then tells me I have a syntax error in my expression.

SELECT Referrals.Client_Number, Referrals.Referral_ID, Sum(Money.Amount) AS
SumOfAmount
FROM Referrals LEFT JOIN [Money] ON Referrals.Referral_ID = Money.Referral_ID
GROUP BY Referrals.Client_Number, Referrals.Referral_ID
HAVING ((Referrals.Client_Number)=[Me].[OpenArgs]);

2) Returns all information for each referral plus the total amount given for
that refferal (summed in #1)

SELECT Referrals.*, qryMoneyByClient.SumOfAmount
FROM Referrals INNER JOIN qryMoneyByClient ON (Referrals.Client_Number =
qryMoneyByClient.Client_Number) AND (Referrals.Referral_ID =
qryMoneyByClient.Referral_ID)
WHERE (((Referrals.Client_Number)=[Me].[OpenArgs]));


Right now when I run these, it prompts me for [Me].[OpenArgs]

Thank you for your help!

--
Valerie


"John W. Vinson" wrote:

On Tue, 22 Sep 2009 20:30:01 -0700, Valerie
wrote:

I have a database for tracking items and money given to needy clients. I
have a Search form I designed based on Allen Browne's tips with entry text
boxes on top, and a filtered continuous form below. Each record in the
continuous form has an "Open" button which opens the Client's record,
frmClientsSearch. My frmClientsSearch is based on the Clients table, with a
subform based on the AddKids table and another subform based on a Referrals
query (First, money from each church is summed by referral, then a second
query pulls all the Refferal data with the total included).

Everything was working fine until I installed it on a wireless network on
Sunday, and I noticed the query was running very slowly. It turns out the
query is going through all the records in the database to sum the amounts,
and then selecting the records to display. I would like to limit the query
to search based on Client_Number, but I can't figure out how to refer to the
Client Number on the selected record on the continuous form of the search
record. In my Open Form button, it uses "[Client_Number] = " &
Me.Client_Number. I tried putting Me.Client_Number in the Criteria field for
the queries, but it does not recognize it (I get a parameter request). I
also tried adding the Client Number as OpenArgs in the OpenForm procedure,
but I can't get the query to link to that either. What is the proper way to
do this?

Thank you,
Valerie Terbush

Correct the error in your query. If you would like help doing so please post
the SQL of the query.

I am guessing that your criteria are in the query's HAVING clause (which is
applied after totalling) rather than the WHERE clause. You can fix it in the
grid by changing the Group By operation under Client_Number to "Where".
--

John W. Vinson [MVP]