Filter information in a subform

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form that contains 3 subforms. Two of the subforms are working
correctly, however I am having a problem getting my information to display
correctly on the third subform.

Within this subform the customer number displayed is keyed to the main form
customer number and is working correctly. However, when you select the
dropdown for the combo box on the account number field within that subform it
shows all of the account numbers for all the customers. I want it to only
show the account numbers that pertain to the customer number shown in the
main form.

How can I do this, please?

David Vollmer
 
David,
Filter the query behind your sub's combo box by including the CustNo field in that
query and using a criteria of...
(use your own names)
=Forms!frmYourMainFormName!frmYourSubformName.Form!CustomerNumber

I'm assuming the Main and Sub forms are linked (Parent to Child) via the
CustomerNumber. If so, there's no need to "display" the CustomerNumber in the sub. Every
time you add a new record, the linked CustomerNumber value is forced into the
CustomerNumber field by the relationship.
 
Al,

Thank you for your reply. I have tried to do what you suggested but am still
not having any luck.

The SQL view of my query within theAccountNumberfield of the subform reads:
SELECT tblCustAndAcctNumbers.[Account Numbers]
FROM tblCustAndAcctNumbers
WHERE (((tblCustAndAcctNumbers.[Account
Numbers])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))
ORDER BY tblCustAndAcctNumbers.[Account Numbers]

My main form is called xfrmCustomerReview.
The subform which is tied to the customer number from the main form is
called xfrmCustomerReviewSubform.
The other subforms on the main form are frmCustAndAcctNumbers and
frmAddlCustNumbers which are working correctly.

The frmCustAndAcctNumbers form on the main form contains the account numbers
that pertain to the displayed customer and are the numbers that I want to be
able to select in the AccountNumber field of the xfrmCustomerReviewSubform.

I can enter the account numbers manually in the subform and the dropdown box
will show me the numbers as I enter them. At the point at which I have a
match I can tab to the next field and enter the remaining information for the
subform.

I am fairly new to Access and am still learning much of what I need to be
doing as I go along. Productivity is at a snail's pace at present.

Thank you for helping me!

David
 
Al,

I moved the SQL language to the CustomerNumber field of the query and it
reads:
[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]

When I save the forms and open the main form (frmCustomerReview) and click
in the AccountNumberID field of the frmCustomerReviewSubform, I get a
parameter value prompt to enter the customer number. The prompt reads:
Forms!xfrmCustomerReview!xfrmCustomerReviewSubform.Form!CustomerNumber

When I enter the current customer number and press Enter, the applicable
account numbers for the customer will be displayed and I can choose one. If I
go to another record and click in the account number field of the subform,
the same account numbers are displayed??

David

David Vollmer said:
Al,

Thank you for your reply. I have tried to do what you suggested but am still
not having any luck.

The SQL view of my query within theAccountNumberfield of the subform reads:
SELECT tblCustAndAcctNumbers.[Account Numbers]
FROM tblCustAndAcctNumbers
WHERE (((tblCustAndAcctNumbers.[Account
Numbers])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))
ORDER BY tblCustAndAcctNumbers.[Account Numbers]

My main form is called xfrmCustomerReview.
The subform which is tied to the customer number from the main form is
called xfrmCustomerReviewSubform.
The other subforms on the main form are frmCustAndAcctNumbers and
frmAddlCustNumbers which are working correctly.

The frmCustAndAcctNumbers form on the main form contains the account numbers
that pertain to the displayed customer and are the numbers that I want to be
able to select in the AccountNumber field of the xfrmCustomerReviewSubform.

I can enter the account numbers manually in the subform and the dropdown box
will show me the numbers as I enter them. At the point at which I have a
match I can tab to the next field and enter the remaining information for the
subform.

I am fairly new to Access and am still learning much of what I need to be
doing as I go along. Productivity is at a snail's pace at present.

Thank you for helping me!

David


Al Camp said:
David,
Filter the query behind your sub's combo box by including the CustNo field in that
query and using a criteria of...
(use your own names)
=Forms!frmYourMainFormName!frmYourSubformName.Form!CustomerNumber

I'm assuming the Main and Sub forms are linked (Parent to Child) via the
CustomerNumber. If so, there's no need to "display" the CustomerNumber in the sub. Every
time you add a new record, the linked CustomerNumber value is forced into the
CustomerNumber field by the relationship.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Click to expand...
Click to expand...
 
