Re-query in a filtered form

[tonygg]

I have a sub (detailed below) to refresh my continuous form (based on a query) after updating a row. It re-queries and takes the user back to the row he/she was on. The form is filtered and the user can move rows out of one filtered view and into another (reclassify the row). This works fine unless the specific row was at the end of the filtered list and has been moved out of the filter (reclassified). The "DoCmd.GoToRecord , , acGoTo, a" fails as "a" is past the end of the filtered list.

What I would like to do is check if the record a is past the end of the last row and if so go to the last record instead. I guess i need also to cater for no rows being left in the filtered view.

Please could some one point in the right direction for how to find the value of the last record in the current view so I can compare it to "a"? I can then compare "a" to it and go to last record if a is greater.

many thanks



tony

Private Sub RefreshTable()

' Refresh table and put curser back to same record


Dim a As Integer
a = CurrentRecord
 Me.Requery
DoCmd.GoToRecord , , acGoTo, a


End Sub

[ranman256]

thats the problem, you dont have to refresh after an update.
The update is immediate. Remove the update and you will remain on the current record.

[tonygg]

Hi ranman,

the form is a list of products and the section they are in. So:

section 1 product 1
section 1 product 2
section 2 product 3
section 4 product 4

etc...

I originally didn't do the requery. However one of the operations I allow on the form is to move the products from one section to another. In this case they are moved in the table but dont not show under the new section in the form. If i refresh/requery then they appear under the new section.

Maybe there is another way to get the form to reflect the products under the new section?

Many thanks

tony

[pbaldy]

This will return to the same record:

http://www.baldyweb.com/Requery.htm

You can take the message box out of the "not found" and it should silently return to the first record. Or you could use GoToRecord and go to the last record.

[tonygg]

Thanks pbaldy

I created the formed based on the following query that does not have a unique value per row. is there a separate index value i can use in place of the Me.EmpID? Does the form have a unique hidden value per row?

SELECT DeletedPrds.*FROM (SELECT [Catalog section].nSectionID, [Catalog section].nParentSectionID AS SectionParent, [Catalog section].status AS SectionStatus, [Catalog section].[Section text], IIf(IsNull(Product.sSingleProductPageName),[Catalog section].[Section text],Null) AS sectionName, [Catalog section].sSectionDescription, iif(Instr( [Catalog section].sSectionDescription  , 'Remaining Fabrics in this range have been moved to Clearance') > 0, 'CLEAR', NULL) As ClearMssage, 
[Catalog section].bHideOnWebSite, [Catalog section].sPageName, 'https://www.secretgardenquilting.co.uk/acatalog/' & [Catalog section].sPageName AS sectionHTMLPage, Product.nParentSectionID, Product.nGroupID, 
iif(Product.nGroupID = 19, 'END RANGE', iif(Product.nGroupID = 20, 'CLEARANCE', '') ) As DiscGroup, Product.status AS ProductStatus, Product.[Product Reference], Product.nStockOnHand, Product.[Short description], Product.sSingleProductPageName, Product.bSuppressHtml, IIf(IsNull(Product.sSingleProductPageName),Null,'https://www.secretgardenquilting.co.uk/acatalog/' & Product.sSingleProductPageName) AS ProductHTMLPage
FROM [Catalog section] LEFT JOIN Product ON [Catalog section].nSectionID = Product.nParentSectionID
WHERE (((Product.[Product Reference]) Not Like '*!*') AND ((InStr([Product].[Short description],' EOB '))=0) AND  ((InStr([Product].[Short description],'TEMP '))=0) AND ((InStr([Product].[Short description],'PDF'))=0) AND ((InStr([Product].[Short description],'Post'))=0) AND ((InStr([Product].[Short description],'Workshop'))=0) AND ((InStr([Product].[Short description],'course'))=0) OR ((IsNull([Product].[Product Reference]))<>False) )
ORDER BY [Catalog section].[Section text], Product.[Product sequence] )  AS DeletedPrds
WHERE (IsNull([DeletedPrds].[Product Reference])  = FALSE) OR ( (IsNull([DeletedPrds].[Product Reference]) ) AND DeletedPrds.SectionParent = 1);

[pbaldy]

If there's no key field you can use, I'm not sure how to get to a particular record. I've never relied on position. Does your code throw an error when a is past the last record? If so, I guess you could use an error trap, and when that error occurs silently trap it and go to the last record.

http://www.baldyweb.com/ErrorTrap.htm