Hello, everyone - I think that

[spherey]

Hello, everyone -

I think that there's probably a way of doing this; in fact, there may be several ways. What I'd like, ideally, is the most elegant and user-friendly way, as I'm going to be setting this up (I hope!) for a multi-user environment.

I'm finishing up a database, and the person in charge of it has asked me whether there can be a log in for each person, in order to track who made the last update. Her ideal as I understand it would be a shortcut to this specific database, placed on each user's desktop, which would prompt for a log in and password when double-clicked; I suppose from that point I'd have to add a field to each of the tables, which would automatically be filled with the user's username when a record was changed or added.

Is this possible? Is it possible that it isn't as complex as it sounds?

Any advice on this would be much appreciated.

Thanks,

Spherey

 

[dhookom]

If users are on a network, you should be able to grab the login. There is code at

http://www.mvps.org/access/api/api0008.htm

that allows you to grab this value.

Access doesn't have a built-in method for updating a record with the current user. You would need to code this into each form that you use for data entry or editing. Roger Carlson has a sample mdb that shows how to do this

http://www.rogersaccesslibrary.com/TableOfContents2k.asp.

Duane
MS Access MVP

 

[JasonPurdueEE]

Spherey,
yes that is all possible and shouldnt be too hard. heres the process I would use to achieve what you want:

1. set up users/groups in the Access security. assign permissions as appropriate. if I'm not mistaken, there is a good faw on access security on this site for more on this.

2. to set up a shortcut on users desktop, you can do several different ways (email all the users a shortcut, allow them to copy it from a network drive, or roll it out to them automaticlly). JeremNYC has helped me do the later in this thread. its pretty painless if you are familiar with batchfiles.
thread181-592345 now about tracking changes by user. I saw a thread about that here not too long ago, but search is broken at the moment and I cant seem to locate it. I think the process involved creating a couple more fields in the table, one for user id and the other for time/date. there was a simple event procedure to put in the After Update property of your controls on your form. sorry I dont remember more about this last step, but I'm sure somebody else can offer more advice on it.

hope this helps
JASON

______________________________
Sleep is a poor substitute for coffee.

 

[spherey]

Thanks a lot to both of you for your help. I'm going to look over your suggestions and try to work something out. If I get stuck on something, I'll post again, and I'd love it if either of you (or anyone else looking over this thread) could help me clear it up, if you have time.

Thanks a lot,

Spherey

 

[spherey]

Jason,

I think getting the shortcut to the users will be the easy part, even without batchfiles. I'm more unclear as to how to keep track of which user last updated/added a record. Setting up users/groups would allow the users to add or change records, but could I also use this to track which user last did so? I'll check around and see if I can find that thread you mentioned about tracking the changes by user. That was in this forum?

Thanks,
Spherey

 

[JasonPurdueEE]

I cant remember what forum it was in for sure (cause I browse most of the Access related ones daily). I wish search wasnt down. anybody know why search isnt working?

______________________________
Sleep is a poor substitute for coffee.

 

[spherey]

Yeah, the search feature would make this a LOT easier. And faster, too . . .so far I haven't had any luck browsing, but I'll keep looking. I also downloaded the example database which Duane suggested (above); maybe that will shed some light on this.

Thanks,

John

 

[spherey]

OK, here's another question.
The reason the users want a log in feature is to have a record of who made the last update. A record of what field was changed and a comparison of its before/after values is not necessary. If all they want is a record of who created or changed a record, and they want that to appear automatically in a field, and the issue isn't so much one of security as of accountability, perhaps a log in feature isn't necessary at all. Maybe there's a simpler way.
(Duane, this is largely in response to what you suggested above.)
I downloaded the sample mdb mentioned above, and it doesn't require a log in feature or password - but it does create a separate table to store the before and after values of whatever fields from whatever tables were changed. While that's certainly functional, I don't need anything that complex. I'm not sure how I'd integrate the tiny part of it that I need (just noting the username in the record that was added or changed) into my existing database, either.

Just to test something, I built a text box on the form in question, and opened the expression builder. Under "Functions," there is a

 CurrentUser()

function listed. I know that

