Solved: get recordcount from query

[Trevor]

I am trying to get the recordcount from my select query and it isn't working. It is supose to select all status from the table that = ststatus(ststus is the result of a dlookup, and the ststus works, I have tested that) I just can't get the record count of totle records it finds that matches the query results, it should place the record count for the query into a textbox[tstr];

Dim qryDB As DAO.Database
Dim loRst As DAO.Recordset
With loRst
Do Until .EOF
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PSwrdAttemptTbl] WHERE" _
& " [Status]= '" & ststatus & "';")
.MoveNext
Loop
Me.[testr] = loRst.RecordCount
End With

[Carl A]

You have to populate the recordset

Private Sub Form_Load()
Dim qryDB As DAO.Database
Dim loRst As DAO.Recordset
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PSwrdAttemptTbl] WHERE" _
& " [Status]= '" & ststatus & "';")
With loRst
.MoveLast
Me.[testr] = loRst.RecordCount
End With
End Sub

[Trevor]

for some reason it still isn't populating the textbox with the recordcount, the textbox is staying blank, I even tried adding movefirst under your move last to try and get it to work, here is my code it is supose to take the record count and use that as the starting # of a counter then if the counter is less or greatern then 3 it should display error messages, but i think I get get the error messages to work because the record counter doesn't seem to work :

If StrComp(Me.[status], ststatus, vbBinaryCompare) <> 0 Then MsgBox "Check"
If StrComp(Me.[Password], stPassword, vbBinaryCompare) = 0 And StrComp(Me.[Tech. Name], stName, vbBinaryCompare) = 0 Then
MsgBox "match"
Else: MsgBox "no match"
End If
Dim qryDB As DAO.Database
Dim loRst As DAO.Recordset
Set loRst = CurrentDb.OpenRecordset("SELECT * FROM [PSwrdAttemptTbl] WHERE" _
& " [Status]= '" & ststatus & "';")
With loRst
.MoveFirst
.MoveLast
Me.[Text15] = loRst.RecordCount

If loRst.RecordCount >= 3 Then loRst.MoveLast
If .Fields("lockedoutTime") > Time() Then MsgBox "your idle Time has been exceeded"
If loRst.RecordCount < 3 Then logonattempted = loRst.RecordCount + 1
If logonattempted = 3 Then MsgBox "you need to wait for your account to reset"
End With

I even changed the textbox name for the recordcount to populate, (cuz it's late, i wanted somting I didn't have to sqwint to make sure it was spelled right. even if the lorst didn't have any records I think that the textbox15 should still populate w/ "0" but I didn't even get that.

[Carl A]

Are you sure the recordset has records in it?

You don't need movefirst

[Trevor]

correction on my post, I must be blind late night, The textbox[text15] on load will display the total record count of the db but won't display the record count of the lorst after the query (same code as above)

[Norie]

Why not just use DCount?

[Trevor]

I didn't know of that command until now, thanks Norie, whas would ehe syntax be?

lorst.Dcount
'instead of
lorst.Recordcount

[Norie]

No it would be absolutely nothing like that.

And I'm afraid I'm just stepping out so don't have time to post an example - try Help.:)

[Trevor]

I'm getting ready to step out to , so I'll have to try later tonight

[Trevor]

ah, thae was an easy find, (Dcount follows the same syntax as a Dlookup), but gotta try it later

[Trevor]

Thanks Norie,
your DCount suggestion worked, but now I'm curious as to why the lorst.Recorcout wouldn't work for this?

[Trevor]

Thanks Norie, That worked