Solved How to hide records if a revision reaches a certain amount

[Sodslaw]

Hi I wonder if anyone can help..

i have this table...
TblQuote

QuoteID	QuoteNo	QuoteVer	HideQuote
2107	2107	1	No
2109	2107	2	No
2111	2107	3	No

when the user creates a new version i would like to know how to calculate and hide old records for a given QuoteNo (all except from the previous version.)
ie as version 3 is created hide version 1 (HideQuote=yes) so that only 2 records exists for each quote, the new and previous record (ver2 &Ver3) and if a 4th version is created hide ver2 and so on

calculating the the revision numbers was easy Me.QuoteVer = DMax("QuoteVer", "[TblQuote]", Me.QuoteNo) but hiding them..... ohhhhh
could i use the dmax?

i know there are good VBAers in our community, but this one for me is well over my head skill or knowledge of VBA (have been trying for several days on and off)

appreciate any comments, thanks guys.

 

[Ranman256]

in the query hide the field , replace the field value with a blank:
MyField: IIf([field]>1, "", [field])

or
just filter out the record in the query using WHERE.

 

[arnelgp]

you can also use Query to hide them:

SELECT tblQuote.*
FROM tblQuote
Where QuoteVer IN
(SELECT TOP 2 QuoteVer FROM tblQuote AS T WHERE T.QuoteNo = tblQuote.QuoteNo Order By QuoteVer Desc)

 

[Uncle Gizmo]

I think you need two queries. The first query would hide all of the records. The second query selects the top 2 records and unhides them...

 

[Uncle Gizmo]

There's an example of a top query on this Microsoft web page Here:-

ALL, DISTINCT, DISTINCTROW, TOP predicates (Microsoft Access SQL)

Office VBA reference topic

docs.microsoft.com

 

[Sodslaw]

Brilliant thanks for the guidance i will i am sure be able to to sort this (TOP 2 i didnt even know there was such a thing) and thanks for the refernce pages its making sence to me now... thanks again guys!

 

[Uncle Gizmo]

I just did a quick check. You can't (well I can't) make a Top selection and update the records at the same time. You'll need another query that takes the results of the top query and updates that..

'Query1
SELECT TOP 2 [Order Details].OrderID, [Order Details].ProductID, [Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount, [Order Details].flag
FROM [Order Details]
WHERE ((([Order Details].OrderID)=10684));

'Query2
UPDATE Query1 SET Query1.flag = True;

 

[Sodslaw]

I'm trying

 

[Gasman]

Could you not just use a WHERE clause of VersionID >= Dmax(the versionID) - 1

 

[arnelgp]

see post #3, you do not need to update the table.
here is the result:
Query1 Query1

ID	QuoteID	QuoteNo	QuoteVer	HideQuote
2​	2109​	2107​	2​	No​
3​	2111​	2107​	3​	No​
4​	2113​	2108​	1​	No​

 

[Sodslaw]

Sorry i had to pop out for a bit but it seems to work well (testing over time will tell)
i used @arnelgp method with a requery and then using the find method to go back to the original record, after it was lost during the requery.

            me.QuoteID = QuoteIDtmp
            Me.Requery
            Me.QuoteID.SetFocus
            DoCmd.FindRecord QuoteIDtmp, , , , , acCurrent

thanks again guys you are all amazing!