remove filter, but stay at current record

[ironj32]

I have a series of functions that: turn filter on/off, show/hide buttons, and make the form to allow/not allow edits.

Is there a way to make it so that when you turn the filter off it will stay at that current record, instead of going to the first record?

[Charlize]

maybe before setting the filter off, store the record no in a variable and use this to go to this record.

[ironj32]

how exactly would i store it?

is this what ur thinking?

Dim Acct as Variant
Set Acct = me.txtID



'====Procedure for Viewing Info======
Sub ViewInfo()
On Error Resume Next
Dim frm As Object
Dim lngBlue As Long
lngBlue = RGB(0, 0, 255)
Set frm = forms!frmAccountInformation
With frm
'set header label color to red & change text
.lblStatus.Caption = "View Account Information"
.lblStatus.ForeColor = lngBlue
.txtAccountNbr.SetFocus
.AllowEdits = False
.AllowAdditions = False
.AllowDeletions = False
.cmdFinish.Visible = False
.cmdDone.Visible = False
.cmdUpdate.Visible = True
.cmdUndo.Visible = False
.cmdClose.Visible = True
.cmdDelete.Visible = True
End With
End Sub


Private Sub cmdDone_click()
On Error Resume Next

Select Case MsgBox("Do you want to save the changes?", vbYesNoCancel + vbquesiton)
Case vbCancel
Cancel = True
Case vbNo
Me.Undo
ViewInfo
????
Me.FilterOn = False
Case Else
ViewInfo
????
Me.FilterOn = False
End Select
End Sub

[ironj32]

got it.


Private Sub cmdDone_click()
On Error Resume Next
Dim Acct As Variant
Acct = Me.AccountNbr
Select Case MsgBox("Do you want to save the changes?", vbYesNoCancel + vbquesiton)
Case vbCancel
Cancel = True
Case vbNo
Me.Undo
txtAccountNbr.SetFocus
ViewInfo
Me.FilterOn = False
DoCmd.FindRecord Acct, acAnywhere, True, acSearchAll, True, , acCurrent
Case Else
txtAccountNbr.SetFocus
ViewInfo
Me.FilterOn = False
DoCmd.FindRecord Acct, acAnywhere, True, acSearchAll, True, , acCurrent
End Select
End Sub

[Charlize]

I had a busy day so I couldn't reply earlier (sorry for that). I was thinking in the way of using the recordset that you created for the database and by using a bookmark to go to the record after you turn the filters off. Anyway, glad you find a solution.

Function NoRecordNum ()
' Purpose: shows how to get around without record numbers.
' Modifications for Access 1.x noted in comments as "v.1:"

Dim db As Database
Dim Rst As Recordset ' v.1: Rst as Table
Dim NumRecords As Long
Dim BM As String

Set db = CurrentDB() ' v.2: Set db = dbengine(0)(0)
Set Rst = db.OpenRecordset("tblCustomers") ' v.1: db.OpenTable("tblCustomers")
If Rst.RecordCount = 0 Then
Exit Function
End If

' Show record count, and loop backwards through records.
Rst.MoveLast
NumRecords = Rst.RecordCount
MsgBox NumRecords & " records."
Do While Not Rst.BOF
Debug.print Rst![ID]
Rst.MovePrevious
Loop

' Use a bookmark to move back to a chosen record.
Randomize Timer
Rst.MoveFirst
Rst.Move Rnd * NumRecords 'Skip to a random record
BM = Rst.BookMark 'Save the bookmark
Rst.MoveLast 'Move somewhere else
Rst.BookMark = BM 'Back to previous place
Rst.Close
Set Rst = Nothing
Set db = Nothing
Debug.Print "Finished"
End Function
Please note that RecordCount contains:

zero if there are no records; the full record count, if the recordset is a "Table" type;
the number of records accessed so far in other cases (including SQLs).Therefore, use the MoveLast method if you need to know the number of records in a recordset based on a query.
Bear in mind that you will loop through records far less often in Access. Wholesale changes are handled more efficiently by action queries (Delete, Update, Append, and MakeTable).

I found this info in some notes that I've gathered from the internet.