Retrieve field value in the table as variable in VBA?

[pvn]

hi all!

I have made a password protection to a form and the code is
below. i.e the form opens only when the user enters password. I also need to give change password access to the user. For that, i made a password table and I am storing password in that. Now, I would like to retrieve the password from the table and place it in the code below(instead of writing "hello" directly in the code below, is it possible to retrieve it from the table?). I;ve tried but it didn't work out. I would be thankful if you could tell me the way. Any changes made to the code would be appreciated.

Thanks
I







Private Sub PassBut_Click()
On Error GoTo Err_PassBut_Click

Dim stDocName As String
Dim stLinkCriteria As String
If InputBox("Enter Password to view medical information", "Enter Your Password&quot = "hello" Then
stDocName = "Medical"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
End If

Exit_PassBut_Click:
Exit Sub

Err_PassBut_Click:
MsgBox Err.Description
Resume Exit_PassBut_Click
End Sub

 

[LonnieJohnson]

Try this instead of the above. On the line that has
rst!Password, you will replace the Password with the name of the field in your table that has the password.

Private Sub PassBut_Click()
On Error GoTo Err_PassBut_Click

Dim strPassword As String
strPassword = InputBox("Enter Password to view medical information", "Enter Your Password&quot

Dim db As Database
Dim rst As Recordset
Set db = CurrentDb
Set rst = db.OpenRecordset("YourTableNameHere&quot 'Must go inside the quotes

Do Until rst.EOF
If rst!Password = strPassword Then

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "Medical"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.Maximize
Exit Sub
End If

MsgBox "That password is incorrect"

Exit_PassBut_Click:
Exit Sub

Err_PassBut_Click:
MsgBox Err.Description
Resume Exit_PassBut_Click
End Sub ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[Guest_imported]

Hi

thanks...

but it says User defined type not defined at Dim db as database

Thanks

 

[LonnieJohnson]

It sounds like we have a problem with the way the compile is reading this line.

Did you cut and paste it? If so there may be some wierd invisible characters called "foot prints" that could of carried over.

Is the word Database capitalized on this line? This is an indication if Access like the line or not. It should be cap'd.

Try Deleting the line and retyping it. You should get an list after you type the word As that will have database as an option.

What version of Access are you using? ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[Guest_imported]

Hi..

I tried retyping it.. The list came but there is no Database option in it..


Pavan.

 

[LonnieJohnson]

The code I gave you was written using Access97. What version are you using?

Are you sure all your features have been installed for your version of Access?

The Database option is pretty common and should be part of your list. ljprodev@yahoo.com
Professional Development
MS Access Applications

 

[Guest_imported]

Hi Mr.Johnson

Mine is Access 2000. Sorry for not mentioning the version...