If statement with Dlookup help

reggiete

Registered User.
Local time
Today, 01:50
Joined
Nov 28, 2015
Messages
56
Hello All,

I am needing up with coming up with a macro/vba to lookup a value in a table and compare it to a record in a form.

Example below

Master Table/Parent Table
ID AAL Access Level Allowed Access
1 AAL123 Level 1 Access Wire Transfer
1 AAL123 Level 1 Access Paypal


Form has the following columns in datasheet view
UserName AAL Access Verified Access Permission
Johndoe AAL123 Wire Transfer
Johndoe AAL123 Paypal


I would like to run a macro or vba code to lookup the AAL for each record on the form and find it in the Parent Table, once the code fields the id, compare the Access column in the form one with the Allowed Access in the parent table, if it matches then place a fail in the Verified Access Permission Column

so maybe dlookup on the AAL in the Form in the Master/Parent table and compare the Allowed Access for the AAL # with the Access column in the form, if it matches then Pass, if not then Fail
 
How do the records get on the Form?<---where do these records come form???
Records(data) typically is stored in tables.
To compare table contents you could run a query.
 
the records in the form will be based on another table from a report that shows users access and i am trying to compare them with the data in the parent table to verify if they have the correct access for the AAL they report in
 
Let's get something clear. Which value on the form do you want to compare with which record on the table. What connects the form record and the table record?
 
here some code i was playing with. Maybe this will help. Im still new to vba so forgive me if this is messy

If Me.AAL = IsNull(DLookup("AAL", "AAL_Matrix", "AAL='" & Me.AAL & "'")) Then
Me.Verified_Access_Permission = " AAL Not Found"
ElseIf Me.AAL = DLookup("AAL", "AAL_Matrix", "AAL='" & Me.AAL & "'") Then
If Me.Access = DLookup("Child", "AAL_Matrix", "Child='" & Me.Access & "'") Then
Me.Verified_Access_Permission = "Pass"
Else
Me.Verified_Access_Permission = "Fail"
End If
End If

The following code works somewhat but what i would also like to consider is
in the AAL_Matrix table there will be multiple rows with AAL #, for example

AAL123 may have multiple rows with level of access allowed, i need to vba code to check all rows with the AAL # matching and verify if the me.access(located on the form) = the allowed access in the AAL_Matrix where the AAL matches
 
Forget the vba for the moment. You have 2 tables---- T1 and T2.
What are the fields in these tables?
What is the data in these tables?

Which field(s) in T1 do you compare with which field(s) in T2?

Your basic comparison does NOT necessarily involve the Form.
T1(field) vs T2 (field) can be done with a query, generally.
 
T1 fields
ID AAL UserName Access Verified Access Permission

T2
ID AAL Parent Child

I would like to compare the AAL field in T1 with T2 AAL column, If true, then compare T1 Access column with Child column in t2 if match then pass, if not a match then Fail.

Basically i want the query to look for levels of access in T2 by AAL in t1
 
Untested but sql along these lines:

For Pass
Code:
Select tt1.*,tt2.*,"Pass"
from tt11 inner join tt2 on
  tt1.aal = tt2.aal and
  tt1.access =tt2.child


For Fail

Code:
SELECT TT1.*, TT2.*, "Fail"
FROM TT1 LEFT JOIN TT2 ON
   (TT1.access = TT2.child) AND 
   (TT1.aal = TT2.aal)
WHERE
 (TT2.AAL Is Null) AND (TT2.Child Is Null);

Good luck.
 

Users who are viewing this thread

Back
Top Bottom