PIN number for employees

[santon]

Hello.


I need a help with my checklist. It contains 5 different combo boxes, with a list of employees.
After they select their name on the list, I need them to input the PIN number I have stored for them in another table to verify themselves.


Operater_p has "after update" setting, macro that opens a small modal form, where you have to enter the pin.




Button "Potvrdi PIN" has event procedure "On click", and this code:

Option Compare Database



Private Sub potvrda_pin_Click()


    Dim Username As String
    Dim strSQL As String
    Dim strMsg As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim password_check As Integer
    
    Username = Form_CO_pakiranje_SD14.operater_p
    Set dbs = CurrentDb()
    strSQL = "SELECT password FROM Evidencija_radnika WHERE ime_prezime = '" & Username & "';"
    Set rst = dbs.OpenRecordset(strSQL)
    password_check = rst![Password]
    rst.Close
    dbs.Close
    If (Form_CO_pakiranje_SD14!potpis_operater) <> password_check Then
    strMsg = Username & ", niste unijeli tocan PIN!"
        MsgBox strMsg
        Form_CO_pakiranje_SD14!potpis_operater = ""
        Else
        Form_CO_pakiranje_SD14!potpis_operater = password_check
        strMsg = Username & ", uspjesno potvrden PIN!"
        MsgBox strMsg
    End If


End Sub

It shows no error, but it doesn't work as it supposed to.
If you click "Potvrdi PIN" first time it returns it's wrong number, and if you click again then it says that is confirmed number.
Can you help me fix this?
And possibly if I can add something so if it is good PIN, then close this form "potpis" and maybe paint the operater_p in green (conditional formatting maybe?)

[ranman256]

sub btnEnter_click()
if IsGoodEmpPin(txtEmpNum, txtPin) then
  'let user in
else
   msgbox "Invalid"
endif




