Filter

If you want to achieve better performance, I would suggest you to move the below function call to your form_load event where you open the recordset for all the staffs:

Call ConnectRS(CN, RS, "SELECT name,password FROM staff")

And then  your CheckNameAndPassword code can be changed as,

Public Sub CheckNameAndPassword()

RS.Filter = "name ='" & txtName.Text & "' and password ='" & txtPwd.Text & "'"

If RS.RecordCount = 0 Then
        MsgBox "Invalid Name & Password combination entered", vbCritical
        txtName.Text = ""
 txtPwd.Text = ""
        CN.Close
        Call ConnectDB(CN, App.Path & "\users.mdb")
        Exit Sub
End If

'reset the filter every time so that you will get all the records from staff table
RS.Filter = ""

'RS.Close - Close the recordset whenever your form closes
End Sub

Here, since we are  using Filter property of recordset,  you dont have to execute the query again and again for each and every name/password change.