Help with different Dlookup with 3 criteria (1 Viewer)

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
HI! This has been raised numerous time with solutions, but i am still confused on how to apply to my db.

I am looking to validate my input based on 3 criteria's. I one of them don't match the status flag should be "UnMatch"

To give brief background of my db:
my db has "tbl_Master" which will have all the valid records then,
db has "tbl_temp_Validate" which will be used to capture datas (3 Fields):
MICR_Scan
CreditAC
Amount

In form "frm_ChqScanValidation" linked to subform "subfrm_tempValidation" i will be capturing these 3 fields.
On the AfterUpdate event it generates data in field MICR after i capture in field MICR_Scan. Currently it gives me popup msg "Incorrect MICR Scanned" but the details are correctly mentioned in "tbl_Master". Secondly i dont know how to validate 2nd and 3rd fields.

Any suggestions pls help.
i am attaching my db in case above is not clear.
There is a Sample table which is used for capturing data. I copy the MICR_Scan; CrediAC & Amount respectively for capturing data in form "frm_ChqScanValidation"
 

Attachments

  • Validate1.3.accdb
    592 KB · Views: 100

theDBguy

I’m here to help
Staff member
Local time
Today, 08:59
Joined
Oct 29, 2018
Messages
21,358
Hi. When checking for duplicates, I tend to use DCount() although DLookup() should work as well.


Try changing this line:
Code:
    If Not IsNull(DLookup("MICR_Scan", "tbl_temp_Validate", "MICR = '" & strMICR1 & "'")) Then
    Me.Undo
To this:
Code:
    If Not IsNull(DLookup("MICR_Scan", "tbl_temp_Validate", "[color=red]MICR_Scan[/color] = '" & strMICR1 & "'")) Then
    Me.Undo
 
Last edited:

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
Thanks theDBguy

i have changed the Dlookup code and it now pops for duplicate MICR_Scan.
How do i validate CreditAC & Amount field next.? I am not sure where to insert these in the existing code.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
19,169
I only did the validation on the Form.
 

Attachments

  • Validate1.3.accdb
    672 KB · Views: 91

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
Thanks Arnlegp.

I tested it and it's fine. However is it possible to prompt popup 1st if Micr scan is mismatch. If not then it moves to Credit AC. Again if it is incorrect to prompt user, if not then move to Amount and then update Status as Match or Unmatch and set focus to next row MicrScan for next scanning.

Now it stops me from moving next row if its Unmatch

Sorry sending this from my nobile., if not clear.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
19,169
try this one
 

Attachments

  • Validate1.3.accdb
    612 KB · Views: 89

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
Hi Arnlegp, after Micr Scan it displays Match if its matching. Can this be stopped.
Also if it's not matching popup msg incorrect Micr, but it does not allow me to move forward to CreditAC. It should popup but allow me to move forward to CreditAC and Amount.

Can the status be updated just before moving to next line.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
19,169
please check the attached.
 

Attachments

  • Validate1.3.accdb
    864 KB · Views: 87

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
Thanks Arnelgp, it prompts popup incase of incorrect.
But it does not go to the next row for scanning next chq. The form freezes and when I hit x to close form it ask me
" You cant save the record at this time'
When I say yes it saves and then able to open the firm again to scan next one.

Sorry for this
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
19,169
again check
 

Attachments

  • Validate1.3.accdb
    1.1 MB · Views: 90

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
Thank you Arnelgp, its perfect what i wanted.

Can i request one more time.

Is is possible to update the Remark field based on the popup msg. If there is mis-match in more than one then field then update as Multiple Mis-Match, else the respective Mis-Match (MICR or CreditAC or Amount)

and

can i add date field to Main form to validate MICR_scan

Thanks i have updated the latest db Validate1.3d
 

Attachments

  • Validate1.3d.accdb
    612 KB · Views: 89

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
19,169
here check the remarks.
 

Attachments

  • Validate1.3d.accdb
    628 KB · Views: 99

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
Thank you Arnelgp, yes this is what i wanted to avoid user selecting the reject reason and if this could be automated.
Thank you very much.

just one final if i can add the date to the validation.
I am requesting this so i can use the code for my other db. I have my previous projects however each time the combination is different and confuses me. I will try and understand your code to apply in my future projects.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:59
Joined
May 7, 2009
Messages
19,169
use the subform's BeforeUpdate event (sorry no more load).

Private Sub Form_BeforeUpdate(Cancel As Integer)
Me.dateTextBoxName = Me.Parent!dateTextBoxName
End Sub
 

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
thank you Arnelgp. Will try the code.

