Open Form and Display Record Count

[MatthewR]

I'm using a combo search box (named "Incident") to open a form (named "frmVictimInformation") that is filtered based on the combo box selection.

If there are records found, I want the form to display the record count in a label (named "VictimRecordNumber") that says "Displaying Victim # X of X"

However, even when there are multiple records, the label always says "Displaying Victim # 1 of 1"

I've pasted my code below, with the problematic part in bold. Where did I go wrong? Thanks for your help. -Matthew

Private Sub Command54_Click()
Dim lngCount As Long
lngCount = DCount("*", "[tblEventInformation]", "[Incident] =" & Me.Incident)
If lngCount = 0 Then
DoCmd.OpenForm "frmEventInformation", , , "Incident=" & Me.Incident.Value
Forms![frmEventInformation]![Incident] = Me![Incident]
Else


DoCmd.OpenForm "frmEventInformation", , , "Incident=" & Me.Incident.Value
DoCmd.GoToRecord acDataForm, "frmVictimInformation", acLast
DoCmd.GoToRecord acDataForm, "frmVictimInformation", acFirst
Form_frmVictimInformation.VictimRecordNumber.Capti on = "Displaying Victim # " & Form_frmVictimInformation.CurrentRecord & " of " & Form_frmVictimInformation.RecordsetClone.RecordCou nt
End If
End Sub

[ItsMe]

Maybe something like this ...

untested

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.MoveLast
If rs.RecordCount > 0 Then
   MsgBox "There are " & rs.RecordCount & " records."
Else
   MsgBox "No records match your criteria"
End If

[ssanfu]

You already have the number of incidents from the DCount function. Why are you using "Form_frmVictimInformation.RecordsetClone.RecordCo unt" to try and get the number of records?

Do you expect the number of records reported by the DCount to be different from the "RecordsetClone.RecordCount" number???

Try

Form_frmVictimInformation.VictimRecordNumber.Caption = "Displaying  Victim #  " & Form_frmVictimInformation.CurrentRecord & "  of  "  & lngCount

Don't know what you will see using "Form_frmVictimInformation.CurrentRecord"....

[MatthewR]

Thanks very much, Steve. Unfortunately, though, I tried your code and it gave me the same problem. It still says "Displaying Victim # 1 of 1" on the form.

In all honesty, I don't know the difference between DCount and RecordsetClone.RecordCount. I'm new to all this, so please pardon my ignorance!

Please let me know if you have any other suggestions.

Matthew

[ItsMe]

Dcount is going to take a second trip to the data where RecordsetClone will not

[MatthewR]

Ok cool. As it happens, I just figured out a workaround. In the form's On Load event, I wrote the following:

If Me.RecordsetClone.RecordCount <> 0 Then
Me.RecordsetClone.MoveLast
Me.VictimRecordNumber.Caption = "Displaying Victim # " & CurrentRecord & " of " & RecordsetClone.RecordCount
End If

That seems to do the trick. Out of curiosity, how would I use the DCount in this situation?

Thanks again for your help. It is much appreciated!

Matthew

[ItsMe]

Out of curiosity, how would I use the DCount in this situation?

Dim lngCount As Long
lngCount = DCount("*", "[tblEventInformation]", "[Incident] =" & Me.Incident)

If lngCount > 0 Then
Me.VictimRecordNumber.Caption = "Displaying Victim # " & CurrentRecord & " of " & RecordsetClone.RecordCount
else
msgbox "No Records"
DoCmd.Close acForm, Me.Name
End If

I would use RecordsetClone

Dim rs As DAO.Recordset
Set rs = Me.RecordsetClone
rs.MoveLast
If rs.RecordCount > 0 Then
Me.VictimRecordNumber.Caption = "Displaying Victim #  " & Me.CurrentRecord & "  of  " & rs.RecordCount
Else
   MsgBox "No records match your criteria"
   DoCmd.Close acForm, Me.Name
End If

[MatthewR]

Awesome, got it. Thanks very much.