Setting password protection on a combobox

[ecco]

Hi,
I am trying to set password protection on a combobox where there are only a few people who I want to be able to select their name to provide authorisation. I found the below code on a website and modified it slightly for my table (tbl_Approval) that contains two fields; Approver and Password. I can see how the code is supposed to work however I keep getting a syntax error (missing operator) in query expression on line: Set rs = CurrentDb.OpenRecordset(sql). Can anyone help? Thank you!

Private Sub Approved_By_Enter()
    Dim pwd As String
    Dim rs As Object, rstable As Object, count As Integer, counter As_ Integer
    Dim sql As String, recount As Integer
    sql = "SELECT * " & "FROM [tbl_Approval] " & "WHERE [Approver]= " & Me.Approved_By
    Set rs = CurrentDb.OpenRecordset(sql)
    recount = rs.RecordCount
    'MsgBox "Record Count" & rs.Password
    pwd = InputBox("Please enter password", "Secure")
    If pwd = rs.Password Then
    rs.Close
    Set rs = Nothing
    Exit Sub ' return control to User on the form to allow another try
    Else
    If pwd <> rs.Password Then
    ' Here if incorrect Password for this Username.
    MsgBox "That is not the correct password for" & Chr(13) & _
        "that username. Please try Again.", vbExclamation, "Invalid Password"
    Me.Approved_By.SetFocus
    ' Start login process over again.
    ' Close the recordset.
    rs.Close  
    Set rs = Nothing
    Exit Sub
    End If
    End If
End Sub

 

[JHB]

Try the following, then I think "Approver" is a text field:

   sql = "SELECT * FROM [tbl_Approval] WHERE [Approver]= " & "'" & Me.Approved_By & "'"

 

[ecco]

Hi JHB,

That works great thank you and I revised it to execute on change, the only problem I have now is that even if the password is incorrect it will still leave the persons name in the combobox when I move to the next field, ideally I want it to just remain null unless the person selects their name from the dropdown menu and enters the correct password, only then will the name remain in the field once you move off of it? Cheers.

 

[JHB]

Can't you clear it by using Me.Approved_By=""