If you have a named constant defined in a module then its value must be given by a constant expression. In a compile/execute module this constant expression will typically be evaluated when compiling the module itself, rather than when it is ultimately referenced.
Either way, there is no possibility for something using a module to affect the evaluation of the named constant's value in that module.
For the example of the question, ACOS(-1.0) is evaluated using whatever the default real kind is at the time of compiling. If this is changed to something like
module pidef
use, intrinsic :: iso_fortran_env, only : piprec => real64
implicit none
real(piprec), parameter :: pi = ACOS(-1.0_piprec)
end module
then the constant expression uses the value of piprec in scope at that point. Being a constant expression every value must be well-defined by then. It will not be the case that something like
program piuse
use, intrinsic :: iso_fortran_env, only : piprec => real32
use pidef
implicit none
end program
will somehow evaluate pi using real32. Equally there is no way to reference in the module's constant expression a variable defined globally later on, after the module is compiled.