Help with Insert query and Validation

[June7]

So you have to not only check that MICR exists in tbl_Master_MICR, you also have to make sure it is not a duplicate for the location. That will likely mean two DLookup.

If MICR is not already in tbl_Master_MICR do you want to allow user to create new record in that table? I think I stated earlier this is what the NotInList event of combobox is for but won't work in your situation so this will also be more complicated.

 

[lookforsmt]

thanks for the reply June7

If MICR is not already in tbl_Master_MICR do you want to allow user to create new record in that table? I think I stated earlier this is what the NotInList event of combobox is for but won't work in your situation so this will also be more complicated.

No, i don't want user to create new record, if its not there then it should not be allowed.

 

[June7]

I'm getting there but still a bit confused about relationships and how Lodged_Date bears on this. Can each location have multiple Lodged_Date? Can each MICR have multiple Lodged_Date? What exactly does Lodged_Date represent?

 

[lookforsmt]

HI! June7, i am trying to explain you a bit more. Hope this answers your doubt.

Scope of this project
UserA completes tbl_Master_MICR dataEntry for a particular Lodged_Date and handsover the docs to UserB
- Only one date will be selected for each MICR at a time
UserB acknowledges the docs by scanning MICR in tbl_linkchqbrcdToMICR_NewMICR
- UserB is responsible for safekeeping these docs.
- He selects the 1st available Loc_Code and
- Enters the date which should be matching with LodgedDate
- If the dates are not a combination of (MICR+Lodged_Date)then should not allow UserB to acknowledge.
- Also, if UserB scans the same MICR twice in the subform it should prompt the user for duplicate

thanks

 

[June7]

So what makes a location 'available'? If it already has a Lodged_Date it is not available? Maybe open form to only 'available' records? If the Lodged_Date must match a record in Master - which record? They all show the same date now. Will there be other dates? Need to do data entry validation for Lodged_Date? Use a combobox for selection of valid date. So if the Lodged_Date must already be associated with MICR code, can't really know what correct date is until the MICR code is calculated. Will MICR code be unique in Master_MICR or can it be repeated with a different Lodged_Date?

Still not clear to me what is considered a 'duplicate' record in NewMICR. If a MICR code must be unique then the date and location are not considered. If MICR code can be repeated with different date and/or location then duplication would occur when the compound index is repeated.

Similarly, will Loc_Code be unique in Master_Location? Will never be more than these 30 records or will this grow?

 

[lookforsmt]

HI! June7
i have tried to answer your questions

So what makes a location 'available'? If it already has a Lodged_Date it is not available? Maybe open form to only 'available' records? If the Lodged_Date must match a record in Master - which record? They all show the same date now. Will there be other dates? Need to do data entry validation for Lodged_Date? Use a combobox for selection of valid date. So if the Lodged_Date must already be associated with MICR code, can't really know what correct date is until the MICR code is calculated.

tbl_Master_Location already has all the locations. UserB will acknowledge the docs by selecting anyone of the location but the date will be the same date UserA has selected. UserB will select another location when he reaches maximum number of docs that can accommodate in that location, e.g. 700 docs, above that he selects another location but the date will be same. So here there will be two locations for one date.

Will MICR code be unique in Master_MICR or can it be repeated with a different Lodged_Date?

No it will unique for one lodged date. but if you see the 33 digit number has the last 6 digits as date which will change if it received again for that date.
MICR. Below example
6286848020201257100540010120619
6286848020201257100540010150719

Still not clear to me what is considered a 'duplicate' record in NewMICR. If a MICR code must be unique then the date and location are not considered. If MICR code can be repeated with different date and/or location then duplication would occur when the compound index is repeated

UserB accidentally scans the same MICR again with the same date. There is already Indexed to Yes, noDuplicates but to stop this through vba is what i am looking. If UserB scans with another date it will not match with tbl_Master_MICR, so one he gets these mis-match he can rectify or simple delete that row.

Similarly, will Loc_Code be unique in Master_Location? Will never be more than these 30 records or will this grow?

Yes, it will grow depending on the filing area. Now its one chub, tomorrow it may more.

 

[lookforsmt]

i have uploaded my latest version
i know you have told me to keep the "Not", but i am able to achieve somewhat.
what is the importance of keeping the Not

Open frm_Scan_MICR
Try to copy one MICR from the previous row it pop-up msg but it does not stop the userB if there is duplicate and then when it reaches the next row it throws a error mentioned in post#16

Also it pop-up msg if there is any mis-match. Try copying the below MICR, it will pop up msg and status will change to UnMatch.

e<000150:703320117:<0793177758<r0

If there are 2 popup msgs, first popup msg for duplicate MICR_code (not MICR) it should stop the user from moving forward.
and second popup msg for mis-match which is already now exist in the form.

 

[lookforsmt]

HI! June7
i managed to get the two msgbox which does not allow me to scan duplicate MICR and accepts mis-match with status as "UnMatch". kindly have look at updated file in post#27

 

[June7]

Don't know purpose of Status field. Does this verify match with Master_MICR? When would there not be a 'match'?

See if this does what you want:

