Add the value of record count

[Syerston]

Would anyone know why this piece of code will not add the record count to the text field txtReference

'** Re-Checks reference number to ensure it has not been allocated. If so adds recordcount to reference

Dim rstRefer As DAO.Recordset
Dim strRef As String
        
        strRef = "SELECT Reference FROM tblClaimants WHERE Reference = '" & txtRef & "'"
Set rstRefer = CurrentDb.OpenRecordset(strRef)
If rstRefer.RecordCount >= 1 Then
    '-- If record count is > 1 move to the last record to retrieve an accurate count
    rstRefer.MoveLast
        intRecCount = rstRefer.RecordCount
        Me!txtRef = Int(Me!txtRef + intRecCount)
End If

rstRefer.Close

John

 

[tomschafer]

Try it like this...


Dim rstRefer As DAO.Recordset
Dim strRef As String

strRef = "SELECT Reference FROM tblClaimants WHERE Reference = '" & txtRef & "'"
Set rstRefer = CurrentDb.OpenRecordset(strRef)
rstRefer.MoveLast
If rstRefer.RecordCount >= 1 Then
'-- If record count is > 1 move to the last record to retrieve an accurate count
intRecCount = rstRefer.RecordCount
Me!txtRef = Int(Me!txtRef + intRecCount)
End If

rstRefer.Close

 

[Syerston]

No, I tried that already but to no avail.

Any other ideas. John

 

[tomschafer]

Instead of using code try using this expression...

Me!txtRef = Int(Me!txtRef + Nz(DLookup("Reference", "tblClaimants", "Reference='" & Me!txtRef & "'&quot, 0))

 

[Syerston]

Unfortunately not, it comes back with a very odd reference number.

I think that the record count would be the way to go, but is there some reason why it will not play the game. John

 

[braindead2]

if all you are trying to get is a count why not let SQL do it
Dim rstRefer As DAO.Recordset
Dim strRef As String

strRef = "SELECT Count(Reference)as reccnt FROM tblClaimants WHERE Reference = '" & txtRef & "'"
Set rstRefer = CurrentDb.OpenRecordset(strRef)
If not rstRefer.EOF then
rstrefer.movefirst
Me!txtRef.value = me!txtref.value & rstref(0)
End If
rstRefer.Close

or use dcount("Reference","tblClaimants"," Reference = '" & txtRef & "'&quot

Me!txtRef.value = me!txtref.value & dcount("Reference","tblClaimants"," Reference = '" & txtRef & "'&quot

I have had problems if a textbox is in a continous form