Show allrecords in subform (1 Viewer)

[jackhot0615]

I success build up a parent-child link form when user select year of birthday from a combo box critical customer will show in the subform.

Here is my question. I union the combo with “*” and want to show all records. But subform just show nothing.

Below is the query. When I replace * with 2005 it will show the 2005’s record.

SELECT Cust_Q.ExprB, Cust_Q.ExprB FROM [Cust_Q] UNION Select "*" as allchoice,"(All)" as bogus FROM [customer];

Any suggestion will be appreciated.

 

[HiTechCoach]

Your forms were corrupt. I had to create a new blank database import the table and then to recreate the forms and queries.

See if the attached is what you want

 

[jackhot0615]

http://www.access-programmers.co.uk/forums/member.php?u=31847 HiTechCoach, Thanks for your solution so quickly. Yes, that’s what I truly want.
I had studied your mdb and found a new critical in query Cust_Q also break the parent-child link of B_year. Is there any possibility to keep the link between B_year and ExprB?

 

[HiTechCoach]

http://www.access-programmers.co.uk/forums/member.php?u=31847 HiTechCoach, Thanks for your solution so quickly. Yes, that’s what I truly want.
I had studied your mdb and found a new critical in query Cust_Q also break the parent-child link of B_year. Is there any possibility to keep the link between B_year and ExprB?

I do not know of a way to link the parent form to the child form when you want to use a Like createia to be able to use Like "*".

The solution I proved is the dimpliest way I know to do it.

Some possible solutions you could try::

1) Is you select "ALL" then remove the master/child linking fields

2) change the combo pox to use 0 for "All". When you select "All" change the sub form's record source to have the linking column to always be 0 so every record matches the combo box.

It may be possible to use a single query and calculate the column so that you do not need to change record sources. I tend to avoid doing this to avoid incompatibly and speed issues.

 

[HiTechCoach]

I was curious so I tried both the suggested solutions.

#1 was easy

#2 was a more difficult to figure out.

I have attached both tests.

 

[jackhot0615]

Dear HiTechCoach:
Yes, I love your idea of #2 which keeping the linked fields ,by the way, your suggestion with Nz() is more simple than my IsNull() to check combo B_year.
Coach just cure my headache and I like to say thank you again and again.

ps. I don't know why #1 can't work in Access 2007.

 

[HiTechCoach]

Dear HiTechCoach:
Yes, I love your idea of #2 which keeping the linked fields ,by the way, your suggestion with Nz() is more simple than my IsNull() to check combo B_year.
Coach just cure my headache and I like to say thank you again and again.

ps. I don't know why #1 can't work in Access 2007.

You're welcome!

Glad I could assist.