Correcting Entered Value and Storing It in Same Table

[adamrf]

I have a form where users can enter the Claim Numbers of New lawsuits....

Unfortunatley they enter the claim numbers in a very short hand method and use dashes and leave out Alpha characters and all kinds of needed characters....The claim number is the only way I can pull all the details of the claim and there are about 400,000 of them....

I have built the form and built a query behind the scenes that can take the entered claim number and run through several IIFs to get the right claim number.......(I guess I could do it with some IF then Else statements in VB but I like the query approach since I use the logic in my reports)

I want it to work so that they type in the claim number and behind the scenes the query is run to get the correct claim number and then stored in the same table...

They enter the claims number After update I requery a list box and I see the corrected claim number in the list box.....but I dont want the user to have to select the value from the list box I just want it to automically paste it in the table.....

 

[Alexandre]

To begin with, not a vital point but an observation: On principle it would be better to go for pre-processing (VB procedure using IF..) then eventually execute a query. This is because when you introduce functions into queries (with a few exceptions Like Sum), you prevent Acces from properly optimizing the query once for all. ie it will run from slower to much slower than it should.

Now, I am at loss with what you are willing to do exactly
1. If your point it to have users register claim numbers in a proper format, use an input mask
2. If it is that you want users to enter a claim number just to retrieve records (as a query criteria), why not just let them select from an unbound listox of existing claim numbers?

Yo seem to be talking about both things at the same time?