Object variable or With block variable not set - HELP, PLEASE

[Robin Donald]

I am tearing my hair out! I would be deliriously happy if someone could
tell me what I am doing wrong.

I have code which creates price and barcode labels - and it works
perfectly and has done for years.

Now, I need to make the same set of stuff for labels in Euros, where
the customer wants labels in this currency.

So my code branches to a duplicated piece of code if the currency is
Euros and I have built identical sets of queries and temporary tables -
mostly by prefixing with the word "Euro" in the new versions.

I have checked (and double checked and triple checked!) all of the
spelling and swear that it is OK.

Part 1, below works perfectly.

11111111111111111111111111111111111111111

Dim qdfLabels_Green As QueryDef

Set qdfLabels_Green = db.QueryDefs("qryPot_Labels_Green_Generate")
qdfLabels_Green.Parameters![Get_Sales_Delivery_Number] =
Me.Sales_Delivery_Number
Set rstLabels_Green = qdfLabels_Green.OpenRecordset
Set rstLabel_Requests_Green =
db.OpenRecordset("Label_Requests_Green")

With rstLabels_Green

etc, etc,
11111111111111111111111111111111111111111

Part 2. - Basically a copy, produces the famous "Object variable or
With block variable not set" error and the error trapping kicks in when
it attempts to execute the second line,
(Set qdfEuro_Green_Labels = db.etc, etc)


22222222222222222222222222222222222222222

Dim qdfEuro_Green_Labels As QueryDef

Set qdfEuro_Green_Labels =
db.QueryDefs("qryEuro_Pot_Labels_Green_Generate")

qdfEuro_Green_Labels.Parameters![Get_Sales_Delivery_Number] =
Me.Sales_Delivery_Number

Set rstEuro_Green_Labels = qdfEuro_Green_Labels.OpenRecordset

Set rstEuro_Green_Label_Requests =
db.OpenRecordset("Euro_Green_Label_Requests")

With rstEuro_Green_Labels

etc, etc,

22222222222222222222222222222222222222222

All of the if/end, for/next with/endwith, etc stuff is AOK and it all
compiles perfectly.

Any pointers will be very gratefuly received.

Robin Donald

 

[TC]

Huh?

What is your question?

TC (MVP Access)
http://tc2.atspace.com

 

[Robin Donald]

Ah, excuse my lack of clarity.

Why do I now get that error message? The error trapping jumps out if I
try to process an order with a Euro currency and the code branches to
the section marked as 2.

Thank you.
Robin Donald

 

[TC]

Ok, sorry, I might not have read all the details of your previous post.
We tend to read the first 5 lines, then spit the dummy if it is not
clear by then!

Are you saying that: "the famous 'Object variable or With block
variable not set' error occurs" on the second line that is shown
below?

Dim qdfEuro_Green_Labels As QueryDef
Set qdfEuro_Green_Labels =
db.QueryDefs("qryEuro_Pot_Labels_Green_Generate")

If so, this just means that:

(1) You have not initialized the db variable, typically by saying: set
db = currentdb() or,

(2) There is no saved query named "qryEuro_Pot_Labels_Green_Generate",
or that query has a syntax error.

Check that you have set the db variable. Also check that you can
double-click the
qryEuro_Pot_Labels_Green_GenerateqryEuro_Pot_Labels_Green_Generate
query & see a resultant datasheet.

HTH,
TC (MVP Access)
http://tc2.atspace.com

 

[Robin Donald]

Ah, wonderful

I had just found it - but your advice wes entirely correct.

My statement "Set db = CurrentDb()" was in only one "branch".

When I moved it into the common declarations section at the top of the
code, everything worked perfectly.

Well done, you and thank you very much!

Robin Donald

 

[TC]

No probs! Thanks for posting back. It's always good to know that we
helped, I appreciate your reply.

TC (MVP Access)
http://tc2.atspace.com