VBA Access with Variables

Question

whats wrong to my code all I want is to update table using Name and Pin of the Employee on their corresponding ID but any pin is accessible to the other Employee please help me!

Dim CanteenPOS As DAO.Database
Dim tblEmployee As DAO.Recordset

Set CanteenPOS = CurrentDb

Dim ID As Integer

If IsNull(Me.txtName) Or IsNull(Me.txtPin) Then
    MsgBox "First! Please enter your EmployeeID before PinID", vbInformation
    Me.txtName.SetFocus
Else
    If (IsNull(DLookup("EmployeeID", "tblEmployeeID", "EmployeeID = '" & Me.txtName & "'"))) Or _
       (IsNull(DLookup("PinID", "tblEmployeeID", " PinID = '" & Me.txtPin & "'"))) Then 
        MsgBox "Invalid EmployeeID or PinID!"
    Else
        Me.txtUsername = DLookup("[EmployeeID]", "tblEmployeeID", "[EmployeeID] = '" & Me.txtName.Value & "'")
        Me.txtPin = DLookup("[PinID]", "tblEmployeeID", " [EmployeeID] = '" & Me.txtName.Value & "'")

        MsgBox ("Your transaction is completed!")
        Set tblEmployee = CanteenPOS.OpenRecordset("tblEmployee")
        tblEmployee.AddNew
        tblEmployee("EmployeeName").Value = Me.txtName
        tblEmployee("OrderName").Value = Me.txtOrderType
        tblEmployee("Price").Value = Me.txtprice
        tblEmployee("Datetime").Value = lblDate.Caption
        tblEmployee("AddOn").Value = Me.txtAdd
        tblEmployee.Update
    End If
End If

Please clarify your question. It is unclear what you mean by any pin is accessible to the other Employee. You need to specify what you want to happen and what is happening. And if there are any error messages, please include the error number, full error message, and indicate which line of code the error occurs on. Please see How to Ask. — Jericho Johnson
– Jericho Johnson, Commented Sep 18, 2018 at 1:46
When im in transaction I used barcode scanner and I want to configure it to bound the name and pin to their respective account but when Im using name like "Howell" and his pin was "1234" and the other one is "Grant" his pincode was " 4567"in the table the issue are pin of howell was can be used also the pin of grant and the pin of grant is can be used by howell what should i do ! — Lou Algabre
– Lou Algabre, Commented Sep 18, 2018 at 1:58
The conditions you use when calling DLookup the second time are not identical. Inside the Else statement you have "[EmployeeID] = '" & which is not correct for either name or PIN. Besides, you should store the previous values in variables so you don't have to call DLookup redundantly. (Besides that, you seemed concerned that an employee could use another employee's PIN. If you are really concerned about security, Access is really a poor choice of database since any user can probably open the database and find all names and PINS... since it is apparent that you store the PIN as plain text.) — C Perkins
– C Perkins, Commented Sep 18, 2018 at 4:52

Margaret JB · Accepted Answer · 2018-09-20 06:20:23Z

I think the problem is after the "Or" in this second "If":

If (IsNull(DLookup("EmployeeID", "tblEmployeeID", "EmployeeID = '" & Me.txtName & "'"))) Or _
   (IsNull(DLookup("PinID", "tblEmployeeID", " PinID = '" & Me.txtPin & "'"))) Then 
    MsgBox "Invalid EmployeeID or PinID!"

You are checking to see that both the EmployeeID and PinID are in the database, but you are NOT checking to see that they are on the SAME record! As long as SOMEBODY has the pin, the test will pass!

Compare that with the way you find the PinID on the record retrieved by looking up the EmployeeID in the Else that follows.

Just find the record using EmployeeID, then find the PinID to go with that EmployeeID. Then compare the PinID found with the PinID entered by the user to be sure they match before saving the new data.

By the way, DLookup is not the most efficient way to get this information. The Execute command used with SQL (or with a saved query) can get you the PinID when you look up the EmployeeID without having to do a second DLookup. (And if you are not sure how to write the SQL, create a regular Access query and then look at the SQL view. That will show you what you need.)

(C. Perkins' comment about security has some merit, but if you are merely trying to keep honest people from making honest mistakes - like mistyping their EmployeeID by one number - then making sure the EmployeeID and PinID match might be enough for your purposes. They probably keep their EmployeeID and PinID together where other employees can see them anyway ;-)

Collectives™ on Stack Overflow

VBA Access with Variables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related