Autoinsert of variable data into update query

  • Thread starter Thread starter HeliCaptKirk
  • Start date Start date
H

HeliCaptKirk

I have several update queries that I would like to automate the Date field to
today's date and the "UpdatedBy" field to the person who's logged in
"GUserName."

When a person logs in, his name is written to a global variable "GUserName"
so that the system can update records automatically with that user's name.
This works perfectly on all the forms.

I have written a number of update queries where I'd like to update the
tables with new values and at the same time update the "UpdatedBy" field and
the "ModfiedDate" field with the values from "GUserName" and today's date
respectively.

Every time I try something, I only get a pop-up parameter input box instead
of the system automatically inserting the value and moving on. Any ideas?

What I've ended up with (not what I want) is to enter the following in the
"Update To" field of the query:
for the date: [(Today's Date as DD/YY/YY)]

for the User: [(Your Name: Last, First)]

These work, but it leads to inaccurate data possible (formatting, spelling,
etc..) as well as having to enter it by hand each time. The system already
knows the User's name (GUserName) and today's date. There must be a way to
automatically include these variables in the query. Thanks in advance.
 
If you only need the date today, you can use the Date() function. If you
need date/time, use Now().

Create a function that returns the value of "GUserName". Call that function
in your query.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
OK, I was reading in the past questions and figured out how to tell the query
to use today's date. Now how to tell it to use a global variable's value.
 
HeliCaptKirk -

You can use Date() for today's date (or Now() for the date/time) for your
Update To field.

For the user, it depends on how/where you store your GUserName. If you
can't reference it directly, you can write a very small function to get it.
Put this in a global module:

Public Function GetUserName() As String

GetUserName = GUserName

End Function

Then in your query you can use =GetUserName() for the User field.
 
Daryl,
You're a life saver. I had the knowlege of how to do it, but not the
syntax. I didn't realize =GUserName() was the format.
I tried =[GUserName] and [(GUserName)] which obviously don't work.

Thanks. again.

Daryl S said:
HeliCaptKirk -

You can use Date() for today's date (or Now() for the date/time) for your
Update To field.

For the user, it depends on how/where you store your GUserName. If you
can't reference it directly, you can write a very small function to get it.
Put this in a global module:

Public Function GetUserName() As String

GetUserName = GUserName

End Function

Then in your query you can use =GetUserName() for the User field.

--
Daryl S


HeliCaptKirk said:
I have several update queries that I would like to automate the Date field to
today's date and the "UpdatedBy" field to the person who's logged in
"GUserName."

When a person logs in, his name is written to a global variable "GUserName"
so that the system can update records automatically with that user's name.
This works perfectly on all the forms.

I have written a number of update queries where I'd like to update the
tables with new values and at the same time update the "UpdatedBy" field and
the "ModfiedDate" field with the values from "GUserName" and today's date
respectively.

Every time I try something, I only get a pop-up parameter input box instead
of the system automatically inserting the value and moving on. Any ideas?

What I've ended up with (not what I want) is to enter the following in the
"Update To" field of the query:
for the date: [(Today's Date as DD/YY/YY)]

for the User: [(Your Name: Last, First)]

These work, but it leads to inaccurate data possible (formatting, spelling,
etc..) as well as having to enter it by hand each time. The system already
knows the User's name (GUserName) and today's date. There must be a way to
automatically include these variables in the query. Thanks in advance.
Click to expand...
Click to expand...
 
OK. Tried the suggestions, the date recommendation works.
The global function does not. Can you be more specific? Thanks.


Jeff Boyce said:
If you only need the date today, you can use the Date() function. If you
need date/time, use Now().

Create a function that returns the value of "GUserName". Call that function
in your query.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

HeliCaptKirk said:
I have several update queries that I would like to automate the Date field
to
today's date and the "UpdatedBy" field to the person who's logged in
"GUserName."

When a person logs in, his name is written to a global variable
"GUserName"
so that the system can update records automatically with that user's name.
This works perfectly on all the forms.

I have written a number of update queries where I'd like to update the
tables with new values and at the same time update the "UpdatedBy" field
and
the "ModfiedDate" field with the values from "GUserName" and today's date
respectively.

Every time I try something, I only get a pop-up parameter input box
instead
of the system automatically inserting the value and moving on. Any ideas?

What I've ended up with (not what I want) is to enter the following in the
"Update To" field of the query:
for the date: [(Today's Date as DD/YY/YY)]

for the User: [(Your Name: Last, First)]

These work, but it leads to inaccurate data possible (formatting,
spelling,
etc..) as well as having to enter it by hand each time. The system
already
knows the User's name (GUserName) and today's date. There must be a way
to
automatically include these variables in the query. Thanks in advance.
Click to expand...


.
Click to expand...
 
Create a function that returns, say, "HeliCaptKirk". That could be as
simple as:

Function UserName() as String
UserName = GUserName
End Function

Now, in your query, in the Update To space under the correct field, try
something like:

=UserName()

(note - this is untested aircode... your mileage may differ)

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

HeliCaptKirk said:
OK. Tried the suggestions, the date recommendation works.
The global function does not. Can you be more specific? Thanks.


Jeff Boyce said:
If you only need the date today, you can use the Date() function. If you
need date/time, use Now().

Create a function that returns the value of "GUserName". Call that
function
in your query.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

HeliCaptKirk said:
I have several update queries that I would like to automate the Date
field
to
today's date and the "UpdatedBy" field to the person who's logged in
"GUserName."

When a person logs in, his name is written to a global variable
"GUserName"
so that the system can update records automatically with that user's
name.
This works perfectly on all the forms.

I have written a number of update queries where I'd like to update the
tables with new values and at the same time update the "UpdatedBy"
field
and
the "ModfiedDate" field with the values from "GUserName" and today's
date
respectively.

Every time I try something, I only get a pop-up parameter input box
instead
of the system automatically inserting the value and moving on. Any
ideas?

What I've ended up with (not what I want) is to enter the following in
the
"Update To" field of the query:
for the date: [(Today's Date as DD/YY/YY)]

for the User: [(Your Name: Last, First)]

These work, but it leads to inaccurate data possible (formatting,
spelling,
etc..) as well as having to enter it by hand each time. The system
already
knows the User's name (GUserName) and today's date. There must be a
way
to
automatically include these variables in the query. Thanks in advance.
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top