Reference a variable and its SQL-based value in two procedures

[RachH]

I am using Access 2003 and need some guidance on the correct way to reference a variable in two different procedures when the variable's value is based on a SQL query.

The database form has a button that, when clicked, will send an email, summarizing in the subject line the $ amount sold so far for the day. This email may be sent several times per day as needed, depending on the pricing volume. Then at the end of the day when all the pricing has been processed, a different button sends a final email (to a larger group of folks) summarizing in the subject line the $ amount for the day plus some other information the midday update email didn't have.

The following code currently exists (and functions fine) in both procedures to create the value for the total $ amount priced during the day (dblPricingSum):

Dim dblPricingSum as Double
dblPricingSum = CurrentProject.Connection.Execute("SELECT Nz(Round(Sum([Price]*[Quantity]),2),0) FROM DailyPricings WHERE ([PricingDate] = Date())")(0)

I then reference dblPricingSum in each email's subject string. However, because the summarizing $ amount is common to both emails, it seems logical to just declare and assign the variable once, then reference it in both procedures as needed. With some experimentation, I determined that if I were assigning a set value, I could declare dblPricingSum as a constant before the first procedure and reference it that way. However, because the variable value is a SQL query-based, I get an invalid outside procedure message if I try to go that route.

I've been largely learning Access and VB as I go so I have a lot of holes in my education. Any guidance would be very appreciated!

[NeoPa]

You lost me in the paragraph after the code :(

Does the value increase as the day progresses?

Where would you be trying to access the value from?

[RachH]

lol sorry!

The DailyPricings table stores pricing information as it occurs throughout the day so the SQL query I included just summarizes whatever the total $ amount is for the current day at the time the SQL query is run (by summing price * quantity for records with the current day's date). So the $ amount may or may not change during the day, depending on whether or not any pricings occur. I just want to be able to assign that $ amount to a variable and reference it in two different procedures.

Does that make any more sense?

[NeoPa]

The question is :- How are these procedures connected?

If they are both invoked from the same piece of code then a Public variable would probably be adequate, but, depending on the thread of your code, you would set the value when you ran the first one so that the second one had it available.

I'm still a little confused as you already have code which seems to cover the basic concept. If there is anything much more complicated I can't see it. Possibly there is something you haven't mentioned?

If they are run independantly, of course, I can't see that it would be a safe process to assume the previously grabbed value is still valid.

Clearly it's possible my ramblings don't fit your situation, but if so either I've missed something you've posted or you've missed posting something.

[RachH]

My existing code works fine but since I reference that total $ amount (as determined by the SQL query) in both the procedure that sends the midday update email, as well as the procedure that sends the final end of day email, I had thought to simplify the overall code by only declaring and assigning the SQL query once (however, given the gaps in my education, thinking often gets me in trouble).

Basically, is there a way I can give a variable public scope and assign it to execute a SQL string to obtain its value each time it is referenced, but do all that outside of a procedure? Or do I have to repeat the assignment in each procedure that uses it because Access will only execute SQL queries inside procedures?

[NeoPa]

Ah, perhaps you're talking about another procedure (function) which returns the value required, but doesn't involve repeating the code in both your existing procedures?

[RachH]

Actually, yup that should do it exactly. It's amazing how obvious the solution becomes when I stop trying to make VB follow my logic process and instead I try to follow VB's logic process! Thank you very much for helping me through that educational experience :-)

[NeoPa]

It's been a pleasure Rach.

That Eureka Moment is so sweet isn't it :)