Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

BIG Problem filtering record

Status
Not open for further replies.

RichardF

Programmer
Oct 9, 2000
239
GB
Hi Guys,

Ok I'm really stuck now ! Apologies in advance for the following wedge of code !

I have 4 navigation buttons, MoveFirst, MoveLast, MovePrev, MoveNext. I want to Enable/Disable the buttons where the user could not press the button: example pressing MovePrevious whilst the First record is select. However I also want to filter the records by field typ which can be A,B or C.

'(removed error handling to improve readability)
' ms access 97

private bFirst as boolean

' event procedures :
Private Sub cmdFirst_Click()
DoCmd.GoToRecord , , acFirst
End Sub

Private Sub cmdLast_Click()
DoCmd.GoToRecord , , acLast
End Sub

Private Sub cmdNext_Click()
DoCmd.GoToRecord , , acNext
End Sub

Private Sub cmdPrev_Click()
DoCmd.GoToRecord , , acPrevious
End Sub

Private Sub Form_Current()
If bFirst = False Then
' skip the first one on form_open
bFirst = True
Exit Sub
End If
CheckNavigation
End Sub

Private Sub Form_Open(Cancel As Integer)
Filter = "typ = 'A'"
FilterOn = True
End Sub

Private Sub Combo10_Click()
' select a new filter
Filter = "typ = '" & Trim$(Combo10.Value) & "'"
FilterOn = True
End Sub

' private functions ...
Private Function GetCurrentRecord() As Recordset
Dim rs As Recordset
On Error GoTo ErrorHandle

Set rs = Me.RecordsetClone
rs.Bookmark = Me.Bookmark
Set GetCurrentRecord = rs
Exit Function
ErrorHandle:
Set GetCurrentRecord = Nothing
End Function

Private Sub CheckNavigation()
Dim rsCurrent As Recordset, rs As Recordset

txtName.SetFocus
If IsNull(Combo10.Value) = True Then
' combo10 must be empty, assume A
Set rs = CurrentDb.OpenRecordset("select count(*) as NumRecords from Addresses where typ = 'A'", dbOpenSnapshot)
Else
Set rs = CurrentDb.OpenRecordset("select count(*) as NumRecord from Addresses where typ = '" & Trim$(Combo10.Value) & "'", dbOpenSnapshot)
End If

Set rsCurrent = GetCurrentRecord
If rsCurrent Is Nothing Then
cmdFirst.Enabled = False
cmdNext.Enabled = False
cmdPrev.Enabled = False
cmdLast.Enabled = False
Else
cmdFirst.Enabled = (rs!NumRecords > 0 And BOF(rsCurrent) = False)
cmdLast.Enabled = (rs!NumRecords > 0 And EOF(rsCurrent) = False)
cmdNext.Enabled = cmdLast.Enabled
cmdPrev.Enabled = cmdFirst.Enabled
End If
End Sub

Private Property Get EOF(rsCurrent As Recordset) As Boolean
' returns TRUE if the Recordset is on the last record
' NOT the same as Recordset.EOF
EOF = (rsCurrent.AbsolutePosition = rsCurrent.RecordCount - 1)
End Property

Private Property Get BOF(rsCurrent As Recordset) As Boolean
' returns TRUE if the Recordset is on the first record
' NOT the same as Recordset.BOF
BOF = (rsCurrent.AbsolutePosition = 0)
End Property

'

The problem being when I open the form all is well, the buttons are being greyed out at the correct record positions, BUT as soon as i select a value from the combo and the filter is invoked all the buttons become Disabled, unless i step through the code in which case it works ! I think it has something to do with the fact that the filter takes some time to process thus the checknavigation sub is not getting the right recordset.

Please Help.

Thanks in advance.
 
This is my solution to your problem.
Code for buttons is:
Private Sub first_Click()
On Error GoTo Err_first_Click


DoCmd.GoToRecord , , acFirst
If Me.CurrentRecord = 1 Then
Me.next.SetFocus
Me.first.Enabled = False
Me.prev.Enabled = False
Me.next.Enabled = True
Me.last.Enabled = True
End If


Exit_first_Click:
Exit Sub

Err_first_Click:
MsgBox Err.Description
Resume Exit_first_Click

End Sub
Private Sub prev_Click()
On Error GoTo Err_prev_Click


If Me.CurrentRecord = 1 Then
Me.next.SetFocus
Me.first.Enabled = False
Me.prev.Enabled = False
Me.next.Enabled = True
Me.last.Enabled = True
Else
Me.first.Enabled = True
Me.prev.Enabled = True
Me.next.Enabled = True
Me.last.Enabled = True
DoCmd.GoToRecord , , acPrevious
End If

Exit_prev_Click:
Exit Sub

Err_prev_Click:
MsgBox Err.Description
Resume Exit_prev_Click

End Sub
Private Sub next_Click()
On Error GoTo Err_next_Click
If Me.CurrentRecord >= RecCounter(Me.RecordSource, Me.Filter, Me.FilterOn) Then
Me.prev.SetFocus
Me.first.Enabled = True
Me.prev.Enabled = True
Me.next.Enabled = False
Me.last.Enabled = False
Else
DoCmd.GoToRecord , , acNext
End If

Exit_next_Click:
Exit Sub

Err_next_Click:
MsgBox Err.Description
Resume Exit_next_Click

End Sub
Private Sub last_Click()
On Error GoTo Err_last_Click


DoCmd.GoToRecord , , acLast
Me.first.Enabled = True
Me.prev.Enabled = True
Me.prev.SetFocus
Me.next.Enabled = False
Me.last.Enabled = False

Exit_last_Click:
Exit Sub

Err_last_Click:
MsgBox Err.Description
Resume Exit_last_Click

End Sub


Then you need this function to decide on whether the last record has been reached.

Function RecCounter(MyRstName As String, RFilter As String, FilterCond As Boolean) As Double
Dim myDb As Database, MyRst As Recordset, SQL As String
Set myDb = CurrentDb
SQL = "SELECT COUNT(*) AS RECS FROM " & MyRstName
If FilterCond Then
SQL = SQL & " WHERE " & RFilter
End If
Set MyRst = myDb.OpenRecordset(SQL, dbOpenDynaset)
MyRst.MoveFirst
RecCounter = MyRst!Recs
MyRst.close
Set myDb = Nothing

End Function
 
Thanks A9, that works.

Can u explain why my 'non-solution' was not working ?
 
Richard,
to be honest I didn't look at your code, this was a slight modification of something I used for a system a little while ago. I remember it took a little while to find out when the last record was reached as there seems to be no built in way of knowing - unless someone else knows any different?
 
Yeah I got It actually, after much hair pulling.

if i can drag you back into my code for a minute ....

Private Function GetCurrentRecord() As Recordset
Dim rs As Recordset
On Error GoTo ErrorHandle

Set rs = Me.RecordsetClone
' add these 2 lines, because DAO is pants..
rs.MoveFirst
rs.MoveLast
'
rs.Bookmark = Me.Bookmark
Set GetCurrentRecord = rs
Exit Function
ErrorHandle:
Set GetCurrentRecord = Nothing
End Function

I don't know why this works, but it works so hey !

Thanks.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top