Help with Insert query and Validation

[lookforsmt]

Hi All,

I am having trouble finding a solution to my problem on the internet.

I am trying to input a text field(MICR_code) on form(frm_scan_MICR) which is linked to a table(tbl_linkchq....)
My input is via scanner. It picks up extra characters which through
function (ReplaceChars) gives me 25 digits clean data which is concatenated
with date (ddmmyy) to make it 31 digits in field MICR

i am trying to acheive 2 things here
a) inputted text to be inserted in another field and
b) the inserted text to be validated with data from another table

My challenge is to get the (a) accomplished via vba code.
As soon as i scan the MICR_code it should populate the MICR and insert it in that field.
The subform recordsource is query so it displays the MICR on the form but it does not write the details on the table.
If i keep the recordsource to table then the MICR field is blank which is required for validation to achieve (b)

i am attaching my db if anyone can suggest me a better way of doing this.
Thanks

 

[June7]

Don't use .Text property to save data. In Access use .Value which is default so don't even need to specify.

If you want to save the calculated MICR, don't need that UPDATE action (which doesn't make sense as is). Don't even need qry_replaceChars_MICR.

If scan takes place in subform then have code behind subform to populate its field.

Me!MICR = replacechars(Me.[MICR_code]) & "" & Format(Me.[LodgedDate],"ddmmyy")

Why does New Record show default values for Location_L and LodgedDate controls?

Can't use Me. qualifier in queries or textboxes, only in VBA behind form or report.

 

[lookforsmt]

Hi! June7, Thanks for the code

Why does New Record show default values for Location_L and LodgedDate controls?

I have linked the main form and subform by Master & Child link.

Is there a way to bring the main form fields from Loc_Code and Lodged_Date to the subform on update of MICR_Code in subform.

with the code provide in post#2 it does not update the 2nd half of the MICR_code since the date field LodgedDate is blank in subform

 

[June7]

I still don't understand why default values show on New Record row.

Why is LodgedDate in both tables?

Me!MICR = replacechars(Me.MICR_code) & "" & Format(Me.Parent.Lodged_Date,"ddmmyy")

Change subform RecordSource to table instead of query.

 

[lookforsmt]

I have changed the recordsource from query to table. when i scan MICR_code it displays the 25 digit without the 6digit ddmmyy.
But after i updated your code on afterupdate. it gives me 31 digit.

Also, i have removed the master & child link and instead on the afterupdate of MICR_code put the below code and it gives me the loc_code & lodgedDate

    Me!LodgedDate = Me.Parent.Lodged_Date
    Me.Location_L = Me.Parent.Loc_Code

This accomplish my task (a)

Now for task (b) the inserted text to be validated with data from another
table.

Why is LodgedDate in both tables?

i want the user to validate the MICR when he scans MICR_Code with the "tbl_Master_MICR" If it matches then it picks up the "statusDesccription" from this table and diplays on the main form in the statusDescription (This is blank currently in the form)

If this was on subform i could have displayed it on the mainform, but here it is one of the table("tbl_Master_MICR"). I have no idea how to bring it to the main form.

 

[lookforsmt]

HI! i am trying to use below code to populate another table value on the form with unbound field

=DLookUp("MICR","tbl_Master_MICR","MICR_m = '" & [MICR_v] & "'")

any suggestions if this is correct way
thanks

 

[June7]

Shouldn't form/subform synchronize on location?

Now I get why textboxes were showing default values. I usually don't display foreign key field so forgot the link was supplying value to those displayed textboxes.

Really should save LocID as foreign key, not text Loc_Code. Possibly same for MICRid and MICR.

I think a conventional approach would be to have a combobox instead of textbox to scan into. If value does not match existing item then user is offered option to enter new MICR record in tbl_Master_MICR. This is what the combobox NotInList event is for. Unfortunately, your scanned value does not conform to values saved in tbl_Master_MICR, hence complication for validating.

A lookup to tbl_Master_MICR can confirm if MICR code already in table but how does Lodged_Date bear on validation? Still not understanding why it is in both tables.

I don't see fields MICR_m and MICR_v in latest version.

 

[lookforsmt]

Thanks June7 for the reply

The table, "tbl_Master_MICR" is my main table where i will get data for the previous day work. User1 hands over the documents to another User2 for safe keeping these documents. Before he files them he needs to acknowledge that he has received all of these one by one which is done my scanning the MICR_code on the "tbl_linkchqbrcdToMICR_NewMICR" subform linked to main form "tbl_Master_Location"

my goal is to receive these documents and acknowledge all of them and also store these documents in a location which the user will capture on the main form for a particular date

step would be:
1) open main form- capture location and date
2) go to subform- capture MICR_code
3) behind the subform beforeupdate event validate the MICR_code is existing in tb_Master_MICR, if not popup msg, "MICR not matching"
4) behind the subform afterupdate event bring the Loc_code and Lodged_Date from main form to subform (This is completed)
5) display the captured MICR code on main form point (2) (This is complted)

i have one more text box on main form to display the MICR_code from main form so the user can see the match micr

thanks

 

[June7]

Okay, I found MICR_m. Expression in statusDesccription textbox (you have two 'c' in statusDesccription):

=DLookup("statusDesccription", "tbl_Master_MICR", "MICR = '" & [MICR_m] & "'")

Validation code can use DLookup.

If IsNull(DLookUp("MICR", "tbl_Master_MICR", "MICR = '" & Me.MICR & "'")) Then
'do something
End If

Or just test if statusDesccription textbox is still null:

If IsNull(Me.Parent.statusDesccription) Then
'do something
End If

 

