Solved

Populating an access table from a form for multiple records, even when parts aren't filled out again

Posted on 2006-06-22
14
257 Views
Last Modified: 2006-11-18
Sorry my title isn't very good. It doesn't sum things up very well.
Currently I have the following situation:
I have a StartUp form that pops up and asks for the User name. This user name is then used to populate the "User Name" field in my main form which the user fills out. This value is then recorded in my table as user name. The problem is, upon going to the next record, the user name field on the main form will say the same user name, but it will not be recorded in the table again. I need it recorded in the table again, the same user name, until a different user signs on and a new user name is entered. Help. I am confused.

marissa
0
Comment
Question by:marissa_baker
  • 7
  • 7
14 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 16964235
Each user will have it's own session when you are in a company network.
Personally I use for setting the "CreatedBy" or "LastUpdatedBy" fields in a record the code:
MeLastUpdatedBy = environ("username")
in the form's BeforeUpdate event.

Getting the idea ?

Nic;o)
0
 

Author Comment

by:marissa_baker
ID: 16964721
ok i'll give that a try once i get home and let you know. thanks!
0
 

Author Comment

by:marissa_baker
ID: 16964983
ok the part I don't understand is the setting for "CreatedBy" or "LastUpdatedBy" fields in a record the code.
0
 
LVL 54

Accepted Solution

by:
nico5038 earned 500 total points
ID: 16966509
I use in general 4 fields to trace the creation and (last) update of a table row like:
CreatedBy
CreatedDate
LastUpdateBy
LastUpdateDate
Now when a new row is inserted the "Created" fields are filled and when another user updates a row that is also recorded.
By showing these fields (read-only) on the form a user can always see who did create the row and when the last change has been made.

To set the value I add to the default value of the CreatedBy:
=environ("username")
and for the CreatedDate:
=now()
This is placed in the SourceObject of the controls, thus it will be filled automatically when a newrecord is made visible.
For the update we'll have to use the BeforeUpdate event of the form.
1) Open the form in design mode
2) Press the properties window button (hand with white sheet)
3) Double click the text "BeforeUpdate" on the Event tab of the properties popup window
    This will place [procedure] in the field and a button [..] will be visible
4) Press the [..] button to get into the procedure code
5) Type where the cursor is positioned:

Me.LastUpdatedBy = environ("username")
Me.LastUpdatedDate = Now()

6) Make sure that your fieldnames are the same, else change them in the above code
7) Close the code and save the form.

Clearer ?

Nic;o)
0
 

Author Comment

by:marissa_baker
ID: 16969318
Ok I think I didn't word my original question the right way, because I still don't really understand the solution. Here's what I have/what I want.
When the user opens the database, a form appears that says, "Please enter your name below to start the tracking database." They enter their username in the text box (or really, whatever name they want), and then click enter and my next form comes up. This form has a date field already set to show the current date. What I want, is when the user fills out the record, the user name they entered in the first screen is automatically entered in the associated table so we can see who made the report. But then, when they press "next record" the original screen executes itself again and the name they entered is recorded again in the table for all the reports they make. Then, when they sign out and the next user signs in, the new user is prompted for their user name and it is automatically recorded in the table for every entry they make. The problem I have been having is in getting the original form to re-execute and put the user name value in the table again upon the user making a new record without the user going through the first form and entering their name again. Does this make sense? Ideally, i would like it to execute itself while it's visibility = false.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16969562
As stated in my first comment, when a user is logged in, you don't need to ask their username as it's already available.
Just create a new form, place a new field and enter in that the:
=environ("username")
to see the identification you're logged in with.

Using my steps above will give what you need and even make sure no false name is entered....

Just give it a try and let me know when stuck the step number.

Nic;o)
0
 

Author Comment

by:marissa_baker
ID: 16969678
Ok. Thanks for your help, sorry I'm being frustrating.
So, I out =environ("username") in the default value of my field.
But when I open the form it just says #Name? in the field, thus I'm obviously missing some simple yet probably important step.

Above you said "This is placed in the SourceObject of the controls".... that part confuses me. Once I can get the field to display the user name then I can move along to getting it to update with each new record.
Thanks again!
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
LVL 54

Expert Comment

by:nico5038
ID: 16969713
Use the field itself when in design mode or the field's controlsource to enter the:
=environ("username")

Nic;o)
0
 

Author Comment

by:marissa_baker
ID: 16969748
yea, unfortunately even upon doing that it still says #Name?
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16969834
Can you upload the zipped .mdb to www.ee-stuff.com and post the link here so I can have a look ?

Nic;o)
0
 

Author Comment

by:marissa_baker
ID: 16970296
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16970707
Strange, the starup form directly displays my name.

I've uploaded the version with the appended fields to the table.
https://filedb.experts-exchange.com/incoming/ee-stuff/256-marketing.zip
Open the table and check or the last two "create" fields show filled in the newrecord row (the lowest one with the [>*] )

Nic;o)
0
 

Author Comment

by:marissa_baker
ID: 16970903
I really appreciate all your help, on my computer it is still showing #Name?
Perhaps this has something to do with security settings at work? I don't know.
I'm just going to tell my manager it isn't working, and he can deal with it. :)
But obviously you gave a working solution and for that I appreciate all your help!
0
 
LVL 54

Expert Comment

by:nico5038
ID: 16971134
I checked your form again and found:
Me.Name = Environ(UserName)
This should be:
Me.Name = Environ("UserName")

Try that, otherwise create a new module e.g. named [modGlobalVariables] with this code:

Option Compare Database
Option Explicit

' module to store the entered username and
' the function fncUsername te retrieve the entered value when needed
'
' Use in e.g. an unbound formfield:
' =fncGetUsername()
'
' don't forget the trailing () !

Public glUsername As String

Function fncGetUsername() As String

fncGetUsername = glUsername

End Function

Now you can use the =fncGetUsername() for getting the username typed on the logon form when needed.

Nic;o)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now