Help with Insert query and Validation

[lookforsmt]

yes you are right, initially i mentioned that unmatch should be stopped from entering. But then how would i add the unmatch in the tbl_link...
The unmatch is due to some characters are not read by the scanner, so the user needs to correct them before entering.
The challenge is how will userB see these details if it does not get populated on the main form "frm_Scan_MICR"

So my post#37 initiated.
Thanks

 

[June7]

MICR_v uses DLookup to pull value from table. If value doesn't exist, then there is nothing to pull.

If scan is not valid and you want user to see the invalid input, then use code to set UNBOUND textbox to display this input.

How can a scanner not read some characters? This is actually possible?

 

[lookforsmt]

HI!
there are times when the scanner does not read correct MICR and this is happens quite regularly.

If scan is not valid and you want user to see the invalid input, then use code to set UNBOUND textbox to display this input.

how can i do this.

 

[June7]

So probably in BeforeUpdate event:

Me.Parent.MICR_v = Me.MICR_code

Also in Current event because an UNBOUND control will hold value when navigating to another record.

 

[lookforsmt]

HI! June7

i have tried to split the MICR_code as MICR_1; MICR_2 & MICR_3 from post#37

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

I have attached new version, is there a way to display in field MICR_v when there is match of split MICR_1;2 or 3 as mentioned above

 

[June7]

You need to parse the saved MICR codes and recombine and see if any combination matches the current scan? I see you now have segments saved into table. So now you need to do 3 more DLookups() to see if current scan matches any of these combinations?

What do you want to happen if one of these partial matches is found?

What if there are multiple possible matches? Two records have the same MICR_2. A scan that matches MICR_2 will always return first record.

 

[lookforsmt]

i am trying to populate the possible match data on the main form, so the userB can look at the data and correct the MICR instead of closing the form and then going to tbl_Master_MICR to view the data and then correct it back on the main form.

i did few changes to the form and brought list field, is this good way?
the matches are based on like used in query.

you mentioned of dlookup, can you assist with the code and where do i put them.

i have updated latest version
kindly assist as i have facing challenges the move ahead.
Pls feel free to suggest any changes

 

[June7]

If a scan misses some characters, how can you be sure Replace() functions work? Consistency of structure is critical in string manipulation.

Yes, I was thinking of a listbox and maybe DLookups not needed. However, not understanding logic of the listbox RowSource filter criteria.

Verify process:

1. user B scans MICR_code
2. compare scan with MASTER and if no match inform user to correct
3. show in listbox possible matches to scan of MICR from MASTER

I removed DLookup from MICR_v textbox. Then code populates this textbox in BeforeUpdate as well as requeries listbox.
Me.Parent.MICR_v = strMICR
Me.Parent.lstResults.Requery

I changed listbox RowSource to:

SELECT tbl_Master_MICR.MICR, tbl_Master_MICR.statusDesccription, Left([tbl_Master_MICR].[MICR],6) AS ChqNos, Mid([tbl_Master_MICR].[MICR],7,9) AS RoutNos, Mid([tbl_Master_MICR].[MICR],16,10) AS DebitNos FROM tbl_Master_MICR WHERE (((Left(tbl_Master_MICR.MICR,6))=Left([MICR_v],6))) Or (((Mid(tbl_Master_MICR.MICR,7,9))=Mid([MICR_v],7,9))) Or (((Mid(tbl_Master_MICR.MICR,16,10))=Mid([MICR_v],16,10))) ORDER BY tbl_Master_MICR.MICR;

 

[lookforsmt]

hI! june7, would it possible to share the updated db, i have done as you mentioned but am getting blank data in list box for unmatch and match items both.

Yes, I was thinking of a listbox and maybe DLookups not needed. However, not understanding logic of the listbox RowSource filter criteria.

Spilt in three, so the user can read them easily

 

[June7]

It will be blank until a scan is input. So, instead of using MICR_v, try MICR_m in the query. And at end of subform Current event, include: Me.Parent.lstResults.Requery

Now is that what you are looking for?

 

[lookforsmt]

Thanks June7 for all the help and support. I have done the changes as mentioned in your last post and it works absolutely fine as required.
I finally rest my case.
Will close this thread as Solved

i have updated the final version.

Thanks again