David,
You have to be bit more clear. What do you mean by "still not having any luck".
Are you still getting a parameter request, or is the there some other error message?

Also, you did not answer the question... does the query behind the combo run by itself
(given a legitimate CustomerNumber). Ordinarily, the combo query will look to the subform
for it's CustomerNumber. When you TEST the combo query, hand enter a legitimate
CustomerNumber just to see if a parameter is requested.

Also...
WHERE (((tblCustAndAcctNumbers.[Account
Numbers])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))
Click to expand...

You appear to be asking what AccountNumber equals the subform CustomerNumber. That
doesn't make any sense.

Should be...

WHERE
(((tblCustAndAcctNumbers.[CustomerNumber])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))

You're trying to find an AccountNumber WHERE the related CustomerNumber = the subform
CustomerNumber value.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


David Vollmer said:
Al,

Thank you for your reply. I have tried to do what you suggested but am still
not having any luck.

The SQL view of my query within theAccountNumberfield of the subform reads:
SELECT tblCustAndAcctNumbers.[Account Numbers]
FROM tblCustAndAcctNumbers
WHERE (((tblCustAndAcctNumbers.[Account
Numbers])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))
ORDER BY tblCustAndAcctNumbers.[Account Numbers]

My main form is called xfrmCustomerReview.
The subform which is tied to the customer number from the main form is
called xfrmCustomerReviewSubform.
The other subforms on the main form are frmCustAndAcctNumbers and
frmAddlCustNumbers which are working correctly.

The frmCustAndAcctNumbers form on the main form contains the account numbers
that pertain to the displayed customer and are the numbers that I want to be
able to select in the AccountNumber field of the xfrmCustomerReviewSubform.

I can enter the account numbers manually in the subform and the dropdown box
will show me the numbers as I enter them. At the point at which I have a
match I can tab to the next field and enter the remaining information for the
subform.

I am fairly new to Access and am still learning much of what I need to be
doing as I go along. Productivity is at a snail's pace at present.

Thank you for helping me!

David


Al Camp said:
David,
Filter the query behind your sub's combo box by including the CustNo field in that
query and using a criteria of...
(use your own names)
=Forms!frmYourMainFormName!frmYourSubformName.Form!CustomerNumber

I'm assuming the Main and Sub forms are linked (Parent to Child) via the
CustomerNumber. If so, there's no need to "display" the CustomerNumber in the sub.
Every
time you add a new record, the linked CustomerNumber value is forced into the
CustomerNumber field by the relationship.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions
Click to expand...
Click to expand...
 
David,
First, let's handle the parameter problem...
1. How is your subform connected to the main form. I would expect via CustomerNumber.
Customer on the Main, and multiple transcations associated
with that CustomerNumber in the subform.
2. Do you have a one to many realtionship set up between the Main table and the Sub
table. You should have referential integrity indicated. If so, every record added to the
sub will have the CustomerNumber automatically entered due to the realtionship.
3. Does the Subform contain different CustomerNumbers? It shouldn't.
4. Read my previous post and let's try to get that parameter problem solved first.
Let's take it one step at a time.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


David Vollmer said:
Al,

I moved the SQL language to the CustomerNumber field of the query and it
reads:
[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]

When I save the forms and open the main form (frmCustomerReview) and click
in the AccountNumberID field of the frmCustomerReviewSubform, I get a
parameter value prompt to enter the customer number. The prompt reads:
Forms!xfrmCustomerReview!xfrmCustomerReviewSubform.Form!CustomerNumber

When I enter the current customer number and press Enter, the applicable
account numbers for the customer will be displayed and I can choose one. If I
go to another record and click in the account number field of the subform,
the same account numbers are displayed??

David

David Vollmer said:
Al,

Thank you for your reply. I have tried to do what you suggested but am still
not having any luck.

The SQL view of my query within theAccountNumberfield of the subform reads:
SELECT tblCustAndAcctNumbers.[Account Numbers]
FROM tblCustAndAcctNumbers
WHERE (((tblCustAndAcctNumbers.[Account
Numbers])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))
ORDER BY tblCustAndAcctNumbers.[Account Numbers]

My main form is called xfrmCustomerReview.
The subform which is tied to the customer number from the main form is
called xfrmCustomerReviewSubform.
The other subforms on the main form are frmCustAndAcctNumbers and
frmAddlCustNumbers which are working correctly.

