Hello All
I have been fumbling around with this for several hours and would
appreciate some guidance.

I have a form based on a query that pulls the current user info as an
audit the form opens hidden on login.

I have a module and need to make several functions running different
append queries load into the same table (to track 15-20 different data
entry type errors) and want to grab the userID from the current user
query and apply it as a constant for each append - is there a simple
way to grab that value and use it.

I pickup the userID in sql

strAddedBy = "SELECT DISTINCT GroupUser.UserID, Users.FullName
FROM GroupUser INNER JOIN Users ON GroupUser.UserID = Users.UserID
WHERE (((GroupUser.UserID)=setcurrentuserid()) =True);"

The private constant should be declared at the start of the module,
setting the constant from the sql but I cant get the code right (or
am I doing this the hard/wrong way?)


Any pointers would be appreciated
Regards
John

flymo wrote:
I found it difficult to understand your problem. You say you have a
form based on a query that pulls the data into a hidden form.
Oftentimes we might refer to that value in a hidden or visibible form like
Forms!TheFormName!TheControlName

Setcurrentuserid() is a function. We don't know what the function does,
how it works, what the value returned is.

For debubbing purposes I might make a column called SCU and get the
value of the function by entering the following for the column value and
then remove (temporarily) the where clause.
SCU : setcurrentuserid()
Run it and see what value(s) is returned.

Also, you might want to google MS-Access Parameter Queries. Maybe add
VBA to the google search as well. Perhaps that is what you need.

On Sep 13, 12:26*am, Salad <sa... (AT) oilandvinegar (DOT) com> wrote:
Hi Salad
tried google multiple searches prior to posting, thanks for the tip.

I did solve this in code although not the way I wanted.

I needed to know how to set a constant in a module, based on an hidden
form value - and use that constant in multiple functions within the
module instead of calling it for each function.

I will just call on every function, copy & paste - wins the day in the
short term.

A poor solution I know, until I can learn better coding techniques

John

"flymo" <fly_mo (AT) hotmail (DOT) com> wrote



By constant, you mean something like a global variable. Since setting a real
constant in code means that the value will NEVER change.

While you can't use a value of a variable (or a constant) in a query, you
can MOST certainly wrap that value in a declared public function (in a
standard code module) and that CAN be used in any query.

So,

Public Function MyVariable as string


MyVariable = strSomeVaraible

or

MyRaviable = some constant

End Function

The, in sql query you can go:

select * from tblCustomers where City = " & MyVariable()


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
Pleasenospam_kallal (AT) msn (DOT) com