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!
