Scanning and Populating the fields in the form through VBA

[rajput]

Hi,

I have this project where I need to scan the MRP zone on the passport to my form. Let me first explain what is MRP zone. MRP is know as Machine Readable Passport Zone which you usally find on the first page of US Passport right under the Photographs and it looks like this

P<USAERIKSSON<<ANNA<MARIA<<<<<<<<<<<<<<<<<<<
0550667081USA6908061F9406236ZE184226B<<<<<14

1st Line
So P stands for Passport and Contry code is USA and then last name Eriksson and first name Anna and middle name Maria.

2nd line
Passport number and Date of birth and sex and so on.

Now I am in process of ordering a keybord that has a MRP reader on the top( http://www.accesskeyboards.co.uk/ATB420 Reader.htm ) from this company in UK. You can find the specification on the keyboard from the above link.


So as soon as I scan/swipe it, it will give me information in the following format.

FIRST NAME, LAST NAME, DOB, SEX, PASSPORT NUMBER.

SO ONCE I HAVE THE INFORMATION IN COMMA FORMAT, HOW DO I GET THIS DATA INTO MY RESPECTIVE FIELDS INSTANTLY, AS SOON AS I SWIPE THE PASSPORT.

My field name in the table are

LastName
FirstName
BirthDate
PassportNo
MaleFemale

String of characters that will be reterieved from the keyboard will allready be broken down with commas and decoded. They just have to be sent to the right fields.

For example we recived after swiping the passport --->

DOE, JOHN, 201199, 123456789, F

After we get this, I want the "DOE" to go to my LASTNAME field.

"JOHN" to my "FIRSTNAME" field on the form

"201199" to my "BIRTHDATE" field

"123456789" to my "PASSPORTNO" field

"F" to my "MALEFEMALE" field.



--------------

Neil has Suggested to use VBA code.

The keyboard is reading this data. I presume that this will appear as a string of characters in the keyboard buffer. So you need to create a form with a text box on it. Make sure the cursor is in the text box when you scan the passport so that the string appears in this box.

Next you will need to chop up this string of characters to reterieve your data. I would use some VBA code to find the position of the first comma and read the characters up to this position which will be the first name which you then assign to your field, FirstName, and so on.

If the data is not presented as a string of characters separated by commas, what does it do?

------------------------------

I have no clue how to do this




Thanks.

Rajput

 

[Bat17]

I don't know the scanner but assuming that it dumps the data in a field as a comma delimited list it is easy to split up using the Split() function.
The split() function takes a list and splits it into an array, you can then loop through the array and paste the bits into the relevant fields.
The main problem will be the event to trigger, this which I imagine will come as part of the scanner software.

Peter

 

[Pat Hartman]

Why did you start a new thread? Having multiple threads going will NOT help you get answers and will only annoy the people trying to help you. Why should they spend their time with your problem when you may have another answer floating around already under a different thread.

 

[neileg]

My fault, Pat. I suggested rajput should post in the VBA forum since his problem was about spliting a string of data. I didn't expect him to quote verbatim from his other posting however!

 

[neileg]

Generosity gets me every time!

Here's a sample that gives you an idea. Open the form, enter your string of data into the input box, tab to the next control and your boxes will be populated.

I've never used Split() before, or arrays so I've learned a lot this afternoon!

It's in Access 2000 format.

 

[rajput]

I am so sorry Pat, will not happen again.

And thank you very much Neileg for that sample. I think it will work, however I am keeping my fingers cross as I am still awating the shipment of the keyboard. Will bother you guys again, if i have further questions.

Thank you

Rajput

 

[rajput]

Dear Neileg, as you suggested in your sample database, I copied it to my database with exact field names but it is giving me Microsoft Visual Basic error window saying "Compile Error: Variable not defined" and it is highligting the "Private Sub txtInput_AfterUpdate()" with yellow and graying the "txtFirstName" field.

And also I was trying to change the Birthdate field in your sample database from text to Date/Time and it is giving me a Run-Time Error saying "the value you entered isn't valid for this field".

Thank you again for your kind help.

Rav

 

[KeithIT]

raj,
I didn't get the compile error, double check that your text box for First Name is still named txtFirstName. If not, is your code exactly the same as Neileg's?

Secondly, did you enter your birthdate as a string (i.e.: 01021983) or as a date (i.e.: 01/02/1983)? This definately matters. If you change the field type to date/time then you must input a date/time formatted value. Otherwise, you'll have to convert from the string to the date/time format before updating the field.

 

[rajput]

Thanks Keith, my bad I totaly forgot about the txt part. I did not have the txt in my database. This part is working fine now. Date part also worked fine with inputing the slashes. I have to check if the keyboard program can input the slashesh. But if not then how can I convert a string to date/time format?

In my database the BirthDate field is Date/Time

Thank you
Rav

 

[Pat Hartman]

To convert 01021983 to a date that Access will recognize -
CDate(Left(TextDate, 2) & "/" & Mid(TextDate,3,2) & "/" & Right(TextDate,4))

 

[rajput]

Thank you Pat, However I am a novice in this field. Can you tell me where to put/insert this code?

Thanks

Rav

 

[Pat Hartman]

CDate(Left(TextDate, 2) & "/" & Mid(TextDate,3,2) & "/" & Right(TextDate,4)) is an expression. It can go wherever an expression can go.

In a query:
Select fld1, fld2, CDate(Left(TextDate, 2) & "/" & Mid(TextDate,3,2) & "/" & Right(TextDate,4)) As MyDate, fld4
From YourTable;

In the ControlSource of a control on a form or report:
=CDate(Left(TextDate, 2) & "/" & Mid(TextDate,3,2) & "/" & Right(TextDate,4))

In code:
SomeField = CDate(Left(TextDate, 2) & "/" & Mid(TextDate,3,2) & "/" & Right(TextDate,4))

 

[rajput]

Thanks Pat for the above explanation, but how would I convert the date I am getting from the Unbound text box(txtInput) in my form. The unbound box is not only getting the date it is also getting the other data like in example listed below:

_______________
DOE.JOHN.221183.PT45454545.M.UK1

LastName.FirstName.BirthDate.PassportNo.MaleFemale.Nationality
_______________


All other data are going to its respective fields except the Date(221183). it is giving me an error "THE VALUE YOU ENTERED ISN'T VALID FOR THIS FIELD". however if i am putting the salashes like this 22/11/83 it is working fine. But in my case the keyboard wedge will ONLY output the date in this 221183 format. My BirthDate field is a Date/Time field. I hope I am making sense here.

I am using the code below to send the data to appropriate fields

_____________
Private Sub txtInput_AfterUpdate()
Dim strArry
strArry = Split(txtInput, ".")
LastName = strArry(0)
FirstName = strArry(1)
Birthdate = strArry(2)
PassportNo = strArry(3)
MaleFemale = strArry(4)
Nationality = strArry(5)

End Sub
_____________

Thanks again for your help.

Rav

 

[neileg]

Though I haven't tested it, this should work:

Private Sub txtInput_AfterUpdate()
Dim strArry
strArry = Split(txtInput, ".")
LastName = strArry(0)
FirstName = strArry(1)
Birthdate = CDate(Left(strArry(2), 2) & "/" & Mid(strArry(2),3,2) & "/" & Right(strArry(2),4))
PassportNo = strArry(3)
MaleFemale = strArry(4)
Nationality = strArry(5)

End Sub

 

[rajput]

Thanks Neil, it worked beautifully. Thank you.

I would also like to thank Pat, Peter, Keith for there help.

Rajput

 

[DjtechStephnix]

Thank you for the information.