Thank you once again for all the help and guidance.
 

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
thanks Arnelgp for your kind support. I have tweaked the code to fit my project.

Thanks I will close the thread now as solved
 

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
HI! Arnelgp,

i had to repost this message as i have another challenge.
It works fine except in one scenario where if the user scans a cheque for any other then the date mentioned in the main form, it still accepts.

I wanted the 4th criteria to check the date (PDC_dueDate) before accepting as Match, else throw a popup message as "Invalid Date" as the 5th Rejection.

i am not sure whether to add the before update on the PDC_dueDate in the subform or add the check on the before update on MICR_Scan.

I tried below code but i am getting error

Code:
    If DCount("1", "tbl_Master", "MICR_ndt = '" & strMIRC & "'" [COLOR="blue"]& PDC_dueDate[/COLOR] = strDate) < 1 Then
        
        Me.Status.Value = "UnMatch"
        Me.cboRemark = DLookup("RejectReason", "tbl_RejectReason", "SrNos = [COLOR="blue"][B]5[/B][/COLOR]")

I am getting popup message as Invalid Date and i have added 5th Reject reason in tbl_RejectReason

can you assist
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:59
Joined
Sep 21, 2011
Messages
14,044
Make the criteria a string in itself and then debug.print it.

Code:
strCriteria =  "MICR_ndt = '" & strMIRC & "'" & PDC_dueDate = strDate
Debug.Print strCriteria

If DCount("1", "tbl_Master", strCriteria) < 1 Then
        
        Me.Status.Value = "UnMatch"
        Me.cboRemark = DLookup("RejectReason", "tbl_RejectReason", "SrNos = 5")

That will show you your errors.
Dates are normally surrounded by #, so not sure why you have it as a string

HTH
 

lookforsmt

Registered User.
Local time
Today, 19:59
Joined
Dec 26, 2011
Messages
672
HI! Gasman,
i am not getting any error, but a popup msg Invalid Date with status "UnMatch". But the data is correct, so it should give me status as "Match"

in the immediate window i am getting as False

i have inserted the code you provided and below is my full code on MICR_Scan before update

Code:
Private Sub MICR_Scan_BeforeUpdate(Cancel As Integer)
    Dim strMIRC As String
[COLOR="Blue"]    Dim strDate As Date
    Dim strCriteria As String[/COLOR]
    
    intRemarks = 0
    
    strMIRC = Trim(ReplaceChars(Me.MICR_Scan) & "")

    
    If Len(strMIRC) < 1 Then
        MsgBox "Need to enter Cheque MIRC", vbExclamation + vbOKOnly
        Exit Sub
    End If
    
    If DCount("1", "tbl_temp_Validate", "MICR = '" & strMIRC & "'") > 0 Then
        'Me.Undo
        'Cancel = True

        'Message box warning of duplication
        MsgBox "Duplicate Cheque MICR and CreditAC already Scanned " & "" _
            & vbCr & Me.MICR_Scan, vbInformation _
            , " Duplicate MICR"
        Exit Sub
    End If
[COLOR="Red"]'    If DCount("1", "tbl_Master", "MICR_ndt = '" & strMIRC & "'") < 1 Then[/COLOR]
        
[COLOR="blue"]strCriteria = "MICR_ndt = '" & strMIRC & "'" & PDC_dueDate = strDate
Debug.Print strCriteria[/COLOR]

[COLOR="blue"]    If DCount("1", "tbl_Master", strCriteria) < 1 Then[/COLOR]
        
        
        Me.Status.Value = "UnMatch"
        Me.cboRemark = DLookup("RejectReason", "tbl_RejectReason", "SrNos = 5")
        intRemarks = intRemarks + 1
        'Cancel = True
        'Undo duplicate entry
        '         Me.Undo
        MsgBox "Incorrect MICR Scanned " & "" _
            & vbCr & Me.MICR_Scan & " " _
            & vbCr & "Kindly check and correct Scanned MICR.", vbInformation _
            , " MICR Information"
    End If
End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 15:59
Joined
Sep 21, 2011
Messages
14,044
What does the debug.print show?

That will show you your error.? As I said dates need to be surrounded by # most times. This being one of them.?

From an immediate window
Code:
tt=12/01/2016
strcriteria="TransactionDate < #" & tt & "#"
? strcriteria
TransactionDate < #01/12/2016#

? dcount("*","Transactions", strcriteria)
 7

HTH
 

Users who are viewing this thread

Top Bottom