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

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):

Me.lngEncrpytion_Password = DLookup("[Password]", "[tblpasswords]", "EncryptionID = '" & mmm owe password & "'")

Thanks for your reply myle

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

Any ideas?

Thanks

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

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

so how do you know when a password has been used before?

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

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.

Thanks

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

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)

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]

3 threads on the same topic merged into one

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

If you're still using the recordset, right after grabbing the password:

Debug.Print ![Password]

set the yes/no field"

.Edit
!YesNoFieldName = True
.Update