Private Sub MICR_code_BeforeUpdate(Cancel As Integer)
    Dim strMICR As String
    strMICR = ReplaceChars(Me.MICR_code) & Format(Me.Parent.Lodged_Date, "ddmmyy")
    If IsNull(DLookup("MICR", "tbl_Master_MICR", "MICR = '" & strMICR & "'")) Then
        Me.Undo
        MsgBox "Not a valid MICR. Enter different scan or enter date"
    ElseIf Not IsNull(DLookup("MICR_code", "tbl_linkchqbrcdToMICR_NewMICR", "MICR = '" & strMICR & "' AND Location_L='" & Me.Parent.Loc_Code & "'")) Then
        Me.Undo
        'Message box warning of duplication
        MsgBox "Cheque MICR " & strMICR & "" _
            & vbCr & " " _
            & vbCr & "Either Duplicate Scanned." _
            & vbCr & "                Or " _
            & vbCr & "Cheque MICR Incorrect " _
            & vbCr & vbCr & "Kindly check and Re-scan correct MICR.", vbInformation _
            , "Incorrect MICR Information"
    Else
        Me.Status = "Match"
    End If
End Sub

How will userB select another location? Form navigation is disabled. So far, don't see need for Lodged_Date in NewMICR. Date is built into MICR and I think you said a location can have only 1 date.

 

[lookforsmt]

Thanks June7 for the support, i have got an idea of using the code and amended to meet my requirement.

i have just one huddle to cross in my project

i want UserB to limit not more than 500 records in one location. It can be less but it cannot be 501.
The maximum value will be stored in tbl_Master_Location in field "Loc_Max_qty" (I have added this field in the existing table now)

If my tbl_Master_MICR has 900 records.
The idea is to stop excess docs filed in container, anything over 500 gets assigned to next location
E-C01-D1-0001 500
E-C01-D1-0002 400

any suggestions

 

[June7]

Do a count of records and compare to 500. Maybe this should be in BeforeUpdate of Location textbox on main form. Use DCount(), something like:

If DCount("*", "tbl_linkchqbrcdToMICR", "Location_L='" & Me.Loc_Code & "'") = 500 Then
MsgBox "something"
Me.Undo
…

 

[lookforsmt]

Hi! June7, i did find somthing on google and applied it here.

but the problem is it is on Main form, i want this to be on sub-form. i tried but not able to get success.

Is there a way, instead of hard coding in the code, i wanted to look in field "Loc_Max_qty" in tbl_Master_Location.
i have attached new version

below is the code on mainform

Private Sub Form_Current()
    Dim intMaxNumRecs As Integer
 
    intMaxNumRecs = 2 'Max Number of Records to Allow
'   intMaxNumRecs = Forms![subfrmchqbrcd_MICR_NewMICR].Form.Loc_Max_qty

   intMaxNumRecs = Forms![frm_scan_MICR].Form.Loc_Max_qty
 
    If Me.NewRecord Then
        With Me.RecordsetClone
            If .RecordCount > 0 Then
                .MoveLast:  .MoveFirst
                If .RecordCount >= intMaxNumRecs Then
                    MsgBox "Can't add more than " & intMaxNumRecs & " records in the Sub Form!"
                    .MoveLast
                    Me.Bookmark = .Bookmark
                End If
            End If
        End With
    End If
End Sub

thanks

 

[June7]

RecordsetClone can work as well as Count (in a textbox expression) or DCount. But as is the RecordsetClone is main form, not subform dataset.

Yes, you can use the textbox to reference maximum allowed but need value in record first because Null causes error. Or handle the Null:

Nz(Forms![frm_scan_MICR].Form.Loc_Max_qty, 1)

Form shows max of 2 yet there are 3 records for same location.

Need to set subform Master/Child Links.

 

[lookforsmt]

Hi! June7, i tried your suggestion but it still accepts more than 2 records. but if i click on last record from navigation it gives me popup msg of more than 2 records.

i need popup when the userB enters MICR_code and moves to next record it should popup msg

 

[June7]

I moved code to subform Current event. As soon as I click into new record row for location 0001, I get message cannot add another record.

 

[lookforsmt]

Thanks June7, I will try that out and share with u later

 

[lookforsmt]

Thanks June7 for all the support and time. its working perfectly now.
i wanted to close this thread as solved.
Actually yes, cause my project objective is accomplished.

But i just realized that when userB scan MICR_code the items which does not match, on the main form the field "MICR_v" does not show any data cause it is not matching with filed "MICR_m".

Is there a way to match the MICR_code from the below 5 combinations
1 & 3 or 2 & 3 or 1 & 2 or All 3 match or No match any of these combinations and display the data from tbl_Master_MICR 0001357033201170793177758120619.

where:
1 = 000135
2 = 703320117
3 = 0793177758120619

e<000135:703320117:<0793177758<r0 result 0001357033201170793177758120619

Data in tbl_Master_MICR is 0001357033201170793177758120619

match with bold and display the details in field MICR_v

0001357033201180793177758120619

0001397033201170793177758120619

0001357033201170793177757120619

is there a function to be written or it should be through dlookup

 

[June7]

Most anything can be done with enough code. But I am not understanding this issue.

Why would MICR_v even need to show any data if there is no match? I am not even sure why that textbox is needed to begin with. It's not referenced in code.

 

[lookforsmt]

HI! June7, as mentioned in the attached images, i have shown two screen comparison
A) Match -
B) UnMatch

For match userB gets real-time information what is mentioned in the tbl_Master_MICR, but for unmatch, he needs to stop and open tbl_Master_MICR what was captured by userA at that time and then needs to correct it. Here possibilities, either userA or userB may be wrong that can be verified by userB with the document he his holding with the MICR_v display.

So i was looking for a combination to display this data here.
hope i am able to clarify this
thanks

 

[June7]

I am not understanding work flow. Much of this thread was concerned with code that would prevent a new record in NewMICR if there was no corresponding MICR in Master. So how can there be an UnMatch?