Hide records from Form View

[Douglasrac]

Hi,

I have 2 forms. One is clients and the other products.
I open the form Clients and when there I open Products, which will show only the products related to that client.

The product have expiration date, which is 1 year. After one year I will add a new product for that client (the client can have many products). Right now what I do is replacing the old record with new data. But I want to keep the old records, but when I'm navigating the form I want to only see the current products, not the old ones.

If it helps, in the record I also put dates. So, maybe there is a way to make a filter that will only show records that are between a valid date. Example:

Product 1: 01/01/2011 - 01/01/2012 (SHOW)
Product 2: 01/01/2010 - 01/01/2011 (HIDE)

[orange]

One way to achieve this is to use a subquery. You will have to adjust the code that opens the second Form to only show the records for the latest ExpirationDate.

Basically, for this Client, you only want to show those products where the ExpirationDate equals the latest expirationDate
(SELECT Top 1 ExpirationDate from Products Order By ExpirationDate Desc)

Here's an example I set up with some existing data - hopefully you will see the technique.
The rates table has data for several ResourceCodes for several years. In order to get a record for each resourceCode for the Latest Year I use this query and subquery.

SELECT Rates.ResourceCode, Rates.StartDate, Rates.EndDate, Rates.Rate
FROM Rates where enddate in
(Select top 1 EndDate from Rates A
where a.ResourceCode = Rates.resourceCode
Order by EndDate desc);

For more info on subqueries, see http://allenbrowne.com/subquery-01.html#TopN

[Douglasrac]

I don't fully understand what you said.

I don't want to change radically my forms. They have a lot of commands in it, and change it would mean a huge amount of work.
I was expecting something simple. Maybe I can create a new Yes/No field, and when marked will show in the forms, if not marked won't show. Is that possible?

[orange]

The approach is to change the recordsource such that only the latest year records are available. Older years' data would be hidden. It's not a significant change, but I understand your hesitancy to change.

Why not set up a new query to select only the latest years' data? Once you get a query constructed to show the proper data, then you may feel more comfortable about adjusting the Recordsource for the form.

The query can be a temporary test that you can discard if you wish.

Just a thought, if you are going to keep the older records, you may have to change the key on the record since you may be trying to store duplicates????

What is the structure of the Products table?

Did you look at Allen Browne's material on subquery?
I'd try to create a query with subquery based on your data, and Allen's material, just to see that I could get the data I want and hide the other.

Good luck with whatever you decide.

[Douglasrac]

I don't understand Allen Browne approach. It seems to me that it shows Top records, not records btw dates.

Wouldn't be much more simple to mark the records I want to show and uncheck the ones I don't wanna show? I just don't know how to filter the forms by that.

It won't have any duplicates, since each product have his own ID (besides the access ID) and different dates.

[orange]

I thought your initial issue was with a client has a Product and the Product has different expiryDates.
If you keep the old records , you would have records with the older expiryDates. So, in effect you could have
clientA product1 expirydate 2008
clientA product1 expirydate 2009
clientA product1 expiryDate 2010

and you only wanted to display the record with the latest expirydate

That is SHOW clientA product1 expiryDate 2010 and HIDE the others.

Are we still talking about this or has the basic request changed?

[Douglasrac]

Yes, the problem remain the same.

My idea is to put a Yes/No field where I could mark:

clientA product1 expirydate 2008 - No
clientA product1 expirydate 2009 - No
clientA product1 expiryDate 2010 - Yes

So the form knows that only the last one should be showing.

Your approach I think is to compares dates, and I don't know how to do that.

The dates in the table are, like I said:
Product 1: 01/01/2011 - 01/01/2012 (SHOW)
Product 2: 01/01/2010 - 01/01/2011 (HIDE)

Which means access just need to see if TODAY is between those dates. If yes, show, if no, doesn't show. But if this solution is not simple I would prefer the Yes/No field.

P.S.: a product is unique. A customer will never have the same product. So product 1 won't appear twice, never ever. Not even with different dates.

[orange]

Douglas,
You are confusing me. You showed how to adjust the records to apply the yes/No field

clientA product1 expirydate 2008 - No
clientA product1 expirydate 2009 - No
clientA product1 expiryDate 2010 - Yes

then you say

P.S.: a product is unique. A customer will never have the same product. So product 1 won't appear twice, never ever. Not even with different dates.

So if there are no duplicates??? Why do you need the yes/No field??

If you give me the field names in your tables, I will work with you to create a query to SHOW only those records you want to show and "HIDE" the others. Once built, you can throw it away if you want.

[Douglasrac]

What Yes/No field has to do with products being unique?

With Yes/No field I know how to make a query to hide them. But I want to hide in the Form, not in a query.

[orange]

OK Doug, go for it.

The query could be your Form recordsource. That way you wouldn't need the yes/no. Only the "YES" record would be available to the form.

see http://msdn.microsoft.com/en-us/libr...ice.11%29.aspx

My suggestion with a query was to help you as an independent exercise, independent of your form, to see that you could hide the records by using a subquery. It was to be an independent exercise only. Nothing you had to keep or adjust forms for. Just to show the concept of recordsource.

However, it seems you are content with a yes/no field, and it is your decision, so go for it.