Global variable not available when needed

[abdoudiaw]

Hi,
I have this declaration in a module called Global Code
Option Compare Database
Public currentCustomerId As Long
Option Explicit

There is one report that simply prints a the firstname of current customer and the amount of each of his invoices.
The current displayed customer's ID is always copied into the variable currentCustomerId.
Below is the SELECT code of the data source of the report.
But whenever I open this report it asks for currentCustomerId.
Is my decalartion not global enough?

SELECT tblCustomers.FirstName, tblInvoice.Amount, FROM tblCustomers INNER JOIN tblInvoices ON tblCustomers.CustomerID=tblInvoices.CustomerID WHERE tblInvoices.CustomerID=currentCustomerId;

Note: I chose to save the current customer's ID in a global variable because, for some reason, when I select the report the Customer form loses the current customer record and goes to the end of the table as if creating a new customer record. This would not be an issue as long as I can retrieve the value in a variable.
Thanks.

 

[pbaldy]

You can't refer to a variable directly from outside VBA. You'd have to create a public function that returned the value of the variable, and call that from the query.

 

[abdoudiaw]

Thanks a lot.
I'll try that and let you know.

 

[abdoudiaw]

thanks a lot: it is working!

 

[pbaldy]

Happy to help!