function IsGoodEmpPin(pvEmpNum,pvPin) as boolean
on error resume next
   IsGoodEmpPin =dLookup("[EmpID],"tEmployees","[EmpID]='" & pvEmpNum & "' and [Pin]='" & pvPin & "'") = pvEmpNum
end function

[santon]

sub btnEnter_click()
if IsGoodEmpPin(txtEmpNum, txtPin) then
  'let user in
else
   msgbox "Invalid"
endif

function IsGoodEmpPin(pvEmpNum,pvPin) as boolean
on error resume next
   IsGoodEmpPin =dLookup("[EmpID],"tEmployees","[EmpID]='" & pvEmpNum & "' and [Pin]='" & pvPin & "'") = pvEmpNum
end function

Excuse me, can you be a little more specific? I am really a noob at programming and I don't fully understand what you wrote.
Do I need it to type in your code with mine or only yours? Can you explain a little bit what I need to change




Also, I had an idea and I don't know can it work, but seems a little bit easier. Can I create a text box (field for entering the pin number) and combo box, which will have list of employees, compare names with PIN and then show the full name of the employee (set on locked so it can't be edited)

[orange]

There is a series of Access/vba tutorials by Steve Bishop here. #56 may be useful to your current post.
Good luck.

[Gicu]

Please review the updates code below:

Option Compare Database
Option Explicit  'always use




Private Sub potvrda_pin_Click()




    Dim Username As String
    Dim strSQL As String
    Dim strMsg As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim password_check As Integer
    
    Username = Form_CO_pakiranje_SD14.operater_p
    Set dbs = CurrentDb()
    strSQL = "SELECT password FROM Evidencija_radnika WHERE ime_prezime = '" & Username & "';"
    Set rst = dbs.OpenRecordset(strSQL)
    password_check = rst![Password]
    rst.Close
    dbs.Close
    If (Form_CO_pakiranje_SD14!potpis_operater) <> password_check Then  'the form in bold red should be the modal one not the one with the combos
        strMsg = Username & ", niste unijeli tocan PIN!"
        MsgBox strMsg
        Form_CO_pakiranje_SD14!potpis_operater = Null    ' was ""
    Else
        'Form_CO_pakiranje_SD14!potpis_operater = password_check  'not needed
        strMsg = Username & ", uspjesno potvrden PIN!"
        MsgBox strMsg
        Form_CO_pakiranje_SD14.operater_p.BackColor=RGB(0,255,0)  'green back color
        DoCmd.Close acForm, Me.Name
    End If




End Sub

RGB(0,255,0)

[ssanfu]

A couple of other VBA corrections:

Option Compare Database
Option Explicit  'always use

Private Sub potvrda_pin_Click()
    Dim Username As String
    Dim strSQL As String
    Dim strMsg As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim password_check As Integer
    
    Username = Form_CO_pakiranje_SD14.operater_p
    Set dbs = CurrentDb()
    strSQL = "SELECT password FROM Evidencija_radnika WHERE ime_prezime = '" & Username & "';"
    Set rst = dbs.OpenRecordset(strSQL)
    password_check = rst![Password]
    rst.Close
    dbs.Close    '<-- You did not "Open" dbs, so you should NOT "Close" it.
    If (Form_CO_pakiranje_SD14!potpis_operater) <> password_check Then  'the form in bold red should be the modal one not the one with the combos
        strMsg = Username & ", niste unijeli tocan PIN!"
        MsgBox strMsg
        Form_CO_pakiranje_SD14!potpis_operater = Null    ' was ""
    Else
        'Form_CO_pakiranje_SD14!potpis_operater = password_check  'not needed
        strMsg = Username & ", uspjesno potvrden PIN!"
        MsgBox strMsg
        Form_CO_pakiranje_SD14.operater_p.BackColor=RGB(0,255,0)  'green back color
        DoCmd.Close acForm, Me.Name
    End If

'Clean up
    Set rst = Nothing    '<-- Added     You should Destroy the pointer after the record set is closed
    Set dbs = Nothing   '<-- Added     You should Destroy the pointer

End Sub

[santon]

Please review the updates code below:

Option Compare Database
Option Explicit  'always use




Private Sub potvrda_pin_Click()




    Dim Username As String
    Dim strSQL As String
    Dim strMsg As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim password_check As Integer
    
    Username = Form_CO_pakiranje_SD14.operater_p
    Set dbs = CurrentDb()
    strSQL = "SELECT password FROM Evidencija_radnika WHERE ime_prezime = '" & Username & "';"
    Set rst = dbs.OpenRecordset(strSQL)
    password_check = rst![Password]
    rst.Close
    dbs.Close
    If (Form_CO_pakiranje_SD14!potpis_operater) <> password_check Then  'the form in bold red should be the modal one not the one with the combos
        strMsg = Username & ", niste unijeli tocan PIN!"
        MsgBox strMsg
        Form_CO_pakiranje_SD14!potpis_operater = Null    ' was ""
    Else
        'Form_CO_pakiranje_SD14!potpis_operater = password_check  'not needed
        strMsg = Username & ", uspjesno potvrden PIN!"
        MsgBox strMsg
        Form_CO_pakiranje_SD14.operater_p.BackColor=RGB(0,255,0)  'green back color
        DoCmd.Close acForm, Me.Name
    End If




End Sub

RGB(0,255,0)

Thank you for your answer. I tried, but it doesn't work :/ I must have done something wrong

[CJ_London]

operater_p should be the name of your control, not the name of the field

[Gicu]

You can also try Forms!CO_pakiranje_SD14!operater_p.BackColor if operater_p is indeed the control name.

Cheers,

[santon]

This is not gonna work. Tried it multiple times, returns various errors, I'm gonna leave that idea with modal form.
This one is working just fine, I just wanted to make it more interesting.

Ok, I will use this code to do this on the pictures below, I just want after PIN is successfully entered (like on the picture)
to make field operater_p and potpis_operater disabled, to make the others aware that "OPERATER" is done with his part of this checklist.
Can you help me with that?
Also thank you for your help so far

Option Compare DatabaseOption Explicit




Private Sub potpis_operater_AfterUpdate()
Dim Username As String
    Dim strSQL As String
    Dim strMsg As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim password_check As Integer
    
    Username = Form_CO_pakiranje_SD14.operater_p
    Set dbs = CurrentDb()
    strSQL = "SELECT password FROM Evidencija_radnika WHERE ime_prezime = '" & Username & "';"
    Set rst = dbs.OpenRecordset(strSQL)
    password_check = rst![Password]
    rst.Close
    
    If CInt(Form_CO_pakiranje_SD14!potpis_operater) <> password_check Then
    strMsg = Username & ", niste unijeli tocan PIN!"
        MsgBox strMsg
        Form_CO_pakiranje_SD14!potpis_operater = ""
        Else
        Form_CO_pakiranje_SD14!potpis_operater = password_check
        strMsg = Username & ", uspjesno potvrden PIN!"
        MsgBox strMsg
    End If
    
    Set rst = Nothing
    Set dbs = Nothing
    
End Sub

[Gicu]

If I understand you correctly I think the easiest way to achieve that is to add in the Current event of the form checks to see if both text boxes are filled in then disable them:

If Not IsNull(Me.operater_p) And Not IsNull(Me.potpis_operater) Then
Me.operater_p.Enabled =False
Me.potpis_operater.Enabled=False
End If
'similar checks for the other pairs

Cheers,
Vlad

[santon]

If I understand you correctly I think the easiest way to achieve that is to add in the Current event of the form checks to see if both text boxes are filled in then disable them:

If Not IsNull(Me.operater_p) And Not IsNull(Me.potpis_operater) Then
Me.operater_p.Enabled =False
Me.potpis_operater.Enabled=False
End If
'similar checks for the other pairs

Cheers,
Vlad

It disabled both fields, but when I want to add new record it is also disabled

[Gicu]

You need to enable them:

If Not IsNull(Me.operater_p) And Not IsNull(Me.potpis_operater) Then
       Me.operater_p.Enabled =False
       Me.potpis_operater.Enabled=False
Else
      Me.operater_p.Enabled =True
      Me.potpis_operater.Enabled=True
End iF

Cheers,
Vlad

[santon]

You need to enable them:

If Not IsNull(Me.operater_p) And Not IsNull(Me.potpis_operater) Then
       Me.operater_p.Enabled =False
       Me.potpis_operater.Enabled=False
Else
      Me.operater_p.Enabled =True
      Me.potpis_operater.Enabled=True
End iF

Cheers,
Vlad

It works only when I press the button to "add a new record", when I return to view the previous one, it is disabled.
But while I enter PIN and it is confirmed, I can still edit everything.

[accesstos]

Hi all!

It shows no error, but it doesn't work as it supposed to.

Form_CO_pakiranje_SD14 is the class of then form "CO_pakiranje_SD14", not the running instance of this class and, in this case of reference, code works accidentally. You have to use a proper way of reference to a loaded form, like:

• Me (for the “potpis” form)
• Forms("CO_pakiranje_SD14")
• Forms!CO_pakiranje_SD14

Please try this version of your potvrda_pin_Click procedure and check if it works properly (I didn’t):

Private Sub potvrda_pin_Click()
    Dim Username As String
    Dim strSQL As String
    Dim strMsg As String
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim password_check As Variant  'Variant for the case of Null password.
    Dim frmSD14 As Form            'Variable for the form "CO_pakiranje_SD14"

    With Me.pin_operater 'Check the user entry.
        If Len(.value & "") = 4 And IsNumeric(.value & "") Then
            'Correct pin entry. Keep going.
        Else
            'Incorrect pin entry. Display a relevant message here.
            'I often use the ValidationText of the control to keep the corresponding message.
            MsgBox .ValidationText, vbExclamation, Me.potvrda_pin.Caption
            .SetFocus
            Exit Sub
        End If
    End With

    On Error Resume Next
    Set frmSD14 = Forms!CO_pakiranje_SD14   'Set a pointer to the form "CO_pakiranje_SD14" (if is loaded).
    On Error GoTo ErrHandler

    If Not frmSD14 Is Nothing Then  'If not is loaded frmSD14 is Nothing.
        Username = Nz(frmSD14.operater_p, "")    'Could be Null.
        If Len(Username) = 0 Then   'Check if there is a name in operater_p.
            '[operater_p] is empty! Display a relevant message here.
        Else
            Set dbs = CurrentDb()
            strSQL = "SELECT password FROM Evidencija_radnika WHERE ime_prezime = '" & Username & "';"
            Set rst = dbs.OpenRecordset(strSQL)
            If rst.BOF And rst.EOF Then
                'User not found! Display a relevant message here.
            Else
                password_check = rst![Password]    'Could be Null.
                If IsNull(password_check) Then
                    'password/pin has not been set! Display a relevant message here.
                Else
                    If (Me.pin_operater) <> password_check Then
                        strMsg = Username & ", niste unijeli tocan PIN!"
                        MsgBox strMsg, vbExclamation, Me.potvrda_pin.Caption
                        frmSD14.potpis_operater = Null
                    Else
                        frmSD14.potpis_operater = password_check
                        strMsg = Username & ", uspjesno potvrden PIN!"
                        MsgBox strMsg, vbInformation, Me.potvrda_pin.Caption
                        DoCmd.Close acForm, Me.Name
                    End If
                End If
            End If
        End If
    End If
ExitHere:
    On Error Resume Next
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
    On Error GoTo 0
    Exit Sub
ErrHandler:
    MsgBox Err.Description, vbExclamation, Me.potvrda_pin.Caption
    Resume ExitHere
End Sub

Cheers,
John