Date()

returns the current date as the default value, so I assumed that

 CurrentUser()

would return the current user. So I selected that function, which made the default value read

 =CurrentUser()

, saved the changes, and made a change in the form to test it. And nothing happened at all. No error, no notation of who had made a change, not in the form, not in the underlying table, nothing.

I think that instead of the form/subform example that stores all changes in a separate table, the first bit of code for simply grabbing the log in is all I need (the one found at mvps.org, also listed above). That or something like it. But how would that work? If I paste that code into a module, as is suggested on the web page, where would that module then store the log in which it grabs? And why wouldn't placing

 CurrentUser()

in the default value section of a field work?

Any further help is greatly appreciated -

Spherey

 

[dhookom]

CurrentUser() only makes sense if you implement security in your application. This is no small implementation.

You could create a new module and paste the code from the web page into the module. Save the module as "basGetUser" or something similar.

Then, add two fields to every table that you want to store who and when of the last update. You would need code in the before update event of every form bound to your tables to update the fields:
Me.txtLastUpdate = Now()
Me.txtLastUser = fOSUserName()

Duane
MS Access MVP

 

[spherey]

Duane -

I copied the code from the mvps.org web page into a new module and saved it as basGetUser. I added two new fields to the table that needs to store who made the last update - the names of the new fields are LastUpdate and LastUser. Then, I copied the code you provided above (thank you very much, by the way!) into the Before Update event of the form. And I saved the changes.

First, when I changed a record to test it, and then closed the form, it did not record my username; it didn't even record the change I had made. So I went back into the form and checked to make sure that the code had been saved; it had. So I made the change to the record again, and this time clicked a Refresh button that I'd put in the form. And when that happens I get this error:

Microsoft Access can't find the macro 'Me.'

Am I missing a step somewhere?

Thanks for your help,

Spherey

 

[dhookom]

This is directly from a form I use in an application. I have two text boxes (invisible) on my form txtUpdateBy and txtUpdateWhen that are bound to my fields. In the Before Update event of the form, I run code with the following lines.

Me.txtUpdateBy = fOSUserName()
Me.txtUpdateWhen = Now()

If your code doesn't work the same, please paste your code in a message to reply back.

Duane

Duane
MS Access MVP

 

[spherey]

It still isn't working. Here's all the relevant info and code:
I used this code from the mvps.org page, listed above. It's pasted into a module, unchanged, and saved as basGetUser.

Option Compare Database

' This code was originally written by Dev Ashish.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Dev Ashish
'
Private Declare Function apiGetUserName Lib "advapi32.dll" Alias _
    "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
    strUserName = String$(254, 0)
    lngLen = 255
    lngX = apiGetUserName(strUserName, lngLen)
    If (lngX > 0) Then
        fOSUserName = Left$(strUserName, lngLen - 1)
    Else
        fOSUserName = vbNullString
    End If
End Function

In the Referral table design, I created two new fields. Their names are LastUser and LastUpdate.

On the Referral form, I created two text boxes, named txtUpdateBy and txtUpdateWhen. They are bound to the two fields in their underlying table.

In the Before Update event of the Referral form, I pasted this code:

Me.txtUpdateBy = fOSUserName()
    Me.txtUpdateWhen = Now()

And what happens is that I still get the same message as the one I listed above:

"Microsoft Access can't find the macro 'Me.'
The macro (or its macro group) doesn't exist, or the macro is new and hasn't been saved."

What is this 'Me.' macro it's referring to? I don't have any macros - do I need one?

 

[dhookom]

I think you pasted the "code" into the Before Update property rather than opening the form's module and pasting the code there. Remove the expression you pasted and select the builder button [...] and select "Code Builder". This will place you into a module with pre-built lines. You paste the code in here. You are now a coder...


Duane
MS Access MVP

 

[spherey]

You know, I would've sworn that I had done that. I know I opened the code builder, but apparently I then pasted the code somewhere it didn't belong, because it gave me a compile error, and I cut it out again - but in any case, once the code made its way to right place, it worked! (Imagine that!) And it's doing exactly what I need it to do now.

Thank you very, very much!

Spherey