Error 91 - Why ?

[lmarconi]

Good morning all. I have the following issue, it seems very stupid but I'm becoming crazy to understand why I got it:
In a Private Sub a declare 2 variables as follow :



Dim dbs As DAO.Database
Dim tmpl As Recordset

Then, as part of the program, i have the following script:

Set tmpl = dbs.OpenRecordset(" SELECT *" & _
" FROM template-FLOC" & _
" WHERE [Level] = '4'", dbOpenDynaset)

Do Until tmpl.EOF
(etc. etc)
........................
tmpl.MoveNext
Loop


Regardless the program is well compiled, it seems the variable tmpl is not set and, during the debug (step by step) when i try to verify the tmpl.Recordset value, i got Run-time Error '91' - Object variable or With block variable not set.

I really cannot understand why, i don't see any issue in the program but the Set looks like doesn't work.

Any suggestion from your experience ?

Thank you,
Cheers
Leo

[CJ_London]

Looks to me like you haven’t set dbs to anything. Also better do declare the type - DAO or ADO to avoid confusion

[ranman256]

there is no need to write code to do this when you can just make a query and open it.
what are you doing with this loop that a query cant do?

[lmarconi]

Looks to me like you haven’t set dbs to anything. Also better do declare the type - DAO or ADO to avoid confusion

Thanks for the answer. Regarding the set of dbs is set, i just forget to mention :
Set dbs = CurrentDb()

as suggested (even if originally i did already, then i change) i change the declaration of the tmpl to:
dim tmpl as DAO.Recordset

But same issue.

Any other suggestion ?
Cheers

[lmarconi]

there is no need to write code to do this when you can just make a query and open it.
what are you doing with this loop that a query cant do?

Thanks for the answer.
The reason why i didn't use a query is because i use this loop for 5 levels (L3, L4, L4A, L4AA and L5) so i don't want to create 5 different queries and keep it dynamic.

Cheers,
L.

[CJ_London]

forget to mention :

perhaps there is something else you forgot to mention? supply all to code, at least to where you get the error - and indicate which line is causing the error. At the moment the assumption has been it is the set line, but perhaps it is somewhere else

[lmarconi]

This is the Declaration:



This is where i count if i have records for each levels:



And this is what i use for each level (in the script i added only 2 levels), if records is > 0 going trough otherwise exit from IF and goes to next level exactly the same changing only the set of tmpl variable.:





The program don't stop with error, simple go out from the IF because doesn't select record because the Set not works. When i debug the program step by step and i verify the variable tmpl.recordcount, i get that error.

Hope is more clear.
Thank you.
Cheers

[Welshgasman]

Does the sql work in the sql window?

[CJ_London]

some inconsistencies

in your original post you had tmpl (L) now you have tmp1

flc_rec is not declared as a DAO.Recordset

don't see anywhere where you are verifying tmp1.recordcount (your text says tmpl.recordcount anyway)

what is the benefit of using your dcount function? so still don't know where your error is being generated

[lmarconi]

The used variable is always "tmpl", where you can see tmp1?
flc_rec agree, i changed;
tmpl.recordcount is not in the script, i use it during the debug to check if the records where selected (the only way i know to find the error);
about the dcount, agree with you, i can test directly in the IF statement;

However i find the problem: as suggested by Welshgasman, i tested the sql in sql window and it seams didn't work. My guilty, it should be the first to be checked. Changing the sql as below, it seems work works fine now.

Set tmpl = dbs.OpenRecordset(" SELECT [template-FLOC].*" & _
" FROM [template-FLOC]" & _
" WHERE [Level] = '5';", dbOpenDynaset)

Thank you all for usual and kindly support.
Cheers.

[CJ_London]

you have screenshot your code rather than copy/pasting so looks like tmp1 to me. But no matter. Reason is you have non alphanumeric characters in the name so you have to surround with square brackets.

[Welshgasman]

Looks lke 1 to me as well now from that pic.

Tell me you have Option Explicit in your modules?

[lmarconi]

Looks lke 1 to me as well now from that pic.

Tell me you have Option Explicit in your modules?

Yes i have it. This is a private Sub used in a Form Button and in the module there is Option Explicit.

[orange]

?? What is latest status??

[lmarconi]

As mentioned, problem solved in this way:

Set tmpl = dbs.OpenRecordset(" SELECT [template-FLOC].*" & _
" FROM [template-FLOC]" & _
" WHERE [Level] = '5';", dbOpenDynaset)

Thank you all.
Cheers.