VBA Record Navigation in Subform

[brorick]

I successfully created navigation buttons on my subform so that I could use images. Now the only problem I have is that my main form and subform are linked by the leadanalyst field. This allows the user to see on the subform only the lead analyst records they have been chosen from a drop down list on the main form. Unfortunately my function showRecord is listing the total records in me.txtCount on the subform rather than the filtered records based on the lead analyst field. Any thoughts? Here is my code.

Option Compare Database
Dim rst As ADODB.Recordset
Dim intCount As Integer
Private Sub cmdFirst_Click()

On Error GoTo Err_cmdFirst_Click
DoCmd.GoToRecord , , acFirst

With rst
.MoveFirst
showRecord .AbsolutePosition & " of " & intCount
End With

Exit_cmdFirst_Click:
Exit Sub
Err_cmdFirst_Click:


Resume Exit_cmdFirst_Click
End Sub
Private Sub cmdLast_Click()

On Error GoTo Err_cmdLast_Click
DoCmd.GoToRecord , , acLast

With rst
.MoveLast
showRecord .AbsolutePosition & " of " & intCount
End With
Exit_cmdLast_Click:
Exit Sub
Err_cmdLast_Click:

Resume Exit_cmdLast_Click


End Sub
Private Sub cmdNext_Click()

On Error GoTo Err_cmdNext_Click
DoCmd.GoToRecord , , acNext

With rst
.MoveNext
If Not .EOF Then
showRecord .AbsolutePosition & " of " & intCount
Else
.MoveLast
showRecord "Last Record"
End If
End With
Exit_cmdNext_Click:
Exit Sub
Err_cmdNext_Click:

Resume Exit_cmdNext_Click


End Sub
Private Sub cmdPrevious_Click()

On Error GoTo Err_cmdPrevious_Click
DoCmd.GoToRecord , , acPrevious

With rst
.MovePrevious
If Not .BOF Then
showRecord .AbsolutePosition & " of " & intCount
Else
.MoveFirst
showRecord "First Record"
End If
End With
Exit_cmdPrevious_Click:
Exit Sub
Err_cmdPrevious_Click:

Resume Exit_cmdPrevious_Click


End Sub

Private Sub Form_Load()

Dim strSQL As String

strSQL = "SELECT activeprojects.Name, activeprojects.status, activeprojects.projectid, " & _
"activeprojects.description, activeprojects.leadanalyst FROM activeprojects"
Set rst = New ADODB.Recordset
rst.CursorLocation = adUseClient
rst.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly

With rst

intCount = .RecordCount
.MoveFirst
showRecord .AbsolutePosition & " of " & intCount
End With
End Sub
Function showRecord(strRecordCount)
With rst

Me.txtCount = strRecordCount
End With
End Function

The navigation works perfectly the only issue is with the filtered count rather than the total count. If the lead analyst has only 4 records, then I would want the text field Me.txtCount to reflect 1 of 4 and so on. :think: Thanks in advance.

Edited 4-Oct-06 by geekgirlau. Reason: insert line breaks

[Cosmos75]

Try putting the code to update txtCount in the OnCurrent event of your subform.

[brorick]

Placing the code in OnCurrent doesn't work. It pretty much acts the same as it does in OnLoad. I believe the code is missing a reference in the ADODB recordset. I am currently seeing a total of 88 records which is what is in the entire table. What I should see in my count is only the total number of records that is related to the lead analyst.

Consider a normal subform that is linked to a specific field on the main form. I will see only the total number of records in the subform that are directly related to the field on the main form. The number appears in my navigation buttons area on the subform. This is what I am trying to replicate with my code. It is purely for cosmetic reasons that I am not using the regular navigation buttons on the subform.