[lookforsmt]

Thanks June7 for the code and help so far.

Expression in statusDesccription textbox (you have two 'c' in statusDesccription):

i realised only now there is spelling mistake, seems it is the way in the excel that gets downloaded.

I have put my vba knowledge and have the below code but i am sure its not the correct one. can you have look at it and let me know what is wrong.

from my post#8 i am trying to achieve the below

3) behind the subform beforeupdate event validate the MICR_code is existing in tb_Master_MICR, if not popup msg, "MICR not matching"

Private Sub MICR_code_AfterUpdate()
    
    Me!MICR = ReplaceChars(Me.MICR_code) & "" & Format(Me.Parent.Lodged_Date, "ddmmyy")
    
    Me!LodgedDate = Me.Parent.Lodged_Date
    Me.Location_L = Me.Parent.Loc_Code

End Sub

Private Sub MICR_code_BeforeUpdate(Cancel As Integer)
 
    Dim SID As String
    Dim stlinkcriteria As String

    SID = Me.MICR_code

    stlinkcriteria = "[MICR]=" & "'" & SID & "'"

    If IsNull(DLookup("MICR", "tbl_Master_MICR", "MICR = '" & Me.MICR & "'")) > 0 Then
    'do something
        
     'Undo duplicate entry
     Me.Undo
     'Message box warning of duplication
     MsgBox "Warning Cheque MICR " _
     & SID & " has already been Scanned." _
     & vbCr & vbCr & "Kindly check previous record and Re-scan correct MICR.", vbInformation _
     , "Duplicate MICR Information"
    End If

End Sub

i have attached my latest version
thanks

 

[June7]

Have you tested? Any issues?

If IsNull(DLookup("MICR", "tbl_Master_MICR", "MICR = '" & Me.MICR & "'")) > 0 Then

Don't use > 0. Cannot compare anything to Null. Null compared to anything will always evaluate to Null which is never True, even Null = Null cannot evaluate to True. In this case, the If Then will probably work but is bad practice.

Use:

If NOT IsNull(DLookup("MICR", "tbl_Master_MICR", "MICR = '" & Me.MICR & "'")) Then

 

[lookforsmt]

i tried your code, but it accepts all the MICR_code.
I am trying to stop, not matching codes with tbl_Master_MICR with field MICR. if not match, or duplicate MICR_code in tbl_linkchqbrcd.... it does not popup msgbox nor gives me an error.
can you help me with the code. thanks

 

[June7]

The lookup is on MICR and that value has not yet been calculated.

Private Sub MICR_code_BeforeUpdate(Cancel As Integer)
 
    Dim strMICR As String

    strMICR = ReplaceChars(Me.MICR_code) & "" & Format(Me.Parent.Lodged_Date, "ddmmyy")
    If Not IsNull(DLookup("MICR", "tbl_Master_MICR", "MICR = '" & strMICR & "'")) Then
         'Undo duplicate entry
         Me.Undo
         'Message box warning of duplication
         MsgBox "Warning Cheque MICR " _
            & Me.MICR_code & " has already been Scanned." _
            & vbCr & vbCr & "Kindly check previous record and Re-scan correct MICR.", vbInformation _
            , "Duplicate MICR Information"
    End If

End Sub

 

[lookforsmt]

i changed the below

If Not IsNull(DLookup("MICR", "tbl_Master_MICR", "MICR = '" & strMICR & "'")) Then

to

    If IsNull(DLookup("MICR", "tbl_Master_MICR", "MICR = '" & strMICR & "'")) Then

is the above correct or there is different way.

It now popups msg when the MICR is not matching in tbl_master.

still it accepts the same MICR_code twice when i scan in subform and on enter to next line give me duplicate error msg.
it should give me a second popup msg for duplicates also.

 

[June7]

I can't test scanner. I copy/paste a MICR_code into control. The code I posted worked.

 

[lookforsmt]

As mentioned in post#14, i have removed the "Not". It accepts records which are available in tbl_Master_MICR. This logic is correct.

Now, if i scan the same MICR twice in subform, it should also popup msg as duplicate, but it accepts and when the focus moves to next line, it gives me error msg. Refer to attach error snapshot

 

[June7]

No, use NOT as posted in my code.

As I said, I cannot test scanner. Typing or copy/paste MICR_code and procedure works.

 

[lookforsmt]

okay, i have used "Not" but it does not accept MICR which are present in tbl_Master_MCIR.

my goal is to scan MICR in subform and whichever matches should be accepted and not rejected.

It should not allow me to scan which are not available in tbl_Master_MICR
i hope i able to explain this

 

[June7]

Wait a minute. I thought matches should be rejected. Didn't you say you don't want duplicate? Okay, you don't want duplicate for same location and/or same date? Consider:

If Not IsNull(DLookup("MICR", "tbl_linkchqbrcdToMICR_NewMICR", "MICR = '" & strMICR & "' AND Location_L='" & Me.Parent.Loc_Code & "'")) Then

 

[lookforsmt]

Hi! June7, sorry for the confusion and not being clear in the beginning.

It stops the user now from scanning duplicate, but it still allows the user to scan MICR even if it is not avaialble in tbl_Master_MICR.

In your code tbl_Master_MICR is not mentioned anywhere. I tried to put this table but it does not give me the end result.

tbl_linkchqbrcdToMICR_NewMICR table after scan MICR will look for existing MICR in tbl_Master_MICR, if available will accept else popup msg MICR not existing.
Also, if the same MICR is scanned twice then also popup msg that MICR is already scanned.