count records gives #ERROR not 0

[pinstripe]

Hi to all,
i know that this query might look long but i know where the problem is - i just don't know how to solve it!

This is the query for a subform. In a form footer i would like to count all records based on this given query.
In a text box control source i am useing =[Form].RecordsetClone.RecordCount

It has always gave me o.k. count, until i was forced to add this into the query bellow:
under Select - Stroski.Armotizacijaa
under From - ON Stroski.IdStroski = Prevoz.IdStroski) ON (Vozilo.IdVozilo = Stroski.IdVozila)
It still gives me a good cout accept for 0 records, count gives #ERROR not 0!
i have tried several different ways to make this work (Nz,IIF,Count(*),Val,Trim,) but with no success.I need help!


SELECT Prevoz.IdPrevoza, Prevoz.Opombe, Prevoz.[Destinacija Opis], Prevoz.Cestnina1, Prevoz.TotalCestnina, Prevoz.TotalSpedicija, Prevoz.[Valuta Spedicija1], Prevoz.[Valuta Cestnina1], Prevoz.[Valuta Cestnina2], Prevoz.[Valuta Spedicija2], Prevoz.Cestnina2, Prevoz.Spedicija1, Prevoz.Spedicija2, Prevoz.Destinacija, Prevoz.[Start Km], Prevoz.[Stop Km], Stroski.Armotizacijaa, Nastavitve.NaKm, Nastavitve.Dnevnica, Nastavitve.[Cena Za Liter], Nastavitve.Euro, Prevoz.[Skupaj Km], Prevoz.[Start Gorivo], Prevoz.[Stop Gorivo], Prevoz.NakupGoriva, Prevoz.[Skupaj Gorivo], Prevoz.[Cena Prevoza Odhod], Prevoz.[Cena Prevoza Prihod], Prevoz.[Cena Prevoza Skupaj], Prevoz.[Datum Vpisa], Prevoz.IdVozilo, Prevoz.IdVoznik, Prevoz.IdNastavitve, Prevoz.IdFirme, Stroski.IdStroski, Prevoz.[Datum Odhoda], Prevoz.DatumPrihoda, Prevoz.[Dodatek Vozniku], Podjetje.[Naziv Podjetja]
FROM Voznik INNER JOIN (Vozilo INNER JOIN (Stroski INNER JOIN (Podjetje INNER JOIN (Nastavitve INNER JOIN Prevoz ON Nastavitve.IdNastavitve = Prevoz.IdNastavitve) ON Podjetje.IdFirme = Prevoz.IdFirme) ON Stroski.IdStroski = Prevoz.IdStroski) ON (Vozilo.IdVozilo = Stroski.IdVozila) AND (Vozilo.IdVozilo = Prevoz.IdVozilo)) ON Voznik.IdVoznik = Prevoz.IdVoznik;

thx

 

[psemianonymous]

Use DCount() instead of your function above, or at the very least wrap that nasty thing in a VBA function. Using RecordsetClone and the .RecordCount property individually yield unpredictable results, so you need to do some weird VBA to make sure you get the correct count. So instead of the weird VBA function, use DCount().

If you're still interested, do a search of comp.databases.ms-access for "recordcount" and see what code others have written. Link below.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm

 

[pinstripe]

hi
thx for your reply!
DCount was also not good, but i have manage to fix the problem with:
Dim rst As Object

Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast

On Error GoTo 0
Me.Text106.Value = rst.RecordCount

but i have still a problem with the SUM in a footers textbox controlsource
if there is no records i get the error otherwise it is working fine.

have you maybe a preposition how to manage that?
or maybe to somehow use the above solution but for this purpose?

thx in advance

 

[psemianonymous]

Basically put all that code into a function (let's say GetRecordCount()) and have the function return the "sum value" you calculated in the code above, and then put the DataSource of Text106 to "=GetRecordCount()" or whatever your function name is. Somewhere in the function put in 0 as the default value. This gets all your use cases I believe.

 

[cmmrfrds]

It is better to check for the recordset being at end of file. The count is not always predictable depending on whether a recordset is even returned, the cursor type, and other factors.

The recordset beginning of file and end of file will be set if no records are returned.

If (rst.BOF and rst.EOF) then
msgbox "no records"
else
msgbox "count = " rst.count
End if