Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jul 2011
    Posts
    19

    Question Unanswered: Changing a record using vba code

    Hi Everyone,

    I need help sorting a problem.
    I am using a vba coded login form for an access database i am working on.

    What i am trying to achieve is if a user inputs the wrong password associated with their user_ID more than three times, then their login becomes locked out until an administrator resets the password.

    i have managed to get it all done apart from the bit where using vba code to automatically change the record in the password field to a set word.

    Basically what i want it to do is lookup the record associated with the entered username on the form, to find the password and change it to a set word.....

    I hope that makes sense

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Have you consodered using the network login instead of an Access userid? saves you having to write, test and maintain this sort of code.
    naturally you have to have one userid per person.

    if the network logon is of use look at the network API's in the code bank or google Dev Ashish API
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2011
    Posts
    19
    i do not have access to login details as this is controlled by the companies IT department and i do not have administrative rights to change networked login details.

    Also i want logins not to be connected to the staff computer logins.

    I just want them to be locked out of the database and not the whole use of their computers. Would not go down too well believe me, lol

    Thanks for your suggestion though

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    if you are using Access then you can have access to the network logon through the API calls

    as said before either look through the code bank (I think PK Stormy posted it) or google Dev Ashish API

    take a step back from where you are
    do your users have access to any / every cmputer, able to have multiple network logons and so on. if they only have the one concurrent logon permitted (the usual) then a network logon is plenty good enough. why
    the changing of that password is enforced by the network not you
    your users only need to know there network logon
    your esecurity within Access is transpaent. you verify that that specific network user is allowed to use the application, whetehr you do that through your own module or use workgroups (assumnign you can is up to you). essentailly the user doesn't see the security as its built inot the application to run in background.

    there is no additional demand to logon, think about it, if your users have authenticated themselves on the network isn't that good enough for your application if they have spoofed the network logon, then they are going tobe clever enough to spoof your access logon.

    what is the actionyou are trying to guard against by settign up your own security? what events do you see happening that your own security will block, but using a network logon will permit

    if you roll your own logon table, just assign the network logon in place of an Access logon and perform the same checks there.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jul 2011
    Posts
    19
    well out of over 1000 members of staff who can access any computer, laptop etc with their network login, i only want say 5 people max to be able to access the database and i need to be able to have complete control over the database and who uses it. if the database relies of the network login details to confirm credebility then will that not allow anyone with a network login access to the database?

    is there not a simple code to update/change a record in a field without using a form to change it?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Sure there is:
    Code:
    Dim dbs As DAO.Database
    Dim strSQL As String
    
    strSQL = "UPDATE <TableName> SET <ColumnName> = <NewValue> WHERE <IDColumnName> = <RowIDValue>;"
    Set dbs = CurrentBd
    dbs.Execute strSQL, dbFailOnError
    Set dbs = Nothing
    But the question is not there. What healdem means is that you should use the Windows or Domain UserName and Password because it does not oblige a user to use several different IDs and passwords.

    Nothing prevents you to store the ID of the users that are allowed to use your application into a local permissions table. When the application starts it checks if the Windows User ID (retrieved through an API function -see below-) matches an entry into the permissions table and exits if it does not.

    Code:
    Public Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long
    
    Public Function GetNTUser() As String
    '
    ' Retrieve the ID of the currently connected user
    '
        Dim strUserName As String
        Dim lngUserNameSize As Long
        
        strUserName = String$(255, 0)
        lngUserNameSize = Len(strUserName)
        GetUserName strUserName, lngUserNameSize
        strUserName = Left$(strUserName, lngUserNameSize - 1)
        GetNTUser = strUserName
        
    End Function
    Have a nice day!

  7. #7
    Join Date
    Jul 2011
    Posts
    19
    Thanx Sinndho,

    i get how that code works but for some reason doesnt seem to work, comes up with the message "object doesnt support this property or method"

    any ideas???

  8. #8
    Join Date
    Jul 2011
    Posts
    19
    i have login details table "tbl_users"
    in that table are two fields, "user_Id" and "passwords"

    in my login form "frm_main" i have two unbound objects, "user_Id" and "password"

    how do i update the password "passwords" from "tbl_users" that corresponds to the "user_id" in "tbl_users" which is the same as the "user_id" from "frm_main" so that that "passwords" record has the word "locked" instead of what is currently in the "passwords" record?

  9. #9
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    for some reason doesnt seem to work, comes up with the message "object doesnt support this property or method"
    Where is this error? Is it in the VBA/SQL code (first bit), or the API code (second bit), that sinndho provided?

    If it's in the first bit, I'd imagine you haven't got the DAO reference library included in your code (you'll want to get into the VBA screen, then Tools, then References..., then tick Microsoft DAO x.x Object Library, Apply, Close).


    You need to click 'Debug' and see which bit of code gets highlighted in yellow when the error comes up.

  10. #10
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    As for your other question:

    i have login details table "tbl_users"
    in that table are two fields, "user_Id" and "passwords"

    in my login form "frm_main" i have two unbound objects, "user_Id" and "password"

    how do i update the password "passwords" from "tbl_users" that corresponds to the "user_id" in "tbl_users" which is the same as the "user_id" from "frm_main" so that that "passwords" record has the word "locked" instead of what is currently in the "passwords" record?
    Depending on how you want this to be updated... This assumes that there is something in the form itself that makes this change. It also assumes that 'user_id' is a textbox.

    Code:
    strSQL = "UPDATE tbl_users " & _
             "SET passwords = 'LOCKED' " & _
             "WHERE tbl_users.[user_Id] = Me.user_id.Value"
    
    DoCmd****nSQL (strSQL)
    The last line should read:
    DoCmd . Run SQL (strSQL)
    Without the spaces...


    Not the greatest way to do it, and it will show you a confirmation box before it updates the fields. To just do it, you'll want to use: 'CurrentDb.execute strSQL, dbFailOnError'


    Also, just noticed why that code from Sinndho is giving you an error, the line that reads:

    Code:
    Set dbs = CurrentBd
    Should read:

    Code:
    Set dbs = CurrentDb
    Last edited by kez1304; 07-29-11 at 08:41.

  11. #11
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Oops!
    Code:
    Set dbs = CurrentDb
    Sorry!
    Have a nice day!

  12. #12
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Haha, great minds!

    I'm forever doing that TBH. Pain in the bum.

  13. #13
    Join Date
    Jul 2011
    Posts
    19
    Ok thats great thanks guys.

    Code works perfect with Do.Cmd way but like you said, you get a confirmation box, which i dont want so i changed that line to the suggested 'CurrentDb.Execute strSQL, dbFailOnError' code instead and that comes back with the error 'Object doesnt support this property or method'

    am i missing something somewhere?

  14. #14
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Try:

    Code:
    CurrentDb.Execute (strSQL, dbFailOnError)
    Looking for the perfect beer...

  15. #15
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    And if that doesn't work, just take out the 'dbFailOnError' so it's:

    Code:
    CurrentDb.Execute (strSQL)
    But I would suggest having a read up on error handling and how to implement it properly as it's usually quite important.

    Either way, hope you're alright now.

    Failing that totally, you can always setWarnings like:

    Code:
    DoCmd.SetWarnings False
    DoCmd . Run SQL (strSQL)
    DoCmd.SetWarnings True
    Again, without the spaces. Not a very good method, but in a small database, probably not an issue.
    Looking for the perfect beer...

Posting Permissions

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