dcount - strsql

[sven2]

Hello,

is it possible recordsource to make a recordcount when the recordsource is based on a strsql?

For example:

dim strsql as string
strsql = "..."
me.recordsource = strsql

how can I count the number of records from this recordsource?

Thanks,
Sven.

 

[KeithG]

Make another sql statement with the same criteria except make this one a totals sql. Can you post your SQL statement?

 

[sven2]

Hello,

this is my sqlstatement:

strSQL = " SELECT Werknemers.Personeelsnummer, Werknemers.Achternaam, Werknemers.Voornaam, Werknemers.[In dienst], Werknemers.Ploeg, Werknemers.[Soort contract], Werknemers.Telefoonnummer, Werknemers.[GSM nummer], Werknemers.[Uit dienst], Werknemers.Statuut, Werknemers.Straat, Werknemers.Nummer, Werknemers.Postnummer, Werknemers.Gemeente, Werknemers.EntiteitID, Werknemers.AfdelingID, Werknemers.Fotolokatie, Werknemers.diploma, Werknemers.Geslacht, Werknemers.Taal " & _
" FROM Werknemers " & _
" WHERE (((Werknemers.Achternaam) Like 'A*') AND (((Werknemers.[Uit dienst]) Is Not Null) And ((Werknemers.Statuut) = 'B')) " & _
" ORDER BY Werknemers.Achternaam; "


Sven.

 

[KeithG]

Which field is the Primary Key?

 

[sven2]

Hi,

personeelsnummer is the primary key

 

[sven2]

Hello,

I tried it this way but it doesn't work:

Dim strSQL As String
Dim Aantal As Long

strSQL = ...

[Forms]![FrmBewerkenPersoneel].RecordSource = strSQL

Dim Rst As ADODB.RecordSet
Set Rst = New ADODB.RecordSet

Rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
With Rst
.MoveFirst
.MoveLast
Aantal = Rst.Recordcount
End With

Rst.Close: Set Rst = Nothing

Is this the way to count the records? And if so, what is the mistake in this code?

Thanks in advance,
Sven.

 

[Rabbie]

The usual way is to have

.movelast
.movefirst

But as long as you do a .movelast then .RecordCount will be accurate.

What actually happened when you tried this?

 

[sven2]

Hello,

I am getting an error, values are missing
error -2147217904

Sven.

 

[Rabbie]

Which line is giving the error. You must help us to help you

 

[sven2]

Hello,

this is the total code:

Dim strSQL As String
Dim Aantal As Long

strSQL = " SELECT Werknemers.Personeelsnummer, Werknemers.Achternaam, Werknemers.Voornaam, Werknemers.[In dienst], Werknemers.Ploeg, Werknemers.[Soort contract], Werknemers.Telefoonnummer, Werknemers.[GSM nummer], Werknemers.[Uit dienst], Werknemers.Statuut, Werknemers.Straat, Werknemers.Nummer, Werknemers.Postnummer, Werknemers.Gemeente, Werknemers.EntiteitID, Werknemers.AfdelingID, Werknemers.Taal, Werknemers.Fotolokatie, Werknemers.Diploma, Werknemers.Geslacht " & _
" FROM Werknemers " & _
" WHERE (((Werknemers.[Uit dienst]) Is Null) And ((Werknemers.Statuut) = 'A') And ((Werknemers.AfdelingID) = [Forms]![FrmPersoneelStatusKeuze]![Kzlafdelingen])) Or (((Werknemers.[Uit dienst]) Is Null) And ((Werknemers.Statuut) = 'A') And (([Forms]![FrmPersoneelStatusKeuze]![Kzlafdelingen]) = 99)) " & _
" ORDER BY Werknemers.Achternaam; "

[Forms]![FrmBewerkenPersoneel].RecordSource = strSQL

Dim Rst As ADODB.RecordSet
Set Rst = New ADODB.RecordSet


Rst.Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic
With Rst
.MoveLast
.MoveFirst
Aantal = Rst.Recordcount
End With

Rst.Close: Set Rst = Nothing

The error is coming from the line where the recordset is opened.
(Rst.open strSQL)

Sven.

 

[Rabbie]

I would put

Debug.print strSQL

just before you open the recordset. You may see something in the query string that is causing the error.

 

[sven2]

Hello,

but as you can see this strsql string is also being used as a recordsource and this gives no error!

So as far as I can tell there is no error in the strsql.

Sven.

 

[sven2]

Hello,

I just did the debug.print and as I expected there is nothing wrong with the sql ... there has to be a problem in the code below.

Sven.

 

[ajetrumpet]

Sven, there are extra spaces that (should) cause errors in the statement. Eliminate them...

strSQL = " SELECT Werknemers.Personeelsnummer, Werknemers.Achternaam, Werknemers.Voornaam, Werknemers.[In dienst], Werknemers.Ploeg, Werknemers.[Soort contract], Werknemers.Telefoonnummer, Werknemers.[GSM nummer], Werknemers.[Uit dienst], Werknemers.Statuut, Werknemers.Straat, Werknemers.Nummer, Werknemers.Postnummer, Werknemers.Gemeente, Werknemers.EntiteitID, Werknemers.AfdelingID, Werknemers.Taal, Werknemers.Fotolokatie, Werknemers.Diploma, Werknemers.Geslacht " & _
" FROM Werknemers " & _
" WHERE (((Werknemers.[Uit dienst]) Is Null) And ((Werknemers.Statuut) = 'A') And ((Werknemers.AfdelingID) = [Forms]![FrmPersoneelStatusKeuze]![Kzlafdelingen])) Or (((Werknemers.[Uit dienst]) Is Null) And ((Werknemers.Statuut) = 'A') And (([Forms]![FrmPersoneelStatusKeuze]![Kzlafdelingen]) = 99)) " & _
" ORDER BY Werknemers.Achternaam; "

Hope this solves something!

The error is coming from the line where the recordset is opened.
(Rst.open strSQL)

The extra spaces would explain this.