Results 1 to 8 of 8
  1. #1
    Join Date
    Jul 2009
    Posts
    4

    Unanswered: Forms with default records by user

    I just registered and while I've found some valuable help from this site while lurking, I think I need to pose my problem here today.

    I have an Access db with vba code. Each year I send this database out to other employees to fill in assessment forms in an annual process. After the users complete their assessments they send me back the databases and I merge them together for further processing. Each person is assigned particular records to fill in and I am usually not aware of the assignments. This year I am keeping track of who opens each form by their windows login code using

    Environ("username")

    This makes it easier for me since now I know who altered which records so I can then send each person the entire database. Since I am now keeping track of which user is running the database I would like to set the default record that opens with the form based on the user. One of the tables in the database is a list of users which includes their name, windows login (the primary key) and the default record I would want to open.

    I've tried running SQL queries like

    dfltSQL = "SELECT Default FROM tblAuditor " & _
    "WHERE UserID = '" & Environ("username") & "'"

    but never really figured out how to get them to work right. Even if I manage to get the query to run, however, I'm still not sure I can figure out how to get the correct form (Default) to open up when the database is opened.

    I tried altering the DefaultValue property of the combo box (unbound) used in selecting the forms but that was trouble. Any idea how to go about this? I'd appreciate any help I can get.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so don't use environ use the network logon
    have a look in the code bank for stuff from PK Stormy or do a google on Dev Ashish API
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2009
    Posts
    4
    Thanks for the response. Why is it a bad idea to use environ? I'm really not concerned about security. In any case even if I use a different means to get the windows login, my core problem remains the same and that's having a different default record based on the user.

    I went through the code bank and didn't see anything like that (admitedly the code bank is large so I can only hope I didn't miss anything).

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    environ can be spoofed
    environ can be tempramental
    environ cannot be guranteed
    the network logon is much harder to spoof, its always there

    it is a little trickier toretrieve (at least for the first time).

    its preferable to currentuser() as the current user function identifies the userid the person logged onto access.. its far easier to spoof an access logon than a network logon

    if you want to play the electonic version of cluedo IE know who did the dirty deed, with which computer and at what time then the API functions are the way to go. the hard work has been done for you, all you need to do is copy and paste the code, do a brief bit on understanding how to use the code and robert is your fathers brother
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2009
    Posts
    4
    Thanks again. Like I said I'm not concerned about security as this is not a multi-user database. I give each person their own copy but for my own admin needs I just want to know who edited each record. But I will change the way I get the windows login ID once I figure out how.

    Does anyone else have any suggestions on how I can use code to change the default record that opens with the form?

    Thanks again.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it aint a security issue

    you state you want to know who edited each record. the only way to do that (assuming your network trolls have set the network up as one user one logon and one logon active at any one time) is to use the windows netwrk logon. its not difficult to set up... either look through the code bank for soemthign from PKStormy or Google "Dev ashish api"

    having got a user id, from the network logon, open the form but with a query which limits rows returned to those entered by that userid
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I was recently "forced" to use the Environ("UserName").

    Being on a Novell network, it doesn't work using the getuser() routine if the users all log into their workstations via a generic username (it's a wierd setup - they login to the Novell network as their username but behind the scenes they are logging into their computer as the generic user). I had to use Envrion("UserName") for these users.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pkstormy
    I was recently "forced" to use the Environ("UserName").

    Being on a Novell network, it doesn't work using the getuser() routine if the users all log into their workstations via a generic username (it's a wierd setup - they login to the Novell network as their username but behind the scenes they are logging into their computer as the generic user). I had to use Envrion("UserName") for these users.

    Ive never had that problem though the API call always retrieved the network logon... its actaully because of this site I became a lot more circumspect about currentuser() and environ().

    Novell networks have some very nasty tricks, the nastiest I cam across was because the PC failed to logon or the logon script failed to complete (I think the local network was down so the user started in local mode, when they did get access to the network the environ settings didn't know about the userid.
    another PC was low on resources (at least we think it was that) and most of the environ variables were unset.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •