Help with Dlookup with 3 criteria (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
Hi! all

Tried to search the net, but unable to find this.

Below 3 fields which I am trying to capture. On the before update event it should match all the below 3 details for the given Entry_Date(if one of them is not matching it should not capture and a pop-up alert msg of the mis-match

The current before update event does not give me the result, can you suggest me what i am doing wrong pls.

Ref_Nos
Branch_Name
Account_Nos

The data to be captured in form, "frm_CheckerVerify" is mentioned below.
Code:
RefNos	BranchName	AccountNos
0001	BRN6	        1234567890123
0002	BRN3	        1234567890222
0003	BRN7	        1234567890444
0004	BRN7	        1234567890444
0005	BRN3	        1234567890222
i am attaching the DB for clarity.
 

Attachments

  • AutoIncrement_2.mdb
    376 KB · Views: 112

June7

AWF VIP
Local time
Today, 02:04
Joined
Mar 9, 2014
Messages
5,425
Table3 should have a better name, maybe Accounts.

How are these tables related? Should not be duplicating Table3 fields in tblVerified. Probably should be saving tbl_EntryDate SrNos values as foreign key into Table3 and Table3 ElowID into tbl_Verified.

Use form BeforeUpdate event to validate combination of data.

You might find combobox helpful to prevent users entering invalid values.
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
Thanks June for your response.

Table3 should have a better name, maybe Accounts
Sure when i am making the final version will have this taken into consideration

How are these tables related? Should not be duplicating Table3 fields in tblVerified. Probably should be saving tbl_EntryDate SrNos values as foreign key into Table3 and Table3 ElowID into tbl_Verified.
Yes probably you are right, but i only going to store the data for a particular day and then the data is deleted. The whole idea was to force the checker to check the 3 fields independently by looking at physical docs against tbl_Verified, save the data in excel before deleting.

Use form BeforeUpdate event to validate combination of data.
You might find combobox helpful to prevent users entering invalid values.
Yes i have it on the form, the challenge is it is 3 combinations which i am asking help. Combobox will not help as the
Below is the dlookup
Code:
Private Sub Account_Nos_BeforeUpdate(Cancel As Integer)
    Dim strAccount As String
    Dim strAccount1 As String
    
    strAccount = Me.Our_Ref & Me.Ref_Nos
    strAccount1 = Me.Our_Ref
        
    If Not IsNull(DLookup("Ref_Nos", "tbl_Verified", "Ref_Nos= '" & strAccount1 & "'")) Then
    Me.Undo
    
    MsgBox "Duplicate Account Nos"
Exit Sub
Else
End If
    If IsNull(DLookup("AccountNos", "Query3", "SpClgRef = '" & strAccount1 & "'")) Then
    Me.Status.Value = "Match"
    Me.Undo
    
    MsgBox "Incorrect Account Nos"
Else
    Me.Status.Value = "UnMatch"
End If
End Sub
Hope you can advice me the dlookup function for 3 criteria mentioned in my previous post. Thanks
 

June7

AWF VIP
Local time
Today, 02:04
Joined
Mar 9, 2014
Messages
5,425
You are using textbox BeforeUpdate event. I suggested using the form BeforeUpdate event to validate multiple values.

Is this what you want:

DLookup("OutwardDate", "Table3", "RefNos='" & Me.Ref_Nos & "' AND BranchName='" & Me.Branch_Name & "' AND AccountNos='" & Me.Account_Nos & "'")

No records have RefNos data.

Why would a combobox not be helpful? With a combobox user could select only valid account. Typing an account that doesn't already exist would be immediately apparent when there is no match in the list.
 

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
HI!, i have applied the Dlookup that you provided however it accepts even the incorrect data.

Combox will not apply, since i want user to type the account nos looking at the doc and not select any drop down.
 

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
HI! all, any help from any one on the dlookup function. I am trying to combine 3 fields validation while matching data. So far no luck.

thanks
 

June7

AWF VIP
Local time
Today, 02:04
Joined
Mar 9, 2014
Messages
5,425
You can still type into a combobox. It has the best features of both a textbox and listbox.

If the DLookup accepts "incorrect" data, why is it incorrect? If the combination of values finds a match, what do you want to happen? Post your procedure code.
 

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
HI! June,
Checker captures the below line in form "frm_CheckerVerify"

RefNos BranchName AccountNos
0001 BRN6 1234567890123

if one of these is mis-match it should stop the user from moving forward until he rectifies the line.
RefNos is unique, however BranchName & AccountNos can be repeated, as mentioned in my post #1

i used the dlookup function provided by you. however it accepts all data.

The code posted in post#3 with the replacement of Dlookup provided by you.
 

Mark_

Longboard on the internet
Local time
Today, 03:04
Joined
Sep 12, 2017
Messages
2,111
lookforsmt,

Having the user type in what they see on the document does make sense, but only if you are trying to test their typing skills or reading skills. If they have the document in hand, why not allow them to use combo boxes to avoid mistakes?

If the goal is to make sure they match documents against existing accounts, then show them something unique to the account that they validate against. If they put in the account number and you show them the business / account holders name, its easy for them to check against the document to make sure it matches. If you are just checking anything they type to see if it is a match, this won't really help eliminate errors.
 

June7

AWF VIP
Local time
Today, 02:04
Joined
Mar 9, 2014
Messages
5,425
Possibly doesn't work as you want because code is in textbox AfterUpdate event. Was data already entered into the other 2 controls involved?

If you want to provide db for analysis, follow instructions at bottom of my post.
 

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
Hi! Mark

I am responding to your remarks

Having the user type in what they see on the document does make sense, but only if you are trying to test their typing skills or reading skills. If they have the document in hand, why not allow them to use combo boxes to avoid mistakes?
I want to avoid combox for the fact that i want the user to look at the document and capture the details rather then selecting the dropdown list.
There are repeat account nos with different ref_nos is also another reason to avoid combobox.

If the goal is to make sure they match documents against existing accounts, then show them something unique to the account that they validate against. If they put in the account number and you show them the business / account holders name, its easy for them to check against the document to make sure it matches.
Yes, the goal is to make sure they type the details mentioned on the documents. If the account number can be seen on the form would be additional feature. But that too much to ask.
If you are just checking anything they type to see if it is a match, this won't really help eliminate errors.
Table3 has the original data, if the data captured does not match, then it should not allow the user to move forward unless he captures the correct details.

SpClgRef is the unique refrence for that particular row. It is the combination of Our_Ref and Ref_Nos.
If Ref_Nos + Branch_Name + Account_Nos for that particular date (Date_Checked) is matching then the status is Match, else it does not allow the user to move forward.

HI! June7,

i have updated my db.

Thanks
 

Attachments

  • AutoIncrement_3.mdb
    380 KB · Views: 105

Mark_

Longboard on the internet
Local time
Today, 03:04
Joined
Sep 12, 2017
Messages
2,111
lookforsmt,

From your data, if they SHOULD be working on RefNos 0003, but they mis-type it as RefNos 0004, your method won't catch the error. If they only need to type in the RefNos and you show them something unique, they are far more likely to catch an error.

If RefNos is the one unique value that matches to the other two, why not only have them type this in? The extra typing will only slow things down. From a users standpoint, it looks like you are trying to make things more difficult and take longer. Normally this is the opposite of what most developers strive for.
 

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
thanks Mark,

Code:
From your data, if they SHOULD be working on RefNos 0003, but they mis-type it as RefNos 0004, your method won't catch the error. If they only need to type in the RefNos and you show them something unique, they are far more likely to catch an error.
Along with RefNos user will capture BranchName & AccountNos. The combination of these 3 text fields will avoid the error. this may slow down the process, but it will help me accuracy. Also, the BranchName is there for a purpose since the income of the transaction goes to this branch from the AccountNos
 

June7

AWF VIP
Local time
Today, 02:04
Joined
Mar 9, 2014
Messages
5,425
What exactly is the process?

User enters values into subfrm_Verified (which is bound to tbl_Verified)?

Determine if combination of RefNos, BranchName, AccountNos values entered is already in tbl_Verified?

If there is a match, what should happen?

Code in just Account_Nos textbox BeforeUpdate event will probably not be enough. All three values need to be entered before verification. I already suggested you use form BeforeUpdate event for that. Or code in each textbox AfterUpdate event that first checks if the other two values are entered before verifying the combination.

Why is RefNos field in Table3? Why no values?

Is RefNos supposed be a unique value in tbl_Verified? Or can it repeat with different BranchName and AccountNos?
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
HI! June7, I will try to explain the scope, hope it helps

1st part of the job is: UserA captures the below details looking at the documents in Table3
using form "frmEIOutward" to a subform "Subfrm_tbl3" which is linked to
Query3

Adds:

a) Entry_Date & Our_Ref one time
b) BankName
c) AccountNos
d) ChqNos
e) Amount
f) BranchName
g) BranchSOL
h) Status ( This field is empty)

i)OutwardDate; OurRef; RefNos & SpClgRef is auto populated

"RefNos" is formated as "0000" and generated based on OutwardDate.
Combination of "OurRef" & "RefNos" gives "SpClgRef" wich is unique code

2nd part is the checker (another user) captures the below fields in another table "tbl_Verified"
which is linked to a table "tbl_Verified"

Enters:

a) On Mainform Find Entry_Date

In subform enters
b) Ref_Nos
c) Branch_Name
d) Account_Name
e) Date_Checked; SpClg_Ref; Our_Ref; is auto populated.

Here, i wanted Dlookup fuction to check the Ref_Nos + Branch_Name + Account_Name combination, if it is matching then update the status as "Match"
This is my original request to validate 3 fields.

Why is RefNos field in Table3? Why no values?

Is RefNos supposed be a unique value in tbl_Verified? Or can it repeat with different BranchName and AccountNos?

RefNos, It only shows on Query3, i dont know how to poupulate the same on Table3

RefNos is unique value for that Date which is field "Checked" field. Account_Nos & Branch_Name can be repeated for a give Date_Checked
Branch_Name can be different depending on the customer visiting any branch
This is the reason i am not able to give combobox cause every transaction will be unpredictable.

Hope the above explanation is clear.
 

June7

AWF VIP
Local time
Today, 02:04
Joined
Mar 9, 2014
Messages
5,425
Let me ask again.

User enters data into subfrm_Verified and you want to run DLookup to see if there is a match in Query3?

I just discovered that RefNos in that query is just a formatted version of SrNo which is a calculated value returned by a function. I think something you said earlier just sunk in. This is why RefNos in Table3 has no values.

Okay, if you want to do DLookup on query, then modify DLookup to reference the query instead of table.

Put code in form BeforeUpdate event.
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
If Not IsNull(DLookup("OutwardDate", "Query3", "RefNos='" & Me.Ref_Nos & "' AND BranchName='" & Me.Branch_Name & "' AND AccountNos='" & Me.Account_Nos & "'")) Then
    Me.Status = "Match"
Else
    Cancel = True
    MsgBox "No Match"
    Me.Undo
    Me.Date_Checked.SetFocus
End If
End Sub
 
Last edited:

June7

AWF VIP
Local time
Today, 02:04
Joined
Mar 9, 2014
Messages
5,425
Looks like I edited my post after you posted yours. Read again.
 

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
Thanks June, yes I did not see your post after edit.
Will check and update you shortly
 

lookforsmt

Registered User.
Local time
Today, 14:04
Joined
Dec 26, 2011
Messages
672
Thank you June7 for the time and the code. this is what i wanted. works perfectly. i was struggling to find the code for long time.

Thank you once again for your support
 

Users who are viewing this thread

Top Bottom