The frmCustAndAcctNumbers form on the main form contains the account numbers
that pertain to the displayed customer and are the numbers that I want to be
able to select in the AccountNumber field of the xfrmCustomerReviewSubform.

I can enter the account numbers manually in the subform and the dropdown box
will show me the numbers as I enter them. At the point at which I have a
match I can tab to the next field and enter the remaining information for the
subform.

I am fairly new to Access and am still learning much of what I need to be
doing as I go along. Productivity is at a snail's pace at present.

Thank you for helping me!

David


Al Camp said:
David,
Filter the query behind your sub's combo box by including the CustNo field in that
query and using a criteria of...
(use your own names)
=Forms!frmYourMainFormName!frmYourSubformName.Form!CustomerNumber

I'm assuming the Main and Sub forms are linked (Parent to Child) via the
CustomerNumber. If so, there's no need to "display" the CustomerNumber in the sub.
Every
time you add a new record, the linked CustomerNumber value is forced into the
CustomerNumber field by the relationship.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a form that contains 3 subforms. Two of the subforms are working
correctly, however I am having a problem getting my information to display
correctly on the third subform.

Within this subform the customer number displayed is keyed to the main form
customer number and is working correctly. However, when you select the
dropdown for the combo box on the account number field within that subform it
shows all of the account numbers for all the customers. I want it to only
show the account numbers that pertain to the customer number shown in the
main form.

How can I do this, please?

David Vollmer
Click to expand...
Click to expand...
Click to expand...
 
Al,

I am still getting the parameter request and if I enter a valid customer
number it will show me (in the account number field of the subform) a drop
down show the correct account numbers for that customer.

If I run the query "standalone" I get the same parameter request for a
customer number and when I enter a valid customer number it will show me the
related account numbers. In other words it acts the same as it does attached
to the AccountNumber combo box.

I changed the "WHERE" portion of the SQL to equal what you entered below and
I still get the same result.

I will now respond to your last reply.

Thank you,
David


Al Camp said:
David,
You have to be bit more clear. What do you mean by "still not having any luck".
Are you still getting a parameter request, or is the there some other error message?

Also, you did not answer the question... does the query behind the combo run by itself
(given a legitimate CustomerNumber). Ordinarily, the combo query will look to the subform
for it's CustomerNumber. When you TEST the combo query, hand enter a legitimate
CustomerNumber just to see if a parameter is requested.

Also...
WHERE (((tblCustAndAcctNumbers.[Account
Numbers])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))
Click to expand...

You appear to be asking what AccountNumber equals the subform CustomerNumber. That
doesn't make any sense.

Should be...

WHERE
(((tblCustAndAcctNumbers.[CustomerNumber])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))

You're trying to find an AccountNumber WHERE the related CustomerNumber = the subform
CustomerNumber value.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


David Vollmer said:
Al,

Thank you for your reply. I have tried to do what you suggested but am still
not having any luck.

The SQL view of my query within theAccountNumberfield of the subform reads:
SELECT tblCustAndAcctNumbers.[Account Numbers]
FROM tblCustAndAcctNumbers
WHERE (((tblCustAndAcctNumbers.[Account
Numbers])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))
ORDER BY tblCustAndAcctNumbers.[Account Numbers]

My main form is called xfrmCustomerReview.
The subform which is tied to the customer number from the main form is
called xfrmCustomerReviewSubform.
The other subforms on the main form are frmCustAndAcctNumbers and
frmAddlCustNumbers which are working correctly.

The frmCustAndAcctNumbers form on the main form contains the account numbers
that pertain to the displayed customer and are the numbers that I want to be
able to select in the AccountNumber field of the xfrmCustomerReviewSubform.

I can enter the account numbers manually in the subform and the dropdown box
will show me the numbers as I enter them. At the point at which I have a
match I can tab to the next field and enter the remaining information for the
subform.

I am fairly new to Access and am still learning much of what I need to be
doing as I go along. Productivity is at a snail's pace at present.

Thank you for helping me!

David


Al Camp said:
David,
Filter the query behind your sub's combo box by including the CustNo field in that
query and using a criteria of...
(use your own names)
=Forms!frmYourMainFormName!frmYourSubformName.Form!CustomerNumber

