CODEWITHSUNDEEP
ms-accessvba
Lou Algabre
Lou Algabre

Reputation: 11

VBA Access with Variables

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

Upvotes: 1

Views: 56

Answers (1)

Margaret JB
Margaret JB

Reputation: 47

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 ;-)

Upvotes: 1

Related Questions