Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Sep 2011
    Posts
    28

    Unanswered: DLookup only returning first value from table

    Hi

    I wonder if someone could help.

    I am using a DLookup function on a command button to populate a field on a form with value from another table. However it only seems to populate the field with the top value from the table.

    Below is the code


    Private Sub Command111_Click()
    Me.lngEncrpytion_Password = DLookup("[Password]", "[tblpasswords]", "EncryptionID = EncryptionID = '" < myknowngoodpassword > "'")
    End Sub

    Any help will be much appreciated.

    Thanks

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    The DLookup() function fetch the value it returns from the first row that satisfies the criteria passed as its third argument. This is by construction.
    From MSDN (http://msdn.microsoft.com/en-us/library/Aa172176):
    The DLookup function returns a single field value based on the information specified in criteria. Although criteria is an optional argument, if you don't supply a value for criteria, the DLookup function returns a random value in the domain.
    Have a nice day!

  3. #3
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Me.lngEncrpytion_Password = DLookup("[Password]", "[tblpasswords]", "EncryptionID = '" & mmm owe password & "'")
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  4. #4
    Join Date
    Sep 2011
    Posts
    28
    Thanks for your reply myle

    I tried your code but I get a data mismatch error.

    Any ideas?

    Thanks

  5. #5
    Join Date
    Sep 2011
    Posts
    28

    Recordset returning blank value

    Hi I wonder if anyone can help.



    I have created a Recordset to populate a field on a form from a table in the database.



    Basically I have database which holds passwords for laptops. What I want is that when a new record is added into the database the password is automtically selected from a table in the database. (This table is basically a list of passwords).



    I have created a Recordset im hope of acheiving this, however when I run the recordset it does not put the password into my desired textbox.



    How would I achieve this?



    The code is below



    Private Sub Command115_Click()
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    Set rs = CurrentDb.OpenRecordset("SELECT [Password] FROM [tblpasswords] WHERE EncryptionID ")
    With rs
    While Not .EOF
    Debug.Print ![Password]
    .MoveNext
    Wend
    End With
    End Sub



    Thanks

  6. #6
    Join Date
    Sep 2011
    Posts
    28

    Recordset returning blank value

    To make myself more clear of what I am trying to acheive.

    I have table consisitng of strings. i.e

    Table Name = Passwords
    Blue1
    Blue2
    Blue3
    Blue4
    Blue5 etc.

    I have a form which holds details of a laptop. I have a text field on this form call Passwords. I have a command button next to this field. When I click this button I want it to select the next available value from the above table.

    So in the previous record, if the laptop has the password 'blue2'. When i create the next new record I want the password to be 'Blue 3'.

    I have compiled the code above in hope to achieve this

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so how do you know when a password has been used before?
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2011
    Posts
    28
    Hi Healm

    Thanks for your reply.

    I would imagine I would need to use some form of validation to aviod repeating data.

    At this moment in time I would just like to get my database working in the manner described in the post.

    I have created a Recordset which is below

    rivate Sub lngEncrpytion_Password_AfterUpdate()
    Dim db As Database
    Dim rs As Recordset
    Set db = CurrentDb()
    Set rs = CurrentDb.OpenRecordset("SELECT [Password] FROM [tblpasswords] WHERE EncryptionID ")
    With rs
    While Not .EOF
    Debug.Print ![Password]
    .MoveNext
    Wend
    End With
    End Sub

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    well if you don't know how to identiofy when a password hasd been allocated then how do you expect to get an answer


    personally I'd probably use a flag column (boolean), called say IsUsed, to indicate a password has been used, but thats just me.
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Sep 2011
    Posts
    28
    Thanks

    But is a Recordset the best way to achieve my objective?

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your onbjective is what?
    ...to retrieve the next available password?

    take a step out for now
    to find out the next available password you must know what the last one used was (or the next one is)

    so you need some storage mechanism, whether thats a recordset to an attached database, a flat file or whatever doesn't matter.

    what doe smatter is that you have a mechanism to identify the next passowrd

    personally if you are already using a realtional db to store 'stuff' then I'd store the element of data in there.

    ..so that probably means using some form of recordset (even the doman functions such as DLOOKUP will resolve back to a recordset somewhere even if you dont' actually program it

    as I see it you need a datastore identifying available passwords AND some means of identifying what passwords have been used. now that could mean looking at the passwords in use, doing a join to the passwords and finding the next one
    it coudl be each time you have used a password you delete it from the table
    it could mean you flag a password int he avaialbel passwords as in use. fundamentally its your design choice.

    anothe r approach is not to store passwords at all, but create a new account / password as and when required.

    using passwords that are preallocated in numeric sequences is frankly a daft idea leaving your security wide open to abuse, but its your design, your desing choices your neck on the line if this password is meant to be secure. bear in mind that there are 3 types of security
    1) none
    2) effective
    3) pretend security (ie where you have the trappings of security but the application isn't at all secured)


    1) & 3) are easy to implement, eacy to break and ineffective
    2) requires carefull thaught about what the potential attavck vectors are, what you can do to protect, and just as importantly what you do to identify if someone is trying to circumvent your security. however implementing a truly e=ffective security in an Access application using JET tables is a tricky act to pull off, assuming it can be done (and I have my doubts)
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    A couple of thoughts. First, "WHERE EncryptionID"...what? Normally it would be where the field was equal to some value, like:

    "...WHERE EncryptionID = 123"
    "...WHERE EncryptionID = " & Me.EncryptionID

    What you have might work if that's a yes/no field and you want the True records. Also, your code isn't going to populate a textbox. All it's going to do is print out any records to the VBA Immediate window, which is here if you're unfamiliar:

    Debugging

    To put it in a textbox:

    Me.TextboxName = ![Password]
    Paul

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    3 threads on the same topic merged into one
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    Join Date
    Sep 2011
    Posts
    28
    Right. I am getting futher now

    I have inserted a Yes/No box in the 'Password's table to help identify which password has been used. However at the moment users have to manaully select 'Yes' for the password which has been allocated. Is there anyway of making this an automated process?

    Again, any help would be much appreciated.

    Thanks

  15. #15
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    If you're still using the recordset, right after grabbing the password:

    Debug.Print ![Password]

    set the yes/no field"

    .Edit
    !YesNoFieldName = True
    .Update
    Paul

Tags for this Thread

Posting Permissions

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