I'm assuming the Main and Sub forms are linked (Parent to Child) via the
CustomerNumber. If so, there's no need to "display" the CustomerNumber in the sub.
Every
time you add a new record, the linked CustomerNumber value is forced into the
CustomerNumber field by the relationship.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a form that contains 3 subforms. Two of the subforms are working
correctly, however I am having a problem getting my information to display
correctly on the third subform.

Within this subform the customer number displayed is keyed to the main form
customer number and is working correctly. However, when you select the
dropdown for the combo box on the account number field within that subform it
shows all of the account numbers for all the customers. I want it to only
show the account numbers that pertain to the customer number shown in the
main form.

How can I do this, please?

David Vollmer
Click to expand...
Click to expand...
Click to expand...
 
Al,

1. My subform is connected to the main form via CustomerNumber.

2. It is a one-to-many relationship (one customer to many account numbers).
Referential integrity is indicated. Each time I go to a new record on the
main form the subform shows the customer number in the subform (although I
hide the customer number column in the subform).

3. The subform does not show different customer numbers - only the number
related to the main form.

4. See my reply to your earlier post.

Al, I appreciate your help so much.

David



Al Camp said:
David,
First, let's handle the parameter problem...
1. How is your subform connected to the main form. I would expect via CustomerNumber.
Customer on the Main, and multiple transcations associated
with that CustomerNumber in the subform.
2. Do you have a one to many realtionship set up between the Main table and the Sub
table. You should have referential integrity indicated. If so, every record added to the
sub will have the CustomerNumber automatically entered due to the realtionship.
3. Does the Subform contain different CustomerNumbers? It shouldn't.
4. Read my previous post and let's try to get that parameter problem solved first.
Let's take it one step at a time.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


David Vollmer said:
Al,

I moved the SQL language to the CustomerNumber field of the query and it
reads:
[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]

When I save the forms and open the main form (frmCustomerReview) and click
in the AccountNumberID field of the frmCustomerReviewSubform, I get a
parameter value prompt to enter the customer number. The prompt reads:
Forms!xfrmCustomerReview!xfrmCustomerReviewSubform.Form!CustomerNumber

When I enter the current customer number and press Enter, the applicable
account numbers for the customer will be displayed and I can choose one. If I
go to another record and click in the account number field of the subform,
the same account numbers are displayed??

David

David Vollmer said:
Al,

Thank you for your reply. I have tried to do what you suggested but am still
not having any luck.

The SQL view of my query within theAccountNumberfield of the subform reads:
SELECT tblCustAndAcctNumbers.[Account Numbers]
FROM tblCustAndAcctNumbers
WHERE (((tblCustAndAcctNumbers.[Account
Numbers])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))
ORDER BY tblCustAndAcctNumbers.[Account Numbers]

My main form is called xfrmCustomerReview.
The subform which is tied to the customer number from the main form is
called xfrmCustomerReviewSubform.
The other subforms on the main form are frmCustAndAcctNumbers and
frmAddlCustNumbers which are working correctly.

The frmCustAndAcctNumbers form on the main form contains the account numbers
that pertain to the displayed customer and are the numbers that I want to be
able to select in the AccountNumber field of the xfrmCustomerReviewSubform.

I can enter the account numbers manually in the subform and the dropdown box
will show me the numbers as I enter them. At the point at which I have a
match I can tab to the next field and enter the remaining information for the
subform.

I am fairly new to Access and am still learning much of what I need to be
doing as I go along. Productivity is at a snail's pace at present.

Thank you for helping me!

David


:

David,
Filter the query behind your sub's combo box by including the CustNo field in that
query and using a criteria of...
(use your own names)
=Forms!frmYourMainFormName!frmYourSubformName.Form!CustomerNumber

I'm assuming the Main and Sub forms are linked (Parent to Child) via the
CustomerNumber. If so, there's no need to "display" the CustomerNumber in the sub.
Every
time you add a new record, the linked CustomerNumber value is forced into the
CustomerNumber field by the relationship.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a form that contains 3 subforms. Two of the subforms are working
correctly, however I am having a problem getting my information to display
correctly on the third subform.

Within this subform the customer number displayed is keyed to the main form
customer number and is working correctly. However, when you select the
dropdown for the combo box on the account number field within that subform it
shows all of the account numbers for all the customers. I want it to only
show the account numbers that pertain to the customer number shown in the
main form.

How can I do this, please?

