Parse data from magstripe card reader to form fields - delimiter i

[RobK]

I want to read name badge magstripe cards into an Access form with a keyboard
emulating swipe reader. There are 78 characters in each of the three lines
on a card. The fields are parsed with a "^" character. "%" begins the first
line of data, "?" marks the end of a mag data line, "@" begins the second
line of data, and "&" begins the 3rd line on the card. A 3-line mag record
would read like:

%UNIQUE_ID^LASTNAME^[email protected]?&STFIELD?

By default, all characters read into the first Access field. I don't expect
someone to do this for me, and I'm willing to learn, but would appreciate
being pointed in the right direction. Need to interpret "^" as a tab, and
ignore the other control characters. Thanks.

 

[John Nurick]

I'd allow the reader to plonk the entire string into the first textbox
and then write VBA code in the textbox's Exit event procedure to parse
it and put the pieces where you want them. (Otherwise, you'd need to
intercept the "keystrokes" generated by the card reader using the
textbox's KeyPress event: this is possible, but it would also intercept
keystrokes from the real keyboard, with ample scope for confusion.)

Use Instr() to check whether there's a ^ in the string, so it doesn't
get parsed every time the field is exited from.

The Replace() function will get rid of the line-delimiting characters,
and Split() can split it into fields on the ^ characters.

Post back if this isn't enough to get you going.

 

[RobK]

John, thanks. I've seen many of your other posts, and appreciate your
helpfulness. Now, I have experimented/self-taught a lot with queries &
reports & the sql behind them, but have no experience with VBA. So, could
you point me in a direction where I could learn enough about VBA to do this?
I wouldn't know where to start, but do want to learn. I need to have this
working by early next week, but am willing to take the time to learn. So,
can you "teach a man to fish..."? Thanks.

 

[John Nurick]

Hi Rob,

I'm not the best person to give advice on learning VBA because I can't
remember how I learnt it and it was my third or fourth programming
language anyway. Almost certainly you'll find something helpful at Jeff
Conrad's site here:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

and/or in a good bookshop.

Here's a little procedure that seems to do the sort of thing you need.
It assumes that your first textbox is called txtA and that there are two
others called txtB and txtC. Obviously you need to replace these with
the actual names of your textboxes (and add lines of code near the end
if you have more than three fields in your mag stripe data.

Private Sub txtA_Exit(Cancel As Integer)
Dim S As String
Dim arItems As Variant

'Use Nz() to handle situation where textbox is empty
'(i.e. its value is Null)
S = Nz(Me.txtA.Value, "")

'We don't want to do anything unless the value has come
'from the mag reader, so we check that it begins with %
'and contains a ^
If Left(S, 1) = "%" And InStr(S, "^") > 0 Then

'Get rid of the characters that mark the mag lines
'Maybe you need to replace these with spaces rather
'than with "" (i.e. nothing)
S = Replace(S, "%", "")
S = Replace(S, "@", "")
S = Replace(S, "&", "")
S = Replace(S, "?", "")

'Split the "cleaned" string on the ^s
arItems = Split(S, "^")

'Put the first field in the current textbox
Me.txtA.Value = arItems(0)
'and other fields where they belong
Me.txtB.Value = arItems(1)
Me.txtC.Value = arItems(2)
End If
End Sub

 

[RobK]

Thanks, John!
Rob

Hi Rob,

I'm not the best person to give advice on learning VBA because I can't
remember how I learnt it and it was my third or fourth programming
language anyway. Almost certainly you'll find something helpful at Jeff
Conrad's site here:

http://home.bendbroadband.com/conradsystems/accessjunkie/resources.html

and/or in a good bookshop.

Here's a little procedure that seems to do the sort of thing you need.
It assumes that your first textbox is called txtA and that there are two
others called txtB and txtC. Obviously you need to replace these with
the actual names of your textboxes (and add lines of code near the end
if you have more than three fields in your mag stripe data.

Private Sub txtA_Exit(Cancel As Integer)
Dim S As String
Dim arItems As Variant

'Use Nz() to handle situation where textbox is empty
'(i.e. its value is Null)
S = Nz(Me.txtA.Value, "")

'We don't want to do anything unless the value has come
'from the mag reader, so we check that it begins with %
'and contains a ^
If Left(S, 1) = "%" And InStr(S, "^") > 0 Then

'Get rid of the characters that mark the mag lines
'Maybe you need to replace these with spaces rather
'than with "" (i.e. nothing)
S = Replace(S, "%", "")
S = Replace(S, "@", "")
S = Replace(S, "&", "")
S = Replace(S, "?", "")

'Split the "cleaned" string on the ^s
arItems = Split(S, "^")

'Put the first field in the current textbox
Me.txtA.Value = arItems(0)
'and other fields where they belong
Me.txtB.Value = arItems(1)
Me.txtC.Value = arItems(2)
End If
End Sub