David Vollmer
Click to expand...
Click to expand...
Click to expand...
 
David,
Without being able to see your forms and your combo, it's pretty hard to say exactly
what the problem is.
If the MDB isn't too large, would you be able to send it to me? Compact and zip it,
and send to me via my website below.
In your email, please specify exactly what form and fields are involved.

Also... use this exact Subject... "NewsgroupHelp" and post back here if you do send the
file. My Spam filters are very aggressive.

I'll try to take a look at it ASAP.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


David Vollmer said:
Al,

1. My subform is connected to the main form via CustomerNumber.

2. It is a one-to-many relationship (one customer to many account numbers).
Referential integrity is indicated. Each time I go to a new record on the
main form the subform shows the customer number in the subform (although I
hide the customer number column in the subform).

3. The subform does not show different customer numbers - only the number
related to the main form.

4. See my reply to your earlier post.

Al, I appreciate your help so much.

David



Al Camp said:
David,
First, let's handle the parameter problem...
1. How is your subform connected to the main form. I would expect via
CustomerNumber.
Customer on the Main, and multiple transcations associated
with that CustomerNumber in the subform.
2. Do you have a one to many realtionship set up between the Main table and the Sub
table. You should have referential integrity indicated. If so, every record added to
the
sub will have the CustomerNumber automatically entered due to the realtionship.
3. Does the Subform contain different CustomerNumbers? It shouldn't.
4. Read my previous post and let's try to get that parameter problem solved first.
Let's take it one step at a time.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions


David Vollmer said:
Al,

I moved the SQL language to the CustomerNumber field of the query and it
reads:
[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]

When I save the forms and open the main form (frmCustomerReview) and click
in the AccountNumberID field of the frmCustomerReviewSubform, I get a
parameter value prompt to enter the customer number. The prompt reads:
Forms!xfrmCustomerReview!xfrmCustomerReviewSubform.Form!CustomerNumber

When I enter the current customer number and press Enter, the applicable
account numbers for the customer will be displayed and I can choose one. If I
go to another record and click in the account number field of the subform,
the same account numbers are displayed??

David

:

Al,

Thank you for your reply. I have tried to do what you suggested but am still
not having any luck.

The SQL view of my query within theAccountNumberfield of the subform reads:
SELECT tblCustAndAcctNumbers.[Account Numbers]
FROM tblCustAndAcctNumbers
WHERE (((tblCustAndAcctNumbers.[Account
Numbers])=[Forms]![xfrmCustomerReview]![xfrmCustomerReviewSubform].[Form]![CustomerNumber]))
ORDER BY tblCustAndAcctNumbers.[Account Numbers]

My main form is called xfrmCustomerReview.
The subform which is tied to the customer number from the main form is
called xfrmCustomerReviewSubform.
The other subforms on the main form are frmCustAndAcctNumbers and
frmAddlCustNumbers which are working correctly.

The frmCustAndAcctNumbers form on the main form contains the account numbers
that pertain to the displayed customer and are the numbers that I want to be
able to select in the AccountNumber field of the xfrmCustomerReviewSubform.

I can enter the account numbers manually in the subform and the dropdown box
will show me the numbers as I enter them. At the point at which I have a
match I can tab to the next field and enter the remaining information for the
subform.

I am fairly new to Access and am still learning much of what I need to be
doing as I go along. Productivity is at a snail's pace at present.

Thank you for helping me!

David


:

David,
Filter the query behind your sub's combo box by including the CustNo field in
that
query and using a criteria of...
(use your own names)
=Forms!frmYourMainFormName!frmYourSubformName.Form!CustomerNumber

I'm assuming the Main and Sub forms are linked (Parent to Child) via the
CustomerNumber. If so, there's no need to "display" the CustomerNumber in the
sub.
Every
time you add a new record, the linked CustomerNumber value is forced into the
CustomerNumber field by the relationship.
--
hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

I have a form that contains 3 subforms. Two of the subforms are working
correctly, however I am having a problem getting my information to display
correctly on the third subform.

Within this subform the customer number displayed is keyed to the main form
customer number and is working correctly. However, when you select the
dropdown for the combo box on the account number field within that subform it
shows all of the account numbers for all the customers. I want it to only
show the account numbers that pertain to the customer number shown in the
main form.

How can I do this, please?

David Vollmer
Click to expand...
Click to expand...
Click to expand...
 
Back
Top